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

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

SELECT a.APNT_NO, c.PT_NAME, c.PT_NO, a.MCDP_CD, b.DR_NAME, a.APNT_YMD
FROM APPOINTMENT a JOIN DOCTOR b ON a.MDDR_ID = b.DR_ID
    JOIN PATIENT c ON a.PT_NO = c.PT_NO
WHERE TO_CHAR(APNT_YMD, 'YYYY-MM-DD') = '2022-04-13' AND APNT_CNCL_YN = 'N'
ORDER BY 6

 

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

SELECT ORDER_ID, PRODUCT_ID, TO_CHAR(OUT_DATE, 'YYYY-MM-DD') AS OUT_DATE, 
    CASE WHEN TO_CHAR(OUT_DATE, 'MM-DD') < '05-02' THEN '출고완료' 
        WHEN OUT_DATE IS NULL THEN '출고미정'
        ELSE '출고대기' END AS "출고여부"
FROM FOOD_ORDER
ORDER BY 1

 

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

SELECT HISTORY_ID, 
    min(DAILY_FEE * (100 - TO_NUMBER(RTRIM(DISCOUNT_RATE, '%'))) * 0.01 * DATES) AS FEE
FROM (
    SELECT HISTORY_ID, DAILY_FEE, DATES, 
        DECODE(DURATION_TYPE, DURATION, DISCOUNT_RATE, 0) AS DISCOUNT_RATE
    FROM (
        SELECT HISTORY_ID, CAR_ID, (END_DATE - START_DATE + 1) AS DATES,
            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 '7일 미만' END AS DURATION
        FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    ) a JOIN CAR_RENTAL_COMPANY_CAR b ON a.CAR_ID = b.CAR_ID
        JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN c ON b.CAR_TYPE = c.CAR_TYPE
    WHERE c.CAR_TYPE = '트럭' 
)
GROUP BY HISTORY_ID
ORDER BY 2 DESC, 1 DESC

 

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

SELECT DISTINCT a.CAR_ID
FROM CAR_RENTAL_COMPANY_CAR a JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY b 
    ON a.CAR_ID = b.CAR_ID
WHERE a.CAR_TYPE = '세단' AND TO_CHAR(b.START_DATE, 'MM') = '10' 
ORDER BY 1 DESC

 

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

SELECT CAR_ID, ROUND(avg(END_DATE - START_DATE + 1), 1) AS AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
HAVING avg(END_DATE - START_DATE + 1) > 6
ORDER BY 2 DESC, 1 DESC

 

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

SELECT *
FROM CAR_RENTAL_COMPANY_CAR 
WHERE OPTIONS LIKE '%네비게이션%'
ORDER BY 1 DESC

 

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

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 > 28 THEN '장기 대여' ELSE '단기 대여' END AS "RENT_TYPE"
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE TO_CHAR(START_DATE, 'YYYY-MM') = '2022-09'
ORDER BY 1 DESC

 

루시와 엘라 찾기

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

 

이름에 el이 들어가는 동물 찾기

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS 
WHERE ANIMAL_TYPE = 'Dog' AND (NAME LIKE 'El%' or NAME LIKE '%el%')
ORDER BY 2

 

중성화 여부 파악하기

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 1

 

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

SELECT ANIMAL_ID, NAME
FROM (
    SELECT a.ANIMAL_ID, a.NAME, rank() over (ORDER BY b.DATETIME - a.DATETIME DESC) AS RANK
    FROM ANIMAL_INS a JOIN ANIMAL_OUTS b ON a.ANIMAL_ID = b.ANIMAL_ID
)
WHERE RANK < 3

 

카테고리 별 상품 개수 구하기

SELECT SUBSTR(PRODUCT_CODE, 1, 2) AS CATEGORY, count(*) AS PRODUCTS
FROM PRODUCT
GROUP BY SUBSTR(PRODUCT_CODE, 1, 2)
ORDER BY 1

 

DATETIME에서 DATE로 형 변환

SELECT ANIMAL_ID, NAME, TO_CHAR(DATETIME, 'YYYY-MM-DD') AS "날짜"
FROM ANIMAL_INS
ORDER BY 1