[프로그래머스] SQL 고득점 Kit 문제풀이 - String, Date

프로그래머스 스쿨 - 온라인 IT 특화 교육 전문 플랫폼

자동차 대여 기록에서 장기/단기 대여 구분하기(Lv. 1)

장기대여, 단기대여를 나타낼 때 조건문은 2가지 방식이 있다.

1. IF

SELECT HISTORY_ID, CAR_ID, 
DATE_FORMAT(START_DATE, '%Y-%m-%d') AS START_DATE, 
DATE_FORMAT(END_DATE, '%Y-%m-%d') AS START_DATE,
IF(DATEDIFF(END_DATE, START_DATE) + 1 >= 30, '장기 대여', '단기 대여') AS RENT_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE LIKE '2022-09%'
ORDER BY HISTORY_ID DESC;

1. CASE WHEN THEN

SELECT HISTORY_ID, CAR_ID, DATE_FORMAT(START_DATE, '%Y-%m-%d') AS START_DATE,
DATE_FORMAT(END_DATE, '%Y-%m-%d') AS END_DATE,
CASE
    WHEN DATEDIFF(END_DATE, START_DATE) + 1 >= 30 THEN '장기 대여'
    ELSE '단기 대여'
END AS RENT_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE LIKE '2022-09%'
ORDER BY HISTORY_ID DESC;

자동차 대여 기록 별 대여 금액 구하기(Lv. 4)

1. WITH

-- 1. 대여 기록 ID, 자동차 ID, 대여기간 조회
WITH TMP AS(
    SELECT HISTORY_ID, CAR_ID, DATEDIFF(END_DATE, START_DATE) + 1 AS DURATION
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
)

SELECT H.HISTORY_ID, 
FLOOR(H.DURATION * C.DAILY_FEE *
CASE
    WHEN H.DURATION >= 90 THEN 0.85
    WHEN H.DURATION >= 30 THEN 0.92
    WHEN H.DURATION >= 7 THEN 0.95
    ELSE 1
END) AS FEE
FROM TMP AS H
JOIN CAR_RENTAL_COMPANY_CAR AS C
ON H.CAR_ID = C.CAR_ID
WHERE C.CAR_TYPE = '트럭'
ORDER BY FEE DESC, HISTORY_ID DESC;

# SELECT * FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN;

1. SELECT B.HISTORY_ID,
   FLOOR(A.DAILY_FEE * (DATEDIFF(B.END_DATE, B.START_DATE) + 1) *
   CASE
    WHEN (DATEDIFF(B.END_DATE, B.START_DATE) + 1) >= 90 THEN 0.85
    WHEN (DATEDIFF(B.END_DATE, B.START_DATE) + 1) >= 30 THEN 0.92
    WHEN (DATEDIFF(B.END_DATE, B.START_DATE) + 1) >= 7 THEN 0.95
    ELSE 1
   END) AS FEE
   FROM CAR_RENTAL_COMPANY_CAR AS A
   JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY AS B
   ON A.CAR_ID = B.CAR_ID
   WHERE A.CAR_TYPE = '트럭'
   ORDER BY FEE DESC, HISTORY_ID DESC;

취소되지 않은 진료 예약 조회하기(Lv. 4)

SELECT A.APNT_NO, P.PT_NAME, A.PT_NO, A.MCDP_CD, D.DR_NAME, A.APNT_YMD
FROM APPOINTMENT AS A
JOIN PATIENT AS P
ON A.PT_NO = P.PT_NO
JOIN DOCTOR AS D
ON A.MDDR_ID = D.DR_ID
WHERE A.APNT_YMD LIKE '2022-04-13%'
AND A.APNT_CNCL_YN = 'N'
AND A.MCDP_CD = 'CS'
ORDER BY APNT_YMD;

• 2022년 4월 13일 취소되지 않은 흉부외과(CS) 진료 예약 내역 → WHERE

1. WITH 절

WITH TMP AS (
    SELECT *
    FROM APPOINTMENT
    WHERE APNT_YMD LIKE '2022-04-13%'
    AND MCDP_CD = 'CS'
    AND APNT_CNCL_YN = 'N'
)
SELECT A.APNT_NO, B.PT_NAME, A.PT_NO, A.MCDP_CD, C.DR_NAME, A.APNT_YMD
FROM TMP AS A
JOIN PATIENT AS B
ON A.PT_NO = B.PT_NO
JOIN DOCTOR AS C
ON A.MDDR_ID = C.DR_ID
ORDER BY APNT_YMD;

