(정답주의) 프로그래머스 SQL 고득점 Kit - JOIN

• 알아도 다시 여러가지 방법으로 풀어보면 좋을 거 같다
• 아래는 레벨 순으로 정리해봄

---

정답주의

1. 조건에 맞는 도서와 저자 리스트 출력하기

SELECT a.BOOK_ID, 
b.AUTHOR_NAME, 
DATE_FORMAT(a.PUBLISHED_DATE, '%Y-%m-%d') as PUBLISHED_DATE
FROM BOOK as a
INNER JOIN AUTHOR as b
on a.AUTHOR_ID = b.AUTHOR_ID
WHERE CATEGORY = '경제'
ORDER BY a.PUBLISHED_DATE;

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

SELECT a.PRODUCT_CODE, 
sum(b.SALES_AMOUNT) * a.PRICE  as  SALES
FROM PRODUCT as a, OFFLINE_SALE as b
WHERE a.PRODUCT_ID = b.PRODUCT_ID
GROUP BY a.PRODUCT_CODE
ORDER BY SALES DESC, a.PRODUCT_CODE;

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

SELECT NAME, DATETIME
FROM ANIMAL_INS
WHERE ANIMAL_ID NOT IN 
(SELECT ANIMAL_ID FROM ANIMAL_OUTS)
ORDER BY DATETIME
LIMIT 3;

SELECT a.NAME, a.DATETIME
FROM ANIMAL_INS as a
LEFT JOIN ANIMAL_OUTS as b
ON a.ANIMAL_ID = b.ANIMAL_ID
WHERE b.DATETIME IS NULL
ORDER BY a.DATETIME
LIMIT 3;

4. 없어진 기록 찾기

SELECT b.ANIMAL_ID, b.NAME
FROM ANIMAL_INS as a
RIGHT JOIN ANIMAL_OUTS as b
ON a.ANIMAL_ID = b.ANIMAL_ID
WHERE a.ANIMAL_ID IS NULL
ORDER BY b.ANIMAL_ID;

5. 있었는데요 없었습니다

SELECT a.ANIMAL_ID, a.NAME
FROM ANIMAL_INS as a
INNER JOIN ANIMAL_OUTS as b
ON a.ANIMAL_ID = b.ANIMAL_ID
WHERE a.DATETIME > b.DATETIME
ORDER BY a.DATETIME;

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

SELECT a.PRODUCT_ID, a.PRODUCT_NAME, a.PRICE * sum(b.AMOUNT) as AMOUNT
FROM FOOD_PRODUCT as a
INNER JOIN FOOD_ORDER as b
ON a.PRODUCT_ID = b.PRODUCT_ID
WHERE DATE_FORMAT(b.PRODUCE_DATE, '%Y-%m') = '2022-05'
GROUP BY a.PRODUCT_ID
ORDER BY AMOUNT DESC, a.PRODUCT_ID;

7.특정 기간동안 대여 가능한 자동차들의 대여비용 구하기

SELECT a.CAR_ID, 
a.CAR_TYPE, 
ROUND( a.DAILY_FEE * 30 * (1 - (c.DISCOUNT_RATE / 100)) ) AS FEE
FROM CAR_RENTAL_COMPANY_CAR as a

INNER JOIN 
(SELECT CAR_TYPE, DISCOUNT_RATE FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
WHERE DURATION_TYPE = '30일 이상' AND CAR_TYPE IN ('세단', 'SUV')) as c
ON a.CAR_TYPE = c.CAR_TYPE

AND a.CAR_ID NOT IN 
(SELECT CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE END_DATE >= '2022-11-01' AND START_DATE <= '2022-11-30')

HAVING FEE BETWEEN 500000 AND 1999999

ORDER BY FEE DESC, a.CAR_TYPE, a.CAR_ID DESC;

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

SELECT FLAVOR FROM

(SELECT FLAVOR,  TOTAL_ORDER FROM FIRST_HALF
UNION ALL
SELECT FLAVOR, TOTAL_ORDER FROM JULY
) AS BASE

GROUP BY FLAVOR
ORDER BY SUM(TOTAL_ORDER) DESC
LIMIT 3
;

• MySQL은 FULL OUTER JOIN (FULL JOIN)이 없다
• 따라서 UNION ALL 처리 또는 LEFT JOIN과 RIGHT JOIN을 합쳐야 한다

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

SELECT b.MEMBER_NAME, REVIEW_TEXT, DATE_FORMAT(REVIEW_DATE, '%Y-%m-%d') as REVIEW_DATE
FROM REST_REVIEW as a
INNER JOIN MEMBER_PROFILE as b
ON a.MEMBER_ID = b.MEMBER_ID


WHERE b.MEMBER_ID IN

(
    
SELECT MEMBER_ID
FROM REST_REVIEW

GROUP BY MEMBER_ID

HAVING COUNT(REVIEW_ID) = 
(SELECT COUNT(REVIEW_ID) AS FREQ
FROM REST_REVIEW
GROUP BY MEMBER_ID
ORDER BY FREQ DESC
LIMIT 1)
    
) 

ORDER BY REVIEW_DATE, REVIEW_TEXT;