/* 0814 실습 - 다음의 SQL을 작성하시오
1. 상품주문에서 주문번호, 제품번호, (제품제조국가위치)를 추출하시오
-(제품제조국가위치): 제품번호의 마지막 2자리가 01이면 ‘국내’, 아니면 ‘해외’
-조건: 총가격(항목수량*항목가격)이 500$이상인 주문 */
select order_num, prod_id, DECODE(SUBSTR(TRIM(prod_id), -2), '01', '국내', '해외') as prod_nation_location
from orderitems
WHERE order_num IN (
SELECT order_num
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price) >= 500
)
;
/* 2. 주문과 고객정보를 조인하여 다음의 결과를 추출하시오
-결과: 주문번호, 주문날자, 제품번호, 고객번호, 고객이름, 고객주소, 고객시, 고객메일주소
-조건: 전체주문에서 가장 많은 상품종류를 판매한 공급업체에서 만든 제품만조회*/
select OI.order_num, O.order_date, P.prod_id, C.cust_id, C.cust_name, C.cust_address, C.cust_city, C.cust_email
from orderitems OI, orders O, customers C, products P
where OI.order_num = O.order_num
AND O.cust_id = C.cust_id
AND OI.prod_id = P.prod_id
AND EXISTS(
SELECT MAX(COUNT(DISTINCT prod_id))
FROM Products
GROUP BY vend_id
)
;
-- 강사님 풀이
SELECT a.order_num, a.order_date, b.prod_id, a.cust_id, c.cust_name, c.cust_address, c.cust_city, c.cust_email
FROM orders a, orderitems b, customers c
WHERE a.order_num = b.order_num
AND a.cust_id = c.cust_id
AND b.prod_id IN (
SELECT prod_id
FROM products
WHERE vend_id IN (
SELECT vend_id
FROM
(
SELECT b.vend_id, COUNT(a.prod_id) AS prod_cnt
FROM orderitems a, products b
WHERE a.prod_id = b.prod_id
GROUP BY b.vend_id
) m
WHERE prod_cnt = (
SELECT MAX(COUNT(a.prod_id)) AS prod_cnt
FROM orderitems a, products b
WHERE a.prod_id = b.prod_id
GROUP BY b.vend_id
)
)
)
;
-- 왈답
select ord.order_num, ord.order_date, ori.prod_id, ct.cust_id, ct.cust_name, ct.cust_address, ct.cust_city, ct.cust_email
from orders ord, orderitems ori, customers ct
where ord.order_num = ori.order_num
AND ord.cust_id = ct.cust_id
and ori.prod_id in (
select pd_m.prod_id
from products pd_m
where pd_m.vend_id in (
select temp.vend_id
from(
select temp3.vend_id, rank() over(order by temp3.vend_cnt desc) as vend_rank, temp3.vend_cnt
from (
select pd1.vend_id, sum(temp2.cnt) as vend_cnt
from(
select oi.prod_id, count(oi.order_num) as cnt
From orderitems oi
group by oi.prod_id
) temp2
, products pd1
where temp2.prod_id = pd1.prod_id
group by pd1.vend_id
) temp3
) temp
where temp.vend_rank = 1
)
);
/* 3. 주문 중에 고객이 사는 도시가 ‘Detroit’이고 주문한 총 제품의 수가 500을 넘는 고객을 추출하시오
-결과: 고객번호, 고객이름, 고객도시 */
SELECT C.cust_id, C.cust_name, C.cust_city
from customers C
WHERE cust_city LIKE '%Detroit%'
AND EXISTS(
SELECT *
FROM Orderitems OI, Orders O
WHERE C.cust_id = O.cust_id
AND O.order_num = OI.order_num
HAVING SUM(OI.quantity) > 500
)
;
/* 4. 모든 주문에 대해 다음의 결과를 추출하시오
-결과: 주문번호, 주문날자(YYYY-MM-DD), 고객번호, 고객이름, 주문제품번호, 주문제품이름, 공급업체번호, 공급업체명
-조건: 3$대의 제품을 판매하는 공급업체의 모든 주문*/
SELECT O.order_num, TO_CHAR(O.order_date, 'YYYY-MM-DD'), C.cust_id, TRIM(C.cust_name), P.prod_id, TRIM(P.prod_name), V.vend_id, V.vend_name
FROM Orders O, Customers C, Products P, Vendors V, orderitems OI
WHERE O.order_num = OI.order_num
AND OI.prod_id = P.prod_id
AND P.vend_id = V.vend_id
AND O.cust_id = C.cust_id
AND TRUNC(prod_price) = 3;
-- 조인 3개 이하로 줄인 것.
SELECT O.order_num, TO_CHAR(O.order_date, 'YYYY-MM-DD'), C.cust_id, TRIM(C.cust_name), K.prod_id, TRIM(K.prod_name), K.vend_id, K.vend_name
FROM Orders O, Customers C,
(SELECT OI.order_num, P.prod_id, P.prod_name, V.vend_id, V.vend_name, P.prod_price
FROM Orderitems OI, Products P, Vendors V
WHERE OI.prod_id = P.prod_id
AND P.vend_id = V.vend_id) K -- from 절에 이렇게 하면 속도가 느려질수 있음.
WHERE O.cust_id = C.cust_id
AND K.order_num = O.order_num
AND TRUNC(K.prod_price) = 3;
-- 5. 주문날자가 2004년도에 발생한 주문 중에 주문상품의 항목가격이 90$를 넘는 항목가격에 대해 10%씩을 DC하여 변경하시오
UPDATE Orderitems
SET item_price = item_price * 0.9
WHERE EXISTS
(SELECT O.order_date
FROM Orders O
WHERE TO_CHAR(O.order_date, 'YYYY') LIKE '%2004%'
AND item_price >= 90
)
;
