[실무]프로그래머스 MySQL 기출문제 모음 Lv.4.1

■ 출처

- 코딩테스트 : MySQL / Oracle / Lv.4

https://school.programmers.co.kr/learn/challenges?order=recent&languages=mysql%2Coracle&page=1&levels=4

코딩테스트 연습 | 프로그래머스 스쿨

 

 

 

■ 연습문제

1. 특정 세대의 대장균 찾기

WITH 문 : 쿼리를 따로 작성하여 임시 테이블처럼 활용
- 장점 : 쿼리 가독성 향상(직관적 코드 작성 및 해석 가능)

               SQL 성능 개선(서브쿼리와 기능은 유사하지만, 동작 방식이 달라 성능 개선효과 

#설명: 3세대 대장균의 ID를 조회

#1세대 대장균 ID조회
WITH FRIST_GEN AS(SELECT ID
                  FROM ECOLI_DATA 
                  WHERE PARENT_ID IS NULL),
#2세대 대장균 ID조회
SECOND_GEN AS(SELECT ECOLI_DATA.ID
              FROM ECOLI_DATA
              INNER JOIN FRIST_GEN
              ON ECOLI_DATA.PARENT_ID = FRIST_GEN.ID)                  
SELECT
    ECOLI_DATA.ID
FROM
    ECOLI_DATA INNER JOIN SECOND_GEN
    ON ECOLI_DATA.PARENT_ID = SECOND_GEN.ID
ORDER BY
    ECOLI_DATA.ID ASC

 

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

#설명: 사원별 성과금 정보 조회

SELECT
    B_Table.EMP_NO,
    B_Table.EMP_NAME,
    (CASE WHEN AVG(C_Table.SCORE) >= 96 THEN 'S'
          WHEN AVG(C_Table.SCORE) >= 90 THEN 'A'
          WHEN AVG(C_Table.SCORE) >= 80 THEN 'B' ELSE 'C' END) AS GRADE,
    (CASE WHEN AVG(C_Table.SCORE) >= 96 THEN B_Table.SAL*0.2
          WHEN AVG(C_Table.SCORE) >= 90 THEN B_Table.SAL*0.15
          WHEN AVG(C_Table.SCORE) >= 80 THEN B_Table.SAL*0.1 ELSE 0 END) AS BONUS      
FROM
    HR_DEPARTMENT AS A_Table
    RIGHT JOIN HR_EMPLOYEES AS B_Table
    ON A_Table.DEPT_ID = B_Table.DEPT_ID
    LEFT JOIN HR_GRADE AS C_Table
    ON B_Table.EMP_NO = C_Table.EMP_NO
GROUP BY
    B_Table.EMP_NO
ORDER BY
    B_Table.EMP_NO ASC

 

3. 언어별 개발자 분류하기

#설명: GRADE별 개발자의 정보를 조회

SELECT 
    (CASE WHEN (SKILL_CODE & (SELECT SUM(CODE) FROM SKILLCODES WHERE CATEGORY LIKE 'FRONT%')) 
               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 SUM(CODE) FROM SKILLCODES WHERE CATEGORY LIKE 'FRONT%') THEN 'C' 
          ELSE NULL END) AS GRADE, 
     ID, 
     EMAIL
FROM
    DEVELOPERS 
GROUP BY
    GRADE, ID, EMAIL
HAVING 
    GRADE IS NOT NULL
ORDER BY
    GRADE ASC, ID ASC

 

4. FrontEnd 개발자 찾기

#설명: Front End 스킬을 보유한 개발자를 조회

SELECT
    DISTINCT B_Table.ID, 
    B_Table.EMAIL, 
    B_Table.FIRST_NAME, 
    B_Table.LAST_NAME
FROM
    SKILLCODES AS A_Table
    INNER JOIN DEVELOPERS AS B_Table
    ON A_Table.CODE & B_Table.SKILL_CODE
WHERE 
    A_Table.CATEGORY = 'Front End'
ORDER BY
    B_Table.ID

※ DISTINCT 함수를 사용하여 중복된 데이터들 제거(만약, 사용하지 않으면 중복된 정보도 함께 제공)

 

5. 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기

#설명: 세단/SUV 중, 22.11.01~30까지 대여 가능하고, 30일간 대여금액이 50만원 이상 200만원 미만인 자동차에 대해 조회

SELECT
    A_Table.CAR_ID,
    A_Table.CAR_TYPE,
    #지불비용 : 일 대여료 * 30일 * 내야할 비율((100-할인율)/100))
    ROUND(A_Table.DAILY_FEE*30*(100-C_Table.DISCOUNT_RATE)/100) AS FEE
FROM
    CAR_RENTAL_COMPANY_CAR AS A_Table
    INNER JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY AS B_Table ON A_Table.CAR_ID = B_Table.CAR_ID
    INNER JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS C_Table ON A_Table.CAR_TYPE = C_Table.CAR_TYPE
