[프로그래머스] 연간 평가점수에 해당하는 평가 등급 및 성과금 조회하기 (MySQL)

문제

https://school.programmers.co.kr/learn/courses/30/lessons/284528

SELECT 
    E.EMP_NO, 
    E.EMP_NAME,
    CASE
        WHEN G.AVG_SCORE >= '96' THEN 'S'
        WHEN G.AVG_SCORE >= '90' THEN 'A'
        WHEN G.AVG_SCORE >= '80' THEN 'B'
        ELSE 'C'
    END AS GRADE,
    CASE
        WHEN G.AVG_SCORE >= '96' THEN E.SAL * 0.20
        WHEN G.AVG_SCORE >= '90' THEN E.SAL * 0.15
        WHEN G.AVG_SCORE >= '80' THEN E.SAL * 0.10
        ELSE 0
    END AS BONUS
FROM HR_EMPLOYEES E
INNER JOIN (
    SELECT EMP_NO, AVG(SCORE) AS AVG_SCORE
    FROM HR_GRADE
    GROUP BY EMP_NO
) G ON E.EMP_NO = G.EMP_NO
;