[Hackerrank SQL] The Report

www.hackerrank.com/challenges/the-report/problem

The Report | HackerRank

Ketty gives Eve a task to generate a report containing three columns: Name, Grade and Mark. Ketty doesn't want the NAMES of those students who received a grade lower than 8. The report must be in descending order by grade -- i.e. higher grades are entered first. If there is more than one student with the same grade (8-10) assigned to them, order those particular students by their name alphabetically. Finally, if the grade is lower than 8, use "NULL" as their name and list them by their grades in descending order. If there is more than one student with the same grade (1-7) assigned to them, order those particular students by their marks in ascending order.

 

요구사항이 많다.

Students 테이블은 ID, NAME, MARKS / Grades 테이블은 Grade, Min_Mark, Max_Mark가 있다.

Students의 Marks를 통해 Grade 테이블의 점수 범위대로 Grade를 구해야 한다.

또한 Grade가 8 미만인 학생은 이름이 아닌 NULL을 출력해야 한다.

정렬은 1. Grade 내림차순, 2. 이름 오름차순, 3. Marks 오름차순이다.

 

SELECT IF(G.GRADE >= 8, S.NAME, 'NULL'), G.Grade, S.Marks
FROM STUDENTS AS S
INNER JOIN GRADES AS G
ON S.MARKS BETWEEN G.MIN_MARK AND G.MAX_MARK
ORDER BY G.GRADE DESC, S.NAME, S.MARKS

INNER JOIN 조건으로 범위를 사용할 수 있는 것을 배웠다. (BETWEEN)