[Oracle] 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기

문제 

CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '세단' 또는 'SUV' 인 자동차 중 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능하고 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차에 대해서 자동차 ID, 자동차 종류, 대여 금액(컬럼명: FEE) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 자동차 종류를 기준으로 오름차순 정렬, 자동차 종류까지 같은 경우 자동차 ID를 기준으로 내림차순 정렬해주세요.

 

Pseudo Code

1. 자동차의 ID, 종류, 할인율이 필요하므로 CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블을 JOIN을 걸어준다.
2. 2022년 11월 1일부터 11월 30일까지 대여 가능한 자동차를 찾기 위해 WHERE절에서 NOT EXISTS 서브쿼리를 사용한다. 필요한 날짜가 있는 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 대여기록을 조회한다.
3. 자동차 종류가 '세단' 또는 'SUV' 이며, 대여기간이 30일 이상이고, 대여 금액이 500,000원 이상 2,000,000원 미만인 자동차만 가져온다.
4. FEE는 DAILY_FEE에 대여기간(30일)을 곱하고 할인율을 적용하여 계산한다.
5. FEE 기준으로 으로 내림차순, 자동차 종류 기준으로 오름차순, 자동차 ID 기준으로 내림차순으로 정렬한다.

 

내 코드

SELECT
    C.CAR_ID,
    C.CAR_TYPE,
    ROUND(DAILY_FEE*(1-DISCOUNT_RATE/100)*30) AS fee
FROM CAR_RENTAL_COMPANY_CAR C
JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P
ON C.CAR_TYPE = P.CAR_TYPE
WHERE C.CAR_TYPE IN ('세단', 'SUV')
AND NOT EXISTS (
	SELECT 1
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H
    WHERE C.CAR_ID = H.CAR_ID
    AND H.END_DATE >= TO_DATE('20221101', 'YYYYMMDD')
    AND H.START_DATE <= TO_DATE('20221130', 'YYYYMMDD')
    )
AND P.DURATION_TYPE = '30일 이상'
AND DAILY_FEE*(1-DISCOUNT_RATE/100)*30 BETWEEN 500000 AND 2000000
ORDER BY FEE DESC, C.CAR_TYPE ASC, C.CAR_ID DESC

    👉🏻 NOT EXISTS는 단순히 조건비교의 역할만 하기 때문에 SELECT절에 아무값이 들어가도 상관없다. 나는 그냥 1로 적어줌

 

WHERE 절에서 NOT EXISTS를 사용해준 이유

대여기간이 겹치는 경우 해당 자동차를 제외해주기 위해 NOT EXISTS를 사용해주는 것이다.NOT EXISTS 절은 CAR_RENTAL_COMPANY_CAR 테이블에서 가져온 자동차 정보 중에서, 대여기간이 지정된 기간과 겹치는 렌탈 기록이 있는 자동차를 제외하도록 하는 역할을 하기 때문에 현재 대여 가능한 자동차 정보만 가져올 수 있다.