[프로그래머스] JOIN (MySQL)

조건에 맞는 도서와 저자 리스트 출력하기 (Lv. 2)

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

프로그래머스

# MySQL 
SELECT A.BOOK_ID, B.AUTHOR_NAME, DATE_FORMAT(A.PUBLISHED_DATE, '%Y-%m-%d') AS PUBLISHED_DATE
FROM BOOK A
LEFT JOIN AUTHOR B ON A.AUTHOR_ID = B.AUTHOR_ID
WHERE A.CATEGORY='경제'
ORDER BY A.PUBLISHED_DATE

 

 

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

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

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

 

 

없어진 기록 찾기 (Lv. 3)

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

프로그래머스

SELECT A.ANIMAL_ID, A.NAME
FROM ANIMAL_OUTS A
LEFT JOIN ANIMAL_INS B ON A.ANIMAL_ID=B.ANIMAL_ID
WHERE A.DATETIME IS NOT NULL AND B.DATETIME IS NULL
ORDER BY ANIMAL_ID, NAME

 

 

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

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

프로그래머스

 

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

 

 

있었는데요 없었습니다(Lv. 3)

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

프로그래머스

SELECT INS.ANIMAL_ID, INS.NAME 
FROM ANIMAL_INS INS, ANIMAL_OUTS OUTS 
WHERE INS.ANIMAL_ID = OUTS.ANIMAL_ID AND INS.DATETIME > OUTS.DATETIME 
ORDER BY INS.DATETIME

 

 

5월 식품들의 총매출 조회하기(Lv. 4)

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

프로그래머스

SELECT PRDT.PRODUCT_ID, PRDT.PRODUCT_NAME, SUM(PRDT.PRICE * ODR.AMOUNT) AS TOTAL_SALES
FROM FOOD_PRODUCT PRDT, FOOD_ORDER ODR
WHERE PRDT.PRODUCT_ID=ODR.PRODUCT_ID AND ODR.PRODUCE_DATE BETWEEN '2022-05-01' AND '2022-05-31'
GROUP BY PRDT.PRODUCT_ID
ORDER BY TOTAL_SALES DESC, PRDT.PRODUCT_ID ASC

 

 

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

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

프로그래머스

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

July 테이블을 flavor 별 total_order를 계산한 결과 테이블과 join한다.