6.8
1) 잡은 물고기 중 가장 큰 물고기의 길이 구하기
select concat(max(length), 'cm') as max_length
from fish_info
2) 잡은 물고기의 평균 길이 구하기
select round(avg(ifnull(length,10)),2) as average_length
from fish_info
3) 대장균의 크기에 따라 분류하기 1
select id,
case when size_of_colony <= 100 then 'LOW'
when size_of_colony > 100 and size_of_colony <=1000 then 'MEDIUM'
else 'HIGH'
end as size
from ecoli_data
order by 1
4) 조건에 맞는 사용자 정보 조회하기
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_board b
join used_goods_user u on b.writer_id = u.user_id
group by u.user_id
having count(*) >= 3
order by 1 desc
5) 조건에 부합하는 중고거래 상태 조회하기
select board_id,
writer_id,
title,
price,
case when status = 'SALE' then '판매중'
when status = 'RESERVED' then '예약중'
else '거래완료'
end as status
from used_goods_board
where created_date = '2022-10-05'
order by 1 desc
6.9
1) 자동차 대여 기록에서 장기/단기 대여 구분하기
select history_id,
car_id,
date_format(start_date, '%Y-%m-%d') as start_date,
date_format(end_date, '%Y-%m-%d') as end_date,
case when datediff(end_date, start_date) >= 29 then '장기 대여'
else '단기 대여' end as rent_type
from car_rental_company_rental_history
where start_date between '2022-09-01' and '2022-09-30'
order by 1 desc
2) 자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기
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 car_id
order by 1 desc
3) 자동차 평균 대여 기간 구하기
select car_id,
round(avg(datediff(end_date, start_date)+1),1) as average_duration
from car_rental_company_rental_history
group by car_id
having average_duration >= 7
order by 2 desc, 1 desc
4) 조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기
select concat('/home/grep/src/', board_id, '/', file_id, file_name, 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 file_id desc
5) 재구매가 일어난 상품과 회원 리스트 구하기
select user_id,
product_id
from online_sale
group by user_id, product_id
having count(*) >= 2
order by 1, 2 desc
6.10
1) 저자 별 카테고리 별 매출액 집계하기
select b.author_id,
a.author_name,
b.category,
sum(s.sales * b.price) as total_sales
from book b
join author a using (author_id)
join book_sales s using(book_id)
where s.sales_date like '2022-01%'
group by b.author_id, b.category
order by 1, 3 desc
2) 노선별 평균 역 사이 거리 조회하기
select route,
concat(round(sum(d_between_dist),1), 'km') as total_distance,
concat(round(avg(d_between_dist),2), 'km') as average_distance
from subway_distance
group by route
order by round(sum(d_between_dist),1) desc
3) 특정 조건을 만족하는 물고기별 수와 최대 길이 구하기
select count(*) as fish_count,
max(ifnull(length,10)) as max_length,
fish_type
from fish_info
group by fish_type
having avg(ifnull(length,10)) >= 33
order by 3
4) 서울에 위치한 식당 목록 출력하기
select i.rest_id,
i.rest_name,
i.food_type,
i.favorites,
i.address,
round(avg(r.review_score),2) as score
from rest_info i
join rest_review r using (rest_id)
where i.address like '서울%'
group by i.rest_id
order by 6 desc, 4 desc
5) 가장 큰 물고기 10마리 구하기
select id,
length
from fish_info
order by 2 desc, 1
limit 10
6.11
1) 년, 월, 성별 별 상품 구매 회원 수 구하기
select year(sales_date) as year,
month(sales_date) as month,
u.gender,
count(distinct user_id) as users
from user_info u
join online_sale o using (user_id)
where u.gender is not null
group by 1,2,3
order by 1,2,3
2) 헤비 유저가 소유한 장소
select id,
name,
host_id
from places
where host_id in (select host_id from places group by host_id having count(*) >= 2)
order by 1
3) 우유와 요거트가 담긴 장바구니
select distinct c1.cart_id
from cart_products c1
inner join cart_products c2 using(cart_id)
where c1.name = 'Milk'
and c2.name = 'Yogurt'
order by c1.id
4) 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기
select month(start_date) as month,
car_id,
count(*) as records
from car_rental_company_rental_history
where start_date between '2022-08-01' and '2022-10-31'
and 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 car_id having count(*) >= 5)
group by car_id, month(start_date)
having count(*) >= 1
order by 1, 2 desc
5) 주문량이 많은 아이스크림들 조회하기
select f.flavor
from first_half f
inner join july j using(flavor)
group by f.flavor
order by sum(f.total_order) + sum(j.total_order) desc
limit 3
6.12
1) 연간 평가점수에 해당하는 평가 등급 및 성과금 조회하기
select he.emp_no,
he.emp_name,
case when avg(hg.score) >= 96 then 'S'
when avg(hg.score) >= 90 then 'A'
when avg(hg.score) >= 80 then 'B'
else 'C' end as grade,
case when avg(hg.score) >= 96 then he.sal * 0.2
when avg(hg.score) >= 90 then he.sal * 0.15
when avg(hg.score) >= 80 then he.sal * 0.1
else 0 end as bonus
from hr_employees he
inner join hr_grade hg using(emp_no)
group by he.emp_no
order by 1
2) 업그레이드 할 수 없는 아이템 구하기
select item_id,
item_name,
rarity
from item_info
where item_id not in (select parent_item_id from item_tree where parent_item_id is not null)
order by 1 desc
3) 물고기 종류 별 대어 찾기
select i.id,
n.fish_name,
i.length
from fish_info i
inner 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
4) 대장균들의 자식의 수 구하기
select e1.id,
count(e2.id) as child_count
from ecoli_data e1
left join ecoli_data e2 on e1.id = e2.parent_id
group by 1
order by 1
5) 그룹별 조건에 맞는 식당 목록 출력하기
select p.member_name,
r.review_text,
r.review_date
from member_profile p
join rest_review r using(member_id)
where r.member_id = (select member_id from rest_review group by 1 order by count(*) desc limit 1)
order by 3, 2
6.13
1) 조건에 부합하는 중고거래 댓글 조회하기
select b.title,
b.board_id,
r.reply_id,
r.writer_id,
r.contents,
r.created_date
from used_goods_board b
inner join used_goods_reply r using (board_id)
where b.created_date between '2022-10-01' and '2022-10-31'
order by 6, 1
2) 분기별 분화된 대장균의 개체 수 구하기
select concat(quarter(differentiation_date), 'Q') as quarter,
count(*) as ecoli_count
from ecoli_data
group by quarter(differentiation_date)
order by 1
3) 오프라인/온라인 판매 데이터 통합하기
select sales_date,
product_id,
user_id,
sales_amount
from online_sale
where sales_date between '2022-03-01' and '2022-03-31'
union all
select sales_date,
product_id,
NULL as user_id,
sales_amount
from offline_sale
where sales_date between '2022-03-01' and '2022-03-31'
order by 1,2,3
4) 업그레이드 된 아이템 구하기
select t.item_id,
i.item_name,
i.rarity
from item_info i
join item_tree t using(item_id)
where t.parent_item_id in (select item_id from item_info where rarity = 'RARE')
order by 1 desc
5) 특정 세대의 대장균 찾기
select e1.id
from ecoli_data e1
join ecoli_data e2 on e1.parent_id = e2.id
join ecoli_data e3 on e2.parent_id = e3.id
where e3.parent_id is null
order by 1
