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

Lv4. 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기 (MySQL)

링크: https://school.programmers.co.kr/learn/courses/30/lessons/157339

 

내가 작성한 코드

SELECT C.CAR_ID, C.CAR_TYPE, FLOOR((C.DAILY_FEE * 30 * (1 - P.DISCOUNT_RATE / 100))) 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 P.DURATION_TYPE = '30일 이상' 
  AND 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')
HAVING FEE BETWEEN 500000 AND 1999999
ORDER BY FEE DESC, C.CAR_TYPE ASC, C.CAR_ID DESC;

 

Oracle

-- 오라클은 SELECT문이 다 실행되어야 FEE라는 이름을 인식함. 따라서 인라인뷰로 FEE를 미리 계산해두고 메인 쿼리에서 FEE를 가져와 50만 이상인지 검사
SELECT * 
FROM (SELECT C.CAR_ID, C.CAR_TYPE, TRUNC(C.DAILY_FEE * 30 * (1 - P.DISCOUNT_RATE / 100)) 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 P.DURATION_TYPE = '30일 이상'
       AND C.CAR_ID NOT IN (SELECT CAR_ID
                            FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                            WHERE START_DATE <= TO_DATE('2022-11-30', 'YYYY-MM-DD') AND END_DATE >= TO_DATE('2022-11-01' , 'YYYY-MM-DD')))
WHERE FEE >= 500000 AND FEE < 2000000
ORDER BY FEE DESC, CAR_TYPE ASC, CAR_ID DESC;