조건별로 분류하여 주문상태 출력하기(Lv. 3)

SELECT ORDER_ID, PRODUCT_ID, DATE_FORMAT(OUT_DATE, '%Y-%m-%d') AS OUT_DATE,
CASE
    WHEN OUT_DATE IS NULL THEN '출고미정'
    WHEN OUT_DATE <= '2022-05-01' THEN '출고완료'
    ELSE '출고대기'
END AS '출고여부'
FROM FOOD_ORDER
ORDER BY ORDER_ID;

• 출고여부는 5월 1일까지 출고완료로 이 후 날짜는 출고 대기로 미정이면 출고미정으로 출력 → CASE WHEN

루시와 엘라 찾기(Lv. 2)

SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE NAME IN('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty')
ORDER BY ANIMAL_ID;

이름에 el이 들어가는 동물찾기(Lv. 2)

• SQL 문은 대소문자를 구분하지 않는다.

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE ANIMAL_TYPE = 'Dog'
AND NAME LIKE '%el%'
ORDER BY NAME;

중성화 여부 파악하기(Lv. 2)

1. IF 이용

SELECT ANIMAL_ID, NAME, 
IF(SEX_UPON_INTAKE LIKE 'Intact%', 'X', 'O') AS 중성화
FROM ANIMAL_INS
ORDER BY ANIMAL_ID;

1. CASE WHEN 이용

SELECT ANIMAL_ID, NAME, 
CASE
    WHEN SEX_UPON_INTAKE LIKE '%Neutered%' OR SEX_UPON_INTAKE LIKE '%Spayed%' 
    THEN 'O'
    ELSE 'X'
END AS 중성화
FROM ANIMAL_INS
ORDER BY ANIMAL_ID;

오랜 기간 보호한 동물(2)(Lv. 3)

보호 기간을 계산하는 방식이 크게 2가지다.

1. -(차)

SELECT AI.ANIMAL_ID, AI.NAME
FROM ANIMAL_INS AS AI
JOIN ANIMAL_OUTS AS AO
ON AI.ANIMAL_ID = AO.ANIMAL_ID
ORDER BY (AO.DATETIME - AI.DATETIME) DESC
LIMIT 2;

• 보호 기간 = 입양일 - 보호시작일 → JOIN
• 보호 기간이 가장 길었던 동물 두 마리 → ORDER BY (AO.DATETIME - AI.DATETIME) DESC , LIMIT 2

1. DATEDIFF()

SELECT A.ANIMAL_ID, A.NAME
FROM ANIMAL_INS AS A
JOIN ANIMAL_OUTS AS B
ON A.ANIMAL_ID = B.ANIMAL_ID
ORDER BY DATEDIFF(B.DATETIME, A.DATETIME) DESC
LIMIT 2;

카테고리 별 상품 개수 구하기(Lv. 2)

1. LEFT()

SELECT LEFT(PRODUCT_CODE, 2) AS CATEGORY, COUNT(PRODUCT_ID) AS PRODUCTS
FROM PRODUCT
GROUP BY CATEGORY
ORDER BY CATEGORY;

• 상품 카테고리 코드(PRODUCT_CODE 앞 2자리) 별 → GROUP BY, LEFT()

1. SUBSTR()

SELECT SUBSTR(PRODUCT_CODE, 1, 2) AS CATEGORY, COUNT(PRODUCT_ID) AS PRODUCTS
FROM PRODUCT
GROUP BY CATEGORY
ORDER BY CATEGORY;

DATETIME 에서 DATE 로 형 변환(Lv. 2)

SELECT ANIMAL_ID, NAME, DATE_FORMAT(DATETIME, '%Y-%m-%d') AS '날짜'
FROM ANIMAL_INS
ORDER BY ANIMAL_ID;

대여 기록이 존재하는 자동차 리스트 구하기(Lv. 3)

1. DISTINCT

SELECT DISTINCT(A.CAR_ID)
FROM CAR_RENTAL_COMPANY_CAR AS A
JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY AS B
ON A.CAR_ID = B.CAR_ID
WHERE A.CAR_TYPE = '세단'
AND MONTH(B.START_DATE) = 10
ORDER BY CAR_ID DESC;

1. GROUP BY

SELECT A.CAR_ID
FROM CAR_RENTAL_COMPANY_CAR AS A
JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY AS B
ON A.CAR_ID = B.CAR_ID
WHERE CAR_TYPE = '세단'
AND MONTH(B.START_DATE) = 10
GROUP BY CAR_ID
ORDER BY CAR_ID DESC;

