[프로그래머스]Oracle SQL_2(Lv4)

 1. 5월 식품들의 매출 조회하기

https://school.programmers.co.kr/learn/courses/30/lessons/131117?language=oracle

 

WITH ORDER_DT AS (
    SELECT
        PRODUCT_ID,
        SUM(AMOUNT) AS TOTAL_AMOUNT
    FROM FOOD_ORDER
    WHERE TO_CHAR(PRODUCE_DATE, 'YYYY-MM') = '2022-05'
    GROUP BY PRODUCT_ID
) -- Closing parenthesis for the CTE

SELECT
    P.PRODUCT_ID,
    P.PRODUCT_NAME,
    (P.PRICE * O.TOTAL_AMOUNT) AS TOTAL_SALES
FROM FOOD_PRODUCT P
    JOIN ORDER_DT O ON O.PRODUCT_ID = P.PRODUCT_ID
ORDER BY TOTAL_SALES DESC, P.PRODUCT_ID ASC;

 

POINT

제품별로 여러번 주문이 들어올 수 있음을 기억하자! 

 

2. 취소되지 않은 진료 예약 조회하기

https://school.programmers.co.kr/learn/courses/30/lessons/132204

SELECT 
    A.APNT_NO,
    P.PT_NAME,
    P.PT_NO,
    A.MCDP_CD,
    D.DR_NAME,
    A.APNT_YMD
FROM APPOINTMENT A
    JOIN DOCTOR D ON A.MDDR_ID= D.DR_ID
    JOIN PATIENT P ON A.PT_NO = P.PT_NO
WHERE A.APNT_CNCL_YN = 'N'
    AND TO_CHAR(A.APNT_YMD,'YYYY-MM-DD') = '2022-04-13'
    AND A.MCDP_CD = 'CS'
ORDER BY A.APNT_YMD;