WHERE
    #22.11.01~22.11.30(30일)간 대여 가능한 차량(NOT IN 함수를 통해 해당 기간에 렌트 기록이 없는 ID조회)
    #참고) CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블은 자동차 대여 기록 정보임을 확인
    A_Table.CAR_ID NOT IN(SELECT CAR_ID
                          FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                          WHERE END_DATE >= '2022-11-01' AND START_DATE <= '2022-11-30')
    AND C_Table.DURATION_TYPE LIKE '30%' 
GROUP BY
    A_Table.CAR_ID
HAVING
    #대여금액이 50만원에서 200만원 미만인 세단 혹은 SUV 자동차
    A_Table.CAR_TYPE IN ('세단', 'SUV') AND (FEE >= 500000 AND FEE < 2000000) 
ORDER BY
    FEE DESC, CAR_TYPE ASC, CAR_ID DESC

 

6. 자동차 대여 기록 별 대여 금액 구하기

#설명: 트럭 자동차의 대여 기록에 대해 대여 기록별 금액을 구하여, 대여 기록 ID와 대여금액 리스트를 조회
#참고: 'CAR_RENTAL_COMPANY_DISCOUNT_PLAN' 테이블을 단독으로 출력해보면 '트럭' 할인율이 문제와 상이함을 확인

SELECT 
    B_Table.HISTORY_ID, 
    ROUND(A_Table.DAILY_FEE * (DATEDIFF(B_Table.END_DATE, B_Table.START_DATE)+1)
    * (CASE WHEN (DATEDIFF(B_Table.END_DATE, B_Table.START_DATE)+1) < 7 THEN 1
            WHEN (DATEDIFF(B_Table.END_DATE, B_Table.START_DATE)+1) < 30 THEN 0.95 -- 7일 이상(5%)
            WHEN (DATEDIFF(B_Table.END_DATE, B_Table.START_DATE)+1) < 90 THEN 0.92 -- 30일 이상(8%)
            ELSE 0.85 END)) AS "FEE"                              -- 90일 이상(15%)

FROM 
    CAR_RENTAL_COMPANY_CAR AS A_Table
    INNER JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY AS B_Table ON A_Table.CAR_ID = B_Table.CAR_ID
    INNER JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS C_Table ON A_Table.CAR_TYPE = C_Table.CAR_TYPE
WHERE
    A_Table.car_type = '트럭'
GROUP BY
    HISTORY_ID
ORDER BY
    FEE DESC, HISTORY_ID DESC

 

 

 

7. 저자 별 카테고리 별 매출액 집계하기

#설명: 22.01에 판매 데이터를 통해 저자별 카테고리별 매출액을 조회

SELECT
    B_Table.AUTHOR_ID,
    B_Table.AUTHOR_NAME, 
    A_Table.CATEGORY,
    SUM(C_Table.SALES * A_Table.PRICE) AS TOTAL_SALES
FROM
    BOOK AS A_Table
    LEFT JOIN AUTHOR AS B_Table 
    ON A_Table.AUTHOR_ID = B_Table.AUTHOR_ID
    RIGHT JOIN BOOK_SALES AS C_Table
    ON A_Table.BOOK_ID = C_Table.BOOK_ID    
WHERE
    C_Table.SALES_DATE LIKE '2022-01%'
GROUP BY
    B_Table.AUTHOR_ID,
    B_Table.AUTHOR_NAME, 
    A_Table.CATEGORY
#HAVING
ORDER BY
    B_Table.AUTHOR_ID ASC,
    B_Table.AUTHOR_NAME ASC, 
    A_Table.CATEGORY DESC

 

 

8. 주문량이 많은 아이스크림들 조회하기

#설명: 7월 아이스크림 총 주문량과 상반기 아이스크림 총 주문량을 더한 값을 상위 3개 맛을 조회

SELECT
    A_Table.FLAVOR
FROM
    FIRST_HALF AS A_Table
    INNER JOIN (SELECT FLAVOR, SUM(TOTAL_ORDER) AS JULY_TOTAL_ORDER 
                FROM JULY 
                GROUP BY FLAVOR) AS B_Table 
    ON A_Table.FLAVOR = B_Table.FLAVOR
ORDER BY
    (A_Table.TOTAL_ORDER+B_Table.JULY_TOTAL_ORDER) DESC
LIMIT 
    3

 

 

9. 취소되지 않은 진료 예약 조회하기

#설명: 22.04.13 취소되지 않은 흉부외과 진료 예약 내역을 조회

SELECT
    B_Table.APNT_NO,
    A_Table.PT_NAME,
    B_Table.PT_NO,
    B_Table.MCDP_CD,
    C_Table.DR_NAME,
    B_Table.APNT_YMD
FROM
    PATIENT AS A_Table
    INNER JOIN APPOINTMENT AS B_Table 
    ON A_Table.PT_NO = B_Table.PT_NO
    INNER JOIN DOCTOR AS C_Table
    ON B_Table.MDDR_ID = C_Table.DR_ID    
WHERE
    #22.04.13에 흉부외과 진료 예약을 취소하지 않은 결과 조회
    B_Table.APNT_CNCL_YN = 'N' 
    AND C_Table.MCDP_CD = 'CS' 
    AND B_Table.APNT_YMD LIKE '2022-04-13%'
ORDER BY
    B_Table.APNT_YMD