특정 옵션이 포함된 자동차 리스트 구하기(Lv. 1)

SELECT CAR_ID, CAR_TYPE, DAILY_FEE, OPTIONS
FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS LIKE '%네비게이션%'
ORDER BY CAR_ID DESC;

자동차 평균 대여 기간 구하기(Lv. 2)

SELECT CAR_ID, 
ROUND(AVG(DATEDIFF(END_DATE, START_DATE) + 1), 1) AS AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
HAVING AVERAGE_DURATION >= 7
ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC;

• 기간 = DATEDIFF(END_DATE, START_DATE) + 1

조건에 맞는 사용자 정보 조회하기(Lv. 3)

1. JOIN, CONCAT()

SELECT B.USER_ID, B.NICKNAME, 
CONCAT(CITY, ' ', B.STREET_ADDRESS1, ' ', B.STREET_ADDRESS2) AS 전체주소,
CONCAT(
    LEFT(B.TLNO, 3), '-', 
    MID(B.TLNO, 4, 4), '-', 
    RIGHT(B.TLNO, 4)
) AS 전화번호
FROM USED_GOODS_BOARD AS A
JOIN USED_GOODS_USER AS B
ON A.WRITER_ID = B.USER_ID
GROUP BY B.USER_ID
HAVING COUNT(A.BOARD_ID) >= 3
ORDER BY USER_ID DESC;

1. JOIN, CONCAT_WS()

SELECT B.USER_ID, B.NICKNAME, 
CONCAT(CITY, ' ', B.STREET_ADDRESS1, ' ', B.STREET_ADDRESS2) AS 전체주소,
CONCAT_WS('-', LEFT(TLNO, 3), MID(TLNO, 4, 4), RIGHT(TLNO, 4)) AS 전화번호
FROM USED_GOODS_BOARD AS A
JOIN USED_GOODS_USER AS B
ON A.WRITER_ID = B.USER_ID
GROUP BY B.USER_ID
HAVING COUNT(A.BOARD_ID) >= 3
ORDER BY USER_ID DESC;

1. 서브쿼리, CONCAT()

SELECT USER_ID, NICKNAME, 
CONCAT(CITY, ' ', STREET_ADDRESS1, ' ', STREET_ADDRESS2) AS 전체주소,
CONCAT(
    LEFT(TLNO, 3), '-',
    MID(TLNO, 4, 4), '-',
    RIGHT(TLNO, 4)
    ) AS 전화번호
FROM USED_GOODS_USER 
WHERE USER_ID IN (
        -- 1. 3번 이상 게시한 회원
    SELECT WRITER_ID
    FROM USED_GOODS_BOARD
    GROUP BY WRITER_ID
    HAVING COUNT(WRITER_ID) >= 3
)
ORDER BY USER_ID DESC;

자동차 대여 기록 별 대여 금액 구하기(Lv. 4)

• SELECT * FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN; 으로 미리 DURATION_TYPE 별 DISCOUNT_RATE 를 확인한다.

  

1. ```sql

-- 1. 대여 기록별 대여 금액 조회
SELECT H.HISTORY_ID,
FLOOR((DATEDIFF(H.END_DATE, H.START_DATE) + 1) * C.DAILY_FEE *
CASE
WHEN DATEDIFF(H.END_DATE, H.START_DATE) + 1 >= 90 THEN 0.85
WHEN DATEDIFF(H.END_DATE, H.START_DATE) + 1 >= 30 THEN 0.92
WHEN DATEDIFF(H.END_DATE, H.START_DATE) + 1 >= 7 THEN 0.95
ELSE 1
END) AS FEE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS H
JOIN CAR_RENTAL_COMPANY_CAR AS C
ON H.CAR_ID = C.CAR_ID
WHERE C.CAR_TYPE = '트럭'
ORDER BY FEE DESC, HISTORY_ID DESC;

1. 서브쿼리

```sql
-- 2. 대여 기록별 대여 금액 조회
SELECT H.HISTORY_ID, 
FLOOR(H.DURATION * C.DAILY_FEE *
CASE
    WHEN H.DURATION >= 90 THEN 0.85
    WHEN H.DURATION >= 30 THEN 0.92
    WHEN H.DURATION >= 7 THEN 0.95
    ELSE 1
END) AS FEE
FROM (
        -- 1. 대여 기록 ID, 자동차 ID, 대여기간 조회
    SELECT HISTORY_ID, CAR_ID, DATEDIFF(END_DATE, START_DATE) + 1 AS DURATION
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
) AS H
JOIN CAR_RENTAL_COMPANY_CAR AS C
ON H.CAR_ID = C.CAR_ID
WHERE C.CAR_TYPE = '트럭'
ORDER BY FEE DESC, HISTORY_ID DESC;

1. WITH

-- 1. 대여 기록 ID, 자동차 ID, 대여기간 조회
WITH TMP AS(
    SELECT HISTORY_ID, CAR_ID, DATEDIFF(END_DATE, START_DATE) + 1 AS DURATION
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
)

-- 2. 대여 기록별 대여 금액 조회
SELECT H.HISTORY_ID, 
FLOOR(H.DURATION * C.DAILY_FEE *
CASE
    WHEN H.DURATION >= 90 THEN 0.85
    WHEN H.DURATION >= 30 THEN 0.92
    WHEN H.DURATION >= 7 THEN 0.95
    ELSE 1
END) AS FEE
FROM TMP AS H
JOIN CAR_RENTAL_COMPANY_CAR AS C
ON H.CAR_ID = C.CAR_ID
WHERE C.CAR_TYPE = '트럭'
ORDER BY FEE DESC, HISTORY_ID DESC;

조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기(Lv. 3)

1. JOIN + 서브쿼리

-- 2. 조회수가 가장 높은 중고거래 게시물에 대한 첨부파일 경로
SELECT CONCAT('/home/grep/src/', B.BOARD_ID, '/', FILE_ID, FILE_NAME, FILE_EXT) AS FILE_PATH
FROM USED_GOODS_FILE AS A
JOIN USED_GOODS_BOARD AS B
ON A.BOARD_ID = B.BOARD_ID
WHERE A.BOARD_ID = (
        -- 1. 조회수가 가장 높은 중고거래 게시물 ID
    SELECT BOARD_ID
    FROM USED_GOODS_BOARD
    ORDER BY VIEWS DESC
    LIMIT 1
)
ORDER BY A.FILE_ID DESC;

• USED_GOODS_FILE AS A JOIN USED_GOODS_BOARD AS B

1. 서브쿼리 + 조인

SELECT CONCAT('/home/grep/src/', B.BOARD_ID, '/', B.FILE_ID, B.FILE_NAME, B.FILE_EXT) AS FILE_PATH
FROM (
        -- 1. 조회수가 가장 높은 중고거래 게시물 ID
    SELECT BOARD_ID
    FROM USED_GOODS_BOARD
    ORDER BY VIEWS DESC
    LIMIT 1
) AS A
JOIN USED_GOODS_FILE AS B
ON A.BOARD_ID = B.BOARD_ID
ORDER BY B.FILE_ID DESC;

1. WITH 절 + ,

-- 1. 조회수가 가장 높은 중고거래 게시물 ID
WITH TMP AS(
    SELECT BOARD_ID
    FROM USED_GOODS_BOARD
    ORDER BY VIEWS DESC
    LIMIT 1
)
-- 2. 조회수가 가장 높은 중고거래 게시물에 대한 첨부파일 경로
SELECT CONCAT('/home/grep/src/', B.BOARD_ID, '/', FILE_ID, FILE_NAME, FILE_EXT) AS FILE_PATH
FROM USED_GOODS_FILE AS A, TMP AS B
WHERE A.BOARD_ID = B.BOARD_ID
ORDER BY A.FILE_ID DESC;

• USED_GOODS_FILE AS A, TMP AS B

1. JOIN + 서브쿼리 + MAX()

SELECT CONCAT('/home/grep/src/', A.BOARD_ID , '/', A.FILE_ID, A.FILE_NAME, A.FILE_EXT) AS FILE_PATH
FROM USED_GOODS_FILE AS A
JOIN USED_GOODS_BOARD AS B
ON A.BOARD_ID = B.BOARD_ID
WHERE B.VIEWS = (
    -- 1. 조회수 최댓값 조회
    SELECT MAX(VIEWS)
    FROM USED_GOODS_BOARD
)
ORDER BY FILE_ID DESC;

조건에 부합하는 중고거래 상태 조회하기(Lv. 2)

SELECT BOARD_ID, WRITER_ID, TITLE, PRICE, 
CASE
    WHEN STATUS = 'SALE' THEN '판매중'
    WHEN STATUS = 'RESERVED' THEN '예약중'
    ELSE '거래완료'
END AS STATUS
FROM USED_GOODS_BOARD
WHERE CREATED_DATE LIKE '2022-10-05'
ORDER BY BOARD_ID DESC;