The Report

---

Link

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

1) Ketty gives Eve a task to generate a report containing three columns: Name, Grade and Mark.
➡️ SELECT 이름, 등급, 점수

2) Ketty doesn't want the NAMES of those students who received a grade lower than 8.
➡️ 5) 8등급 미만은 이름 대신 NULL 출력 (IF문 사용)

3) The report must be in descending order by grade
➡️ 정렬조건1) 등급순 내림차순

4) If there is more than one student with the same grade (8-10) assigned to them, order those particular students by their name alphabetically.
➡️ 정렬조건2) 8-10등급 (이름O) -> 이름 알파벳순 정렬 -> ORDER BY s.name

5) Finally, if the grade is lower than 8, use "NULL" as their name and list them by their grades in descending order.
➡️ 8등급 미만은 이름을 NULL 로 출력

6) 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.
➡️ 정렬조건3) 1-7등급 (이름X,NULL) -> 점수 오름차순 정렬 -> ORDER BY s.marks

Sample Output

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

풀이

---

1. Students 테이블 Grades 테이블 JOIN ( ID - NAME - MARKS)
2. IF문으로 2번 조건 설정
3. 정렬조건1
4. 정렬조건2
5. 정렬조건3

My Answer

---

SELECT IF(g.grade < 8, NULL, s.name), 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, s.marks