특정 기간동안 대여 가능한 자동차들의 대여비용 구하기 (JOIN - LEVEL4)
예시
CAR_RENTAL_COMPANY_CAR 테이블
CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블
CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블이 다음과 같다면
자동차 종류가 '세단' 또는 'SUV' 인 자동차 중 2022년 11월 1일 부터 2022년 11월 30일까지 대여가능한 자동차는 자동차 ID가 1, 4, 5인 자동차이다.
일일 대여 요금에 자동차 종류 별 대여기간이 30일 이상인 경우의 할인율을 적용하여 30일간의 대여 금액을 구하면,
- 자동차 ID가 1인 경우, 일일 대여 금액 25,000원에서 8% 할인율을 적용하고 30일을 곱하면 총 대여 금액은 690,000원
- 자동차 ID가 4인 경우, 일일 대여 금액 12,000원에서 10% 할인율을 적용하고 30일을 곱하면 총 대여 금액은 324,000원
- 자동차 ID가 5인 경우, 일일 대여 금액 22,000원에서 10% 할인율을 적용하고 30일을 곱하면 총 대여 금액은 621,000원이고, 대여 금액이 50만원 이상 200만원 미만인 경우에 대해서 대여 금액을 기준으로 내림차순, 자동차 종류를 기준으로 오름차순 및 자동차 ID를 기준으로 내림차순 정렬한다.
위의 내용 기반으로 실행 결과는 다음과 같아야 한다.
- FEE의 경우 예시처럼 정수부분만 출력되어야 한다.(소수점 X)
SQL문 - MySQL
SELECT C.CAR_ID as CAR_ID, -- 자동차 ID
C.CAR_TYPE as CAR_TYPE, -- 자동차 종류
-- 대여료 x 30일 x 내야할 비율 = 요금
-- (100 - 할인률)/100 = 내야할 비율
ROUND(C.DAILY_FEE*30*(100-P.DISCOUNT_RATE)/100) AS FEE -- 지불 비용
FROM CAR_RENTAL_COMPANY_CAR C
-- C가 각각 연결될 수 있기 때문에 C를 기준으로 나머지 두 테이블을 연결한다.
JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY H ON C.CAR_ID = H.CAR_ID -- CAR_ID기준으로
JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P ON C.CAR_TYPE = P.CAR_TYPE -- CAR_TYPE기준으로
-- 대여 가능한 2022-11-01 ~ 2022-12-01에 대여가 가능한 자동차를 목록을 가져와야하므로
-- NOT IN을 써서 해당 기간에 렌탈 기록이 없는 CAR_ID를 가져와야 한다
WHERE C.CAR_ID NOT IN (
SELECT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE END_DATE >= '2022-11-01' AND START_DATE <= '2022-12-01'
) AND P.DURATION_TYPE like '30%' -- 그리고 대여 기간이 30일 이상인 것을 검색해야하므로
GROUP BY C.CAR_ID -- 자동차 ID 기준으로 그룹화하여
HAVING C.CAR_TYPE IN ('세단', 'SUV') -- 자동차 종류가 세단과 SUV인 것만
AND (FEE >= 500000 AND FEE < 2000000) -- 30일간의 대여 금액이 50만원 200만원 미만인 자동차
ORDER BY FEE DESC, CAR_TYPE, CAR_ID DESC
- 다시 말해 렌탈 기록에 EndDate >= 2022-11-01 and StartDate <= 2022-12-01의 기록이 있는 CAR_ID가 있다면 지금 그 기간동안 렌탈을 하고 있다는 뜻이므로 대여가 불가능하다.
- 즉, Not In을 쓰인 이유는 그 목록에 없어야 대여가 가능하기 때문이다.
