[SQL] JOIN 연습문제

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

SELECT book_id, author_name, date_format(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 b.published_date;

없어진 기록 찾기

SELECT O.animal_id, O.name
from animal_outs O
where O.animal_id not in (
    select I.animal_id 
    from animal_ins I join animal_outs O on I.animal_id = O.animal_id
)
order by O.animal_id;

있었는데요 없었습니다

SELECT I.animal_id, I.name
from animal_ins I join animal_outs O on I.animal_id = O.animal_id
where I.datetime > O.datetime
order by I.datetime

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

SELECT I.name, I.datetime
from animal_ins I
where I.animal_id not in (
    select I.animal_id
    from animal_ins I join animal_outs O on I.animal_id = O.animal_id
)
order by I.datetime
limit 3;

보호소에서 중성화한 동물

SELECT O.animal_id, O.animal_type, O.name
from animal_outs O join animal_ins I on O.animal_id = I.animal_id
where O.sex_upon_outcome not like "Intact%" and I.sex_upon_intake like "Intact%"
order by O.animal_id;

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

select p.product_code, cnt*p.price sales
from product p join (
    SELECT p.product_id, sum(s.sales_amount) cnt
    from product p join offline_sale s on p.product_id = s.product_id 
    group by p.product_id
    ) e on p.product_id = e.product_id
order by cnt*p.price DESC, p.product_code

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

select year(os.SALES_DATE) year,
month(os.SALES_DATE) month,
count(distinct usr2021.user_id) purchased_users,
round((count(distinct usr2021.user_id)/(select count(*) from user_info ui
    where year(ui.joined) = "2021")),1) purchased_ratio
from online_sale os join (
    SELECT ui.user_id
    from user_info ui
    where year(ui.joined) = "2021"
    ) usr2021 on os.user_id = usr2021.user_id
group by year, month
order by year, month

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

select mem.member_name, rev.review_text, date_format(rev.review_date, "%Y-%m-%d") as review_date
from rest_review rev join member_profile mem on rev.member_id = mem.member_id
where rev.member_id = (
    select id
    from (
        select mem.member_id as id, count(mem.member_id) as myCount
        from member_profile mem join rest_review rev on mem.member_id = rev.member_id
        group by mem.member_id
        order by myCount DESC
        limit 1
    ) as e
)
order by rev.review_date, rev.review_text

출처: https://school.programmers.co.kr/learn/challenges?tab=sql_practice_kit