[SQL문제풀기] 연간 평가점수에 해당하는 평가 등급 및 성과금 조회하기

문제

내가 작성한 정답

1. cte를 사용하여 평균점수를 구한 테이블을 따로 빼서 사용했다.

with score_tb as (select e.emp_no, e.emp_name, e.sal, avg(score) score
                from HR_EMPLOYEES e
                join HR_GRADE g on e.emp_no = g.emp_no
                group by e.emp_no, e.emp_name, e.sal )
select emp_no, emp_name,
       case 
        when 96 <= score then 'S'
        when 90 <= score and score < 96 then 'A'
        when 80 <= score and score < 90 then 'B'
        else 'C' end as grade,
       case 
         when 96 <= score then sal*0.2
         when 90 <= score and score < 96 then sal*0.15
         when 80 <= score and score < 90 then sal*0.1
         else 0 end as bonus
from score_tb 
order by emp_no asc

2. grade를 case when의 조건으로 사용

with score_tb as (select e.emp_no, e.emp_name, e.sal, avg(score) score
                from HR_EMPLOYEES e
                join HR_GRADE g on e.emp_no = g.emp_no
                group by e.emp_no, e.emp_name, e.sal )
, grade_tb as (select emp_no, emp_name, sal,
                           case 
                            when 96 <= score then 'S'
                            when 90 <= score and score < 96 then 'A'
                            when 80 <= score and score < 90 then 'B'
                            else 'C' end as grade
                from score_tb )                  
select emp_no,emp_name,grade
        ,case 
            when grade = 'S' then sal*0.2
            when grade = 'A' then sal*0.15
            when grade = 'B' then sal*0.1
            else 0 end as bonus
from grade_tb
order by emp_no asc

다른 사람들이 작성한 정답

SELECT G.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(
    SELECT EMP_NO,
    CASE WHEN AVG(SCORE) >= 96 THEN 'S' 
		     WHEN AVG(SCORE) < 96 AND AVG(SCORE) >= 90 THEN 'A' 
		     WHEN AVG(SCORE) < 90 AND AVG(SCORE) >= 80 THEN 'B' 
		     ELSE 'C' END AS GRADE
    FROM HR_GRADE
    GROUP BY EMP_NO
    ORDER BY EMP_NO
) G
LEFT JOIN HR_EMPLOYEES E
ON G.EMP_NO = E.EMP_NO;