테이블 조인

--조인:테이블 간에 동일한 컬럼들을 연결시켜서 통합된 정보를 추출하는 방법
--Main Table 판단 -> 가상의 Table
--Main Table을 기준으로 모든 Key Join
--상품에 대한 공급업체 정보를 함께 추출
SELECT *
FROM vendors, products
WHERE vendors.vend_id = products.vend_id;

--ANSI SQL - 내부 조인
SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id;

--여러 테이블 조인
--상품에 대한 공급업체 정보와 주문수량까지 추출
SELECT vend_name, prod_name, prod_price, quantity
FROM orderitems, vendors, products
WHERE products.vend_id = vendors.vend_id
AND orderitems.prod_id = products.prod_id
AND order_num = 20007;

--상품번호가 RGAN01인 주문자이름과 연락처 추출
SELECT cust_name, cust_contact
FROM orderitems, orders, customers
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num
AND prod_id = 'RGAN01';

--테이블 별칭 사용
--상품번호가 RGAN01인 주문자이름과 연락처 추출
SELECT cust_name, cust_contact
FROM orderitems OI, orders O, customers C
WHERE C.cust_id = O.cust_id
AND OI.order_num = O.order_num
AND prod_id = 'RGAN01';

--동일 테이블 조인
--Jim Jones라는 사람이 일하는 회사의 모든 고객 담당자의 메일 주소 추출
SELECT cust_email, cust_id, cust_name, cust_contact
FROM customers
WHERE cust_name = ( 
                    SELECT cust_name
                    FROM customers
                    WHERE cust_contact = 'Jim Jones'
                  );

--같은 쿼리를 조인으로 추출
SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM customers c1, customers c2
WHERE c1.cust_name = c2.cust_name
AND c2.cust_name = c1.cust_name;

--조인 시 전체 컬럼 조회 처리
SELECT C.*, O.order_num, O.order_date, OI.prod_id
FROM orderitems OI, orders O, customers C
WHERE C.cust_id = O.cust_id
AND OI.order_num = O.order_num
AND prod_id = 'RGAN01';

--1. 주문과 고객정보를 조인하여 다음의 결과를 추출하시오
--결과: 주문번호, 주문날자, 고객번호, 고객이름, 고객주소, 고객시, 고객메일주소
--조건: 고객별로 마지막 주문날자의 주문
SELECT o.order_num, o.order_date, o.cust_id, c.cust_name, c.cust_address, c.cust_city, c.cust_email
FROM orders o, customers c
WHERE o.cust_id = c.cust_id
AND o.order_date = (
                      SELECT MAX(order_date)
                      FROM orders
                      WHERE cust_id = c.cust_id
                    );                 
                    
--2. 공급업체와 제품을 조인하여 다음의 결과를 추출하시오
--결과: 공급업체번호, 공급업체이름, 공급업체주소, 제품번호, 제품이름, 제품가격, 제품설명
--조건: 공급업체별로 평균제품가격 이상인 제품
SELECT v.vend_id, v.vend_name, v.vend_address, p.prod_id, p.prod_name, p.prod_price, p.prod_desc
FROM vendors v, products p
WHERE v.vend_id = p.vend_id
AND p.prod_price >= (
                    SELECT AVG(prod_price)
                    FROM products
                    WHERE vend_id = v.vend_id
                 );


--3. 제품, 주문, 주문제품을 조인하여 다음의 결과를 추출하시오
--결과: 주문번호, 주문일자, 제품번호, 제품이름, 제품가격, 항목가격
--조건: 제품가격과 항목가격이 다른 제품
SELECT o.order_num, TO_CHAR(o.order_date, 'YYYY-MM-DD'), p.prod_id, p.prod_name, p.prod_price, oi.item_price
FROM products p, orders o, orderitems oi
WHERE p.prod_id = oi.prod_id
AND oi.order_num = o.order_num
AND p.prod_price <> oi.item_price;

--4. 공급업체, 제품, 주문제품, 주문을 조인하여 다음의 결과를 추출하시오
--결과: 공급업체번호, 공급업체이름, 공급업체주소, 주문번호, 제품번호, 제품이름
SELECT v.vend_id, v.vend_name, v.vend_address, oi.order_num, p.prod_id, p.prod_name
FROM vendors v, products p, orderitems oi, orders o
WHERE v.vend_id = p.vend_id
AND p.prod_id = oi.prod_id
AND oi.order_num = o.order_num;