SQL 20240929

20240929===========================================

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

SELECT CATEGORY, SUM(SALES) AS TOTAL_SALES
FROM BOOK A
RIGHT JOIN BOOK_SALES B ON A.BOOK_ID = B.BOOK_ID
WHERE B.SALES_DATE BETWEEN DATE '2022-01-01' AND DATE '2022-01-31'
GROUP BY A.CATEGORY
ORDER BY 1;

-- LV 2. 상품별 오프라인 매출 구하기

SELECT P.PRODUCT_CODE, SUM(SALES_AMOUNT * P.PRICE) AS SALES
FROM PRODUCT P 
RIGHT JOIN OFFLINE_SALE O
ON P.PRODUCT_ID = O.PRODUCT_ID
GROUP BY P.PRODUCT_CODE
ORDER BY SALES DESC, P.PRODUCT_CODE;

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

SELECT ORDER_ID, PRODUCT_ID, TO_CHAR(OUT_DATE,'YYYY-MM-DD') AS OUT_DATE, CASE WHEN OUT_DATE <= DATE '2022-05-01' THEN '출고완료' WHEN OUT_DATE > DATE '2022-05-01' THEN '출고대기' ELSE '출고미정' END AS "출고여부"
FROM FOOD_ORDER
ORDER BY ORDER_ID;