[HackerRank] The Report Solution(풀이)

the report 문제 바로가기 

The Report | HackerRank

---

문제 

You are given two tables: Students and Grades. Students contains three columns ID, Name and Marks.

Grades contains the following data:

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.

Write a query to help Eve.

문제정리
1.  Students 테이블에서 점수와 Grades테이블의 등급표(?)를 가지고 학생들의 등급 매기기 
2. 결과값의 컬럼은 Name, Grade, Mark
3. 8등급 미만인 학생의 이름은 NULL로 지정
4. 결과값은 높은 grade부터 정렬, 같은 grade면 이름 알파벳 순 또는 이름이 NULL이면 mark를 오름차순 정렬

---

풀이 

SELECT CASE WHEN g.grade >= 8 THEN s.name ELSE NULL END AS Name,
               g.grade AS Grade,
               s.marks AS Mark
FROM students s
           INNER JOIN grades g ON s.marks BETWEEN g.min_mark AND g.max_mark
ORDER BY Grade DESC, Name, Mark

 

Expected Output

Britney 10 95
Heraldo 10 94
Julia 10 96
Kristeen 10 100
Stuart 10 99
Amina 9 89
Christene 9 88
Salma 9 81
Samantha 9 87
Scarlet 9 80
Vivek 9 84
Aamina 8 77
Belvet 8 78
Paige 8 74
Priya 8 76
Priyanka 8 77
NULL 7 64
NULL 7 66
NULL 6 55
NULL 4 34
NULL 3 24