[SQL Lv.3] 프로그래머스 SQL Level 3 문제 정답 (자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기, 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기, 카테고리 별 도서 판매량 집계하기, 즐겨찾기가 가장 많은 식당 정보 출력하기, 조건별로 분류하여 주문상태 출력하기)

모든 문제는 MySQL로 풀이했습니다 ❕

오늘의 문제 5개는 아래와 같습니다 ⤵️

오늘의 문제 5개

 

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

🔗 문제 링크

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

 

⌨️ 작성한 쿼리문 (MySQL)

WITH TMP AS (
    SELECT CAR_ID,
    CASE 
        WHEN START_DATE <= "2022-10-16" AND END_DATE >= "2022-10-16" THEN "대여중"
        ELSE "대여 가능" 
    END AS AVAILABILITY
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
)

SELECT A.CAR_ID, 
    CASE 
        WHEN "대여중" IN (SELECT DISTINCT AVAILABILITY FROM TMP WHERE CAR_ID = A.CAR_ID) THEN "대여중"
        ELSE "대여 가능"
    END AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY A
GROUP BY A.CAR_ID
ORDER BY CAR_ID DESC;

 

2. 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기

🔗 문제 링크

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

 

⌨️ 작성한 쿼리문 (MySQL)

SELECT MONTH(START_DATE) MONTH, CAR_ID, COUNT(*) RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE CAR_ID IN (
      SELECT CAR_ID
      FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
      WHERE START_DATE BETWEEN '2022-08-01' AND '2022-10-31'
      GROUP BY CAR_ID
      HAVING COUNT(*) >= 5
     )
AND START_DATE BETWEEN '2022-08-01' AND '2022-10-31'
GROUP BY MONTH(START_DATE), CAR_ID
HAVING RECORDS != 0
ORDER BY MONTH(START_DATE), CAR_ID DESC

 

3. 카테고리 별 도서 판매량 집계하기

🔗 문제 링크

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

 

⌨️ 작성한 쿼리문 (MySQL)

SELECT A.CATEGORY, SUM(B.SALES) AS TOTAL_SALES
FROM BOOK AS A JOIN BOOK_SALES AS B ON A.BOOK_ID = B.BOOK_ID
WHERE B.SALES_DATE LIKE "2022-01%"
GROUP BY A.CATEGORY
ORDER BY A.CATEGORY

 

4. 즐겨찾기가 가장 많은 식당 정보 출력하기

🔗 문제 링크

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

 

⌨️ 작성한 쿼리문 (MySQL)

WITH TEMP AS (
    SELECT FOOD_TYPE, MAX(FAVORITES) AS FAVORITES FROM REST_INFO
    GROUP BY FOOD_TYPE
)
SELECT A.FOOD_TYPE, REST_ID, REST_NAME, A.FAVORITES
FROM REST_INFO A JOIN TEMP B ON (A.FOOD_TYPE = B.FOOD_TYPE AND A.FAVORITES = B.FAVORITES)
GROUP BY A.FOOD_TYPE
ORDER BY A.FOOD_TYPE DESC

 

5. 조건별로 분류하여 주문상태 출력하기

🔗 문제 링크

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

 

⌨️ 작성한 쿼리문 (MySQL)

SELECT ORDER_ID, PRODUCT_ID, DATE_FORMAT(OUT_DATE, "%Y-%m-%d") AS OUT_DATE, 
       CASE 
           WHEN DATE_FORMAT(OUT_DATE, "%Y-%m-%d") IS NULL THEN "출고미정"
           WHEN DATE_FORMAT(OUT_DATE, "%Y-%m-%d") <= "2022-05-01" THEN "출고완료"
           WHEN DATE_FORMAT(OUT_DATE, "%Y-%m-%d") > "2022-05-01" THEN "출고대기"
           END AS "출고여부"
       FROM FOOD_ORDER
ORDER BY ORDER_ID ASC

 

쿼리문에 대해 이해 안 가거나

궁금한 내용이 있으신 분은 댓글 남겨주세요!

감사합니다 🤓