왕행님의 개발블로그

문제

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

정답

-- 코드를 입력하세요
SELECT AA.CAR_ID, BB.CAR_TYPE, BB.FEE FROM
(
    SELECT 
        B.CAR_ID CAR_ID, 
        MAX(START_DATE) START_DATE2,
        MAX(END_DATE) END_DATE2
    FROM 
        CAR_RENTAL_COMPANY_CAR A, 
        CAR_RENTAL_COMPANY_RENTAL_HISTORY B
    WHERE 1=1
        AND A.CAR_ID = B.CAR_ID
    GROUP BY B.CAR_ID
) AA
,(
    SELECT 
        A.CAR_ID AS CAR_ID
        ,A.CAR_TYPE AS CAR_TYPE
        ,(A.DAILY_FEE * (1-B.DISCOUNT_RATE*0.01) * 30) AS FEE
    FROM CAR_RENTAL_COMPANY_CAR A, CAR_RENTAL_COMPANY_DISCOUNT_PLAN B
    WHERE 1=1
        AND A.CAR_TYPE = B.CAR_TYPE
        AND A.CAR_TYPE IN ('세단', 'SUV')
        AND B.DURATION_TYPE = '30일 이상'
) BB
WHERE 1=1
    AND AA.CAR_ID = BB.CAR_ID
    AND ( AA.START_DATE2 > TO_DATE('20221130', 'YYYYMMDD') 
         OR AA.END_DATE2 < TO_DATE('20221101', 'YYYYMMDD') )
    AND BB.FEE >= 500000 AND BB.FEE < 2000000
ORDER BY FEE DESC, CAR_TYPE, CAR_ID DESC
;

결과

풀이

아래 두 테이블을 JOIN하면 되는 문제이다.

1.  2022년 11월 1일부터 2022년 11월 30일까지 대여 가능한 차동차 구하기

SELECT * FROM
(
    SELECT 
        B.CAR_ID CAR_ID, 
        MAX(START_DATE) START_DATE2,
        MAX(END_DATE) END_DATE2
    FROM 
        CAR_RENTAL_COMPANY_CAR A, 
        CAR_RENTAL_COMPANY_RENTAL_HISTORY B
    WHERE 1=1
        AND A.CAR_ID = B.CAR_ID
        AND A.CAR_TYPE IN ('세단', 'SUV')
    GROUP BY B.CAR_ID
) AA
WHERE 1=1
    AND ( AA.START_DATE2 > TO_DATE('20221130', 'YYYYMMDD') 
         OR AA.END_DATE2 < TO_DATE('20221101', 'YYYYMMDD') )
;

2. 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차 구하기

SELECT * FROM
(
    SELECT 
        A.CAR_ID AS CAR_ID
        ,A.CAR_TYPE AS CAR_TYPE
        ,(A.DAILY_FEE * (1-B.DISCOUNT_RATE*0.01) * 30) AS FEE
    FROM CAR_RENTAL_COMPANY_CAR A, CAR_RENTAL_COMPANY_DISCOUNT_PLAN B
    WHERE 1=1
        AND A.CAR_TYPE = B.CAR_TYPE
        AND A.CAR_TYPE IN ('세단', 'SUV')
        AND B.DURATION_TYPE = '30일 이상'
) BB
WHERE 1=1
    AND BB.FEE >= 500000 AND BB.FEE < 2000000
;

문제

https://school.programmers.co.kr/learn/courses/30/lessons/133027?language=oracle

정답

-- 아이스크림 가게의 상반기 주문 정보를 담은 FIRST_HALF
-- 7월의 아이스크림 주문 정보를 담은 JULY
-- (7월 아이스크림 총 주문량 + 상반기의 아이스크림 총 주문량) 
-- 상위 3개의 맛을 조회하는 SQL 문을 작성해주세요.
SELECT B.FLAVOR
FROM (
SELECT A.FLAVOR, SUM(A.TOTAL_ORDER) AS TOTAL_ORDER FROM (
SELECT FLAVOR, TOTAL_ORDER FROM FIRST_HALF
    UNION 
SELECT FLAVOR, TOTAL_ORDER FROM JULY  
) A
GROUP BY A.FLAVOR
ORDER BY TOTAL_ORDER DESC) B
WHERE ROWNUM < 4

• UNION을 하면 중복값 제외, UNION ALL 을 쓰면 중복값 포함이다
• GROUP BY 된 채로 ORDER BY를 사용할 수 있다!

SELECT A.FLAVOR, SUM(A.TOTAL_ORDER) AS TOTAL_ORDER FROM (
SELECT FLAVOR, TOTAL_ORDER FROM FIRST_HALF
    UNION 
SELECT FLAVOR, TOTAL_ORDER FROM JULY  
) A
GROUP BY A.FLAVOR
ORDER BY TOTAL_ORDER DESC

문제

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

정답

-- 코드를 입력하세요
SELECT 
	history_id as HISTORY_ID
	, car_id as CAR_ID
	, to_char(start_date, 'yyyy-mm-dd') as START_DATE
	, to_char(end_date, 'yyyy-mm-dd') as END_DATE
	, case when (end_date - start_date+1)>=30
		then '장기 대여'
		else '단기 대여'
	  end as RENT_TYPE
from car_rental_company_rental_history
where 
	start_date >= to_date('20220901', 'yyyy-mm-dd') 
	and start_date < to_date('20221001', 'yyyy-mm-dd')
order by history_id desc;