[코딩테스트] 프로그래머스 SQL String Date 1 ~ 10

코딩테스트/SQL

[코딩테스트] 프로그래머스 SQL String Date 1 ~ 10

[dev] hiro 2024. 11. 5. 22:04

제가 공부한 내용을 정리하는 블로그입니다.
아직 많이 부족하고 배울게 너무나도 많습니다. 틀린내용이 있으면 언제나 가감없이 말씀해주시면 감사하겠습니다😁
Programmers SQL 고득점 Kit(String, Date)입니다.

자동차 평균 대여 기간 구하기

SELECT 
    CAR_ID, 
    TO_CHAR(ROUND(AVG(END_DATE - START_DATE + 1), 1), 'FM9990.0') AS AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
HAVING AVG(END_DATE - START_DATE + 1) >= 7
ORDER BY ROUND(AVG(END_DATE - START_DATE + 1), 1) DESC, CAR_ID DESC

자동차 평균 대여 기간을 구하기 위해서 자동차 ID를 기준으로 그룹화를 진행합니다.
GROUP BY CAR_ID
평균 대여 기간이 7일 이상인 레코드를 조회하기 위해 HAVING 절을 이용합니다.
HAVING AVG(END_DATE - START_DATE + 1) >= 7
소수점을 표현하기 위해 FM9990.0의 데이터 형식으로 표현해줍니다.
TO_CHAR(ROUND(AVG(END_DATE - START_DATE + 1), 1), 'FM9990.0') AS AVERAGE_DURATION
자동차 대여 기간, 자동차 ID를 기준으로 내림차순 정렬합니다.

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

SELECT
    CONCAT(CONCAT(
    	CONCAT(CONCAT(CONCAT('/home/grep/src/', B.BOARD_ID),'/'),file_id), 
    FILE_NAME),file_ext) AS FILE_PATH
FROM (
    SELECT
        BOARD_ID
    FROM USED_GOODS_BOARD
    ORDER BY VIEWS DESC
    FETCH NEXT 1 ROWS ONLY
) B JOIN USED_GOODS_FILE F ON B.BOARD_ID = F.BOARD_ID
ORDER BY F.FILE_ID DESC

조회수가 가장 높은 레코드를 조회하기 위해 서브 테이블 B를 생성합니다.
조회수를 기준으로 내림차순 정렬하고 첫번째 레코드를 조회합니다.

(
    SELECT
        BOARD_ID
    FROM USED_GOODS_BOARD
    ORDER BY VIEWS DESC
    FETCH NEXT 1 ROWS ONLY
) B

B 테이블과 USED_GOODS_FILE 테이블과 BOARD_ID를 기준으로 JOIN 합니다.
FROM(...) B JOIN USED_GOODS_FILE F ON B.BOARD_ID = F.BOARD_ID
첨부파일의 경로를 표시하기 위해 여러 컬럼을 더해주기 위해 CONCAT을 여러번 이용합니다.
CONCAT(CONCAT(CONCAT(CONCAT(CONCAT('/home/grep/src/', B.BOARD_ID),'/'),file_id), FILE_NAME),file_ext) AS FILE_PATH
파일 ID를 기준으로 내림차순 정렬합니다.

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

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

가격 정책과 조인하기 위해 서브 테이블 H을 생성합니다.
대여 기간 컬럼(RENTAL_DATE)와 조인을 위한 컬럼(DURATION_TYPE) 컬럼을 추가해줍니다.

(
    SELECT 
        HISTORY_ID,
        CAR_ID,
        END_DATE - START_DATE + 1 AS RENTAL_DATE,
        CASE WHEN END_DATE - START_DATE + 1 >= 90 THEN '90일 이상'
            WHEN END_DATE - START_DATE + 1 >= 30 THEN '30일 이상'
            WHEN END_DATE - START_DATE + 1 >= 7 THEN '7일 이상'
            ELSE NULL
        END AS DURATION_TYPE
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
)H

자동차 ID를 기준으로 JOIN합니다.
FROM CAR_RENTAL_COMPANY_CAR C JOIN (...) H ON C.CAR_ID = H.CAR_ID
트럭에 대한 자동차 대여 기록별 대여 금액을 구하기 위해 서브테이블 P를 생성하여 대여 기간을 기준으로 LEFT JOIN 합니다.
LEFT JOIN을 하는 이유는 7일 이하로 렌트하는 레코드를 조회하기 위함입니다.

LEFT JOIN (
    SELECT 
        DURATION_TYPE,
        (1 - (DISCOUNT_RATE / 100)) AS DISCOUNT_RATE
    FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
    WHERE CAR_TYPE = '트럭'
)P ON H.DURATION_TYPE = P.DURATION_TYPE

