SQL 고득점 Kit [JOIN 시리즈]

 

 

그룹별 조건에 맞는 식당 목록 출력하기

-- 코드를 입력하세요
SELECT A.MEMBER_NAME, B.REVIEW_TEXT, B.REVIEW_DATE
FROM MEMBER_PROFILE AS A, REST_REVIEW AS B
WHERE A.MEMBER_ID = B.MEMBER_ID
AND B.MEMBER_ID IN (
                    SELECT S.MEMBER_ID
                    FROM (
                        SELECT MEMBER_ID, COUNT(MEMBER_ID) AS COUNT
                        FROM REST_REVIEW
                        GROUP BY MEMBER_ID
                        ORDER BY COUNT DESC
                        LIMIT 1
                        )
                    AS S)
ORDER BY REVIEW_DATE

#그 중 최대
# SELECT S.MEMBER_ID
# FROM (
#     SELECT MEMBER_ID, COUNT(MEMBER_ID) AS COUNT
#     FROM REST_REVIEW
#     GROUP BY MEMBER_ID
#     ORDER BY COUNT DESC
#     LIMIT 1
# ) AS S


#리뷰 작성 숫자를 구하기 
# SELECT MEMBER_ID, COUNT(MEMBER_ID) AS COUNT
# FROM REST_REVIEW
# GROUP BY MEMBER_ID

# SELECT * FROM REST_REVIEW ORDER BY MEMBER_ID

 

 

 

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

#ORDER TABLE에 product_id가 같은 행이 여러개 존재할 수 있음 
SELECT T.PRODUCT_ID, T.PRODUCT_NAME, (S.AMOUNT * T.PRICE) AS TOTAL_SALES
FROM FOOD_PRODUCT AS T, (
    SELECT PRODUCT_ID ,SUM(AMOUNT) AS AMOUNT
    FROM FOOD_ORDER
    WHERE YEAR(PRODUCE_DATE) = '2022' 
    AND MONTH(PRODUCE_DATE) = '5'
    GROUP BY PRODUCT_ID) 
    AS S
WHERE T.PRODUCT_ID = S.PRODUCT_ID
ORDER BY TOTAL_SALES DESC




# SELECT PRODUCT_ID ,SUM(AMOUNT) AS AMOUNT
# FROM FOOD_ORDER
# WHERE YEAR(PRODUCE_DATE) = '2022' 
# AND MONTH(PRODUCE_DATE) = '5'
# GROUP BY PRODUCT_ID

 

 

 

없어진 기록찾기

-- 코드를 입력하세요
SELECT ANIMAL_OUTS.ANIMAL_ID, ANIMAL_OUTS.NAME
FROM ANIMAL_INS RIGHT OUTER JOIN ANIMAL_OUTS ON ANIMAL_INS.ANIMAL_ID = ANIMAL_OUTS.ANIMAL_ID
WHERE ANIMAL_INS.DATETIME IS NULL

 

 

 

 

있었는데요 없었습니다

-- 코드를 입력하세요
SELECT ANIMAL_INS.ANIMAL_ID, ANIMAL_OUTS.NAME
FROM ANIMAL_INS, ANIMAL_OUTS
WHERE ANIMAL_INS.ANIMAL_ID = ANIMAL_OUTS.ANIMAL_ID 
AND ANIMAL_OUTS.DATETIME < ANIMAL_INS.DATETIME
ORDER BY ANIMAL_INS.DATETIME

 

 

 

오랜기간 보호한 동물(1)

-- 코드를 입력하세요
SELECT ANIMAL_INS.NAME, ANIMAL_INS.DATETIME
FROM ANIMAL_INS LEFT OUTER JOIN ANIMAL_OUTS ON ANIMAL_INS.ANIMAL_ID = ANIMAL_OUTS.ANIMAL_ID
WHERE ANIMAL_OUTS.DATETIME IS NULL
ORDER BY ANIMAL_INS.DATETIME 
LIMIT 3

 

 

 

보호소에서 중성화한 동물

-- 코드를 입력하세요
SELECT ANIMAL_INS.ANIMAL_ID, ANIMAL_INS.ANIMAL_TYPE, ANIMAL_INS.NAME
FROM ANIMAL_INS, ANIMAL_OUTS
WHERE ANIMAL_INS.ANIMAL_ID = ANIMAL_OUTS.ANIMAL_ID
AND SEX_UPON_INTAKE LIKE "Intact%" AND (SEX_UPON_OUTCOME LIKE "Spayed%" OR SEX_UPON_OUTCOME LIKE "Neutered%")

 

상품별 오프라인 매출 구하기

-- 코드를 입력하세요
SELECT T.PRODUCT_CODE ,(T.PRICE * S.AMOUNT) AS SALES
FROM PRODUCT AS T, (SELECT PRODUCT_ID, SUM(SALES_AMOUNT) AS AMOUNT
                    FROM OFFLINE_SALE
                    GROUP BY PRODUCT_ID) AS S 
WHERE T.PRODUCT_ID = S.PRODUCT_ID
ORDER BY SALES DESC, PRODUCT_CODE;


#PRODUCT ID별 판매량
# SELECT PRODUCT_ID, SUM(SALES_AMOUNT)
# FROM OFFLINE_SALE
# GROUP BY PRODUCT_ID

 

 

 

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

-- 코드를 입력하세요
SELECT FLAVOR
FROM (SELECT FLAVOR, SUM(TOTAL_ORDER) AS TOTAL
FROM JULY 
GROUP BY FLAVOR
UNION
SELECT FLAVOR, SUM(TOTAL_ORDER) AS TOTAL
FROM FIRST_HALF
GROUP BY FLAVOR) AS T
GROUP BY FLAVOR
ORDER BY SUM(TOTAL) DESC
LIMIT 3

뭔가를 건들이지 않고 그냥 합치고 싶을 때 UNION ALL

 

상품을 구매한 회원 비율 구하기

-- 1. 2021년에 가입한 회원들만
-- 2. 상품을 구매한 회원의 비율 (둘째 자리 반올림)
-- 3. 년도 + 월별로 (오름차순 년->월) 



-- 1. 2021년에 가입한 회원 고르기
# SELECT *
# FROM USER_INFO
# WHERE YEAR(JOINED) = '2021'

-- 1-1. 2021년에 가입한 회원 수 
# SELECT COUNT(*)
# FROM USER_INFO
# WHERE YEAR(JOINED) = '2021'


-- 2. 특정 상품별 구매 회원
# SELECT PRODUCT_ID, COUNT(*)
# FROM ONLINE_SALE
# GROUP BY PRODUCT_ID




-- 2. 2021년 가입자의 상품 구매 수 
SELECT YEAR(SALES_DATE) AS YEAR , MONTH(SALES_DATE) AS MONTH , COUNT(DISTINCT(T.USER_ID)) AS PUCHASED_USERS, ROUND(COUNT(DISTINCT(T.USER_ID))/
(SELECT COUNT(*)
FROM USER_INFO
WHERE YEAR(JOINED) = '2021'),1) AS PUCHASED_RATIO
FROM USER_INFO AS T, ONLINE_SALE AS S
WHERE YEAR(T.JOINED) = '2021' AND T.USER_ID = S.USER_ID
GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH

DISTINCT로 중복을 제거하는 것을 잊지 말기 

ROUND (A/B, 1) <-반올림을 원하는 자리수(반올림 된)