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

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

프로그래머스

 

SELECT C.CAR_ID,C.CAR_TYPE, round(  30*C.DAILY_FEE*(1-P.DISCOUNT_RATE*0.01)  ) as FEE
from CAR_RENTAL_COMPANY_CAR AS C
join CAR_RENTAL_COMPANY_RENTAL_HISTORY AS H on C.CAR_ID =H.CAR_ID 
join CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS P on C.CAR_TYPE =P.CAR_TYPE 
where P.DURATION_TYPE='30일 이상'
    and (C.CAR_TYPE='SUV' or C.CAR_TYPE='세단')
    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')
group by C.CAR_ID
having FEE >=500000 and FEE <2000000
order by FEE desc, C.CAR_TYPE, C.CAR_ID desc