[MySQL][Practice] 마지막 연습 문제 !

Table - products

id	name	price
1	랩톱	1200
2	핸드폰	800
3	타블렛	400

 

Table - orders

id	product_id	quantity	order_date
101	1	2	2023-03-01
102	2	1	2023-03-02
103	3	5	2023-03-04

---

44.모든 주문의 주문 ID와 주문된 상품의 이름을 나열하는 쿼리를 작성해주세요!

select o.id,
       p.name
from products p inner join orders o on p.id = o.product_id

45.총 매출(price * quantity의 합)이 가장 높은 상품의 ID와 해당 상품의 총 매출을 가져오는 쿼리를 작성해주세요!

select o.id,
       sum(p.price*o.quantity) as total_sales
from products p inner join orders o on p.id = o.product_id
group by o.id
order by total_sales desc
limit 1

 

46.각 상품 ID별로 판매된 총 수량(quantity)을 계산하는 쿼리를 작성해주세요!

select o.id,
       sum(quantity) as total_quantity
from products p inner join orders o on p.id = o.product_id
group by o.id

 

47.2023년 3월 3일 이후에 주문된 모든 상품의 이름을 나열하는 쿼리를 작성해주세요!

select p.name,
       o.order_date
from products p inner join orders o on p.id = o.product_id
where o.order_date > '2023-03-03'

48.가장 많이 판매된 상품의 이름을 찾는 쿼리를 작성해주세요!

select *
from(
select p.name,
       o.quantity,
       rank() over(order by o.quantity desc) as quantity_rank
from products p inner join orders o on p.id = o.product_id
)a
where quantity_rank = 1

49.각 상품 ID별로 평균 주문 수량을 계산하는 쿼리를 작성해주세요!

select o.id,
       avg(o.quantity) as avg_quantity
from products p inner join orders o on p.id = o.product_id
group by o.id

50.판매되지 않은 상품의 ID와 이름을 찾는 쿼리를 작성해주세요!

select p.name,
       o.product_id
from products p inner join orders o on p.id = o.product_id
where p.price = 0 or o.quantity = 0

-- null 값 활용
select p.name,
       o.product_id
from products p left join orders o on p.id = o.product_id
where o.quantity is null

---