[Programmers/MySQL] JOIN 문제 풀이 모음

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

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

 

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

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

 

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

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

 

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

SELECT ANIMAL_INS.ANIMAL_ID, ANIMAL_INS.NAME
FROM ANIMAL_INS
LEFT JOIN ANIMAL_OUTS
ON ANIMAL_INS.ANIMAL_ID = ANIMAL_OUTS.ANIMAL_ID
WHERE ANIMAL_INS.DATETIME > ANIMAL_OUTS.DATETIME
ORDER BY ANIMAL_INS.DATETIME ASC;

 

5. 없어진 기록 찾기

SELECT ANIMAL_OUTS.ANIMAL_ID, ANIMAL_OUTS.NAME
FROM ANIMAL_OUTS
LEFT OUTER JOIN ANIMAL_INS
ON ANIMAL_OUTS.ANIMAL_ID = ANIMAL_INS.ANIMAL_ID
WHERE ANIMAL_INS.ANIMAL_ID IS NULL
ORDER BY ANIMAL_OUTS.ANIMAL_ID ASC;

 

6. 보호소에서 중성화한 동물

SELECT ANIMAL_INS.ANIMAL_ID, ANIMAL_INS.ANIMAL_TYPE, ANIMAL_INS.NAME
FROM ANIMAL_INS
LEFT JOIN ANIMAL_OUTS
ON ANIMAL_INS.ANIMAL_ID = ANIMAL_OUTS.ANIMAL_ID
WHERE ANIMAL_INS.SEX_UPON_INTAKE LIKE 'Intact%'
AND ( ANIMAL_OUTS.SEX_UPON_OUTCOME LIKE 'Spayed%'
     OR ANIMAL_OUTS.SEX_UPON_OUTCOME LIKE 'Neutered%')
ORDER BY ANIMAL_INS.ANIMAL_ID ASC;

 

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

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

 

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

SELECT FOOD_ORDER.PRODUCT_ID, FOOD_PRODUCT.PRODUCT_NAME, SUM(FOOD_PRODUCT.PRICE * FOOD_ORDER.AMOUNT) AS TOTAL_SALES
FROM FOOD_ORDER JOIN FOOD_PRODUCT
ON FOOD_ORDER.PRODUCT_ID = FOOD_PRODUCT.PRODUCT_ID
WHERE FOOD_ORDER.PRODUCE_DATE LIKE '2022-05%'
GROUP BY FOOD_ORDER.PRODUCT_ID
ORDER BY TOTAL_SALES DESC, FOOD_PRODUCT.PRODUCT_ID ASC;

 

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

SELECT MEMBER_PROFILE.MEMBER_NAME, REST_REVIEW.REVIEW_TEXT, DATE_FORMAT(REST_REVIEW.REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE
FROM MEMBER_PROFILE
INNER JOIN REST_REVIEW
ON MEMBER_PROFILE.MEMBER_ID = REST_REVIEW.MEMBER_ID
WHERE MEMBER_PROFILE.MEMBER_ID = (SELECT MEMBER_ID FROM REST_REVIEW
                                 GROUP BY MEMBER_ID
                                 ORDER BY COUNT(*) DESC LIMIT 1)
ORDER BY REVIEW_DATE ASC, REVIEW_TEXT ASC;

 

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

SELECT YEAR, MONTH, COUNT(*) AS PUCHASED_USERS, ROUND((COUNT(*) / (SELECT COUNT(*)
                                                                  FROM USER_INFO
                                                                  WHERE YEAR(JOINED) = 2021)), 1) AS PUCHASED_RATIO
FROM (
    SELECT DISTINCT YEAR(ONLINE_SALE.SALES_DATE) AS YEAR, MONTH(ONLINE_SALE.SALES_DATE) AS MONTH, USER_INFO.USER_ID
    FROM ONLINE_SALE
    JOIN USER_INFO
    ON USER_INFO.USER_ID = ONLINE_SALE.USER_ID
    AND YEAR(USER_INFO.JOINED)=2021) A
    
GROUP BY YEAR, MONTH
ORDER BY YEAR ASC, MONTH ASC;

 

 

 

 

프로그래머스