[프로그래머스]SQL 쿼리테스트 Lv4(1)

1. 보호소에서 중성화한 동물

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

프로그래머스

SELECT 
    I.ANIMAL_ID, 
    I.ANIMAL_TYPE,
    I.NAME
FROM 
    (SELECT  ANIMAL_ID, ANIMAL_TYPE, NAME, SEX_UPON_OUTCOME
    FROM ANIMAL_OUTS
    WHERE SEX_UPON_OUTCOME LIKE 'Neutered%' 
        OR SEX_UPON_OUTCOME LIKE'Spayed%') AS OUTS
        LEFT JOIN ANIMAL_INS AS I ON OUTS.ANIMAL_ID = I.ANIMAL_ID
WHERE I.SEX_UPON_INTAKE LIKE 'Intact%'
ORDER BY ANIMAL_ID;

2. 식품분류별 가장 비싼 식품의 정부 조회하기

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

프로그래머스

SELECT CATEGORY, PRICE ,PRODUCT_NAME
FROM (
    SELECT *, 
           ROW_NUMBER() OVER (PARTITION BY CATEGORY ORDER BY PRICE DESC) AS rn
    FROM FOOD_PRODUCT
) AS sub
WHERE rn = 1 
    AND CATEGORY IN ('과자', '국', '김치', '식용유')
ORDER BY PRICE DESC;

3. 5월 식품의 총 매출 조회하기

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

프로그래머스

WITH MAY_ORDER AS (
    SELECT PRODUCT_ID, SUM(AMOUNT) AS TOTAL_AMOUNT
    FROM FOOD_ORDER
    WHERE DATE_FORMAT(PRODUCE_DATE, '%Y-%m') = '2022-05'
    GROUP BY PRODUCT_ID)

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

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

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

프로그래머스

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

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

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

프로그래머스

WITH JANUARY_SALES AS (
    SELECT  
        A.AUTHOR_ID,
        A.AUTHOR_NAME,
        B.CATEGORY,
        B.PRICE,
        S.SALES
    FROM BOOK_SALES AS S
    LEFT JOIN BOOK AS B ON S.BOOK_ID = B.BOOK_ID
    LEFT JOIN AUTHOR AS A ON B.AUTHOR_ID = A.AUTHOR_ID
    WHERE DATE_FORMAT(S.SALES_DATE,'%Y-%m') = '2022-01'
)
    
SELECT 
    AUTHOR_ID, 
    AUTHOR_NAME, 
    CATEGORY, 
    SUM(PRICE * SALES) AS TOTAL_SALES
FROM JANUARY_SALES
GROUP BY 
    CATEGORY,
    AUTHOR_ID, 
    AUTHOR_NAME
ORDER BY AUTHOR_ID ASC, CATEGORY DESC;

POINT!

WITH나 서브 쿼리 내부에서는 같은 이름의 컬럼이 2개 있을 수 없기 때문에 오류가 발생합니다.

그래서 필요한 컬럼만 지정해줘야합니다!