자동차 타입이 트럭인 레코드를 조회하기 위한 조건을 걸어줍니다.
WHERE C.CAR_TYPE = '트럭'
대여 금액을 구하기 위해 해당 수식의 컬럼을 추가합니다.
null인 것은 할인 대상이 아니기에 * 1을 해줍니다.
C.DAILY_FEE * H.RENTAL_DATE * NVL(P.DISCOUNT_RATE, 1) AS FEE
대여 금액과 대여 기록 ID를 기준으로 내림차순 조회합니다.

조건에 부합하는 중고거래 상태 조회하기

SELECT 
    BOARD_ID, 
    WRITER_ID, 
    TITLE, 
    PRICE, 
    CASE WHEN STATUS = 'SALE' THEN '판매중'
         WHEN STATUS = 'RESERVED' THEN '예약중'
         WHEN STATUS = 'DONE' THEN '거래완료'
    END 
FROM USED_GOODS_BOARD
WHERE TO_CHAR(CREATED_DATE, 'YYYY-MM-DD') = '2022-10-05'
ORDER BY BOARD_ID DESC;

2022년 10월 5일에 등록된 레코드를 조회하기 위해 조건을 걸어줍니다.
WHERE TO_CHAR(CREATED_DATE, 'YYYY-MM-DD') = '2022-10-05'
STATUS를 통해 판매중/예약중/거래완료를 처리하기 위해 CASE 절을 사용합니다.
CASE WHEN STATUS = 'SALE' THEN '판매중'
         WHEN STATUS = 'RESERVED' THEN '예약중'
         WHEN STATUS = 'DONE' THEN '거래완료'
    END
게시물 ID를 기준으로 내림차순 정렬합니다.

특정 옵션이 포함된 자동차 리스트 구하기

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

네비게이션 옵션이 있는 레코드를 조회하기 위해 like 절을 사용합니다.
WHERE OPTIONS LIKE '%네비게이션%'
자동차 ID를 기준으로 내림차순 정렬합니다.

자동차 대여 기록에서 장기/단기 대여 구분하기

SELECT
    HISTORY_ID,
    CAR_ID,
    TO_CHAR(START_DATE, 'YYYY-MM-DD') AS START_DATE,
    TO_CHAR(END_DATE, 'YYYY-MM-DD') AS END_DATE,
    CASE WHEN END_DATE - START_DATE + 1 >= 30 THEN '장기 대여'
    ELSE '단기 대여'
    END AS RENT_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE EXTRACT(MONTH FROM START_DATE) = 9
ORDER BY HISTORY_ID DESC

대여 시작일이 9월인 레코드를 조회하기 위해 조건을 걸어줍니다.
DATETIME 형식에서 월만 추출하기 위해 EXTRACT 절을 사용합니다.
WHERE EXTRACT(MONTH FROM START_DATE) = 9
30일 이상 대여하는 레코드에 대해서는 장기 대여 아닌 것에 대해서는 단기 대여를 위해 CASE 절을 사용합니다.
CASE WHEN END_DATE - START_DATE + 1 >= 30 THEN '장기 대여'
ELSE '단기 대여'
END AS RENT_TYPE
예약 ID를 기준으로 내림차순 정렬합니다.

조건별로 분류하여 주문상태 출력하기

SELECT
    ORDER_ID, 
    PRODUCT_ID, 
    TO_CHAR(OUT_DATE, 'YYYY-MM-DD') AS OUT_DATE,
    CASE WHEN TO_CHAR(OUT_DATE, 'YYYYMMDD') <= '20220501' THEN '출고완료'
         WHEN TO_CHAR(OUT_DATE, 'YYYYMMDD') > '20220501' THEN '출고대기'
         ELSE '출고미정'
    END AS 출고여부
FROM FOOD_ORDER
ORDER BY ORDER_ID

2022년 5월 1일을 기준으로 출고 여부를 조회하기 위해 CASE 절을 사용합니다.
CASE WHEN TO_CHAR(OUT_DATE, 'YYYYMMDD') <= '20220501' THEN '출고완료'
         WHEN TO_CHAR(OUT_DATE, 'YYYYMMDD') > '20220501' THEN '출고대기'
         ELSE '출고미정'
    END AS 출고여부
주문 ID를 기준으로 오름차순 정렬합니다.

대여 기록이 존재하는 자동차 리스트 구하기

SELECT
    H.CAR_ID
FROM (
    SELECT
        DISTINCT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE EXTRACT(MONTH FROM START_DATE) = 10
) H JOIN (
    SELECT
        CAR_ID
    FROM CAR_RENTAL_COMPANY_CAR
    WHERE CAR_TYPE = '세단'
) C ON H.CAR_ID = C.CAR_ID
ORDER BY H.CAR_ID DESC

