문제

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

정답

-- 코드를 입력하세요
SELECT AA.CAR_ID, BB.CAR_TYPE, BB.FEE FROM
(
    SELECT 
        B.CAR_ID CAR_ID, 
        MAX(START_DATE) START_DATE2,
        MAX(END_DATE) END_DATE2
    FROM 
        CAR_RENTAL_COMPANY_CAR A, 
        CAR_RENTAL_COMPANY_RENTAL_HISTORY B
    WHERE 1=1
        AND A.CAR_ID = B.CAR_ID
    GROUP BY B.CAR_ID
) AA
,(
    SELECT 
        A.CAR_ID AS CAR_ID
        ,A.CAR_TYPE AS CAR_TYPE
        ,(A.DAILY_FEE * (1-B.DISCOUNT_RATE*0.01) * 30) AS FEE
    FROM CAR_RENTAL_COMPANY_CAR A, CAR_RENTAL_COMPANY_DISCOUNT_PLAN B
    WHERE 1=1
        AND A.CAR_TYPE = B.CAR_TYPE
        AND A.CAR_TYPE IN ('세단', 'SUV')
        AND B.DURATION_TYPE = '30일 이상'
) BB
WHERE 1=1
    AND AA.CAR_ID = BB.CAR_ID
    AND ( AA.START_DATE2 > TO_DATE('20221130', 'YYYYMMDD') 
         OR AA.END_DATE2 < TO_DATE('20221101', 'YYYYMMDD') )
    AND BB.FEE >= 500000 AND BB.FEE < 2000000
ORDER BY FEE DESC, CAR_TYPE, CAR_ID DESC
;

결과

풀이

아래 두 테이블을 JOIN하면 되는 문제이다.

1.  2022년 11월 1일부터 2022년 11월 30일까지 대여 가능한 차동차 구하기

SELECT * FROM
(
    SELECT 
        B.CAR_ID CAR_ID, 
        MAX(START_DATE) START_DATE2,
        MAX(END_DATE) END_DATE2
    FROM 
        CAR_RENTAL_COMPANY_CAR A, 
        CAR_RENTAL_COMPANY_RENTAL_HISTORY B
    WHERE 1=1
        AND A.CAR_ID = B.CAR_ID
        AND A.CAR_TYPE IN ('세단', 'SUV')
    GROUP BY B.CAR_ID
) AA
WHERE 1=1
    AND ( AA.START_DATE2 > TO_DATE('20221130', 'YYYYMMDD') 
         OR AA.END_DATE2 < TO_DATE('20221101', 'YYYYMMDD') )
;

2. 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차 구하기

SELECT * FROM
(
    SELECT 
        A.CAR_ID AS CAR_ID
        ,A.CAR_TYPE AS CAR_TYPE
        ,(A.DAILY_FEE * (1-B.DISCOUNT_RATE*0.01) * 30) AS FEE
    FROM CAR_RENTAL_COMPANY_CAR A, CAR_RENTAL_COMPANY_DISCOUNT_PLAN B
    WHERE 1=1
        AND A.CAR_TYPE = B.CAR_TYPE
        AND A.CAR_TYPE IN ('세단', 'SUV')
        AND B.DURATION_TYPE = '30일 이상'
) BB
WHERE 1=1
    AND BB.FEE >= 500000 AND BB.FEE < 2000000
;