인프런- 데이터 분석을 위한 고급 SQL: 섹션4 - JOIN 조건이 특이한 문제풀이: The Report(틀림)

인프런 강의 <데이터 분석을 위한 고급 SQL>을 듣고, 중요한 점을 정리한 글입니다.

문제

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.

풀이

• 나의 틀린 풀이과정

SELECT Students.id
      , Students.name
      , (CASE
            WHEN Students.marks BETWEEN 0 AND 9 THEN Grades.grade = 1
            WHEN Students.marks BETWEEN 10 AND 19 THEN Grades.grade = 2
            WHEN Students.marks BETWEEN 20 AND 29 THEN Grades.grade = 3
            WHEN Students.marks BETWEEN 30 AND 39 THEN Grades.grade = 4
            WHEN Students.marks BETWEEN 40 AND 49 THEN Grades.grade = 5
            WHEN Students.marks BETWEEN 50 AND 59 THEN Grades.grade = 6
            WHEN Students.marks BETWEEN 60 AND 69 THEN Grades.grade = 7
            WHEN Students.marks BETWEEN 70 AND 79 THEN Grades.grade = 8
            WHEN Students.marks BETWEEN 80 AND 89 THEN Grades.grade = 9
            WHEN Students.marks BETWEEN 90 AND 100 THEN Grades.grade = 10
         ELSE NULL
         END) AS results
FROM Students
     INNER JOIN results ON Students.marks = results.?

: CASE문과 INNER JOIN을 활용해서 풀면 된다고 생각했다. 그런데 Students 테이블의 Marks와 Grades 테이블에서 Min_Mark와 Max_Mark를 어떻게 JOIN해줘야 할지 구현하는 데서 막혔다.

• 선생님 풀이

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

배운점

• JOIN해줄 때, ON 조건에도 BETWEEN을 써줄 수 있다.
• CASE문 자체도 Alias로 표현해줄 수 있다.