🌎 2022.02.22 updated

Level 2️⃣

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

-- '경제' 카테고리에 속하는 도서들의 도서 ID(BOOK_ID), 저자명(AUTHOR_NAME), 출판일(PUBLISHED_DATE) 리스트를 출력하는 SQL문을 작성해주세요. 
-- 결과는 출판일을 기준으로 오름차순 정렬해주세요.

select book_id, author_name, date_format(published_date, '%Y-%m-%d') as published_date
from book, author
where book.author_id=author.author_id
and category='경제'
order by published_date

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

-- PRODUCT 테이블과 OFFLINE_SALE 테이블에서 상품코드 별 매출액(판매가 * 판매량) 합계를 출력하는 SQL문을 작성해주세요.
-- 결과는 매출액을 기준으로 내림차순 정렬해주시고 매출액이 같다면 상품코드를 기준으로 오름차순 정렬해주세요.

SELECT product_code, sum(price*sales_amount) as sales
from product as p, offline_sale as o
where p.product_id=o.product_id
group by product_code
order by sales desc, product_code

-- sales를 price*sales_amount가 아니라 sum(price*sales_amount)으로 해주어야 함

Level 3️⃣

없어진 기록 찾기

-- 천재지변으로 인해 일부 데이터가 유실되었습니다.
-- 입양을 간 기록은 있는데, 보호소에 들어온 기록이 없는 동물의 ID와 이름을 ID 순으로 조회하는 SQL문을 작성해주세요.

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

있었는데요 없었습니다

-- 관리자의 실수로 일부 동물의 입양일이 잘못 입력되었습니다.
-- 보호 시작일보다 입양일이 더 빠른 동물의 아이디와 이름을 조회하는 SQL문을 작성해주세요.
-- 이때 결과는 보호 시작일이 빠른 순으로 조회해야합니다.

select animal_ins.animal_id, animal_ins.name
from animal_ins, animal_outs
where animal_ins.animal_id=animal_outs.animal_id
and animal_ins.datetime > animal_outs.datetime
order by animal_ins.datetime

-- 처음에 datediff 함수를 사용하여 틀렸다.
-- 날짜가 동일해도 시작 시간보다 입양 시간이 빠를 경우도 있으므로 timediff를 사용해주어야 한다.

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

-- 아직 입양을 못 간 동물 중, 가장 오래 보호소에 있었던 동물 3마리의 이름과 보호 시작일을 조회하는 SQL문을 작성해주세요.
-- 이때 결과는 보호 시작일 순으로 조회해야 합니다.

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.animal_id is null
order by animal_ins.datetime
limit 0,3

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

-- MEMBER_PROFILE와 REST_REVIEW 테이블에서 리뷰를 가장 많이 작성한 회원의 리뷰들을 조회하는 SQL문을 작성해주세요. 
-- 회원 이름, 리뷰 텍스트, 리뷰 작성일이 출력되도록 작성해주시고, 
-- 결과는 리뷰 작성일을 기준으로 오름차순, 리뷰 작성일이 같다면 리뷰 텍스트를 기준으로 오름차순 정렬해주세요.
 
select m.member_name, r.review_text, date_format(r.review_date, '%Y-%m-%d') as review_date from
member_profile as m, rest_review as r
where m.member_id=r.member_id
and m.member_name in (
    select * from (select m.member_name from
member_profile as m, rest_review as r
where m.member_id=r.member_id
group by m.member_name
order by count(r.review_text) desc
limit 0, 1) as t
)
order by review_date, review_text

Level 4️⃣

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

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

-- 7월 아이스크림 총 주문량과 상반기의 아이스크림 총 주문량을 더한 값이 큰 순서대로 상위 3개의 맛을 조회하는 SQL 문을 작성해주세요.

select flavor
from
(select shipment_id, flavor, total_order
from first_half
union all
select shipment_id, flavor, total_order
from july) as temp
group by flavor
order by sum(total_order) desc
limit 0, 3

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

-- FOOD_PRODUCT와 FOOD_ORDER 테이블에서 생산일자가 2022년 5월인 식품들의 식품 ID, 식품 이름, 총매출을 조회하는 SQL문을 작성해주세요.
-- 이때 결과는 총매출을 기준으로 내림차순 정렬해주시고 총매출이 같다면 식품 ID를 기준으로 오름차순 정렬해주세요.

select p.product_id, p.product_name, sum(amount)*price as total_sales
from food_product as p, food_order o
where p.product_id=o.product_id
and produce_date like '2022-05%'
group by p.product_id, p.product_name
order by total_sales desc, p.product_id

보호소에서 중성화한 동물

-- 보호소에서 중성화 수술을 거친 동물 정보를 알아보려 합니다.
-- 보호소에 들어올 당시에는 중성화되지 않았지만, 보호소를 나갈 당시에는 중성화된 동물의 아이디와 생물 종, 이름을 조회하는 아이디 순으로 조회하는 SQL 문을 작성해주세요.

-- 중성화 X - Intact
-- 중성화 O - Spayed 또는 Neutered

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 regexp 'Spayed|Neutered'

-- regexp는 함수가 아니라, like 같은 검색 방식이므로 괄호를 쓰지 않는다

Level 5️⃣

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

-- USER_INFO 테이블과 ONLINE_SALE 테이블에서 2021년에 가입한 전체 회원들 중 상품을 구매한 회원수와 상품을 구매한 회원의 비율(=2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수)을 년, 월 별로 출력하는 SQL문을 작성해주세요.
-- 상품을 구매한 회원의 비율은 소수점 두번째자리에서 반올림하고, 
-- 전체 결과는 년을 기준으로 오름차순 정렬해주시고 년이 같다면 월을 기준으로 오름차순 정렬해주세요.

select year(sales_date) as year, month(sales_date) as month, 
count(distinct sale.user_id) as puchased_user, 
round((count(distinct sale.user_id)/(select count(user_id) from user_info where year(joined)=2021)), 1) as puchased_ratio
from user_info as user, online_sale as sale
where user.user_id=sale.user_id
and year(joined)=2021
group by year, month
order by year, month