sql 문제풀이 4

문제 

 

with car_with_discount as -- with ~ as를 이용한 풀이
    (select c.car_id, c.car_type,
    round(c.daily_fee * 30 * (1 - p.discount_rate / 100), 0) as 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 = '30일 이상')

select t.car_id, t.car_type, t.fee
from car_with_discount t
where t.car_type in ('세단', 'SUV')
    and t.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 t.fee between 500000 and 1999999
order by
    t.fee desc, t.car_type asc, t.car_id desc;

select t.car_id, t.car_type, t.fee -- 인라인뷰를 이용한 풀이
from
    (select c.car_id, c.car_type,
     round(c.daily_fee * 30 * (1 - p.discount_rate / 100), 0) as 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 = '30일 이상')
     as t
where t.car_type in ('세단', 'SUV')
        and t.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 t.fee between 500000 and 1999999
order by
    t.fee desc, t.car_type asc, t.car_id desc;