하나둘셋넷

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

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

-- 코드를 입력하세요
SELECT P.PRODUCT_CODE, SUM(O.SALES_AMOUNT) * P.PRICE as SALES
    FROM PRODUCT P
    INNER JOIN OFFLINE_SALE O on P.PRODUCT_ID = O.PRODUCT_ID
    GROUP BY P.PRODUCT_ID
    ORDER BY SALES DESC,  P.PRODUCT_CODE

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

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

-- 코드를 입력하세요
SELECT F.FLAVOR
FROM ( 
    SELECT FLAVOR, TOTAL_ORDER
    FROM FIRST_HALF
    
) F
INNER JOIN JULY J ON F.FLAVOR = J.FLAVOR
GROUP BY F.FLAVOR
ORDER BY (SUM(F.TOTAL_ORDER) + SUM(J.TOTAL_ORDER)) DESC
LIMIT 3

SQL JOIN] 오랜 기간 보호한 동물

SQL JOIN] 있었는데요 없었습니다

-- 코드를 입력하세요
SELECT I.ANIMAL_ID, I.NAME
    FROM ANIMAL_INS I
    INNER JOIN(
        SELECT ANIMAL_ID,DATETIME
        FROM ANIMAL_OUTS
    ) AS O ON I.ANIMAL_ID = O.ANIMAL_ID
    WHERE I.DATETIME > O.DATETIME
    ORDER BY I.DATETIME

SQL JOIN] 없어진 기록 찾기

-- 코드를 입력하세요
SELECT o.ANIMAL_ID, o.NAME
    FROM ANIMAL_OUTS o # 보호소에 들어온 기록
    LEFT JOIN ANIMAL_INS b on b.ANIMAL_ID = o.ANIMAL_ID
    WHERE b.ANIMAL_ID IS NULL # 입양을 갔는데 보호소에 들어온 기록이 없다.

SQL JOIN] 즐겨찾기가 가장 많은 식당 정보 출력하기

-- 코드를 입력하세요
SELECT b.BOOK_ID, A.AUTHOR_NAME, DATE_FORMAT(b.PUBLISHED_DATE, "%Y-%m-%d")
    FROM BOOK b
    INNER JOIN(
        SELECT AUTHOR_ID, AUTHOR_NAME
        FROM AUTHOR
        
    ) AS A ON b.AUTHOR_ID = A.AUTHOR_ID
    WHERE CATEGORY = '경제'
    ORDER BY b.PUBLISHED_DATE;

1] BOOK 테이블에서 BOOK_ID, PUBLISHED_DATE 가져오기

2] DATE_FORMAT(b.PUBLISHED_DATE, "%Y-%m-%d")을 사용하여 형식 맞추기

3] INNER JOIN을 위해 AUTHOR 테이블의 AUTHOR_ID 필요

4] 테이블 출력 시에 AUTHOR 테이블의 AUTHOR_NAME 필요