[SQL] 04. join - todo 문제⭐ (23.09.06)

1. 고객 ID가 120인 고객의 이름, 성별, 가입일과 지금까지 주문한 주문정보 중 주문_ID, 주문일, 총금액을 조회

select 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;

where c.cust_id = 150;인 경우=> 고객정보는 나오지만, 주문한 적이 없으므로 null값으로 나옴

2. 고객 ID가 120인 고객의 정보와 지금까지 주문한 주문정보를 모두 조회

-- 주의 ) * 사용 x -> 주문정보는 모두 조회but 고객정보는 특정 컬럼을 보고 싶음
select c.cust_name, 
	   o.*
from customers c left join orders o on c.cust_id = o.cust_id
where c.cust_id = 120;

3. 날짜 형식 - 'YYYY-mm-dd'

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

3. 4개의 테이블을 join
   => 고객테이블, 주문테이블,상품테이블,주문제품테이블 4개를 전부 조인

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

<< 테이블을 묶는 방법 >>
고객 - 주문 : 고객아이디cust_id로 연결
주문 - 주문 제품 : 주문아이디 order_id로 연결
주문제품 - 제품 : 제품아이디product_id로 연결

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

4.제품 ID가 200인 제품(order_items)이 2017년에(orders) 몇개 주문되었는지 조회.

select count(product_id=200)
from products p left join order_items oi on p.product_id = ot.product_id
where year(o.order_date) = 2017;

-- 선생님 답
select sum(quantity) as "총판매개수"
from order_items oi join orders o on oi.order_id = o.order_id
where year(o.order_date) = 2017
and oi.product_id = 200;
------------------------
products 테이블도 조인해야하나 했는데 select에 해당 x이므로 where에서 and조건으로 연결
count() x -> sum(quantity)를 해야 데이터값들이 더해짐 

5.Group by -> 제품분류별 총 주문량을 조회

select p.category,
	   ifnull(sum(oi.quantity),0) as "총주문개수"
from products p left join order_items oi on p.product_id = oi.product_id -- 팔린 제품은 order정보 null
group by p.category;
---------------
총주문량을 합치려면 sum(quantity)를 하면됨
카테고리별로 보고싶은 것이기 때문에 group by 사용

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 -- 팔린 제품은 order정보 null
group by p.category
order by 2 desc;
-----
주문횟수를 추가하면 product_id의 행이 몇개인지 세면되므로 count(product_id)사용