JOIN

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

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

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

SELECT MEMBER_NAME, REVIEW_TEXT
    , date_format(REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE
FROM MEMBER_PROFILE m
    JOIN REST_REVIEW r
        ON m.MEMBER_ID = r.MEMBER_ID
WHERE m.MEMBER_ID IN (SELECT MEMBER_ID
                      FROM (SELECT MEMBER_ID, COUNT(*) CNT
                              FROM REST_REVIEW
                              GROUP BY MEMBER_ID
                              ORDER BY CNT DESC LIMIT 1) as t
                     )
ORDER BY REVIEW_DATE, REVIEW_TEXT

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

SELECT A.CAR_ID, A.CAR_TYPE, FLOOR(A.DAILY_FEE*((100-B.DISCOUNT_RATE)/100)*30) AS FEE FROM CAR_RENTAL_COMPANY_CAR A
RIGHT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN B ON A.CAR_TYPE = B.CAR_TYPE
WHERE A.CAR_TYPE IN ('세단', 'SUV') AND B.duration_type = '30일 이상' AND A.DAILY_FEE*((100-B.DISCOUNT_RATE)/100)*30>=500000 AND A.DAILY_FEE*((100-B.DISCOUNT_RATE)/100)*30<2000000
AND A.CAR_ID NOT IN (
    SELECT CAR_ID FROM (
        SELECT C.CAR_ID FROM CAR_RENTAL_COMPANY_CAR C
            RIGHT JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY D ON C.CAR_ID = D.CAR_ID
        WHERE (D.START_DATE <"2022-12-01" AND D.END_DATE>="2022-11-01")
    ) E 
)
ORDER BY FEE DESC, A.CAR_TYPE ASC ,A.CAR_ID DESC

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

// Some code

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

// Some code

없어진 기록 찾기

SELECT o.ANIMAL_ID, o.NAME 
FROM ANIMAL_OUTS As o
    LEFT JOIN ANIMAL_INS As i 
    ON o.ANIMAL_ID = i.ANIMAL_ID
WHERE i.ANIMAL_ID IS NULL

있었는데요 없었습니다

SELECT DISTINCT ins.ANIMAL_ID, ins.NAME 
FROM ANIMAL_INS ins
    JOIN ANIMAL_OUTS outs
    ON ins.ANIMAL_ID = outs.ANIMAL_ID
WHERE ins.DATETIME > outs.DATETIME
ORDER BY ins.DATETIME

오랜 기간 보호한 동물 (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 DATETIME LIMIT 3

보호소에서 중성화한 동물

SELECT i.ANIMAL_ID, i.ANIMAL_TYPE, i.NAME
FROM ANIMAL_INS i
    JOIN ANIMAL_OUTS o
    ON i.ANIMAL_ID = o.ANIMAL_ID
WHERE i.SEX_UPON_INTAKE <> o.SEX_UPON_OUTCOME
ORDER BY i.ANIMAL_ID

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

SELECT i.ANIMAL_ID, i.ANIMAL_TYPE, i.NAME
FROM ANIMAL_INS i
    JOIN ANIMAL_OUTS o
    ON i.ANIMAL_ID = o.ANIMAL_ID
WHERE i.SEX_UPON_INTAKE <> o.SEX_UPON_OUTCOME
ORDER BY i.ANIMAL_ID

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

SELECT YEAR(o.SALES_DATE) AS YEAR
        , MONTH(o.SALES_DATE) AS MONTH
        , COUNT(DISTINCT o.USER_ID) AS PUCHASED_USERS
        , ROUND(COUNT(DISTINCT o.USER_ID) / (SELECT COUNT(USER_ID)
                FROM USER_INFO 
                WHERE JOINED BETWEEN '2021-01-01' AND '2021-12-31')
                , 1) AS PUCHASED_RATIO
FROM USER_INFO u
JOIN ONLINE_SALE o
    ON u.USER_ID = o.USER_ID
WHERE JOINED BETWEEN '2021-01-01' AND '2021-12-31'
    AND o.USER_ID IS NOT NULL
GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH