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

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

1. 문제

테이블

• HR_EMPLOYEES(EMP_NO, EMP_NAME, DEPT_ID, POSITION, EMAIL, COMP_TEL, HIRE_DATE, SAL)
• HR_DEPARTMENT(DEPT_ID, DEPT_NAME_KR, DEPT_NAME_EN, LOCATION)
• HR_GRADE(EMP_NO, YEAR, HALF_YEAR, SCORE)

등급 기준 / 보너스 계산

• SCORE >= 96 → S → BONUS = SAL * 0.2
• 90 <= SCORE < 96 → A → BONUS = SAL * 0.15
• 80 <= SCORE < 90 → B → BONUS = SAL * 0.1
• SCORE < 80 → C → BONUS = 0

조회 컬럼

• EMP_NO, EMP_NAME, GRADE, BONUS

정렬

• EMP_NO ASC

2. 오답쿼리

2.1 첫번째 시도

-- 코드를 작성해주세요
SELECT e.EMP_NO,
       e.EMP_NAME,
       CASE (WHEN g.SCORE >= 96 THEN 'S'
            WHEN g.SCORE >= 90 THEN 'A'
            WHEN g.SCORE >= 80 THEN 'B'
            ElSE 'C')
       END AS GRADE,
       CASE (WHEN g.SCORE >= 96 THEN e.SAL * 0.2
            WHEN g.SCORE >= 90 THEN e.SAL * 0.15
            WHEN g.SCORE >= 80 THEN e.SAL * 0.1)
       END AS BONUS
FROM HR_EMPLOYEES e
     JOIN HR_DEPARTMENT d ON d.DEPT_ID = e.DEPT_ID
     JOIN HR_GRADE g ON g.EMP_NO = e.EMP_NO
GROUP BY e.EMP_NO, e.EMP_NAME,
ORDER BY e.EMP_NO;

1. CASE문에는 괄호 치지 않는다 → CASE 괄호 제거
2. 두 번째 CASE문에서 ELSE 0 처리 → 등급 C일때 보너스 0 적용
3. 집계 함수가 없으므로 GROUP BY는 불필요하다. → 불필요한 GROUP BY 제거

2.2 두번째 시도

-- 코드를 작성해주세요
SELECT e.EMP_NO,
       e.EMP_NAME,
       CASE WHEN g.SCORE >= 96 THEN 'S'
            WHEN g.SCORE >= 90 THEN 'A'
            WHEN g.SCORE >= 80 THEN 'B'
            ELSE 'C'
       END AS GRADE,
       CASE WHEN g.SCORE >= 96 THEN e.SAL * 0.2
            WHEN g.SCORE >= 90 THEN e.SAL * 0.15
            WHEN g.SCORE >= 80 THEN e.SAL * 0.1
            ELSE 0
       END AS BONUS
FROM HR_EMPLOYEES e
     JOIN HR_DEPARTMENT d ON d.DEPT_ID = e.DEPT_ID
     JOIN HR_GRADE g ON g.EMP_NO = e.EMP_NO
ORDER BY e.EMP_NO;

1. 같은 사원이 중복 출력되는 문제

JOIN 후 결과를 보면 같은 사람이 2번씩 등장한다. 중복 원인은 JOIN 대상 테이블에 한 명당 여러 행이 존재하기 때문이다.
HR_GRADE 테이블에 사원은 상반기/하반기 점수 2건씩이 있어서 그냥 JOIN하면 사원당 2행이 나온다.
상/하반기 평균을 내서 연간 GRADE와 BONUS를 계산해야 사원당 1행의 결과를 얻을 수 있다.

3. 정답쿼리

-- 코드를 작성해주세요
SELECT e.EMP_NO,
       e.EMP_NAME,
       CASE WHEN AVG(g.SCORE) >= 96 THEN 'S'
            WHEN AVG(g.SCORE) >= 90 THEN 'A'
            WHEN AVG(g.SCORE) >= 80 THEN 'B'
            ELSE 'C'
       END AS GRADE,
       CASE WHEN AVG(g.SCORE) >= 96 THEN e.SAL * 0.2
            WHEN AVG(g.SCORE) >= 90 THEN e.SAL * 0.15
            WHEN AVG(g.SCORE) >= 80 THEN e.SAL * 0.1
            ELSE 0
       END AS BONUS
FROM HR_EMPLOYEES e
    JOIN HR_DEPARTMENT d ON d.DEPT_ID = e.DEPT_ID
    JOIN HR_GRADE g ON e.EMP_NO = g.EMP_NO
GROUP BY e.EMP_NO, e.EMP_NAME
ORDER BY e.EMP_NO;

1. CASE문에서 AVG()함수로 점수를 감싸서 해결
2. GROUP BY 추가
   • SELECT절에 AVG(g.SCORE)이라는 집계 함수를 쓰기 위해 반드시 GROUP BY 가 필요하다.
   • 사원별 상반기/하반기 점수 평균을 계산하려면 사원 단위로 묶어야 하기 때문이다.

---