[프로그래머스 스쿨 - MYSQL] 자동차 문제 Lv.1 ~Lv.3

LV.1 특정 옵션이 포함된 자동차 리스트 구하기

 

문제

CAR_RENTAL_COMPANY_CAR 테이블에서 '네비게이션' 옵션이 포함된

자동차 리스트를 출력하는 SQL문을 작성해주세요. 결과는 자동차 ID를 기준으로 내림차순 정렬해주세요.

 

해설

추출해야할 것 car_id, car_type, daily_fee, options

조건 "네비게이션"이 포함될때만

정렬 car_id 기준 내림차순

 

1. WHERE LIKE 구문을 활용하여 네비게이션을 가지고 있는 필드를 검색해준다

 

이를 구현한 코드는 아래와 같다

SELECT car_id, car_type, daily_fee, options 
FROM car_rental_company_car
WHERE options LIKE "%네비게이션%"
ORDER BY car_id DESC

 

LV.2 자동차 평균 대여기간 구하기

 

문제

CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 평균 대여 기간이 7일 이상인 자동차들의 자동차 ID와 평균 대여 기간(컬럼명: AVERAGE_DURATION) 리스트를 출력하는 SQL문을 작성해주세요. 평균 대여 기간은 소수점 두번째 자리에서 반올림하고, 결과는 평균 대여 기간을 기준으로 내림차순 정렬해주시고, 평균 대여 기간이 같으면 자동차 ID를 기준으로 내림차순 정렬해주세요.

 

해설

추출해야할 것 car_id, average_duration

조건 "average_duration >= 7"

정렬 average_duration DESC, car_id DESC

 

1. start_date와 end_date를 이용해 날짜 차이를 계산해준다

2. GROUP BY와 AVG 함수를 이용해 car_id별

평균 대여 일수를 반올림하여 구한다 <날짜 차이기 때문에 1을 더해야함>

3. HAVING을 이용해 조건절을 붙여준다

4. 지시에 따라 정렬한다

 

이를 구현한 코드는 아래와 같다.

SELECT car_id, ROUND(AVG(DATEDIFF(end_date, start_date)),1)+1 AS AVERAGE_DURATION
FROM  car_rental_company_rental_history 
GROUP BY car_id 
HAVING AVERAGE_DURATION >= 7
ORDER BY AVERAGE_DURATION DESC, car_id DESC

 

LV.3 대여기록이 존재하는 자동차 리스트 구하기

문제

CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 자동차 종류가 '세단'인 자동차들 중 10월에 대여를 시작한 기록이 있는 자동차 ID 리스트를 출력하는 SQL문을 작성해주세요. 자동차 ID 리스트는 중복이 없어야 하며, 자동차 ID를 기준으로 내림차순 정렬해주세요.

 

해설

추출해야할 것 DISTINCT(car_id)

조건 MONTH(start_date) = "10" AND car_type = "세단" 

정렬 car_id DESC

 

1. car_id를 기준으로 두 테이블을 INNER JOIN한다 

2. 조건식을 기입한다

3. car_id가 중복되지 않도록 DISTINCT 처리를 해준다

4. car_id를 내림차순으로 정렬한다

 

이를 구현한 코드는 아래와 같다

SELECT DISTINCT(c.car_id)
FROM car_rental_company_car AS c 
INNER JOIN car_rental_company_rental_history AS h ON c.car_id = h.car_id
WHERE MONTH(h.start_date) = "10" AND c.car_type = "세단"
ORDER BY c.car_id DESC

 

LV.3 자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기

 

문제

CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 2022년 10월 16일에 대여 중인 자동차인 경우 '대여중' 이라고 표시하고, 대여 중이지 않은 자동차인 경우 '대여 가능'을 표시하는 컬럼(컬럼명: AVAILABILITY)을 추가하여 자동차 ID와 AVAILABILITY 리스트를 출력하는 SQL문을 작성해주세요. 이때 반납 날짜가 2022년 10월 16일인 경우에도 '대여중'으로 표시해주시고 결과는 자동차 ID를 기준으로 내림차순 정렬해주세요.

 

해설

추출해야할 것 car_id, AVAILABILITY

조건 "2022-10-16" BETWEEN start_date AND end_date

정렬 car_id DESC

 

1. 먼저 SELECT * FROM car_rental_company_rental_history를 찍어서 확인해보자

하나의 자동차도 여러 기간에 걸쳐서 대여된 것을 알 수 있다

2. 원하는 것은 자동차의 최근 대여 여부 상태가 필요한 것이다.

3. car_id를 기준으로 GROUP BY를한다

4. 가장 최근의 값을 가져오기 위해서 MAX를 써준다

5. CASE ~ END 절을 이용하여 "대여중"과 "대여 가능"을 표시한다

 

이를 구현한 코드는 아래와 같다

SELECT car_id, 
(CASE
    WHEN MAX("2022-10-16" BETWEEN start_date AND end_date) THEN "대여중"
    ELSE "대여 가능"
END) AS AVAILABILITY
FROM car_rental_company_rental_history
GROUP BY car_id
ORDER BY car_id DESC

 

*여기서 이 부분이 왜 사용되었고 어떻게 표현되는지 궁금할 수 있다

WHEN MAX("2022-10-16" BETWEEN start_date AND end_date) THEN "대여중"

잠깐만 생각해보면 알 수 있다.

지정한 날짜에 대여중이라면 True 값으로 1을,

대여중이 아니라면 False 값으로 0을 뱉을 것이다.

 

이 값들이 모든 대여건에 붙어서 나올텐데

이를 GROUP BY와 MAX로 집계해주면 대여중인 것은 최대값으로 1을 내놓게된다

(같은 차를 중복 대여하는 케이스는 없으니까)