[HACKERRANK] The Report

문제
(1) a report containing three columns: Name, Grade and Mark
(2) The report must be in descending order by grade
(3) If there is more than one student with the same grade (8-10) assigned to them, order those particular students by their name alphabetically.
(4) if the grade is lower than 8, use "NULL" as their name and list them by their grades in descending order.

[내가 작성한 답 - 정답!]
SELECT CASE WHEN G.GRADE <8 THEN NULL ELSE S.NAME END, G.GRADE, S.MARKS
FROM STUDENTS S
    INNER JOIN GRADES G ON S.MARKS BETWEEN G.MIN_MARK AND G.MAX_MARK 
ORDER BY G.GRADE DESC, S.NAME


[정답]
select CASE WHEN g.grade <8 THEN NULL ELSE s.name END AS name
        ,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, name, s.marks


[Solution]
1. INNER JOIN으로 grades 테이블과 조인
2. student 테이블의 mark는 grade 데이블 범위에 속함 
> inner join on 조건절도 between으로 표현 가능 
3. grade가 8이하면 이름이 NULL값으로 나오게 해줘 
> CASE WHEN 구문 
4. grade 내림차순, 이름은 오름차순, 동일 grade일 경우 marks로 오름차순