[mysql] 즐겨찾기가 가장 많은 식당 정보 출력하기, 카테고리 별 도서 판매량 집계하기, 헤비 유저가 소유한 장소, 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기

문제 설명은 문제 아래의 링크를 참고.

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

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

풀이

SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
FROM REST_INFO
WHERE (FOOD_TYPE, FAVORITES) IN 
    (SELECT FOOD_TYPE, MAX(FAVORITES) 
     FROM REST_INFO 
     GROUP BY FOOD_TYPE)
ORDER BY FOOD_TYPE DESC;

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

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

풀이

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

헤비 유저가 소유한 장소

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

풀이

SELECT ID, NAME, HOST_ID
FROM PLACES
WHERE HOST_ID IN (SELECT HOST_ID FROM PLACES GROUP BY HOST_ID HAVING COUNT(HOST_ID) >=2)
ORDER BY ID;

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

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

풀이

SELECT MONTH(START_DATE) AS "MONTH", CAR_ID, COUNT(CAR_ID) AS "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(CAR_ID) >= 5)
                 AND START_DATE BETWEEN "2022-08-01" AND "2022-10-31"
GROUP BY MONTH, CAR_ID
ORDER BY MONTH ASC, CAR_ID DESC;