4일차

주요내용 : 테이블 조인(OUTER, UNION)

/* 0729 실습1 SQL */
-- 1. 고객에서 고객번호, 고객이름, 고객주소, '주문한 총 금액', '주문한 제품중에 가장비싼금액의 상품명' 을 추출하시오-
-- '주문한 총 금액' : 주문상품에서 주문한 상품의 항목가격의 SUM
-- '주문한 제품중에 가장비싼 금액의 상품명' : 주문상품에서 해당고객이 주문한 상품중 가장비싼 상품의 상품명
SELECT cust_id, cust_name, cust_address, 
    (SELECT SUM(item_price)
    FROM Orderitems a, orders b
    WHERE a.order_num = b.order_num
    AND b.cust_id = k.cust_id
    )order_tot_price,
    (SELECT MAX(prod_name)
    FROM Orderitems a, orders b, products c
    WHERE a.order_num = b.order_num
    AND b.cust_id = k.cust_id
    AND a.item_price = (
        SELECT MAX(item_price)
        FROM Orderitems aa, orders bb
        WHERE aa.order_num = bb.order_num
        AND bb.cust_id = k.cust_id
        )
    ) max_prod_name
FROM CUSTOMERS k;

-- 2. 상품을주문한 고객중에 고객국가, 고객주별 주문수를 추출하시오 // orderitem이 메인테이블. 결과가 동일한게 메인테이블
-- 결과: 고객국가,고객주, 주문수

SELECT cust_country, cust_state, count(DISTINCT a.order_num) order_num
FROM Orderitems a, orders b, customers c
WHERE a.order_num = b.order_num
AND b.cust_id = c.cust_id
GROUP BY cust_country, cust_state;

-- 3. 주문상품에서 고객주별 가장비싼 제품의 가격을 추출하시오
-- 결과: 고객주, 비싼 제품의 가격

SELECT cust_state, MAX(a.item_price)
FROM Orderitems a, orders b, customers c
WHERE a.order_num = b.order_num
AND b.cust_id = c.cust_id
GROUP BY c.cust_state;

/* 테이블 조인*/
-- OUTER 조인
-- 실습
-- 1. 제품과 주문항목을 조인하여 다음의 결과를 추출하시오 결과: 제품번호, 제품이름, 주문번호, 항목수량, 항목가격 조건: 모든 제품이 조회되어야 한다
SELECT P.prod_id, P.prod_name, OI.order_num, OI.quantity, OI.item_price
FROM Products P, Orderitems OI
WHERE P.prod_id (+) = OI.prod_id; --  Main이 되는 테이블에 (+) 표시

-- 2. 제품을 주문한 고객 수와 주문 안 한 고객 수를 추출하시오 결과: 주문여부, 고객 수
SELECT gubn, COUNT(gubn)
FROM
(
    SELECT NVL((SELECT MAX('Y')
            FROM   orders
            WHERE  cust_id = a.cust_id
           ), 'N') gubn
    FROM   customers a
)
GROUP BY gubn;

-- 3. 고객별로 주문한 상품 중에 가장 높은 금액을 가지는 상품을 추출하시오 결과: 고객번호, 고객이름, 제품번호, 제품이름, 항목가격
-- 답맞는지 모르겠음??
SELECT C.cust_id, C.cust_name, P.prod_id, P.prod_name, OI.item_price
FROM Customers C, Products P, Orderitems OI
WHERE P.prod_id = OI.PROD_ID
AND OI.item_price = (
    SELECT MAX(item_price)
    FROM Orderitems
    WHERE OI.prod_id = P.prod_id
-- GROUP BY (OI.cust_id)
    )
;

-- 4. 공급업체별로 생산하는 제품의 수와 최고제품가격을 추출하시오 결과: 공급업체번호, 공급업체이름, 제품 수, 최고제품가격 조건: 8$이상의 제품을 생산하는 공급업체

-- 5. 주문 중에 고객이 사는 도시가 ‘Detroit’이고 주문한 총 제품의 수가 500을 넘는 고객을 추출하시오 결과: 고객번호, 고객이름, 고객도시

/*쿼리의 결합 - UNION*/
-- 실습
-- 3. 다음의 조건에 따라 공급업체의 공급업체번호, 공급업체명, 공급업체주소를 추출하시오
-- 조건: 주문된 상품이 2건이상이거나 제조하는 제품이 2개이상인 공급업체
SELECT vend_id, vend_name, vend_address
FROM VENDORS V
WHERE EXISTS(SELECT P.vend_id
             FROM ORDERITEMS OI, PRODUCTS P
             WHERE OI.prod_id = P.prod_id
             AND P.vend_id = V.vend_id
             GROUP BY P.vend_id
             HAVING COUNT(*) >= 2
UNION
         SELECT vend_id
         FROM   PRODUCTS P
         WHERE  vend_id = V.vend_id
         GROUP BY vend_id
         HAVING COUNT(*) >= 2);

-- 4. 다음의 조건에 따라 제품의 제품번호, 제품이름, 제품가격수준, 제품설명을 추출하시오
-- 제품가격수준: 제품가격이 3$대이면 ‘적당’ 11%대이면 ‘비쌈’ 나머지는 ‘보통’
-- 조건: 공급업체의 우편번호 앞의 2자리가 ‘44’, ‘45’, ’99’ 인 공급업체에서 만든 만들고 주문된 날자가 5월이 아닌 제품
SELECT P.prod_id, P.prod_name, DECODE(TRUNC(P.prod_price),3, '적당', 11, '비쌈', '보통') as price_level, P.prod_desc
FROM Products P, Vendors V
WHERE P.vend_id = V.vend_id
AND SUBSTR(V.vend_zip, 1,2) IN ('44', '45', '99')
UNION
SELECT P.prod_id, P.prod_name, DECODE(TRUNC(P.prod_price),3, '적당', 11, '비쌈', '보통') as price_level, P.prod_desc
FROM Products P
WHERE EXISTS(
    SELECT *
    FROM Orders O, Orderitems OI
    WHERE O.order_num = OI.order_num
    AND OI.prod_id = P.prod_id
    AND TO_CHAR(O.order_date, 'MM') = '05'
    )
;