[SQL] 프로그래머스 SQL 고득점 Kit GROUP BY (4)

---

언어별 개발자 분류하기

1. WITH 절 사용해서 비트 연산을 위한 임시 테이블 만들기

   (1). 스킬의 코드(CODE)의 합계 조회 

   (2). 스킬의 범주(CATEGORY)가 'Front End'인 데이터만 추출 

2. CASE WHEN 절과 비트연산(&)을 사용해서 GRADE 컬럼 만들고 조회

   (1). Front End 스킬과 Python 스킬을 함께 가지고 있는 개발자인 경우 'A'

   (2). C# 스킬을 가진 개발자인 경우 'B'

   (3). 그 외의 Front End 개발자인 경우 'C'

3. 개발자의 ID(ID)와 이메일(EMAIL) 조회 

4. HAVING 절을 사용해서 GRADE가 NULL이 아닌 데이터만 추출

5. GRADE, ID 기준으로 오름차순 정렬

WITH FRONT_END AS (
    SELECT 
        SUM(CODE) AS CODE
    FROM SKILLCODES 
    WHERE CATEGORY = 'Front End')

SELECT  
    CASE
        WHEN (SKILL_CODE & (SELECT CODE FROM FRONT_END)) 
            AND (SKILL_CODE & (SELECT CODE FROM SKILLCODES
                               WHERE NAME = 'Python')) THEN 'A'
        WHEN (SKILL_CODE & (SELECT CODE FROM SKILLCODES
                            WHERE NAME = 'c#')) THEN 'B'
        WHEN (SKILL_CODE & (SELECT CODE FROM FRONT_END)) THEN 'C'
    END AS GRADE,
    ID, EMAIL
FROM DEVELOPERS 
HAVING GRADE IS NOT NULL
ORDER BY GRADE, ID ASC

 

프로그래머스

---

조건에 맞는 사원 정보 조회하기

1. WITH 절 사용해서 2022년도의 상,하반기 점수의 합 임시 테이블 만들기

   (1). 사번(EMP_NO) 별로 GROUP BY 한 뒤 평가 점수(SCORE)의 합계 구하기 

2. 점수(SCORE), 사번(EMP_NO) , 성명(EMP_NAME), 직책(POSITION), 이메일(EMAIL) 조회

3. 사원 정보(HR_EMPLOYEES) 테이블에 임시 테이블(SCORE_2022)를 사번(EMP_NO) 기준으로 LEFT JOIN

4. 점수(SCORE)가 MAX인 데이터만 추출 

WITH SCORE_2022 AS(
    SELECT EMP_NO, SUM(SCORE) AS SCORE
    FROM HR_GRADE 
    GROUP BY EMP_NO)

SELECT SCORE, A.EMP_NO, EMP_NAME, POSITION, EMAIL
FROM HR_EMPLOYEES AS A 
LEFT JOIN SCORE_2022 AS B ON A.EMP_NO = B.EMP_NO
WHERE SCORE = (SELECT MAX(SCORE) FROM SCORE_2022)

 

프로그래머스

---

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

1. WITH 절 사용해서 2022년도의 상,하반기 점수의 평균 임시 테이블 만들기

   (1). 사번(EMP_NO) 별로 GROUP BY 한 뒤 평가 점수(SCORE)의 평균 구하기 

2. CASE WHEN 절을 사용해서 GRADE 컬럼 만들고 조회

   (1). 기준점수(SCORE)가 96점 이상인 경우 'S'

   (2). 기준점수(SCORE)가 90점 이상인 경우 'A'

   (3). 기준점수(SCORE)가 80점 이상인 경우 'B'

   (4). 그 외 'C'

3. CASE WHEN 절을 사용해서 BONUS 컬럼 만들고 조회

   (1). 기준점수(SCORE)가 96점 이상인 경우 연봉(SAL)의 20%

   (2). 기준점수(SCORE)가 90점 이상인 경우 연봉(SAL)의 15%

   (3). 기준점수(SCORE)가 80점 이상인 경우 연봉(SAL)의 10%

   (4). 그 외 0

4. 사원 정보(HR_EMPLOYEES) 테이블에 임시 테이블(SCORE_2022)를 사번(EMP_NO) 기준으로 LEFT JOIN

5. 사번(EMP_NO) 기준으로 오름차순 정렬

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

SELECT A.EMP_NO, EMP_NAME, 
    CASE
        WHEN SCORE >= 96 THEN 'S'
        WHEN SCORE >= 90 THEN 'A'
        WHEN SCORE >= 80 THEN 'B'
        ELSE 'C'
    END AS GRADE,
    CASE
        WHEN SCORE >= 96 THEN SAL*0.2
        WHEN SCORE >= 90 THEN SAL*0.15
        WHEN SCORE >= 80 THEN SAL*0.1
        ELSE 0
    END AS BONUS
FROM HR_EMPLOYEES AS A 
LEFT JOIN SCORE_2022 AS B ON A.EMP_NO = B.EMP_NO
ORDER BY A.EMP_NO ASC

 

프로그래머스

---

부서별 평균 연봉 조회하기

1. 부서 ID(DEPT_ID), 영문 부서명(DEPT_NAME_EN) 조회

2. 부서 별 평균 연봉(AVG_SAL) 조회, 소수점 첫째 자리에서 반올림 

3. 사원 정보(HR_EMPLOYEES) 테이블에 부서 정보(HR_DEPARTMENT) 테이블을 부서 ID(DEPT_ID) 기준으로 LEFT JOIN

4. 부서 ID(DEPT_ID) 별로 GROUP BY 한 뒤 평가 연봉(SAL)의 평균 구하기

5. 평균 연봉(AVG_SAL) 기준으로 내림차순 정렬

SELECT DEPT_ID, DEPT_NAME_EN, 
    ROUND(AVG(SAL),0) AS AVG_SAL
FROM HR_EMPLOYEES 
LEFT JOIN HR_DEPARTMENT USING (DEPT_ID)
GROUP BY DEPT_ID
ORDER BY AVG_SAL DESC

 

프로그래머스

---

노선별 평균 역 사이 거리 조회하기

1. 노선(ROUTE) 조회

2. 노선(ROUTE) 별 역 사이 거리(D_BETWEEN_DIST)의 합계 조회, 소수 둘째자리에서 반올림하고 'km' 붙이기 

3. 노선(ROUTE) 별 역 사이 거리(D_BETWEEN_DIST)의 평균 조회, 소수 셋째자리에서 반올림하고 'km' 붙이기 

4. 노선(ROUTE) 별로 GROUP BY 

5. 노선(ROUTE) 별 역 사이 거리(D_BETWEEN_DIST)의 합계 기준으로 내림차순 정렬

SELECT ROUTE,
    CONCAT(ROUND(SUM(D_BETWEEN_DIST),1),'km') AS TOTAL_DISTANCE,
    CONCAT(ROUND(AVG(D_BETWEEN_DIST),2),'km') AS AVERAGE_DISTANCE
FROM SUBWAY_DISTANCE 
GROUP BY ROUTE
ORDER BY SUM(D_BETWEEN_DIST) DESC

 

프로그래머스