7.27
1) 보호소에서 중성화한 동물
select i.animal_id,
i.animal_type,
i.name
from animal_ins i
join animal_outs o using(animal_id)
where i.sex_upon_intake like 'Intact%'
and (o.sex_upon_outcome like 'Spayed%' or o.sex_upon_outcome like 'Neutered%')
2) 식품분류별 가장 비싼 식품의 정보 조회하기
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
7.28
1) 5월 식품들의 총매출 조회하기
select p.product_id,
p.product_name,
sum(p.price * o.amount) as total_sales
from food_product p
join food_order o using(product_id)
where o.produce_date between '2022-05-01' and '2022-05-31'
group by 1
order by 3 desc, 1
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
7.29
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 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
7.30
1) 년, 월, 성별 별 상품 구매 회원 수 구하기
select year(o.sales_date) as year,
month(o.sales_date) as month,
i.gender,
count(distinct user_id) as users
from online_sale o
join user_info i using(user_id)
where i.gender is not null
group by 1,2,3
order by 1,2,3
2) 우유와 요거트가 담긴 장바구니
select distinct cart_id
from cart_products c1
inner join cart_products c2 using(cart_id)
where c1.name = 'Milk' and c2.name = 'Yogurt'
order by 1
7.31
1) 주문량이 많은 아이스크림들 조회하기
select f.flavor
from first_half f
inner join july j using (flavor)
group by 1
order by sum(f.total_order) + sum(j.total_order) desc
limit 3
2) 연간 평가점수에 해당하는 평가 등급 및 성과금 조회하기
select e.emp_no,
e.emp_name,
case when avg(g.score) >= 96 then 'S'
when avg(g.score) >= 90 and avg(g.score) < 96 then 'A'
when avg(g.score) >= 80 and avg(g.score) < 90 then 'B'
else 'C' end as grade,
case when avg(g.score) >= 96 then e.sal * 0.2
when avg(g.score) >= 90 and avg(g.score) < 96 then e.sal * 0.15
when avg(g.score) >= 80 and avg(g.score) < 90 then e.sal * 0.1
else 0 end as bonus
from hr_employees e
inner join hr_grade g using(emp_no)
group by 1
order by 1
