연간 평가점수에 해당하는 평가 등급 및 성과금 조회하기

문제

코드

SELECT HG.EMP_NO, HE.EMP_NAME, (CASE
                                 WHEN AVG(HG.SCORE) >= 96 THEN 'S'
                                 WHEN AVG(HG.SCORE) < 96 AND AVG(HG.SCORE) >= 90 THEN 'A'
                                 WHEN AVG(HG.SCORE) < 90 AND AVG(HG.SCORE) >= 80 THEN 'B'
                                 ELSE 'C'
                                END) AS GRADE, 
                                (CASE
                                 WHEN AVG(HG.SCORE) >= 96 THEN HE.SAL*0.2
                                 WHEN AVG(HG.SCORE) < 96 AND AVG(HG.SCORE) >= 90 THEN HE.SAL*0.15
                                 WHEN AVG(HG.SCORE) < 90 AND AVG(HG.SCORE) >= 80 THEN HE.SAL*0.1
                                 ELSE 0
                                END) AS BONUS
                                
FROM HR_EMPLOYEES AS HE RIGHT JOIN HR_GRADE AS HG ON HE.EMP_NO = HG.EMP_NO
GROUP BY HG.EMP_NO, HE.EMP_NAME
ORDER BY HG.EMP_NO

 

출처: 프로그래머스 코딩 테스트 연습, https://school.programmers.co.kr/learn/courses/30/lessons/284528