6일차

/* 0731 실습1 */
-- 1.고객별로 주문한 제품의 수와 주문한 제품 중 //  최고항목가격(판매가격)을 추출하시오
-- 결과: 고객번호, 고객이름, 주문제품 수, 주문제품 중 최고항목가격
SELECT C.cust_id, C.cust_name,
    (SELECT SUM(OI.quantity)
    FROM Orderitems OI, Orders O
    WHERE OI.order_num = O.order_num
    AND O.cust_id = C.cust_id
    ) order_quantity,
        (SELECT MAX(item_price)
        FROM Orderitems OII, Orders OO
        WHERE OII.order_num = OO.order_num
        AND OO.cust_id = C.cust_id
        GROUP BY OO.cust_id
        ) max_price
FROM CUSTOMERS C
GROUP BY C.cust_id, C.cust_name;

-- 집합개념으로 풀이
SELECT m.cust_id, m.cust_name, n.quantity, n.max_price
FROM   customers m,
       (SELECT b.cust_id, SUM(a.quantity) quantity, MAX(a.item_price) max_price
        FROM   orderitems a, orders b
        WHERE  a.order_num = b.order_num
        GROUP BY b.cust_id
       ) n
WHERE  m.cust_id = n.cust_id    
;

-- 2.주문된 제품 중에 // 제품의 공급업체가 있는 도시가 ‘Dollsville’이고 // 주문된 총 제품의 수가 300을 넘는 제품명을 추출하시오
SELECT TRIM(prod_name)
FROM Products P
WHERE vend_id IN(
    SELECT vend_id
    FROM Vendors
    WHERE vend_city = 'Dollsville' 
    GROUP BY vend_id
    )
AND EXISTS(
    SELECT *
    FROM Orderitems
    WHERE prod_id = P.prod_id
    HAVING SUM(quantity) >=300
    )
;

-- 집합개념으로 풀이
SELECT m.prod_name
FROM   products m, vendors n
WHERE  m.vend_id = n.vend_id
AND    prod_id IN (
         SELECT prod_id
         FROM   orderitems a
         WHERE  prod_id = m.prod_id
         GROUP BY prod_id
         HAVING SUM(quantity) > 300
       )
AND    n.vend_city = 'Dollsville'        
;

-- 3.고객별로 주문한 상품 중에 //  가장 비싼 제품명을 추출하시오
-- 결과: 고객번호, 고객이름, 제품번호, 제품이름, 항목가격(판매가격)

-- 수정완료
SELECT C.cust_id, TRIM(C.cust_name), TRIM(P.prod_id), TRIM(P.prod_name), OI.item_price
FROM Customers C, Products P, Orders O, Orderitems OI
WHERE P.prod_id = OI.prod_id
AND OI.order_num = O.order_num
AND O.cust_id = C.cust_id
AND OI.item_price IN(
            (SELECT MAX(item_price)
            FROM Orderitems OII, Orders OO
            WHERE OII.order_num = OO.order_num
            AND OO.cust_id = C.cust_id
            GROUP BY C.cust_id
            )
    )
;

-- 집합개념으로 풀이
SELECT m.cust_id, m.cust_name, o.prod_id, o.prod_name, l.item_price
FROM   customers m, orders n, orderitems l, products o,
       (SELECT b.cust_id, MAX(a.item_price) max_price
        FROM   orderitems a, orders b
        WHERE  a.order_num = b.order_num
        GROUP BY b.cust_id
       ) t
WHERE  m.cust_id = n.cust_id
AND    n.order_num = l.order_num
AND    l.prod_id = o.prod_id
AND    m.cust_id = t.cust_id
AND    l.item_price = t.max_price
;