SQL 코드카타 61 ~ 70

 

61. 서울에 위치한 식당 목록 출력하기

WITH REVIEW_AVG AS (SELECT REST_ID, ROUND(AVG(REVIEW_SCORE),2) AS REVIEW_AVG_SCORE
FROM REST_REVIEW
GROUP BY REST_ID)

SELECT REST_ID, REST_NAME, FOOD_TYPE, FAVORITES, ADDRESS, REVIEW_AVG_SCORE
FROM REVIEW_AVG RA INNER JOIN REST_INFO RI
USING(REST_ID)
WHERE ADDRESS LIKE '서울%'
ORDER BY 6 DESC, FAVORITES DESC;

 

 

62. 자동차 대여 기록에서 장기/단기 대여 구분하기

WITH RENTAL_HISTORY AS (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
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE DATE_FORMAT(start_date, '%Y-%m-%d') BETWEEN '2022-09-01' AND '2022-09-30')

SELECT history_id, car_id, start_date, end_date, IF (DATEDIFF(end_date, start_date)+1 >= 30, '장기 대여', '단기 대여') AS RENT_TYPE
FROM RENTAL_HISTORY
ORDER BY 1 DESC;

 

* DATEDIFF 함수의 매개변수가 DB 마다 다릅니다.

 

 

63. 자동차 평균 대여 시간 구하기

SELECT car_id, ROUND(AVG(DATE_DIFF), 1) AS AVERAGE_DURATION
FROM (SELECT history_id, car_id, DATEDIFF(end_date, start_date)+1 as DATE_DIFF
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY) origin
GROUP BY car_id
HAVING AVERAGE_DURATION >= 7
ORDER BY AVERAGE_DURATION DESC, car_id DESC;

 

 

64. 헤비 유저가 소유한 장소

WITH PLACES_COUNT AS (SELECT HOST_ID
FROM PLACES
GROUP BY HOST_ID
HAVING COUNT(NAME) >= 2)

SELECT ID, NAME, HOST_ID
FROM PLACES p INNER JOIN PLACES_COUNT pc
USING(HOST_ID)
ORDER BY ID;

 

 

65. 우유와 요거트가 담긴 장바구니

SELECT CART_ID
FROM CART_PRODUCTS
WHERE NAME IN ('Yogurt', 'Milk')
GROUP BY CART_ID
HAVING COUNT(DISTINCT NAME) = 2
ORDER BY CART_ID;

 

 

66. 조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기

WITH MOST_VIEW AS (SELECT BOARD_ID, VIEWS, RANK() OVER(ORDER BY VIEWS DESC) AS RANK_VIEWS,
FILE_ID, FILE_EXT, FILE_NAME
FROM USED_GOODS_BOARD ugb INNER JOIN USED_GOODS_FILE ugf
USING(BOARD_ID))


SELECT CONCAT('/home/grep/src/', BOARD_ID, '/', FILE_ID, FILE_NAME, FILE_EXT) AS FILE_PATH
FROM MOST_VIEW
WHERE RANK_VIEWS = 1
ORDER BY FILE_PATH DESC;

 

 

67. 주문량이 많은 아이스크림들 조회하기

SELECT FLAVOR
FROM (SELECT j.FLAVOR, IF(j.FLAVOR = fi.FLAVOR, j.TOTAL_ORDER + fi.TOTAL_ORDER, j.TOTAL_ORDER) AS TOTAL_ORDER
FROM JULY j LEFT JOIN FIRST_HALF fi
ON j.SHIPMENT_ID = fi.SHIPMENT_ID) a
GROUP BY FLAVOR
ORDER BY SUM(TOTAL_ORDER) DESC LIMIT 3

 

 

68. 저자별 카테고리 별 매출액 집계하기

SELECT AUTHOR_ID, AUTHOR_NAME, CATEGORY, SUM(sales * price) AS TOTAL_SALES
FROM BOOK_SALES bs INNER JOIN BOOK b
USING(BOOK_ID) INNER JOIN AUTHOR a
USING(AUTHOR_ID)
WHERE DATE_FORMAT(sales_date, '%Y-%m') = '2022-01'
GROUP BY 1,2,3
ORDER BY 1, 3 DESC;

 

 

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

SELECT MONTH(start_date) AS MONTH, car_id, COUNT(history_id) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY history INNER JOIN 
(SELECT CAR_ID, COUNT(*)
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE DATE_FORMAT(start_date, '%Y-%m') BETWEEN '2022-08' AND '2022-10'
GROUP BY 1
HAVING COUNT(*) >= 5) a
USING(CAR_ID)
WHERE START_DATE >= '2022-08-01' and START_DATE <= '2022-10-31'
GROUP BY 1, 2
ORDER BY 1, 2 DESC;

 

 

70. 그룹별 조건에 맞는 식당 목록 출력하기

SELECT MEMBER_NAME, REVIEW_TEXT, DATE_FORMAT(REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE
FROM (SELECT MEMBER_ID, MEMBER_NAME, COUNT(*)
FROM REST_REVIEW rr INNER JOIN MEMBER_PROFILE mr
USING(MEMBER_ID)
GROUP BY MEMBER_ID, MEMBER_NAME
ORDER BY COUNT(*) DESC LIMIT 1) ORIGIN INNER JOIN REST_REVIEW rr
USING(MEMBER_ID)
ORDER BY REVIEW_DATE, REVIEW_TEXT;