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

[문제]
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를 기준으로 내림차순 정렬해주세요.

CAR_RENTAL_COMPANY_CAR : 자동차들의 정보를 담은 테이블 (CAR)

CAR_RENTAL_COMPANY_RENTAL_HISTORY : 자동차 대여 기록 정보 (HISTORY)

CAR_RENTAL_COMPANY_DISCOUNT_PLAN : 자동차 종류 별 대여기간 종류 별 할인 정책 (PLAN)

 

자동차 ID, 자동차 종류, 대여 금액(컬럼명: FEE) 을 출력

대여금액 = 1일 가격 * 기간 * 종류별기간별 할인율

 

[단계별 풀이]

1. CAR 테이블과 HISTORY 테이블을 CAR_ID 기준으로 JOIN 하는데 추가 조건을 걸어준다

2. 11월 사이에 대여 기록이 있는 행들만 필터링해서 LEFT JOIN 한다

 

3. 두 테이블을 JOIN 하면 다음 PLAN 테이블도 CAR_TYPE을 기준으로 JOIN 하는데 역시 추가조건을 걸어준다.

4. DURATION_TYPE이 30일 이상인 행들만 필터링해서 INNER JOIN 한다

 

5. HISTORY 테이블의 CAR_ID가 NULL인 행들만 필터링한다 (11월에 대여기록이 있는 차량들)

6.CAR_TYPE이 SUV이거나 세단인 행들만 필터링한다

 

7.  CAR_ID와 CAR_TYPE, 그리고 대여금액을 구하는데 대여금액은 DAILY_FEE에 30을 곱하고 할인율을 곱한 값이다. 해당 컬럼을 FEE로 별칭지어준다

8. MYSQL에서는 SELECT 문의 별칭을 HAVING 조건절에 쓸 수 있다. 이를 살려 HAVING 조건절에서 FEE가 50만이상이고 200만 미만인 행들을 필터링 한다.

9. 대여금액을 내림차순, 자동차 종류를 오름차순, 자동차 ID를 내림차순 정렬한다.

[정답]

SELECT A.CAR_ID,
       A.CAR_TYPE,
       ROUND(A.DAILY_FEE * 30 * (1-C.DISCOUNT_RATE/100)) AS FEE
FROM CAR_RENTAL_COMPANY_CAR  A
LEFT JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY B
    ON A.CAR_ID = B.CAR_ID
        AND B.START_DATE <= '2022-11-30'
        AND B.END_DATE >= '2022-11-01'
JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN C
    ON A.CAR_TYPE = C.CAR_TYPE
        AND C.DURATION_TYPE = '30일 이상'
WHERE B.CAR_ID IS NULL
AND A.CAR_TYPE IN ('SUV', '세단')
HAVING 500000 <= FEE AND FEE < 2000000
ORDER BY FEE DESC, CAR_TYPE ASC, CAR_ID DESC