[TIL] 10-11주차 코딩 스터디 과제

다음과 같은 직원(employees) 테이블과 부서(departments) 테이블이 있습니다.

• employees 테이블

id	department_id	name
1	101	르탄이
2	102	배캠이
3	103	구구이
4	101	이션이

• departments 테이블

id	name
101	인사팀
102	마케팅팀
103	기술팀

1. 현재 존재하고 있는 총 부서의 수를 구하는 쿼리를 작성해주세요!
   select count(id)
   from departments

2. 모든 직원과 그들이 속한 부서의 이름을 나열하는 쿼리를 작성해주세요!

select e.id,
e.department_id,
e.name,
d.name dep_name
from employees e left join departments d e.department_id = d.id

1. '기술팀' 부서에 속한 직원들의 이름을 나열하는 쿼리를 작성해주세요!
   select name
   from
   (
   select e.id,
   e.department_id,
   e.name,
   d.name dep_name
   from employees e left join departments d e.department_id = d.id
   ) t
   where dep_name = '기술팀'

2. 부서별로 직원 수를 계산하는 쿼리를 작성해주세요!
   select dep_name,
   count(1)
   from
   (
   select e.id,
   e.department_id,
   e.name,
   d.name dep_name
   from employees e left join departments d e.department_id = d.id
   ) t
   group by 1

3. 직원이 없는 부서의 이름을 찾는 쿼리를 작성해주세요!
   1) select dep_name,
   count(1)
   from
   (
   select e.id,
   e.department_id,
   e.name,
   d.name dep_name
   from employees e left join departments d e.department_id = d.id
   ) t
   group by 1
   order by 2
   limit 1
   2) select dep_name,
   count(1)
   from
   (
   select e.id,
   e.department_id,
   e.name,
   d.name dep_name
   from employees e left join departments d e.department_id = d.id
   ) t
   group by 1
   where count(1) = 0

4. '마케팅팀' 부서에만 속한 직원들의 이름을 나열하는 쿼리를 작성해주세요!(
   select name
   from
   (
   select e.id,
   e.department_id,
   e.name,
   d.name dep_name
   from employees e left join departments d e.department_id = d.id
   ) t
   where dep_name = '마케팅팀'

다음과 같은 상품(products) 테이블과 주문(orders) 테이블이 있습니다.

• products 테이블

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

• 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

1. 모든 주문의 주문 ID와 주문된 상품의 이름을 나열하는 쿼리를 작성해주세요!
   select o.id,
   o.product_id,
   o.quantity,
   o.order_date,
   p.name,
   p.price
   from orders o left join products p o.product_id = p.id

2. 총 매출(price quantity의 합)이 가장 높은 상품의 ID와 해당 상품의 총 매출을 가져오는 쿼리를 작성해주세요!
   select product_id,
   price,
   quantity,
   sum(pricequantiy)"총 매출"
   from
   (
   select o.id,
   o.product_id,
   o.quantity,
   o.order_date,
   p.name,
   p.price
   from orders o left join products p o.product_id = p.id
   ) a
   order by 4 desc
   limit 1

3. 각 상품 ID별로 판매된 총 수량(quantity)을 계산하는 쿼리를 작성해주세요!
   select product_id,
   sum(quantity) total
   from
   (
   select o.id,
   o.product_id,
   o.quantity,
   o.order_date,
   p.name,
   p.price
   from orders o left join products p o.product_id = p.id
   ) a
   group by 1

1. 2023년 3월 3일 이후에 주문된 모든 상품의 이름을 나열하는 쿼리를 작성해주세요!
   select name
   from
   (
   select o.id,
   o.product_id,
   o.quantity,
   o.order_date,
   p.name,
   p.price
   from orders o left join products p o.product_id = p.id
   ) a
   where order_date > '2023-03-03'

2. 가장 많이 판매된 상품의 이름을 찾는 쿼리를 작성해주세요!
   select name,
   sum(quantity) total
   from
   (
   select o.id,
   o.product_id,
   o.quantity,
   o.order_date,
   p.name,
   p.price
   from orders o left join products p o.product_id = p.id
   ) a
   group by 1
   order by 2 desc
   limit 1

3. 각 상품 ID별로 평균 주문 수량을 계산하는 쿼리를 작성해주세요!
   select product_id,
   avg(quantity) avg_quan
   from
   (
   select o.id,
   o.product_id,
   o.quantity,
   o.order_date,
   p.name,
   p.price
   from orders o left join products p o.product_id = p.id
   ) a
   group by 1

4. 판매되지 않은 상품의 ID와 이름을 찾는 쿼리를 작성해주세요!
   select product_id,
   name,
   sum(quantity) total
   from
   (
   select o.id,
   o.product_id,
   o.quantity,
   o.order_date,
   p.name,
   p.price
   from orders o left join products p o.product_id = p.id
   ) a
   group by 1,2
   where id IS NULL