[SQL_JOIN]

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

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

📌있었는데요 없었습니다

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

📌없어진 기록 찾기

SELECT
    outs.animal_id, outs.name
FROM animal_outs AS outs
	LEFT JOIN animal_ins AS ins
	ON outs.animal_id = ins.animal_id
WHERE
    ins.animal_id IS NULL;

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

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

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

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

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

#극악이다.. 이런문제 마주치고 싶지 않음
SELECT M.MEMBER_NAME, R.REVIEW_TEXT, DATE_FORMAT(R.REVIEW_DATE, "%Y-%m-%d")
FROM MEMBER_PROFILE M
JOIN (
    SELECT REVIEW_TEXT, REVIEW_DATE, MEMBER_ID
    FROM REST_REVIEW
    WHERE MEMBER_ID = (
        SELECT MEMBER_ID
        FROM REST_REVIEW
        GROUP BY MEMBER_ID
        ORDER BY COUNT(*) DESC
        LIMIT 1)
    ) R
ON R.MEMBER_ID = M.MEMBER_ID
ORDER BY R.REVIEW_DATE, R.REVIEW_TEXT