10월에 대여 시작한 테이블을 조회하기 위해 서브 테이블 H를 생성합니다.
중복을 제거하기 위해 DISTINCT 키워드를 사용합니다.

(
    SELECT
        DISTINCT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE EXTRACT(MONTH FROM START_DATE) = 10
) H

자동차 종류가 세단인 자동차 데이터를 조회하기 위해 서브 테이블 C를 생성합니다.

(
    SELECT
        CAR_ID
    FROM CAR_RENTAL_COMPANY_CAR
    WHERE CAR_TYPE = '세단'
) C

자동차 ID를 기준으로 H 테이블과 C 테이블을 JOIN합니다.
FROM (...) H JOIN (...) C ON H.CAR_ID = C.CAR_ID
자동차 ID를 기준으로 내림차순 정렬합니다.

조건에 맞는 사용자 정보 조회하기

SELECT
    U.USER_ID,
    U.NICKNAME,
    CONCAT(CONCAT(CONCAT(CONCAT(CITY, ' '), STREET_ADDRESS1), ' '), STREET_ADDRESS2) AS 전체주소,
    CONCAT(CONCAT(CONCAT(SUBSTR(TLNO,0, 3), '-'), CONCAT(SUBSTR(TLNO, 4, 4), '-')), SUBSTR(TLNO, 8)) AS 전화번호
FROM USED_GOODS_USER U JOIN (
    SELECT
        WRITER_ID
    FROM USED_GOODS_BOARD
    GROUP BY WRITER_ID
    HAVING COUNT(*) >= 3
) B ON U.USER_ID = B.WRITER_ID
ORDER BY U.USER_ID DESC

중고 거래 게시물을 작성한 갯수를 구하기 위해 3건 이상 작성한 작성자 ID를 기준으로 그룹화한 서브 테이블 B를 생성합니다.

(
    SELECT
        WRITER_ID
    FROM USED_GOODS_BOARD
    GROUP BY WRITER_ID
    HAVING COUNT(*) >= 3
) B

USED_GOODS_USER 테이블과 USER_ID를 B.WRITER_ID를 기준으로 JOIN합니다.
FROM USED_GOODS_USER U JOIN (...) B ON U.USER_ID = B.WRITER_ID
전체 주소를 조회하기 위해 CITY와 도로명 주소 사이에 CONCAT 함수를 이용하여 띄어쓰기를 넣어줍니다.
CONCAT(CONCAT(CONCAT(CONCAT(CITY, ' '), STREET_ADDRESS1), ' '), STREET_ADDRESS2) AS 전체주소,
전화번호 11자 사이에 대쉬(-)를 넣어주기 위해 SUBSTR과 CONCAT 함수를 이용합니다.
SUBSTR(A, B, C) => A를 B부터 C자리까지 자름
SUBSTR(A, B) => A를 B이후 문자열
CONCAT(CONCAT(CONCAT(SUBSTR(TLNO,0, 3), '-'), CONCAT(SUBSTR(TLNO, 4, 4), '-')), SUBSTR(TLNO, 8)) AS 전화번호
USER_ID를 기준으로 내림차순 정렬합니다.

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

SELECT
    A.APNT_NO,
    P.PT_NAME,
    A.PT_NO,
    A.MCDP_CD,
    D.DR_NAME,
    A.APNT_YMD
FROM DOCTOR D JOIN (
    SELECT
        APNT_YMD,
        APNT_NO,
        PT_NO,
        MCDP_CD,
        MDDR_ID
    FROM APPOINTMENT
    WHERE APNT_CNCL_YN = 'N' AND TO_CHAR(APNT_YMD, 'YYYY-MM-DD') = '2022-04-13'
) A ON D.DR_ID = A.MDDR_ID JOIN PATIENT P ON A.PT_NO = P.PT_NO
WHERE A.MCDP_CD = 'CS'
ORDER BY A.APNT_YMD ASC

2022년 4월 13일에 취소되지 않은 진료예약을 구하기 위해 서브테이블 A를 생성합니다.

(
    SELECT
        APNT_YMD,
        APNT_NO,
        PT_NO,
        MCDP_CD,
        MDDR_ID
    FROM APPOINTMENT
    WHERE APNT_CNCL_YN = 'N' AND TO_CHAR(APNT_YMD, 'YYYY-MM-DD') = '2022-04-13'
) A

의사 ID와 환자 ID를 기준으로 JOIN합니다.
FROM DOCTOR D JOIN (...) A ON D.DR_ID = A.MDDR_ID JOIN PATIENT P ON A.PT_NO = P.PT_NO
흉부외과 데이터를 조회하기 위해 조건을 걸어줍니다.
WHERE A.MCDP_CD = 'CS'
진료 예약일시를 기준으로 오름차순 정렬합니다.