[MySQL] SQL 프로그래머스 4단계 다 풀어보기 (2)

6문제

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

 

포인트: 서브 쿼리, WITH 절, 대여 기간은 DATEDIFF + 1이라는거, CASE WHEN THEN END 문

 

너무 어렵다..

 

https://school.programmers.co.kr/learn/courses/30/lessons/151141

프로그래머스

 

WITH DISCOUNT AS (
SELECT H.HISTORY_ID AS HISTORY_ID, C.DAILY_FEE AS DAILY_FEE,DATEDIFF(H.END_DATE, H.START_DATE) + 1 AS DATEDIFF, C.CAR_TYPE AS CAR_TYPE, (
    CASE WHEN DATEDIFF(H.END_DATE, H.START_DATE)+1 >= 90 THEN '90일 이상'
    WHEN DATEDIFF(H.END_DATE, H.START_DATE)+1 >= 30 THEN '30일 이상'
    WHEN DATEDIFF(H.END_DATE, H.START_DATE)+1 >= 7 THEN '7일 이상'
    ELSE ''
    END
) AS DURATION_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H
JOIN CAR_RENTAL_COMPANY_CAR C ON C.CAR_ID = H.CAR_ID
JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P ON P.CAR_TYPE = C.CAR_TYPE
WHERE C.CAR_TYPE = '트럭'
)
SELECT D.HISTORY_ID AS HISTORY_ID,
(CASE WHEN P.DISCOUNT_RATE IS NULL THEN D.DAILY_FEE * D.DATEDIFF
    ELSE CONVERT(D.DAILY_FEE * D.DATEDIFF * (100 - P.DISCOUNT_RATE) / 100, SIGNED) END
) AS FEE
FROM DISCOUNT AS D
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS P ON P.CAR_TYPE = D.CAR_TYPE AND P.DURATION_TYPE = D.DURATION_TYPE
GROUP BY HISTORY_ID
ORDER BY FEE DESC, HISTORY_ID DESC;

 

---

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

포인트: 서브쿼리, 우선 GRADE 부터 구해놓고 JOIN

https://school.programmers.co.kr/learn/courses/30/lessons/284528

프로그래머스

 

 

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

 

---

특정 세대의 대장균 찾기

포인트: 테이블 3개 셀프 조인, 계층 구조

https://school.programmers.co.kr/learn/courses/30/lessons/301650

프로그래머스

 

SELECT E1.ID AS ID
FROM ECOLI_DATA AS E1
JOIN ECOLI_DATA E2 ON E2.ID = E1.PARENT_ID
JOIN ECOLI_DATA E3 ON E3.ID = E2.PARENT_ID
WHERE E3.PARENT_ID IS NULL
ORDER BY ID;

 

---

FRONTEND 개발자 찾기

포인트: MySQL의 비트연산(&), FRONTEND에 해당하는 스킬 코드를 모두 더한뒤 & 연산하는 것

https://school.programmers.co.kr/learn/courses/30/lessons/276035

프로그래머스

 

WITH FRONT AS (
    SELECT SUM(CODE) FROM SKILLCODES WHERE CATEGORY = 'Front End'
)
SELECT ID, EMAIL, FIRST_NAME, LAST_NAME
FROM DEVELOPERS
WHERE SKILL_CODE & (SELECT * FROM FRONT) >= 1
ORDER BY ID;

 

---

보호소에서 중성화한 동물

포인트: inner join, like

이거는 4단계 치고 쉽다. 2, 3단계 느낌이다

https://school.programmers.co.kr/learn/courses/30/lessons/59045

프로그래머스

 

SELECT I.ANIMAL_ID AS ANIMAL_ID, I.ANIMAL_TYPE AS ANIMAL_TYPE, I.NAME AS NAME
FROM ANIMAL_OUTS AS O
JOIN ANIMAL_INS AS I ON O.ANIMAL_ID = I.ANIMAL_ID
WHERE I.SEX_UPON_INTAKE LIKE 'Intact%'
AND (O.SEX_UPON_OUTCOME LIKE 'Spayed%' OR O.SEX_UPON_OUTCOME LIKE 'Neutered%')
ORDER BY ANIMAL_ID

 

---

식품 분류별 가장 비싼 식품의 정보 조회하기

포인트: FROM절 서브 쿼리

https://school.programmers.co.kr/learn/courses/30/lessons/131116

프로그래머스

 

SELECT FP.CATEGORY AS CATEGORY, FP.PRICE AS MAX_PRICE, FP.PRODUCT_NAME AS PRODUCT_NAME
FROM FOOD_PRODUCT AS FP
JOIN (SELECT F.CATEGORY AS CATEGORY, MAX(F.PRICE) AS MAX_PRICE FROM FOOD_PRODUCT AS F GROUP BY F.CATEGORY) AS MP
ON MP.CATEGORY = FP.CATEGORY
WHERE MP.MAX_PRICE = FP.PRICE
AND FP.CATEGORY IN ('과자', '국', '김치', '식용유')
ORDER BY MAX_PRICE DESC;