[SQL] JOIN 문제모음

링크텍스트

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

MEMBER_PROFILE와 REST_REVIEW 테이블에서 리뷰를 가장 많이 작성한 회원의 리뷰들을 조회하는 SQL문을 작성해주세요. 회원 이름, 리뷰 텍스트, 리뷰 작성일이 출력되도록 작성해주시고,
결과는 리뷰 작성일을 기준으로 오름차순, 리뷰 작성일이 같다면 리뷰 텍스트를 기준으로 오름차순 정렬해주세요.

• MAX, ORDER BY 후 LIMIT 1을 하면 최대 개수가 여러 명인 경우에 논리적 오류가 생긴다.
• RANK를 이용해야 한다.
• GROUP BY, RANK, 서브 쿼리, 조인, 정렬이 모두 사용된다.

SELECT M.MEMBER_NAME, R.REVIEW_TEXT, R.REVIEW_DATE
FROM MEMBER_PROFILE M
INNER JOIN REST_REVIEW R
ON M.MEMBER_ID = R.MEMBER_ID
WHERE M.MEMBER_ID = 
	(SELECT MEMBER_ID
	FROM REST_REVIEW
	GROUP BY MEMBER_ID
	ORDER BY COUNT(*) DESC
	LIMIT 1)
ORDER BY REVIEW_DATE, REVIEW_TEXT;

SELECT B.MEMBER_NAME, A.REVIEW_TEXT, A.REVIEW_DATE
FROM REST_REVIEW A
JOIN (
    SELECT R.MEMBER_ID, M.MEMBER_NAME, RANK() OVER(ORDER BY CNT DESC) AS RANKING
    FROM (
        SELECT *, COUNT(MEMBER_ID) AS CNT
        FROM REST_REVIEW
        GROUP BY MEMBER_ID) AS R
    JOIN MEMBER_PROFILE M ON R.MEMBER_ID = M.MEMBER_ID) B
ON A.MEMBER_ID = B.MEMBER_ID
WHERE B.RANKING = 1
ORDER BY A.REVIEW_DATE;

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

다음은 아이스크림 가게의 상반기 주문 정보를 담은 FIRST_HALF 테이블과 7월의 아이스크림 주문 정보를 담은 JULY 테이블입니다. FIRST_HALF 테이블 구조는 다음과 같으며, SHIPMENT_ID, FLAVOR, TOTAL_ORDER는 각각 아이스크림 공장에서 아이스크림 가게까지의 출하 번호, 아이스크림 맛, 상반기 아이스크림 총주문량을 나타냅니다. FIRST_HALF 테이블의 기본 키는 FLAVOR입니다. FIRST_HALF테이블의 SHIPMENT_ID는 JULY테이블의 SHIPMENT_ID의 외래 키입니다.

NAME TYPE NULLABLE
SHIPMENT_ID INT(N) FALSE
FLAVOR VARCHAR(N) FALSE
TOTAL_ORDER INT(N) FALSE

JULY 테이블 구조는 다음과 같으며, SHIPMENT_ID, FLAVOR, TOTAL_ORDER 은 각각 아이스크림 공장에서 아이스크림 가게까지의 출하 번호, 아이스크림 맛, 7월 아이스크림 총주문량을 나타냅니다. JULY 테이블의 기본 키는 SHIPMENT_ID입니다. JULY테이블의 FLAVOR는 FIRST_HALF 테이블의 FLAVOR의 외래 키입니다. 7월에는 아이스크림 주문량이 많아 같은 아이스크림에 대하여 서로 다른 두 공장에서 아이스크림 가게로 출하를 진행하는 경우가 있습니다. 이 경우 같은 맛의 아이스크림이라도 다른 출하 번호를 갖게 됩니다.

NAME TYPE NULLABLE
SHIPMENT_ID INT(N) FALSE
FLAVOR VARCHAR(N) FALSE
TOTAL_ORDER INT(N) FALSE

문제
7월 아이스크림 총 주문량과 상반기의 아이스크림 총 주문량을 더한 값이 큰 순서대로 상위 3개의 맛을 조회하는 SQL 문을 작성해주세요.

SELECT F.FLAVOR
FROM FIRST_HALF F 
LEFT JOIN (SELECT FLAVOR, SUM(TOTAL_ORDER) AS TOTAL_ORDER
    FROM JULY
    GROUP BY FLAVOR) 
ON F.FLAVOR = J.FLAVOR
ORDER BY (F.TOTAL_ORDER + J.TOTAL_ORDER) DESC 
LIMIT 3;

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

FOOD_PRODUCT와 FOOD_ORDER 테이블에서
생산일자가 2022년 5월인 식품들의 식품 ID, 식품 이름, 총매출을 조회하는 SQL문을 작성.
이때 결과는 총매출을 기준으로 내림차순 정렬해주시고 총매출이 같다면 식품 ID를 기준으로 오름차순 정렬.

