[SQL] 프로그래머스 코딩테스트 : Lv4. (1)

 

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

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

SELECT AI.ANIMAL_ID, AI.ANIMAL_TYPE, AI.NAME
FROM ANIMAL_INS AS AI
    LEFT JOIN ANIMAL_OUTS AS AO ON AI.ANIMAL_ID = AO.ANIMAL_ID
    WHERE AI.SEX_UPON_INTAKE LIKE '%INTACT%'
        AND AO.SEX_UPON_OUTCOME NOT LIKE '%INTACT%'

❗LEFT JOIN 으로 테이블 결합
❗테이블 결합 시 WHERE 사용해서 조건 걸기

 

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

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

SELECT CATEGORY, PRICE AS MAX_PRICE
    , PRODUCT_NAME
FROM FOOD_PRODUCT
WHERE (CATEGORY, PRICE) IN (SELECT CATEGORY, MAX(PRICE) AS MAX_PRICE
                            FROM FOOD_PRODUCT
                            WHERE CATEGORY IN ('과자', '국', '김치', '식용유')
                            GROUP BY CATEGORY)
ORDER BY MAX_PRICE DESC

+) WHERE 절 서브쿼리

❗카테고리에 해당하는 것들의 카테고리, 최대값 출력
❗WHERE 절에 IN 사용해서 조건 충족하기

 

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

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

-- 성공
SELECT FO.PRODUCT_ID
    , FP.PRODUCT_NAME
    , SUM(FO.AMOUNT) * FP.PRICE AS TOTAL_SALES
FROM FOOD_ORDER AS FO
    INNER JOIN FOOD_PRODUCT AS FP ON FO.PRODUCT_ID = FP.PRODUCT_ID
WHERE FO.PRODUCE_DATE BETWEEN '2022-05-01' AND '2022-05-31'
GROUP BY FO.PRODUCT_ID
ORDER BY TOTAL_SALES DESC, FO.PRODUCT_ID ASC

❗SUM()으로 같은 것의 합계를 구하는 것이 중요
❗INNER JOIN 사용해서 데이터 결합
❗WHERE 절에 날짜 조건 사용하기

 

-- 실패
SELECT FO.PRODUCT_ID
    , FP.PRODUCT_NAME
    , FO.AMOUNT * FP.PRICE AS TOTAL_SALES
FROM FOOD_ORDER AS FO
    INNER JOIN FOOD_PRODUCT AS FP ON FO.PRODUCT_ID = FP.PRODUCT_ID
WHERE FO.PRODUCE_DATE BETWEEN '2022-05-01' AND '2022-05-31'
ORDER BY TOTAL_SALES DESC, FO.PRODUCT_ID ASC

❗SUM()을 사용하지 않아서 실패

 

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

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
     LEFT JOIN PATIENT AS P ON A.PT_NO = P.PT_NO
     LEFT JOIN DOCTOR AS D ON A.MDDR_ID = D.DR_ID
WHERE A.APNT_YMD LIKE '%2022-04-13%'
    AND A.APNT_CNCL_YN = 'N'
    AND A.MCDP_CD = 'CS'
ORDER BY A.APNT_YMD ASC

❗LEFT JOIN 두 번 사용해서 테이블 결합하기
❗WHERE 절에 조건 충족시키기

 

Lv4. 서울에 위치한 식당 목록 출력하기

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

SELECT RI.REST_ID, RI.REST_NAME, RI.FOOD_TYPE, RI.FAVORITES, RI.ADDRESS
    , ROUND(AVG(RR.REVIEW_SCORE),2) AS SCORE
FROM REST_INFO AS RI
    INNER JOIN REST_REVIEW AS RR ON RI.REST_ID = RR.REST_ID
WHERE ADDRESS LIKE '서울%'
GROUP BY RI.REST_ID
ORDER BY SCORE DESC, RI.FAVORITES DESC

❗ROUND(), AVG() 사용해서 출력 조건 맞추기
❗INNER JOIN 사용해서 테이블 결합
❗WHERE 절에 '서울%'로 조건 맞추기

• '%서울%'은 정답 요건이 아님 (이유는 모르겠다...)
  ❗참고 : https://20240228.tistory.com/270

 

Lv4. 년, 월, 성별 별 상품 구매 회원 수 구하기

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

SELECT YEAR(OS.SALES_DATE) AS YEAR
    , MONTH(OS.SALES_DATE) AS MONTH
    , UI.GENDER
    , COUNT(DISTINCT UI.USER_ID) AS USERS
FROM ONLINE_SALE AS OS
    INNER JOIN USER_INFO AS UI ON OS.USER_ID = UI.USER_ID
WHERE UI.GENDER IS NOT NULL
GROUP BY YEAR(OS.SALES_DATE), MONTH(OS.SALES_DATE), UI.GENDER
ORDER BY YEAR(OS.SALES_DATE), MONTH(OS.SALES_DATE), UI.GENDER

