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

제가 공부한 내용을 정리하는 블로그입니다.
아직 많이 부족하고 배울게 너무나도 많습니다. 틀린내용이 있으면 언제나 가감없이 말씀해주시면 감사하겠습니다😁
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'
• 진료 예약일시를 기준으로 오름차순 정렬합니다.