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

문제

MYSQL

내가 작성한 정답

SELECT C.CAR_ID, C.CAR_TYPE, FLOOR(C.DAILY_FEE*30*(100-D.DISCOUNT_RATE)*0.01) FEE
FROM CAR_RENTAL_COMPANY_CAR C
JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN D
    ON C.CAR_TYPE=D.CAR_TYPE AND DURATION_TYPE = '30일 이상' AND C.CAR_TYPE IN ('세단', 'SUV')
WHERE C.CAR_ID NOT IN (SELECT CAR_ID
                    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                    WHERE START_DATE <= '2022-11-30' AND END_DATE >= '2022-11-01')
AND (C.DAILY_FEE*30*(100-D.DISCOUNT_RATE)*0.01 >= 500000
    AND C.DAILY_FEE*30*(100-D.DISCOUNT_RATE)*0.01 < 2000000)
ORDER BY 3 DESC, 2, 1 DESC;

ORACLE

내가 작성한 정답

SELECT C.CAR_ID, C.CAR_TYPE, FLOOR(DAILY_FEE*30*(100-DISCOUNT_RATE)*0.01) FEE
FROM CAR_RENTAL_COMPANY_CAR C
JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P 
ON C.CAR_TYPE = P.CAR_TYPE AND P.DURATION_TYPE LIKE '%30%' AND C.CAR_TYPE IN ('세단','SUV')
WHERE CAR_ID NOT IN (SELECT CAR_ID 
                     FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                     WHERE TO_CHAR(START_DATE,'YYMM')<='2211' 
                        AND TO_CHAR(END_DATE,'YYMM')>='2211')
    AND (DAILY_FEE*30*(100-DISCOUNT_RATE)*0.01 >= 500000 AND DAILY_FEE*30*(100-DISCOUNT_RATE)*0.01 <2000000)
ORDER BY 3 DESC, 2, 1 DESC;