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

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

• 문제 풀이

SELECT C.CAR_ID, C.CAR_TYPE, (C.DAILY_FEE * 30) * (100 - 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 1 = 1 
AND CAR_ID NOT IN (
                  SELECT CAR_ID 
                  FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                  WHERE END_DATE > TO_DATE(20221101, 'YYYY-MM-DD'))
AND C.CAR_TYPE IN ('세단', 'SUV')
AND P.DURATION_TYPE = '30일 이상'
AND (C.DAILY_FEE * 30) * (100 - P.DISCOUNT_RATE) / 100 >= 500000 
AND (C.DAILY_FEE * 30) * (100 - P.DISCOUNT_RATE) / 100 < 2000000
ORDER BY FEE DESC, C.CAR_TYPE, C.CAR_ID DESC;

SELECT CAR_ID, C.CAR_TYPE, (DAILY_FEE * 30) * (100-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 AND P.DURATION_TYPE = '30일 이상'
WHERE CAR_ID NOT IN (
    SELECT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE TO_CHAR(END_DATE, 'YYYY-MM-DD') > '2022-11-01'
)
AND C.CAR_TYPE IN ('세단', 'SUV')
AND (DAILY_FEE * 30) * (100-DISCOUNT_RATE) / 100 >= 500000
AND (DAILY_FEE * 30) * (100-DISCOUNT_RATE) / 100 < 2000000
ORDER BY 3 DESC, 2, 1 DESC

SELECT
    CAR_ID,
    C.CAR_TYPE,
    (DAILY_FEE * 30) * (100 - 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
    P.DURATION_TYPE = '30일 이상'
    AND CAR_ID NOT IN (
        SELECT CAR_ID
        FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
        WHERE TO_CHAR(END_DATE, 'YYYY-MM-DD') > '2022-11-01'
    )
    AND C.CAR_TYPE IN ('세단', 'SUV')
    AND (DAILY_FEE * 30) * (100 - DISCOUNT_RATE) / 100 >= 500000
    AND (DAILY_FEE * 30) * (100 - DISCOUNT_RATE) / 100 < 2000000
ORDER BY
    FEE DESC,
    C.CAR_TYPE,
    CAR_ID DESC;

• 오답

• 실행 결과