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

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

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

프로그래머스

SELECT 
    YEAR(S.SALES_DATE) AS YEAR, 
    MONTH(S.SALES_DATE) AS MONTH, 
    I.GENDER,
    COUNT(DISTINCT(S.USER_ID)) AS USERS
FROM 
	ONLINE_SALE AS S
	LEFT JOIN USER_INFO AS I
	ON S.USER_ID = I.USER_ID
WHERE I.GENDER IS NOT NULL
GROUP BY YEAR(S.SALES_DATE), MONTH(S.SALES_DATE), I.GENDER
ORDER BY 1, 2, 3;

POINT!

구매 건수가 아니라 구매 회원수이기 때문에 DISTINCT를 써야한다.

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

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

프로그래머스

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

POINT! 

SELECT에서 평균을 구하잖아요. 이때 가게마다의 평균을 구해야한다는 것을 잊으시면 안됩니다!

그래서 GROUP BY를 해줘야하는 겁니다 ㅎㅎ

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

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

프로그래머스

SELECT CART_ID
FROM (SELECT CART_ID, GROUP_CONCAT(NAME) AS ITEMS
    FROM CART_PRODUCTS
    GROUP BY CART_ID ) AS TOTAL
WHERE ITEMS LIKE '%Milk%' 
    AND ITEMS LIKE '%Yogurt%'
ORDER BY CART_ID

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

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

프로그래머스

이 문제는 지금까지 풀어온 문제랑 다릅니다!

단순하게 조인을 하면 안됩니다.

왜냐하면 FIRST_HALF에는 7월 데이터가 없으니 JULY랑 그냥 JOIN하면 7월에만 팔린 맛이 누락되거나, 누락된 값이 NULL로 나올 수 있어요.

 

풀이 방법

① 두 테이블을  UNION ALL로 합친다
② FLAVOR로 GROUP BY해서 총합으로 정렬을 한다
③ TOP 3 추출한다.

 

SELECT FLAVOR
FROM (
    SELECT FLAVOR, TOTAL_ORDER FROM FIRST_HALF
    UNION ALL
    SELECT FLAVOR, TOTAL_ORDER FROM JULY
) AS ALL_ORDERS
GROUP BY FLAVOR
ORDER BY SUM(TOTAL_ORDER) DESC
LIMIT 3;

---

UNION ALL이란

: 두 개 이상의 SELECT 결과를  그대로 아래로 이어붙이는 것입니다. 단, 컬럼 개수와 타입이 맞아야해요.

각각 다른 테이블에서 동일한 구조의 데이터를 모아서 한번에 보고싶을 때 사용합니다.

 

문법 구조

SELECT 컬럼1, 컬럼2
FROM 테이블A
UNION ALL
SELECT 컬럼1, 컬럼2
FROM 테이블B;

• UNION ALL :  중복 허용, 두 SELECT 결과를 이어붙임
• UNION :이어 붙이되, 중복 제거

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

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

프로그래머스

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