SELECT A.PRODUCT_ID, B.PRODUCT_NAME, (SUM(A.AMOUNT) * B.PRICE) AS TOTAL_SALES
FROM FOOD_ORDER A
JOIN FOOD_PRODUCT B 
ON A.PRODUCT_ID = B.PRODUCT_ID
WHERE YEAR(PRODUCE_DATE) = 2022 AND MONTH(PRODUCE_DATE) = 5
GROUP BY A.PRODUCT_ID
ORDER BY TOTAL_SALES DESC, A.PRODUCT_ID ASC;

SELECT A.PRODUCT_ID, A.PRODUCT_NAME, (SUM(B.AMOUNT) * A.PRICE) AS TOTAL_SALES
FROM FOOD_PRODUCT A 
INNER JOIN FOOD_ORDER B
ON A.PRODUCT_ID = B.PRODUCT_ID
WHERE YEAR(PRODUCE_DATE) = '2022' AND MONTH(PRODUCE_DATE) = '5'
GROUP BY A.PRODUCT_ID
ORDER BY TOTAL_SALES DESC, A.PRODUCT_ID ASC

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

SELECT A.PRODUCT_CODE, SUM(A.PRICE * B.SALES_AMOUNT) AS SALES
FROM PRODUCT A
INNER JOIN OFFLINE_SALE B
ON A.PRODUCT_ID = B.PRODUCT_ID
GROUP BY PRODUCT_CODE
ORDER BY SALES DESC, A.PRODUCT_CODE

없어진 기록 찾기

천재지변으로 인해 일부 데이터가 유실되었습니다. 입양을 간 기록은 있는데, 보호소에 들어온 기록이 없는 동물의 ID와 이름을 ID 순으로 조회하는 SQL문을 작성해주세요.

# 입양은 간 기록은 있지만, 보호소에 들어온 기록이 없는 DATA 조회
# -> 입양 간 테이블 기준 OUTER JOIN을 하고,
# 보호소 테이블 ANIMAL_ID에 NULL이 있는 COLUMN 추출 

SELECT OUTS.ANIMAL_ID, OUTS.NAME
FROM ANIMAL_OUTS OUTS LEFT OUTER JOIN ANIMAL_INS INS
on (OUTS.ANIMAL_ID = INS.ANIMAL_ID)
WHERE INS.ANIMAL_ID IS NULL
ORDER BY ANIMAL_ID;

있었는데요 없었습니다

관리자의 실수로 일부 동물의 입양일이 잘못 입력되었습니다. 보호 시작일보다 입양일이 더 빠른 동물의 아이디와 이름을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 시작일이 빠른 순으로 조회해야합니다.

# 보호 시작일 보다 입양일이 더 빠른 데이터 조회
# ANIMAL_ID 기준 INNER JOIN을 하고 
# ANIMAL_INS의 DATETIME이 ANIMAL_OUTS DATETIME 보다 큰 경우 출력

SELECT INS.ANIMAL_ID, INS.NAME
FROM ANIMAL_INS INS INNER JOIN ANIMAL_OUTS OUTS
on (INS.ANIMAL_ID = OUTS.ANIMAL_ID)
WHERE INS.DATETIME > OUTS.DATETIME
ORDER BY INS.DATETIME;

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

아직 입양을 못 간 동물 중, 가장 오래 보호소에 있었던 동물 3마리의 이름과 보호 시작일을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 시작일 순으로 조회해야 합니다.

# ANIMAL_INS 테이블 기준 ANIMAL_ID OUTER JOIN 후
# ANIMAL_OUTS.ANIMAL_ID 가 NULL 인 데이터 출력

SELECT INS.NAME, INS.DATETIME
FROM ANIMAL_INS INS LEFT OUTER JOIN ANIMAL_OUTS OUTS 
ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
WHERE OUTS.ANIMAL_ID IS NULL 
ORDER BY DATETIME
LIMIT 3;

보호소에서 중성화한 동물

보호소에서 중성화 수술을 거친 동물 정보를 알아보려 합니다. 보호소에 들어올 당시에는 중성화되지 않았지만, 보호소를 나갈 당시에는 중성화된 동물의 아이디와 생물 종, 이름을 조회하는 아이디 순으로 조회하는 SQL 문을 작성해주세요.

# ANIMAL_OUTS 과 ANIMAL_INS 를 
# NAME 기준 INNER JOIN 후 SEX_UPON_OUTCOME 확인

SELECT OUTS.ANIMAL_ID, OUTS.ANIMAL_TYPE, OUTS.NAME
FROM ANIMAL_OUTS OUTS INNER JOIN ANIMAL_INS INS
ON OUTS.ANIMAL_ID = INS.ANIMAL_ID
WHERE INS.SEX_UPON_INTAKE != OUTS.SEX_UPON_OUTCOME;