171. The Report

문제

You are given two tables: Students and Grades
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.

데이터 이해

Students 테이블 : ID, Name, Mark
Grades 테이블 : Grade, Min_Mart, Max_Mark

marks가 개인 점수고
marks가 어떤 min_mark와 max_mark 사이에 있냐에 따라 grade가 결정된다

같은 점수를 받은 학생은 이름 알파벳순 (name asc)
Grade가 8 미만인 학생들의 Name은 원치 않는다(NULL로 대체)

grade로 desc
이때 같은 성적이라면 mark asc로 정렬

첫 코드

하드코딩 요소가 들어가면서 지저분하고 비효율적인 코드가 완성되었다
그냥 참고만 하라고 첨부한다
모범적인 코드는 아래 풀이 과정 내용 참고

SELECT
    CASE 
        WHEN (marks+10)/10 >= 8 THEN name
        ELSE null
    END as 'name'
    , CASE WHEN marks = 100 THEN 10 ELSE floor((marks+10)/10) END as 'grade'
    , marks
FROM
    students
ORDER BY
    grade desc
    , name
    , marks
;

풀이 과정

JOIN을 하는 데 연결해줄 key값이 마땅치 않다
또한 grades의 min_mark와 max_mark 사이에 marks가 포함되는 row만 그대로 가져오고 싶다

이를 해결해주는 것이 ON절에 조건을 거는 것이다

ON절에 조건 적용하기

내가 활용하던 기존 ON절은 A INNER JOIN B ON A.key = B.key 형태였다
즉 A와 B 테이블에 존재하는 key값이 '일치하는' 경우에만 가져오는 방식이었다

하지만 ON절에는 = 이외에도 다양한 수식을 사용하여 조건을 걸 수 있다.

이 문제처럼 'min_mark와 max_mark 사이에 marks 값이 해당하는 경우만 가져온다'는 목적이 있다면, 아래와 같이 코드를 작성할 수 있다.

SELECT
    *
FROM
    students s
    INNER JOIN grades g
        ON s.marks BETWEEN g.min_mark AND g.max_mark

나머지 조건들

여기까지 왔으면 나머지는 전에 자주 해본 내용들이므로 스무스하게 진행할 수 있다.
하나씩 정리해보자면
(1) grade가 8 미만인 학생들의 Name은 null로 받는다
(2) grade 내림차순 정렬, 같다면 이름 오름차순, 같다면 marks 오름차순 정렬

SELECT
    CASE 
        WHEN g.grade < 8 THEN null
        ELSE s.Name
    END as '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
    , Name
    , s.marks
;