2주차_실전 데이터 추출

 

1. 상품(product)의 카테고리(category)별로, 상품 수와 평균 가격대(list_price) 를 찾는 쿼리를 작성하세요.

select count(1) as "카데고리 별 상품 수", avg(list_price) as "평균 가격대"
from products
group by category

2. 2006년 1분기에 고객(customer)별 주문(order) 횟수, 주문한 상품(product) 의 카테고리(category) 수, 총 주문 금액(quantity * unit_price)을 찾는 쿼리를 작성하세요. (힌트: join)

select customer_id as 고객ID,count(distinct o.id) as "고객 별 주문횟수",count(distinct p.category) as "주문한 상품의 카테고리 수",sum(ord.quantity * ord.unit_price) as "총 주문 금액"
from orders o
    left join order_details ord on o.id=ord.order_id
    left join products p on p.id=ord.product_id
where QUARTER(order_date)=1
group by o.customer_id;

3. 2006년 3월에 주문(order)된 건의 주문 상태(status_name)를 찾는 쿼리를 작성하세요. (join을 사용하지 않고 쿼리를 작성하세요.) (힌트: orders_status 사용, sub-query)

#서브쿼리 사용
select o.order_date as 날짜,o.status_id as ID ,(select status_name from orders_status os where os.id = o.status_id) status_name
from orders o
where substr(o.order_date,6,2)='03';

select o.order_date as 날짜,os.id,os.status_name as "주문 상태"
from orders o
    left join orders_status  os on o.status_id=os.id 
where substr(o.order_date,6,2)='03';

4. 2006년 1분기 동안 세 번 이상 주문(order) 된 상품(product)과 그 상품의 주문 수를 찾는 쿼리를 작성하세요. (order_status는 신경쓰지 않으셔도 됩니다.) (힌트: sub-query or having)

select p.product_name as 상품이름, count(1) as 주문수
from orders o
    left join order_details ord on o.id=ord.order_id
    left join products p on p.id=ord.product_id
where quarter(order_date)=1
group by p.product_name
having count(1)>=3

5-1. 2006년 1분기, 2분기 연속으로 주문(order)을 받은 직원(employee)을 찾는 쿼리를 작성하세요. (order_status는 신경쓰지 않으셔도 됩니다.) (힌트: sub-query, inner join)

select DISTINCT e.last_name, e.first_name
from 
(select employee_id
from orders
where quarter(order_date)=1) e1
inner join
(select employee_id
from orders
where quarter(order_date)=2) e2
on e1.employee_id=e2.employee_id
inner join employees e on e1.employee_id=e.id

5-2. 2006년 1분기, 2분기 연속으로 주문(order)을 받은 직원(employee) 별로, 2006년 월별 주문 수를 찾는 쿼리를 작성하세요. (order_status는 신경쓰지 않으셔도 됩니다.) (힌트: sub-query 중첩, date_format() )

select o.employee_id,date_format(o.order_date,'%Y-%m'),e.last_name, e.first_name,count(*)
from orders o inner join employees e on o.employee_id=e.id
where employee_id in (
    select e1.employee_id
    from 
        (select distinct employee_id
        from orders
        where quarter(order_date)=1) e1
        inner join
        (select distinct employee_id
        from orders
        where quarter(order_date)=2) e2
        on e1.employee_id=e2.employee_id)
group by 1,2