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

Link

Table

erDiagram
	HR_DEPARTMENT {
		DEPT_ID VARCHAR
		DEPT_NAME_KR VARCHAR
		DEPT_NAME_EN VARCHAR
		LOCATION VARCHAR
	}
	HR_EMPLOYEES {
		EMP_NO VARCHAR
		EMP_NAME VARCHAR
		DEPT_ID VARCHAR
		POSITION VARCHAR
		EMAIL VARCHAR
		COMP_TEL VARCHAR
		HIRE_DATE DATE
		SAL NUMBER
	}
	HR_GRADE {
		EMP_NO VARCHAR
		YEAR NUMBER
		HALF_YEAR NUMBER
		SCORE NUMBER
	}
	HR_DEPARTMENT }o--o{ HR_EMPLOYEES : Contract
	HR_EMPLOYEES }o--|| HR_GRADE : Grade
Mermaid
복사

Question

HR_DEPARTMENT, HR_EMPLOYEES, HR_GRADE 테이블을 이용해 사원별 성과금 정보를 조회하려합니다. 평가 점수별 등급과 등급에 따른 성과금 정보가 아래와 같을 때, 사번, 성명, 평가 등급, 성과금을 조회하는 SQL문을 작성해주세요.

평가등급의 컬럼명은 GRADE로, 성과금의 컬럼명은 BONUS로 해주세요.

결과는 사번 기준으로 오름차순 정렬해주세요.

DataFlow

EMP_NO를 기준으로 성과금 정보 추출 연도에 따라서 추가적인 필터는 필요 없어 보임 HR_DEPARTMENT 따로 필요 없어 보임 HR_EMPLOYEES 테이블에 case 문 사용해서 컬럼 추가 GRADE 테이블에 반기 정보로 중복이 생김, 두 반기를 합친 평가 점수

Answer

WITH grade AS (
    SELECT
        EMP_NO,
        AVG(SCORE) AS SCORE
    FROM HR_GRADE
    GROUP BY EMP_NO
),

agg_data AS (
    SELECT
        e.EMP_NO,
        e.EMP_NAME,
        e.SAL,
        g.SCORE,
        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 0.2
            WHEN g.SCORE >= 90 THEN 0.15
            WHEN g.SCORE >= 80 THEN 0.1
            ELSE 0
        END AS bmr
    FROM HR_EMPLOYEES e
    LEFT JOIN (
        SELECT
            *
        FROM grade
    ) g
        ON e.EMP_NO = g.EMP_NO
)

SELECT
    EMP_NO,
    EMP_NAME,
    GRADE,
    SAL * bmr AS BONUS
FROM agg_data
ORDER BY EMP_NO ASC;
SQL
복사