6.1
1) 상품 별 오프라인 매출 구하기
select p.product_code,
sum(p.price * os.sales_amount) as sales
from product p
right join offline_sale os using (product_id)
group by product_code
order by 2 desc, 1
2) 3월에 태어난 여성 회원 목록 출력하기
select member_id,
member_name,
gender,
date_of_birth
from member_profile
where tlno is not null
and month(date_of_birth) = 3
and gender = 'W'
order by 1
3) 조건에 맞는 도서 리스트 출력하기
select book_id,
published_date
from book
where year(published_date) = 2021
and category = '인문'
order by 2
4) 있었는데요 없었습니다
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
5) 오랜 기간 보호한 동물(1)
select name,
datetime
from animal_ins
where animal_id not in (select animal_id from animal_outs)
order by 2
limit 3
6.2
1) 대여 기록이 존재하는 자동차 리스트 구하기
select distinct ch.car_id
from car_rental_company_car c
right join car_rental_company_rental_history ch using (car_id)
where month(ch.start_date) = 10
and c.car_type = '세단'
order by 1 desc
2) 조건에 맞는 사용자와 총 거래금액 조회하기
select ugs.user_id,
ugs.nickname,
sum(ugb.price) as total_sales
from used_goods_board ugb
join used_goods_user ugs on ugb.writer_id = ugs.user_id
where ugb.status = 'DONE'
group by ugs.user_id
having sum(ugb.price) >= 700000
order by 3
3) 연도 별 평균 미세먼지 농도 조회하기
select year(ym) as 'year',
round(avg(pm_val1),2) as 'pm10',
round(avg(pm_val2),2) as 'pm2.5'
from air_pollution
where location2 = '수원'
group by year(ym)
order by 1
4) 식품분류별 가장 비싼 식품의 정보 조회하기
select category,
price as max_price,
product_name
from food_product
where category in ('과자', '국', '김치', '식용유')
and price in (select max(price) from food_product group by category)
order by 2 desc
5) 보호소에서 중성화한 동물
select ai.animal_id,
ai.animal_type,
ai.name
from animal_ins ai
join animal_outs ao using(animal_id)
where ai.sex_upon_intake like 'Intact%'
and ao.sex_upon_outcome not like 'Intact%'
6.3
1) 오랜 기간 보호한 동물(2)
with base as (
select ai.animal_id,
ai.name,
datediff(ai.datetime, ao.datetime) as time_gap
from animal_ins ai
join animal_outs ao using(animal_id)
)
select animal_id,
name
from base
order by time_gap
limit 2
2) 부서별 평균 연봉 조회하기
select he.dept_id,
hd.dept_name_en,
round(avg(he.sal),0) as avg_sal
from hr_department hd
right join hr_employees he using(dept_id)
group by dept_id
order by 3 desc
3) 5월 식품들의 총매출 조회하기
select product_id,
product_name,
sum(fo.amount * fd.price) as total_sales
from food_product fd
join food_order fo using(product_id)
where fo.produce_date between '2022-05-01' and '2022-05-31'
group by product_id
order by 3 desc, 1
4) 물고기 종류 별 잡은 수 구하기
select count(*) as fish_count,
fni.fish_name
from fish_info fi
join fish_name_info fni using(fish_type)
group by fish_name
order by 1 desc
5) 잔챙이 잡은 수 구하기
select count(*) as fish_count
from fish_info
where length is null
6.4
1) 월별 잡은 물고기 수 구하기
select count(*) as fish_count,
month(time) as month
from fish_info
group by month
having fish_count is not null
order by 2
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 food_type
order by 1 desc
3) 가격대 별 상품 개수 구하기
select case when (0 < price) and (price < 10000) then 0
when (10000 <= price) and (price < 20000) then 10000
when (20000 <= price) and (price < 30000) then 20000
when (30000 <= price) and (price < 40000) then 30000
when (40000 <= price) and (price < 50000) then 40000
when (50000 <= price) and (price < 60000) then 50000
when (60000 <= price) and (price < 70000) then 60000
when (70000 <= price) and (price < 80000) then 70000
when (80000 <= price) and (price < 90000) then 80000
end as price_group,
count(*) as products
from product
group by price_group
order by 1
4) 평균 일일 대여 요금 구하기
select round(avg(daily_fee),0) as averge_fee
from car_rental_company_car
where car_type = 'SUV'
5) 특정 옵션이 포함된 자동차 리스트 구하기
select *
from car_rental_company_car
where options like '%네비게이션%'
order by car_id desc
6.5
1) 과일로 만든 아이스크림 고르기
select flavor
from first_half fh
join icecream_info i using (flavor)
where fh.total_order > 3000
and i.ingredient_type = 'fruit_based'
order by fh.total_order desc
2) Python 개발자 찾기
select id,
email,
first_name,
last_name
from developer_infos
where skill_1 = 'Python'
or skill_2 = 'Python'
or skill_3 = 'Python'
order by 1
3) 루시와 엘라 찾기
select animal_id,
name,
sex_upon_intake
from animal_ins
where name in ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty')
order by 1
4) 모든 레코드 조회하기
select *
from animal_ins
order by animal_id
5) ROOT 아이템 구하기
select item_id,
item_name
from item_info ii
join item_tree it using(item_id)
where it.parent_item_id is null
order by 1
6.6
1) 한 해에 잡은 물고기 수 구하기
select count(*) as fish_count
from fish_info
where year(time) = 2021
2) 없어진 기록 찾기
select ao.animal_id,
ao.name
from animal_ins ai
right join animal_outs ao using (animal_id)
where ai.animal_id is null
order by 1,2
3) 조건에 맞는 사원 정보 조회하기
select sum(hg.score) as score,
he.emp_no,
he.emp_name,
he.position,
he.email
from hr_employees he
join hr_grade hg using(emp_no)
where hg.year = 2022
group by he.emp_no
order by 1 desc
limit 1
6.7
1) 특정 물고기를 잡은 총 수 구하기
select count(*) as fish_count
from fish_info fi
join fish_name_info fn using(fish_type)
where fn.fish_name = 'BASS'
or fn.fish_name = 'SNAPPER'
2) 취소되지 않은 진료 예약 조회하기
select a.apnt_no,
p.pt_name,
p.pt_no,
a.mcdp_cd,
d.dr_name,
a.apnt_ymd
from appointment a
join doctor d on a.mddr_id = d.dr_id
join patient p using(pt_no)
where date(a.apnt_ymd) = '2022-04-13'
and a.apnt_cncl_ymd is null
and a.mcdp_cd = 'CS'
order by 6
3) 최댓값 구하기
select datetime as 시간
from animal_ins
where datetime = (select max(datetime) from animal_ins)
