7.20
1) 조건별로 분류하여 주문상태 출력하기
select order_id,
product_id,
out_date,
case when out_date > '2022-05-01' then '출고대기'
when out_date is null then '출고미정' else '출고완료' end as 출고여부
from food_order
order by 1
2) 카테고리 별 도서 판매량 집계하기
select b.category,
sum(bs.sales) as total_sales
from book b
join book_sales bs using(book_id)
where bs.sales_date like '2022-01%'
group by category
order by 1
7.21
1) 있었는데요 없었습니다
select ai.animal_id,
ai.name
from animal_ins ai
join animal_outs ao using(animal_id)
where ai.datetime > ao.datetime
order by ai.datetime
2) 오랜 기간 보호한 동물(1)
select name,
datetime
from animal_ins
where animal_id not in (select animal_id from animal_outs)
order by 2
limit 3
3) 대여 기록이 존재하는 자동차 리스트 구하기
select distinct h.car_id
from car_rental_company_car c
join car_rental_company_rental_history h using(car_id)
where c.car_type = '세단'
and month(h.start_date) = 10
order by 1 desc
4) 조건에 맞는 사용자와 총 거래금액 조회하기
select u.user_id,
u.nickname,
sum(b.price) as total_sales
from used_goods_board b
join used_goods_user u on b.writer_id = u.user_id
where b.status = 'Done'
group by 1
having sum(b.price) >= 700000
order by 3
5) 오랜 기간 보호한 동물(2)
with base as (
select ai.animal_id,
ai.name,
datediff(ai.datetime, ao.datetime) as protection_time
from animal_ins ai
join animal_outs ao using(animal_id)
)
select animal_id,
name
from base
order by protection_time
limit 2
7.22
1) 부서별 평균 연봉 조회하기
select e.dept_id,
d.dept_name_en,
round(avg(e.sal),0) as avg_sal
from hr_department d
join hr_employees e using(dept_id)
group by 2
order by 3 desc
2) 즐겨찾기가 가장 많은 식당 정보 출력하기
select food_type,
rest_id,
rest_name,
favorites
from rest_info
where favorites in (select max(favorites) from rest_info group by food_type)
group by 1
order by 1 desc
3) 조건에 맞는 사용자 정보 조회하기
select u.user_id,
u.nickname,
concat(u.city, ' ', u.street_address1, ' ', u.street_address2) as 전체주소,
concat(substring(u.tlno,1,3), '-', substring(u.tlno,4,4), '-', substring(u.tlno,8)) as 전화번호
from used_goods_user u
join used_goods_board b on u.user_id = b.writer_id
group by 1
having count(*) >= 3
order by 1 desc
4) 대장균의 크기에 따라 분류하기 1
select id,
case when size_of_colony <= 100 then 'LOW'
when size_of_colony > 1000 then 'HIGH'
else 'MEDIUM' end as size
from ecoli_data
order by 1
5) 없어진 기록 찾기
select o.animal_id,
o.name
from animal_outs o
left join animal_ins i using(animal_id)
where i.animal_id is null
order by 1,2
7.23
1) 자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기
select car_id,
max(case when '2022-10-16' between start_date and end_date then '대여중'
else '대여 가능' end) as availability
from car_rental_company_rental_history
group by 1
order by 1 desc
2) 조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기
select concat('/home/grep/src/', f.board_id, '/', f.file_id, f.file_name, f.file_ext) as file_path
from used_goods_file f
join used_goods_board b using(board_id)
where b.views = (select max(views) from used_goods_board)
order by f.file_id desc
3) 특정 조건을 만족하는 물고기별 수와 최대 길이 구하기
select count(*) as fish_count,
max(length) as max_length,
fish_type
from fish_info
group by 3
having avg(ifnull(length,10)) >= 33
order by 3
4) 헤비 유저가 소유한 장소
select id,
name,
host_id
from places
where host_id in (select host_id from places group by 1 having count(*) >= 2)
order by 1
5) 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기
select month(start_date) as month,
car_id,
count(*) as records
from car_rental_company_rental_history
where car_id in (select car_id
from car_rental_company_rental_history
where start_date between '2022-08-01' and '2022-10-31'
group by 1
having count(*) >= 5)
and start_date between '2022-08-01' and '2022-10-31'
group by 1,2
having count(*) > 0
order by 1, 2 desc
7.24
1) 업그레이드 할 수 없는 아이템 구하기
select item_id,
item_name,
rarity
from item_info i
join item_tree t using(item_id)
where item_id not in (select parent_item_id from item_tree where parent_item_id is not null)
order by 1 desc
2) 물고기 종류 별 대어 찾기
select i.id,
n.fish_name,
i.length
from fish_info i
join fish_name_info n using(fish_type)
where (i.fish_type,i.length) in (select fish_type, max(length) from fish_info group by fish_type)
order by 1