❗구매 건수가 아니라 회원수이기 때문에 DISTINCT를 사용
❗INNER JOIN으로 테이블에 동시에 존재하는 것만 결합
❗GROUP BY 여러 개 일때 순서 맞추기
❗ORDER BY 여러 개 일때 순서 맞추고 정렬 조건 맞추기
❗참고 : https://noaahhh.tistory.com/82

 

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

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

WITH T1 AS (
    SELECT B.AUTHOR_ID, A.AUTHOR_NAME, B.CATEGORY
        , B.PRICE * BS.SALES AS TOTAL_SALES
    FROM BOOK_SALES AS BS
        LEFT JOIN BOOK AS B ON BS.BOOK_ID = B.BOOK_ID
        LEFT JOIN AUTHOR AS A ON B.AUTHOR_ID = A.AUTHOR_ID
            WHERE BS.SALES_DATE LIKE '%2022-01%'
)

SELECT AUTHOR_ID, AUTHOR_NAME, CATEGORY
    , SUM(TOTAL_SALES) AS TOTAL_SALES
FROM T1
GROUP BY AUTHOR_ID, AUTHOR_NAME, CATEGORY
ORDER BY AUTHOR_ID ASC, CATEGORY DESC

❗SUM() 집계 함수 사용해서 조건 맞추기
❗GROUP BY 여러 개 일때 순서 맞추기
❗ORDER BY 여러 개 일때 순서 맞추고 정렬 조건 맞추기

+) WITH 절 서브쿼리

❗LEFT JOIN 두 번 사용해서 테이블 결합하기
❗테이블 조인 시 WHERE 절에 날짜 조건 달아서 해당하는 데이터만 결합

 

Lv4. 우유와 요거트가 담긴 장바구니

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

WITH T1 AS (
    SELECT CART_ID, NAME
    FROM CART_PRODUCTS
    WHERE NAME LIKE 'Yogurt'
)

SELECT DISTINCT CP.CART_ID
FROM CART_PRODUCTS AS CP
    INNER JOIN T1 ON T1.CART_ID = CP.CART_ID
    WHERE CP.NAME LIKE 'MILK'
ORDER BY CP.CART_ID ASC

❗물품이 겹치는 카트 아이디만 추출하기 때문에 DISTINCT 사용
❗INNER JOIN 으로 동시에 존재하는 데이터만 결합
❗테이블 결합 시 WHERE 절 사용해서 해당하는 데이터만 결합

+) WITH 절 서브쿼리

❗WHERE 절에 해당하는 데이터만 추출

 

Lv4. 주문량이 많은 아이스크림들 조회하기

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

WITH T1 AS (
    SELECT *
    FROM FIRST_HALF

    UNION ALL

    SELECT *
    FROM JULY
)

SELECT FLAVOR
FROM T1
GROUP BY FLAVOR
ORDER BY SUM(TOTAL_ORDER) DESC
LIMIT 3

❗GROUP BY 절에 조건 충족 시키기
❗ORDER BY 절에 SUM() 집계 함수 사용해서 조건 충족 시키기

+) WITH 절 서브쿼리

❗데이터 테이블 두 개를 UNION ALL 로 결합

• 테이블 형태가 같기 때문에 결합해서 사용

 

Lv4. 연간 평가점수에 해당하는 평가 등급 및 성과금 조회하기

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

WITH T1 AS (
    SELECT HG.EMP_NO, HE.EMP_NAME,
        CASE
            WHEN AVG(SCORE) >= 96 THEN 'S'
            WHEN AVG(SCORE) >= 90 THEN 'A'
            WHEN AVG(SCORE) >= 80 THEN 'B'
            ELSE 'C'
        END AS GRADE   
        , HE.SAL
    FROM HR_GRADE AS HG
        LEFT JOIN HR_EMPLOYEES AS HE ON HG.EMP_NO = HE.EMP_NO
    GROUP BY HG.EMP_NO
    ORDER BY HG.EMP_NO ASC
)
SELECT EMP_NO, EMP_NAME, GRADE
    , CASE
        WHEN GRADE = 'S' THEN SAL*0.2
        WHEN GRADE = 'A' THEN SAL*0.15
        WHEN GRADE = 'B' THEN SAL*0.1
        ELSE 0
    END AS BONUS
FROM T1

❗CASE WHEN 사용해서 GRADE에 맞춰서 BONUS 계산하기

+) WITH 절 서브쿼리

❗CASE WHEN 사용해서 평균 점수에 따라 등급 계산하기

• 반기 점수로 1년에 점수가 2개씩 존재
• 점수의 평균을 사용해서 최종 점수로 사용
  ❗LEFT JOIN 을 사용해 GRADE가 존재하는 사원 기준으로 테이블 결합
  ❗GROUP BY 조건 맞추기
  ❗ORDER BY 조건 맞추기