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;
'Algorithm > SQL' 카테고리의 다른 글
SQL 20241002 (0) | 2024.10.02 |
---|---|
SQL 20241001 (0) | 2024.10.01 |
SQL 20240924 (2) | 2024.09.24 |
SQL 20240922 (0) | 2024.09.22 |
SQL 20240921 (0) | 2024.09.21 |