문제풀이
평균 일일 대여 요금 구하기
-- 코드를 입력하세요
SELECT ROUND(AVG(DAILY_FEE),0) AS AVERAGE_FEE
FROM CAR_RENTAL_COMPANY_CAR
WHERE CAR_TYPE = 'SUV'< 풀이 과정 >
차량 종류가 SUV인 상황에서의 평균 일일 요금을 구하고자 하므로 소수점을 제거하기 위해 ROUND를 활용하여 결과 SELECT
조건에 맞는 도서 리스트 출력하기
SELECT BOOK_ID, LEFT(PUBLISHED_DATE, 10)
FROM BOOK
WHERE YEAR(PUBLISHED_DATE) = 2021 AND CATEGORY = '인문'
ORDER BY PUBLISHED_DATE< 풀이 과정 >
출판일이 2021년이고, CATEGORY가 인문인 도서 ID와 출판일을 출력하는 문제
대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기
SELECT MONTH(START_DATE) AS MONTH, CAR_ID, COUNT(HISTORY_ID) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE CAR_ID IN (
SELECT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE (DATE_FORMAT(START_DATE, '%Y-%m') BETWEEN '2022-08' AND '2022-10')
GROUP BY CAR_ID
HAVING COUNT(CAR_ID) >= 5) AND (DATE_FORMAT(START_DATE, '%Y-%m') BETWEEN '2022-08' AND '2022-10')
GROUP BY MONTH, CAR_ID
HAVING RECORDS > 0
ORDER BY MONTH, CAR_ID DESC
< 풀이 과정 >
1. START_DATE를 기준으로 8~10월로 커트하기 위해 MONTH를 지정해주고 CAR_ID, HISTORY_ID 개수를 기준으로 RECORDS를 SELECT한다.
2. 22년 8월~10월 사이에 CAR_ID 개수 합이 5이상인 경우를 필터하기 위해 서브쿼리를 사용한다.
3. MONTH와 CAR_ID 기준 그룹핑 해주고 월 대여 횟수 0보다 큰 경우만 필터링 해주고 MONTH 오름차순, CAR_ID 내림차순 정렬하여 리턴
자동차 종류 별 특정 옵션이 포함된 자동차 수 구하기
SELECT CAR_TYPE, COUNT(CAR_ID) AS CARS
FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS LIKE '%시트%'
GROUP BY CAR_TYPE
ORDER BY CAR_TYPE< 풀이 과정 >
시트라는 단어가 들어간 OPTION에서 CAR_TYPE 기준 그룹핑하여 SELECT 진행!
자동차 대여 기록에서 장기/단기 대여 구분하기
SELECT HISTORY_ID, CAR_ID,
DATE_FORMAT(START_DATE,"%Y%-%m-%d") as START_DATE,
DATE_FORMAT(END_DATE,"%Y-%m-%d") as END_DATE,
IF(DATEDIFF(END_DATE, START_DATE) >= 29, '장기 대여', '단기 대여') AS RENT_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
where DATE_FORMAT(START_DATE,"%Y-%m") LIKE '2022-09'
ORDER BY HISTORY_ID DESC< 풀이 과정 >
문제에서의 핵심은 START_DATE를 포함해 1일로 시작하는 것이므로 END_DATE - START_DATE를 빼서 29일 이상인 경우를 장기 대여로, 이외는 단기 대여로 만들어서 SELECT 진행해주는 것이 핵심
자동차 대여 기록 별 대여 금액 구하기
# 1번 풀이
SELECT HISTORY_ID,
ROUND(CASE
WHEN HISTORY.DURATION < 7 THEN REN.DAILY_FEE * HISTORY.DURATION
WHEN HISTORY.DURATION < 30 THEN REN.DAILY_FEE * HISTORY.DURATION * (SELECT 1 - DISCOUNT_RATE/100 FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN WHERE CAR_TYPE = '트럭' AND DURATION_TYPE = '7일 이상')
WHEN HISTORY.DURATION < 90 THEN REN.DAILY_FEE * HISTORY.DURATION * (SELECT 1 - DISCOUNT_RATE/100 FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN WHERE CAR_TYPE = '트럭' AND DURATION_TYPE = '30일 이상')
ELSE REN.DAILY_FEE * HISTORY.DURATION * (SELECT 1 - DISCOUNT_RATE/100 FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN WHERE CAR_TYPE = '트럭' AND DURATION_TYPE = '90일 이상') END) FEE
FROM (SELECT HISTORY_ID,CAR_ID,(DATEDIFF(END_DATE,START_DATE)+1) AS DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY) HISTORY
JOIN CAR_RENTAL_COMPANY_CAR REN
ON HISTORY.CAR_ID = REN.CAR_ID
WHERE REN.CAR_TYPE = '트럭'
ORDER BY FEE DESC, HISTORY_ID DESC< 풀이 과정 >
대여 기록 별 금액을 측정하도록 CASE ~ WHEN THEN ELSE를 이용하여 표현하였고, FROM으로 CAR_RENTAL_COMPANY_RENTAL_HISTORY에서 필요한 컬럼만 추출하여 계산을 진행하고 이외의 테이블과 JOIN하여 결과를 출력하는 방법.
# 2번 풀이
SELECT HISTORY_ID, ROUND((DAILY_FEE * (100 - IFNULL(DISCOUNT_RATE, 0))/100) * PERIOD) AS FEE
FROM
(SELECT CAR.CAR_ID, CAR.CAR_TYPE, DAILY_FEE, HISTORY_ID, DATEDIFF(END_DATE, START_DATE)+1 AS PERIOD,
CASE WHEN DATEDIFF(END_DATE, START_DATE)+1 >= 90 THEN '90일 이상'
WHEN DATEDIFF(END_DATE, START_DATE)+1 >= 30 THEN '30일 이상'
WHEN DATEDIFF(END_DATE, START_DATE)+1 >= 7 THEN '7일 이상'
ELSE '7일 미만'
END AS DURATION_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS HISTORY
INNER JOIN CAR_RENTAL_COMPANY_CAR AS CAR ON CAR.CAR_ID = HISTORY.CAR_ID) AS PERIOD_TABLE
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS PLAN ON PERIOD_TABLE.CAR_TYPE = PLAN.CAR_TYPE
AND PERIOD_TABLE.DURATION_TYPE = PLAN.DURATION_TYPE
WHERE PERIOD_TABLE.CAR_TYPE = '트럭'
ORDER BY FEE DESC, HISTORY_ID DESC
< 풀이 과정 >
FROM 절로 새로운 테이블을 생성하고 이를 기존 주어진 테이블과 조인하여 트럭인 것만 추출하여 결과 리턴
