[JOIN/LEVEL4] 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기 (MySQL/Oracle)

MySQL

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

 

---

 

Oracle

SELECT * 
FROM 
    ( SELECT 
              A.CAR_ID   
            , A.CAR_TYPE
            , A.DAILY_FEE*30*((100-B.DISCOUNT_RATE)/100) AS FEE
        FROM   CAR_RENTAL_COMPANY_CAR A
             , CAR_RENTAL_COMPANY_DISCOUNT_PLAN B
        WHERE A.CAR_TYPE = B.CAR_TYPE
        AND A.CAR_ID NOT IN
                    (
                    SELECT DISTINCT CAR_ID
                    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                    WHERE (TO_CHAR(START_DATE,'YYYYMM') <= '202211') AND
                        (TO_CHAR(END_DATE,'YYYYMM') >= '202211')
                    )
AND B.DURATION_TYPE LIKE '%30일%'
)
    WHERE CAR_TYPE IN ('세단', 'SUV')
    AND FEE >= 500000 AND FEE < 2000000
ORDER BY FEE DESC, CAR_TYPE, CAR_ID DESC;