저자 별 카테고리 매출액 집계하기 외 2건

1. 주문량이 많은 아이스크림을 조회하기

WITH ANS AS 
(
SELECT A.FLAVOR, A.TOTAL_ORDER
FROM JULY A

UNION ALL

SELECT B.FLAVOR, B.TOTAL_ORDER
FROM FIRST_HALF B
)

SELECT FLAVOR
FROM ANS
GROUP BY FLAVOR
ORDER BY SUM(ANS.TOTAL_ORDER) DESC
FETCH NEXT 3 ROWS ONLY

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

WITH BOOK_RES AS (
	SELECT A.AUTHOR_ID, A.BOOK_ID, A.CATEGORY, B.SALES_DATE, (B.SALES*A.PRICE) AS TOTAL_SALES
	FROM BOOK A INNER JOIN BOOK_SALES B ON A.BOOK_ID = B.BOOK_ID
	WHERE EXTRACT(YEAR FROM B.SALES_DATE) = 2022 AND EXTRACT(MONTH FROM B.SALES_DATE) = 1
)

SELECT B.AUTHOR_ID, B.AUTHOR_NAME, A.CATEGORY, SUM(A.TOTAL_SALES) AS TOTAL_SALES
FROM BOOK_RES A INNER JOIN AUTHOR B ON A.AUTHOR_ID = B.AUTHOR_ID
GROUP BY A.CATEGORY, B.AUTHOR_ID, B.AUTHOR_NAME
ORDER BY B.AUTHOR_ID ASC, A.CATEGORY DESC

3. 조건에 맞는 도서 리스트 출력하기

SELECT BOOK_ID
	   , TO_CHAR(PUBLISHED_DATE,'YYYY-MM-DD') AS PUBLISHED_DATE
FROM BOOK
WHERE CATEGORY = '인문' 
  AND EXTRACT(YEAR FROM PUBLISHED_DATE)=2021
ORDER BY PUBLISHED_DATE ASC

오마이갓
줄맞춤을 하나도 안했다

언젠가의 내가... 고치겠지...?