[SQL] The Report

✅ The Report

---

📝 문제

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.

Sample Input

Sample Output

Maria 10 99
Jane 9 81
Julia 9 88 
Scarlet 8 78
NULL 7 63
NULL 7 68

Note
Print "NULL" as the name if the grade is less than 8.

Explanation
Consider the following table with the grades assigned to the students:

So, the following students got 8, 9 or 10 grades:

• Maria (grade 10)
• Jane (grade 9)
• Julia (grade 9)
• Scarlet (grade 8)

---

💻 풀이

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, S.Name ASC;

---

💡 Idea

Students 테이블과 Grades 테이블에는 공통 컬럼이 존재하지 않으므로 BTWEEN A AND B를 이용하여 JOIN한다.

그 결과로 ID, Name, Marks, Grade, Min_Mark, Max_Mark 순서로 합쳐진 테이블이 나타난다.

위 결과 테이블을 이용하여 CASE 함수와 ORDER BY절로 조건을 처리하면 쉽게 해결된다.

---