SQL Interview — 2

HackerRank Questions

Problem 1:

Query the Name of any student in STUDENTS who scored higher than Marks. Order your output by the last three charactersof each name. If two or more students both have names ending in the same last three characters (i.e.: Bobby, Robby, etc.), secondary sort them by ascending ID.

Input Format

The STUDENTS table is described as follows:

The Name column only contains uppercase (A-Z) and lowercase (a-z) letters.

Sample Input

Sample Output

Ashley
Julia
Belvet

Explanation

Only Ashley, Julia, and Belvet have Marks > . If you look at the last three characters of each of their names, there are no duplicates and ‘ley’ < ‘lia’ < ‘vet’.

SELECT NAME FROM STUDENTS WHERE MARKS > 75 ORDER BY RIGHT(NAME, 3), ID ASC;

Problem 2 :

Given the CITY and COUNTRY tables, query the names of all the continents (COUNTRY.Continent) and their respective average city populations (CITY.Population) rounded down to the nearest integer.

Note: CITY.CountryCode and COUNTRY.Code are matching key columns.

Input Format

The CITY and COUNTRY tables are described as follows:

SELECT Country.Continent, FLOOR(AVG(City.Population))
FROM Country, City 
WHERE Country.Code = City.CountryCode 
GROUP BY Country.Continent ;

Problem 3 :

SQL Notes: Hackerrank The Report - Memogrocery

Çözüm Yolu 1 :

SELECT  IIF(G.GRADE<8, NULL, S.NAME),G.GRADE, S.MARKS FROM STUDENTS AS S JOIN GRADES AS G ON S.MARKS BETWEEN G.MIN_MARK AND G.MAX_MARK ORDER BY G.GRADE DESC, S.NAME,S.MARKS

Not : IF değil IFF kullanımına dikkat!

Çözüm Yolu 2:

select (case when G.GRADE<8  then NULL else S.NAME end),G.GRADE, S.MARKS FROM STUDENTS AS S JOIN GRADES AS G ON S.MARKS BETWEEN G.MIN_MARK AND G.MAX_MARK ORDER BY G.GRADE DESC, S.NAME,S.MARKS

Problem 4 :

Top Competitors | HackerRank

select S.hacker_id, H.name
from Submissions S
inner join Challenges C on C.challenge_id=S.challenge_id
inner join Difficulty D on D.difficulty_level = C.difficulty_level and D.score = S.score
inner join Hackers H on S.hacker_id = H.hacker_id 
group by S.hacker_id, H.name
having count(S.score) > 1
order by count(S.score) desc, S.hacker_id

https://github.com/shivkrthakur/HackerRankSolutions/tree/master/Practice/AllDomains/SpecializedSkills/SQL/MSSQL → İncele