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

너무 길잖아...

---

CREATE 문

CREATE TABLE CAR_RENTAL_COMPANY_CAR (
CAR_ID			NUMBER 			NOT NULL,
CAR_TYPE		VARCHAR(255)		NOT NULL,
DAILY_FEE		NUMBER 			NOT NULL,
OPTIONS			VARCHAR(255)		NOT NULL
);


CREATE TABLE CAR_RENTAL_COMPANY_RENTAL_HISTORY (
HISTORY_ID		NUMBER			NOT NULL,
CAR_ID			NUMBER			NOT NULL,
START_DATE		DATE			NOT NULL,
END_DATE		DATE			NOT NULL
);


CREATE TABLE CAR_RENTAL_COMPANY_DISCOUNT_PLAN (
PLAN_ID			NUMBER 			NOT NULL,
CAR_TYPE		VARCHAR(255)		NOT NULL,
DURAION_TYPE		VARCHAR(255)		NOT NULL,
DISCOUNT_RATE		NUMBER			NOT NULL,
);

 

---

• 내 풀이

-- 코드를 입력하세요
SELECT
	C.CAR_ID,
	C.CAR_TYPE,
	(C.DAILY_FEE * 30 - C.DAILY_FEE * 30 *0.01 * P.DISCOUNT_RATE) AS FEE
FROM CAR_RENTAL_COMPANY_CAR C INNER JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P
    ON C.CAR_TYPE = P.CAR_TYPE
AND C.CAR_TYPE IN ('세단' , 'SUV')
AND P.DURATION_TYPE = '30일 이상'
AND C.CAR_ID NOT IN 
    (SELECT DISTINCT CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
        WHERE TO_CHAR(START_DATE, 'YYYYMM') <='202211'       
        AND TO_CHAR(START_DATE, 'YYYYMM') >='202211'
    )
AND (C.DAILY_FEE * 30 - C.DAILY_FEE * 30 *0.01 * P.DISCOUNT_RATE) BETWEEN 500000 AND 1999999
ORDER BY C.DAILY_FEE DESC , C.CAR_TYPE ASC , C.CAR_ID DESC;