#69. JOIN : 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기 | 프로그래머스 | SQL 고득점 Kit

문제

 

입력 코드

SELECT 
    CAR.CAR_ID, 
    CAR.CAR_TYPE,
    ROUND((CAR.DAILY_FEE * ((100 - DISCOUNT.DISCOUNT_RATE)/100) * 30)) AS FEE
FROM CAR_RENTAL_COMPANY_CAR AS CAR
    JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY AS RENT
    ON CAR.CAR_ID = RENT.CAR_ID
    JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS DISCOUNT
    ON CAR.CAR_TYPE = DISCOUNT.CAR_TYPE
WHERE CAR.CAR_ID NOT IN(
    SELECT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE END_DATE > '2022-11-01' AND START_DATE < '2022-12-01'
)
    AND CAR.CAR_TYPE IN ('세단', 'SUV')
    AND DISCOUNT.DURATION_TYPE = '30일 이상'
GROUP BY CAR.CAR_ID
HAVING (FEE >= 500000 AND FEE < 2000000)
ORDER BY FEE DESC, CAR_TYPE ASC, CAR_ID DESC;

 

코드 설명

#SELECT #ROUND #JOIN #NOT IN #SUBQUERY #GROUP BY #HAVING #ORDER BY

 

 

문제 출처

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