KATA#96

WHAT IS KATA?

KATA는 기술과 기술 향상에 초점을 맞춘 코드 챌린지입니다.
일부는 프로그래밍 기본 사항을 교육하는 반면 다른 일부는 복잡한 문제 해결에 중점을 둡니다.

이 용어는 The Pragmatic Programmer 라는 책의 공동 저자인 Dave Thomas 가
무술에서 일본의 카타 개념을 인정하면서 처음 만들어졌습니다.
Dave의 개념 버전은 코드 카타를 프로그래머가
연습과 반복을 통해 기술을 연마하는 데 도움이 되는 프로그래밍 연습으로 정의합니다.

---

- SQL

---

프로그래머스에서 SQL 문제 풀기

✔️ 문제 #1: 연간 평가점수에 해당하는 평가 등급 및 성과금 조회하기

문제

• 사원들의 평가 점수별 등급(GRADE)을 나누고 등급별 성과금을 계산하는 쿼리 작성
  • S: SCORE = 96 이상 → BONUS = SAL의 20%
  • A: SCORE = 90 이상 → BONUS = SAL의 15%
  • B: SCORE = 80 이상 → BONUS = SAL의 10%
  • C: SCORE = 80 미만 → BONUS = SAL의 0%
• 각 사원들의 EMP_NO, EMP_NAME, GRADE, BONUS 를 조회하고,
  EMP_NO 를 기준으로 오름차순 정렬

✔️ 제출 쿼리

✔️ 쿼리 분석

# WITH 절 ; GRADING 임시 테이블 생성
WITH GRADING AS (
    # HR_GRADE 테이블에서 EMP_NO를 중복없이 가져오고, 성적에 따른 등급을 부여
    SELECT DISTINCT EMP_NO
         , CASE 
                # 직원의 평균 점수가 96 이상인 경우 'S' 등급 부여
                WHEN AVG(SCORE) OVER (PARTITION BY EMP_NO) >= 96
                    THEN 'S'
                # 직원의 평균 점수가 90 이상인 경우 'A' 등급 부여
                WHEN AVG(SCORE) OVER (PARTITION BY EMP_NO) >= 90
                    THEN 'A'
                # 직원의 평균 점수가 80 이상인 경우 'B' 등급 부여
                WHEN AVG(SCORE) OVER (PARTITION BY EMP_NO) >= 80
                    THEN 'B'
                # 위 조건에 해당하지 않는 경우 'C' 등급 부여
                ELSE 'C' END GRADE
    FROM HR_GRADE
)

# 메인 쿼리
SELECT H.EMP_NO
     , H.EMP_NAME
     , G.GRADE
     , CASE
            # 등급이 'S'인 경우 보너스는 기본 급여의 20%
            WHEN G.GRADE = 'S' THEN H.SAL * 0.2
            # 등급이 'A'인 경우 보너스는 기본 급여의 15%
            WHEN G.GRADE = 'A' THEN H.SAL * 0.15
            # 등급이 'B'인 경우 보너스는 기본 급여의 10%
            WHEN G.GRADE = 'B' THEN H.SAL * 0.1
            # 등급이 'C'인 경우 보너스는 없음 (0%)
            WHEN G.GRADE = 'C' THEN H.SAL * 0
            # 위 조건에 해당하지 않는 경우 NULL
            ELSE NULL END BONUS
FROM HR_EMPLOYEES H
     # GRADING 임시 테이블과 HR_EMPLOYEES 테이블을 EMP_NO를 기준으로 이너 조인
     INNER JOIN GRADING G ON H.EMP_NO = G.EMP_NO
# EMP_NO 기준 오름차순 정렬
ORDER BY 1

---

✔️ CHECK POINT

• SQL

  • CTE 생성 (WITH 절)

    • GRADING 테이블 생성
      각 직원의 EMP_NO 와 성적에 따른 등급(GRADE) 부여

    • GRADE : 윈도우 함수 AVG(SCORE) OVER (PARTITION BY EMP_NO) 를 사용하여
      각 직원의 평균 점수 계산

  • CTE & MAIN TABLE (+ CASE WHEN)

    • BONUS : 직원 등급에 따른 보너스 금액

    • 등급 조건에 따라 보너스 비율 적용 :
      'S' : 기본 급여의 20%
      'A' : 기본 급여의 15%
      'B' : 기본 급여의 10%
      'C' : 보너스 없음 (0%)

---

# WITH 절 ; GRADING 임시 테이블 생성
WITH GRADING AS (
    # HR_GRADE 테이블에서 EMP_NO를 중복없이 가져오고, 성적에 따른 등급을 부여
    SELECT DISTINCT EMP_NO
         , CASE 
                # 직원의 평균 점수가 96 이상인 경우 'S' 등급 부여
                WHEN AVG(SCORE) OVER (PARTITION BY EMP_NO) >= 96
                    THEN 'S'
                # 직원의 평균 점수가 90 이상인 경우 'A' 등급 부여
                WHEN AVG(SCORE) OVER (PARTITION BY EMP_NO) >= 90
                    THEN 'A'
                # 직원의 평균 점수가 80 이상인 경우 'B' 등급 부여
                WHEN AVG(SCORE) OVER (PARTITION BY EMP_NO) >= 80
                    THEN 'B'
                # 위 조건에 해당하지 않는 경우 'C' 등급 부여
                ELSE 'C' END GRADE
    FROM HR_GRADE
)

# 메인 쿼리
SELECT H.EMP_NO
     , H.EMP_NAME
     , G.GRADE
     , CASE
            # 등급이 'S'인 경우 보너스는 기본 급여의 20%
            WHEN G.GRADE = 'S' THEN H.SAL * 0.2
            # 등급이 'A'인 경우 보너스는 기본 급여의 15%
            WHEN G.GRADE = 'A' THEN H.SAL * 0.15
            # 등급이 'B'인 경우 보너스는 기본 급여의 10%
            WHEN G.GRADE = 'B' THEN H.SAL * 0.1
            # 등급이 'C'인 경우 보너스는 없음 (0%)
            WHEN G.GRADE = 'C' THEN H.SAL * 0
            # 위 조건에 해당하지 않는 경우 NULL
            ELSE NULL END BONUS
FROM HR_EMPLOYEES H
     # GRADING 임시 테이블과 HR_EMPLOYEES 테이블을 EMP_NO를 기준으로 이너 조인
     INNER JOIN GRADING G ON H.EMP_NO = G.EMP_NO
# EMP_NO 기준 오름차순 정렬
ORDER BY 1