-- HR_DEPARTMENT: DEPT_ID, DEPT_NAME_KR, DEPT_NAME_EN, LOCATION
-- HR_EMPLOYEES: EMP_NO, EMP_NAME, DEPT_ID, POSITION, EMAIL, COMP_TEL, HIRE_DATE, SAL
-- HR_GRADE: EMP_NO, YEAR, HALF_YEAR, SCORE
WITH TOTAL_GRADE AS (
SELECT EMP_NO,
CASE
WHEN SUM(SCORE) >= 192 THEN 'S'
WHEN SUM(SCORE) >= 180 THEN 'A'
WHEN SUM(SCORE) >= 160 THEN 'B'
ELSE 'C'
END AS GRADE
FROM HR_GRADE
GROUP BY EMP_NO
)
SELECT E.EMP_NO, E.EMP_NAME, G.GRADE,
CASE
WHEN G.GRADE = 'S' THEN E.SAL * 0.2
WHEN G.GRADE = 'A' THEN E.SAL * 0.15
WHEN G.GRADE = 'B' THEN E.SAL * 0.1
ELSE 0
END AS BONUS
FROM TOTAL_GRADE AS G
JOIN HR_EMPLOYEES AS E ON G.EMP_NO = E.EMP_NO
ORDER BY 1;