67 주문량이 많은 아이스크림들 조회하기
SELECT H.FLAVOR
FROM FIRST_HALF H JOIN JULY J ON H.FLAVOR=J.FLAVOR
GROUP BY H.FLAVOR
ORDER BY SUM(H.TOTAL_ORDER + J.TOTAL_ORDER) DESC
LIMIT 3
68 저자 별 카테고리 별 매출액 집계하기
SELECT B.AUTHOR_ID, AUTHOR_NAME, CATEGORY, SUM(SALES*PRICE)TOTAL_SALES
FROM BOOK B
JOIN AUTHOR A ON B.AUTHOR_ID=A.AUTHOR_ID
JOIN BOOK_SALES BS ON BS.BOOK_ID=B.BOOK_ID
WHERE SALES_DATE LIKE '2022-01%'
GROUP BY B.AUTHOR_ID, CATEGORY
ORDER BY AUTHOR_ID, CATEGORY DESC
69 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기
SSELECT MONTH(START_DATE) AS MONTH, CAR_ID, COUNT(*) 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, CAR_ID DESC
70 그룹별 조건에 맞는 식당 목록 출력하기
SELECT MEMBER_NAME, REVIEW_TEXT,
DATE_FORMAT(REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE
FROM REST_REVIEW A
JOIN (SELECT MEMBER_ID
FROM REST_REVIEW
GROUP BY MEMBER_ID
ORDER BY COUNT(*) DESC
LIMIT 1) B
ON A.MEMBER_ID = B.MEMBER_ID
JOIN MEMBER_PROFILE M
ON M.MEMBER_ID = A.MEMBER_ID
ORDER BY REVIEW_DATE, REVIEW_TEXT
71 오프라인/온라인 판매 데이터 통합하기
SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE,
PRODUCT_ID, USER_ID, SALES_AMOUNT
FROM ONLINE_SALE
WHERE SALES_DATE LIKE '2022-03%'
UNION ALL
SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE,
PRODUCT_ID, NULL AS USER_ID, SALES_AMOUNT
FROM OFFLINE_SALE
WHERE SALES_DATE LIKE '2022-03%'
ORDER BY SALES_DATE, PRODUCT_ID, USER_ID