CAR_RENTAL_COMPANY_CAR 테이블에서 자동차 종류가 ‘SUV’인 자동차들의 평균 일일 대여 요금을 출력하는 SQL문을 작성해주세요. 이때 평균 일일 대여 요금은 소수 첫 번째 자리에서 반올림하고, 컬럼명은 AVERAGE_FEE 로 지정해주세요.
1
2
3
SELECT ROUND(AVG(DAILY_FEE), 0) AS AVERAGE_FEE
FROM CAR_RENTAL_COMPANY_CAR
WHERE CAR_TYPE = 'SUV';
2. 자동차 종류 별 특정 옵션이 포함된 자동차 수 구하기
CAR_RENTAL_COMPANY_CAR 테이블에서 ‘통풍시트’, ‘열선시트’, ‘가죽시트’ 중 하나 이상의 옵션이 포함된 자동차가 자동차 종류 별로 몇 대인지 출력하는 SQL문을 작성해주세요. 이때 자동차 수에 대한 컬럼명은 CARS로 지정하고, 결과는 자동차 종류를 기준으로 오름차순 정렬해주세요.
1
2
3
4
5
SELECT CAR_TYPE, COUNT(CAR_TYPE) AS CARS
FROM CAR_RENTAL_COMPANY_CAR
WHERE REGEXP\_LIKE\(OPTIONS, '열선시트|가죽시트|통풍시트')
GROUP BY CAR_TYPE
ORDER BY CAR_TYPE;
3. 자동차 대여 기록에서 장기/단기 대여 구분하기
CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 대여 시작일이 2022년 9월에 속하는 대여 기록에 대해서 대여 기간이 30일 이상이면 ‘장기 대여’ 그렇지 않으면 ‘단기 대여’ 로 표시하는 컬럼(컬럼명: RENT_TYPE)을 추가하여 대여기록을 출력하는 SQL문을 작성해주세요. 결과는 대여 기록 ID를 기준으로 내림차순 정렬해주세요.
1
2
3
4
5
6
7
8
9
10
11
SELECT HISTORY_ID, 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 EXTRACT(MONTH FROM START_DATE) = 9
ORDER BY HISTORY_ID DESC;
4. 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기
CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 대여 시작일을 기준으로 2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차들에 대해서 해당 기간 동안의 월별 자동차 ID 별 총 대여 횟수(컬럼명: RECORDS) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 월을 기준으로 오름차순 정렬하고, 월이 같다면 자동차 ID를 기준으로 내림차순 정렬해주세요. 특정 월의 총 대여 횟수가 0인 경우에는 결과에서 제외해주세요.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
WITH MONTH_HISTORY AS (
SELECT HISTORY_ID, CAR_ID, EXTRACT(MONTH FROM START_DATE) AS MONTH
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE EXTRACT(MONTH FROM START_DATE) BETWEEN 8 AND 10)
SELECT MONTH, CAR_ID, COUNT(*) as RECORDS
FROM MONTH_HISTORY
WHERE CAR_ID IN (
SELECT CAR_ID
FROM MONTH_HISTORY
GROUP BY CAR_ID
HAVING COUNT(*) >= 5)
GROUP BY MONTH, CAR_ID
ORDER BY MONTH, CAR_ID DESC;
5. 자동차 대여 기록 별 대여 금액 구하기
CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 ‘트럭’인 자동차의 대여 기록에 대해서 대여 기록 별로 대여 금액(컬럼명: FEE)을 구하여 대여 기록 ID와 대여 금액 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 대여 기록 ID를 기준으로 내림차순 정렬해주세요.
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT *
FROM(
SELECT CH.HISTORY_ID, AVG(CH.DAILY_FEE) * ROUND(MAX(CH.END_DATE - CH.START_DATE + 1), 0) * (100 - NVL(TO_NUMBER(MAX(DISCOUNT_RATE)), 0)) / 100 AS FEE
FROM (
SELECT C.CAR_ID, START_DATE, END_DATE, CAR_TYPE, HISTORY_ID, DAILY_FEE
FROM CAR_RENTAL_COMPANY_CAR C,
CAR_RENTAL_COMPANY_RENTAL_HISTORY H
WHERE C.CAR_ID = H.CAR_ID AND C.CAR_TYPE = '트럭') CH
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P
ON CH.END_DATE - CH.START_DATE + 1 >= TO_NUMBER(REGEXP_REPLACE(P.DURATION_TYPE, '[^0-9]'))
AND CH.CAR_TYPE = P.CAR_TYPE
GROUP BY CH.HISTORY_ID)
ORDER BY FEE DESC, HISTORY_ID DESC;
