[SQL] 프로그래머스 MySQL - Lv.4 (1~10)

https://school.programmers.co.kr/learn/challenges?order=recent&levels=4&languages=mysql

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

 

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

SELECT A.ID
FROM ECOLI_DATA A
JOIN ECOLI_DATA B
ON A.PARENT_ID = B.ID
JOIN ECOLI_DATA C
ON B.PARENT_ID = C.ID
WHERE ISNULL(C.PARENT_ID)
ORDER BY ID

 

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

SELECT E.EMP_NO, E.EMP_NAME, G.GRADE,
        CASE WHEN G.GRADE = 'S' THEN SAL*0.2
        WHEN G.GRADE = 'A' THEN SAL*0.15
        WHEN G.GRADE = 'B' THEN SAL*0.1
        ELSE 0 END AS BONUS
FROM HR_EMPLOYEES E
JOIN (SELECT EMP_NO, 
    IF(AVG(SCORE) >= 96, 'S',
      IF(AVG(SCORE) >= 90, 'A',
        IF(AVG(SCORE) >= 80, 'B', 'C'))) AS GRADE
      FROM HR_GRADE
     GROUP BY EMP_NO, YEAR) G
ON E.EMP_NO = G.EMP_NO
ORDER BY E.EMP_NO

 

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

WITH CDT AS (SELECT D.ID, SUM(IF(S.NAME = 'Python',1,0)) AS PYTHON,
    SUM(IF(S.NAME = 'C#',1,0)) as C,
    SUM(IF(S.CATEGORY = 'Front End',1,0)) AS FE
FROM DEVELOPERS D
JOIN SKILLCODES S
ON D.SKILL_CODE & S.CODE = S.CODE
GROUP BY D.ID)

SELECT IF(C.PYTHON > 0 AND C.FE > 0, 'A',
         IF(C.C > 0, 'B', 'C')) AS GRADE,
         A.ID, A.EMAIL
FROM DEVELOPERS A
JOIN CDT C
ON A.ID = C.ID
WHERE C.FE != 0 OR C.C != 0
ORDER BY GRADE, A.ID

 

4. FrontEnd 개발자 찾기

WITH CDT AS (SELECT D.ID, SUM(IF(S.NAME = 'Python',1,0)) AS PYTHON,
    SUM(IF(S.NAME = 'C#',1,0)) as C,
    SUM(IF(S.CATEGORY = 'Front End',1,0)) AS FE
FROM DEVELOPERS D
JOIN SKILLCODES S
ON D.SKILL_CODE & S.CODE = S.CODE
GROUP BY D.ID)

SELECT IF(C.PYTHON > 0 AND C.FE > 0, 'A',
         IF(C.C > 0, 'B', 'C')) AS GRADE,
         A.ID, A.EMAIL
FROM DEVELOPERS A
JOIN CDT C
ON A.ID = C.ID
WHERE C.FE != 0 OR C.C != 0
ORDER BY GRADE, A.ID

 

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

SELECT a.car_id,
       a.car_type,
       round((daily_fee * 30 * (100 - CAST(replace(discount_rate, '%', '') AS unsigned))) / 100,
             0) AS fee
FROM car_rental_company_car a
         JOIN (SELECT car_id,
                      COUNT(
                              CASE
                                  WHEN start_date > '2022-11-30' OR end_date < '2022-11-01'
                                      THEN NULL
                                  ELSE 1
                                  END) AS temp
               FROM car_rental_company_rental_history
               GROUP BY car_id
               HAVING temp = 0) b ON a.car_id = b.car_id
         JOIN (SELECT *
               FROM car_rental_company_discount_plan
               WHERE duration_type LIKE '30%') c ON a.car_type = c.car_type
having 500000 <= FEE AND  FEE <= 2000000
ORDER BY 3 DESC, 2, 1 DESC

 

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

WITH A AS (SELECT HISTORY_ID, CAR_TYPE, DATEDIFF(END_DATE, START_DATE)+1 AS PERIODS, DAILY_FEE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H
JOIN CAR_RENTAL_COMPANY_CAR C
ON H.CAR_ID = C.CAR_ID -- TYPE, FEE 붙이기
WHERE C.CAR_TYPE = '트럭')

SELECT A.HISTORY_ID, ROUND((1-IF(ISNULL(D.DISCOUNT_RATE),0,D.DISCOUNT_RATE)/100)*DAILY_FEE*A.PERIODS ,0) AS FEE
FROM A
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN D
ON A.CAR_TYPE = D.CAR_TYPE AND
IF(A.PERIODS BETWEEN 7 AND 30,'7일 이상',
  IF(A.PERIODS BETWEEN 30 AND 90, '30일 이상',
  IF(A.PERIODS >= 90, '90일 이상','-'))) = DURATION_TYPE
ORDER BY FEE DESC, HISTORY_ID DESC

 

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

SELECT A.AUTHOR_ID, A.AUTHOR_NAME, B.CATEGORY, SUM(SALES*PRICE) AS TOTAL_SALES
FROM BOOK_SALES S
JOIN BOOK B 
ON S.BOOK_ID = B.BOOK_ID -- SALES + 도서 정보
JOIN AUTHOR A
ON B.AUTHOR_ID = A.AUTHOR_ID
WHERE YEAR(SALES_DATE) = '2022' AND MONTH(SALES_DATE) = '1'
GROUP BY B.AUTHOR_ID, B.CATEGORY
ORDER BY A.AUTHOR_ID ASC, B.CATEGORY DESC

 

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

WITH JULY_F AS (SELECT FLAVOR, SUM(TOTAL_ORDER) AS TOTAL_ORDER
               FROM JULY GROUP BY FLAVOR)

SELECT A.FLAVOR
FROM (SELECT FH.FLAVOR, FH.TOTAL_ORDER + J.TOTAL_ORDER AS TOTAL FROM FIRST_HALF FH
LEFT JOIN JULY_F J ON FH.FLAVOR = J.FLAVOR
UNION
SELECT J.FLAVOR, FH.TOTAL_ORDER + J.TOTAL_ORDER AS TOTAL FROM FIRST_HALF FH
RIGHT JOIN JULY_F J ON FH.FLAVOR = J.FLAVOR) A
ORDER BY A.TOTAL DESC
LIMIT 3

 

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

WITH APNT AS (SELECT *
FROM APPOINTMENT
WHERE DATE_FORMAT(APNT_YMD, '%Y-%m-%d') = '2022-04-13' 
AND MCDP_CD = 'CS' AND APNT_CNCL_YN = 'N')

SELECT APNT_NO, PT_NAME, APNT.PT_NO, APNT.MCDP_CD, D.DR_NAME, APNT_YMD
FROM APNT
JOIN PATIENT P 
ON APNT.PT_NO = P.PT_NO
JOIN DOCTOR D
ON APNT.MDDR_ID = D.DR_ID
ORDER BY APNT_YMD

 

10. 오프라인/온라인 판매 데이터 통합하기

SELECT DATE_FORMAT(SALES_DATE,'%Y-%m-%d') AS SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
FROM 
(SELECT USER_ID, PRODUCT_ID, SALES_AMOUNT, SALES_DATE FROM ONLINE_SALE
UNION ALL
SELECT NULL AS USER_ID, PRODUCT_ID, SALES_AMOUNT, SALES_DATE FROM OFFLINE_SALE) A
WHERE DATE_FORMAT(SALES_DATE,'%Y-%m') = '2022-03'
ORDER BY SALES_DATE, PRODUCT_ID, USER_ID