[프로그래머스/오라클] 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기 - ORACLE

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

프로그래머스

 

SELECT DISTINCT C.CAR_ID, C.CAR_TYPE, round((100-p.discount_rate)/100*30*c.daily_fee,0) AS FEE FROM 
CAR_RENTAL_COMPANY_CAR C
JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY H
ON C.CAR_ID = H.CAR_ID
JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P
ON C.CAR_TYPE = P.CAR_TYPE
WHERE (C.CAR_TYPE = '세단' or C.CAR_TYPE = 'SUV') and P.DURATION_TYPE ='30일 이상'
      and ((100-p.discount_rate)/100)*30*c.daily_fee between 500000 and 2000000
      and C.CAR_ID NOT IN (
          SELECT H.CAR_ID 
          FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H
          WHERE 20221101 between (TO_CHAR(START_DATE,'YYYYMMDD')) AND (TO_CHAR(END_DATE,'YYYYMMDD'))
                OR 20221130 BETWEEN (TO_CHAR(START_DATE,'YYYYMMDD')) AND (TO_CHAR(END_DATE,'YYYYMMDD')))
ORDER BY 3 DESC, 2, 1 DESC;