SQL_06_문제풀이(JOIN 복습)

-- TODO: 4개의 테이블에 어떤 값들이 있는지 확인.
select * from customers;
select * from orders;
select * from order_items;
select * from products;
select distinct category from products;

-- TODO: 주문 번호가 1인 주문의 주문자 이름, 주소, 우편번호, 전화번호 조회
select o.order_id,
	   c.cust_name,
	   c.address,
       c.postal_code,
       c.phone_number
from customers c join orders o on c.cust_id = o.cust_id
where o.order_id = 1;

-- TODO : 주문 번호가 2인 주문의 주문일, 주문상태, 총금액, 주문고객 이름, 주문고객 이메일 주소 조회
select o.order_id,
	   o.order_date,
       o.order_status,
       o.order_total,
       c.cust_name,
       c.cust_email
from customers c join orders o on c.cust_id = o.cust_id
where o.order_id = 2;

-- TODO : 고객 ID가 120인 고객의 이름, 성별, 가입일과 지금까지 주문한 주문정보중 주문_ID, 주문일, 총금액을 조회
select c.cust_id,
	   c.cust_name,
       if(c.gender = 'M', '남성', '여성') as 'gender',
       c.join_date,
       o.order_id,
       o.order_date,
       format(o.order_total, 0) as 'order_total'
from customers c left join orders o on c.cust_id = o.cust_id
where c.cust_id = 120;


-- TODO : 고객 ID가 110인 고객의 이름, 주소, 전화번호, 그가 지금까지 주문한 주문정보중 주문_ID, 주문일, 주문상태 조회
 select c.cust_id,
	    c.cust_name,
        c.address,
        c.phone_number,
        o.order_id,
        o.order_date,
        o.order_status
from customers c left join orders o on c.cust_id = o.cust_id
where c.cust_id = 110;

-- TODO : 고객 ID가 120인 고객의 정보와 지금까지 주문한 주문정보를 모두 조회.
select *
from customers c left join orders o on c.cust_id = o.cust_id
where c.cust_id = 120;

-- TODO : 고객 ID가 120인 고객의 이름과 지금까지 주문한 주문정보를 모두 조회.
select c.cust_name,
	   o.*					-- o.* -> orders 테이블의 모든 정보 조회
from customers c left join orders o on c.cust_id = o.cust_id
where c.cust_id = 120;

-- TODO : '2017/11/13'(주문날짜) 에 주문된 주문의 주문고객의 고객_ID, 이름, 주문상태, 총금액을 조회
 select c.cust_id,
	    c.cust_name,
		o.order_status,
        o.order_total
from customers c right join orders o on c.cust_id = o.cust_id
where o.order_date = '2017/11/13';

-- TODO : 주문상세 ID가 xxxx(임의의 id - 1)인 주문제품의 제품이름, 판매가격, 제품가격을 조회.
select p.product_name,
	   oi.sell_price 판매가격,
       p.price 제품가격
from order_items oi join orders o on oi.order_id = o.order_id
			  join products p on oi.product_id = p.product_id
where oi.order_id = 1;


-- TODO : 주문 ID가 4인 주문의 주문 고객의 이름, 주소, 우편번호, -- customers
-- 주문일, 주문상태, 총금액, -- orders 
-- 주문 제품이름, 제조사, 제품가격, -- products 
-- 판매가격, 제품수량을 조회. -- order_items

select c.cust_name,
	   c.address,
	   c.postal_code, -- customers
       o.order_date,
       o.order_status,
       o.order_total, -- orders
       p.product_name,
       p.maker,
       p.price, -- products
       oi.sell_price,
       oi.quantity -- order_items
from orders o left join customers c on o.cust_id = c.cust_id
			  left join order_items oi on o.order_id = oi.order_id
              left join products p on oi.product_id = p.product_id;


-- TODO : 제품 ID가 200인 제품이 2017년에 몇개 주문되었는지 조회.
select sum(oi.quantity) as '총판매개수'
from order_items oi join orders o on oi.order_id = o.order_id
where oi.product_id = 200 and year(o.order_date) = 2017;


-- TODO : 제품분류별 총 주문량을 조회
select p.category,
	   ifnull(sum(oi.quantity), 0) as '총 주문량',
       count(oi.product_id) as '주문개수'
from products p left join order_items oi on p.product_id = oi.product_id
group by p.category
order by 2 desc;