Nested Subquery

<학습목표>
1. nested subquery(ing)
- where절에 사용
(1)corre lated 상관관계 된
(2)exists 포함관계(교집합)
(3)not exists 차집합


단일행(Sing-Row) 서브쿼리
: 오직 한개의 행(값)을 반환
- 단일행 연산자만 사용가능
(=, <, >, >=,<=,<>, !=)

다중행(Multiple-Row) 
: 서브쿼리의 실행결과가 여러행
 포함관계
 **IN(∩,교집합),*Exists(∩,교집합),NOT IN, NOT Exists(차집합)
 ANY(하나만 만족), ALL(모두만족)
 
 상관관계 서브쿼리(Correlated Subqueries)
: 바깥쪽 쿼리의 컬럼 중의 하나가 안쪽 서브쿼리의 조건에 이용, 
  그 결과는 다시 바깥쪽 쿼리에 영향주는 처리방식
  
 

---

실습

 

--NESTED 서브쿼리 : WHERE절에 사용된 서브쿼리
--문제1
--상품분류가 전자제품인 상품매입 현황 리스트
 
SELECT BUY_DATE 입고일자
    ,  BUY_PROD 상품코드
    ,  BUY_QTY 매입수량
    ,  BUY_COST 매입단가
FROM BUYPROD
WHERE SUBSTR(BUY_PROD,1,4)
        =(SELECT LPROD_GU FROM LPROD WHERE LPROD_NM ='전자제품')
AND BUY_PROD LIKE
     (SELECT LPROD_GU FROM LPROD WHERE LPROD_NM ='전자제품')|| '%' ; 
 
 
--NESTED 서브쿼리5
--장바구니테이블에서 정은실 회원의 구매 현황을 출력
--ALIAS : 주문번호, 상품코드, 회원ID, 수량
SELECT CART_NO 주문번호
    ,  CART_PROD 상품코드
    ,  CART_MEMBER 회원ID
    ,  CART_QTY 수량
FROM CART
WHERE CART_MEMBER 
    = (SELECT MEM_ID FROM MEMBER WHERE MEM_NAME = '정은실');
 
--P.255
--상품Table에서 판매가가 상품평균판매가 보다 큰 상품을
--검색하시오
--(ALIAS : 상품명, 판매가, 평균판매가)
 
--SCALAR,NESTED
SELECT PROD_NAME 상품명
     , PROD_SALE 판매가
     , (SELECT AVG(PROD_SALE) FROM PROD) 평균판매가
FROM PROD
WHERE PROD_SALE>(SELECT AVG(PROD_SALE) FROM PROD);
 
--P.255
--회원테이블에서 마일리지가 평균마일리지 보다 큰 회원을 
--검색하시오 ?  (Alias는 회원명,마일리지,평균마일리지)
 
--SCALAR,NESTED
SELECT MEM_NAME 회원명
      ,MEM_MILEAGE 마일리지
      ,(SELECT AVG(MEM_MILEAGE)FROM MEMBER) 평균마일리지
FROM MEMBER
WHERE MEM_MILEAGE > (SELECT AVG(MEM_MILEAGE)FROM MEMBER);
 
--INLINE VIEW
SELECT A.MEM_NAME 회원명
      ,A.MEM_MILEAGE 마일리지
      ,B.AVG_MILE 평균마일리지
FROM MEMBER A,(SELECT ROUND (AVG(MEM_MILEAGE),2) AVG_MILE FROM MEMBER)B--가상의테이블B
WHERE A.MEM_MILEAGE > B.AVG_MILE;
 
--문제) 
--장바구니Table에서 판매수가 평균판매수 보다 큰 데이터를 
--검색하시오 ?  
--(Alias는 회원ID, 장바구니번호, 상품코드, 판매수, 평균판매수)
 
--SCALAR,NESTED
SELECT CART_MEMBER 회원ID
     , CART_NO 장바구니번호
     , CART_PROD 상품코드
     , CART_QTY 판매수
     ,(SELECT ROUND(AVG(CART_QTY),2) FROM CART) 평균판매수
FROM CART
WHERE CART_QTY > (SELECT AVG(CART_QTY) FROM CART);
 
--INLINE VIEW- FROM절
SELECT A.CART_MEMBER 회원ID
     , A.CART_NO 장바구니번호
     , A.CART_PROD 상품코드
     , A.CART_QTY 판매수
     , B.AVG_QTY 평균판매수
FROM CART A, (SELECT ROUND(AVG(CART_QTY),2) AVG_QTY FROM CART)B
WHERE A.CART_QTY > B.AVG_QTY;
 
 
--Correlated Subqueries 상관관계 서브쿼리
--장바구니테이블에서 회원별 최고의 구매수량을 가진 자료의 회원
--NESTED 서브쿼리 중 상관관계 서브쿼리, 단일행서브쿼리
 
SELECT A.CART_MEMBER 회원
      ,A.CART_NO 주문번호
      ,A.CART_PROD 상품코드
      ,A.CART_QTY 수량
FROM CART A
WHERE A.CART_QTY =(SELECT MAX(B.CART_QTY)
                   FROM CART B 
                   WHERE B.CART_MEMBER = A.CART_MEMBER );--************
 
--Correlated Subqueries 상관관계 서브쿼리
--입고테이블에서 상품별 최고매입수량검색
--NESTED 서브쿼리 중 상관관계 서브쿼리, 단일행서브쿼리
 
SELECT  A.BUY_DATE 입고일자
      , A.BUY_PROD 상품코드
      , A.BUY_QTY 매입수량
      , A.BUY_COST 매입단가
FROM BUYPROD A
WHERE A.BUY_QTY =(SELECT MAX(B.BUY_QTY) 
                  FROM BUYPROD B 
                  WHERE B.BUY_PROD=A.BUY_PROD) ----***
                  ORDER BY BUY_PROD;
--상관관계서브쿼리 예제2)
--입고테이블(BUYPROD)에서 "상품별" 
--최고 매입수량을 가진 자료의
--입고일자, 상품코드, 매입수량, 매입단가를 검색하기
--NESTED 서브쿼리 중 상관관계 서브쿼리, 단일행서브쿼리
SELECT A.BUY_DATE 입고일자
     , A.BUY_PROD 상품코드
     , A.BUY_QTY  매입수량
     , A.BUY_COST 매입단가
FROM   BUYPROD A
WHERE  A.BUY_QTY = (
        SELECT MAX(B.BUY_QTY)
        FROM   BUYPROD B
        WHERE  B.BUY_PROD = A.BUY_PROD --*******
    )
ORDER BY A.BUY_PROD;
 
--상관관계서브쿼리 예제3)
-- 장바구니Table에서 일자별 최고의 구매수량을 가진 자료의 회원, 
--주문번호, 상품, 수량에 대해 모두 검색하시오 
--(Alias는 회원, 일자, 상품, 수량)
--NESTED 서브쿼리 중 상관관계 서브쿼리, 단일행서브쿼리
 
SELECT  A.CART_MEMBER 회원
      , SUBSTR(A.CART_NO,1,8) 일자
      , A.CART_PROD 상품
     ,  A.CART_QTY 수량
FROM CART A
WHERE A.CART_QTY = (
        SELECT MAX(B.CART_QTY)
        FROM   CART B 
        WHERE   SUBSTR(A.CART_NO,1,8)= SUBSTR(B.CART_NO,1,8))
ORDER BY 2;
 
 
--모든 거래처의 2005년도 거래처별 매입금액 합계를 조회
 
SELECT B.BUYER_ID 거래처코드
     , B.BUYER_NAME 거래처명
     , NVL(SUM(BP.BUY_COST  * BP.BUY_QTY ),0) 매입금액합계
FROM BUYER B, PROD P, BUYPROD BP
WHERE B.BUYER_ID = P.PROD_BUYER(+)
AND P.PROD_ID =BP.BUY_PROD(+)
AND EXTRACT(YEAR FROM BP.BUY_DATE(+))=2005
GROUP BY B.BUYER_ID, B.BUYER_NAME
ORDER BY 1;
 
--거래처별 매입금액 합계
--INLINE VIEW, OUTER JOIN
SELECT  T.BUYER_ID, T.BUYER_NAME, U.SUM_QTY
FROM(SELECT BUYER_ID
    ,  BUYER_NAME
FROM BUYER) T,
(
SELECT P.PROD_BUYER
     , NVL(SUM(BP.BUY_COST  * BP.BUY_QTY ),0) SUM_QTY
FROM  PROD P, BUYPROD BP
WHERE 1=1
AND P.PROD_ID =BP.BUY_PROD
AND EXTRACT(YEAR FROM BP.BUY_DATE)=2005
GROUP BY P.PROD_BUYER
) U
WHERE T.BUYER_ID = U.PROD_BUYER(+)
ORDER BY 1;
 
--ANSI
SELECT  T.BUYER_ID, T.BUYER_NAME, U.SUM_QTY
FROM(SELECT BUYER_ID
    ,  BUYER_NAME
FROM BUYER) T LEFT OUTER JOIN
(
SELECT P.PROD_BUYER
     , NVL(SUM(BP.BUY_COST  * BP.BUY_QTY ),0) SUM_QTY
FROM  PROD P, BUYPROD BP
WHERE 1=1
AND P.PROD_ID =BP.BUY_PROD
AND EXTRACT(YEAR FROM BP.BUY_DATE)=2005
GROUP BY P.PROD_BUYER
) U
ON( T.BUYER_ID = U.PROD_BUYER)
ORDER BY 1;
 
--P.259
--모든 거래처의 2005년도 거래처별 매출금액합계를 검색하시오 ?
--(Alias는 거래처코드, 거래처명, 매출금액합계 거래처명 순)
--(cart 테이블 이용, 매출금액은 prod_sale * cart_qty)
 
SELECT T.BUYER_ID, T.BUYER_NAME, U.SUM_SALE
FROM (SELECT BUYER_ID 거래처코드
      ,BUYER_NAME 거래처명
FROM BUYER)T
(
SELECT SUM(P.PROD_SALE * C.CART_QTY) SUM_SALE
FROM  PROD P, CART C
WHERE 1=1
AND PROD_ID=BUY_PROD(+)
AND EXTRACT(YEAR FROM BP.BUY_DATE)=2005
GROUP BY P.PROD_BUYER
) U
WHERE T.BUYER_ID = U.PROD_BUYER(+)
ORDER BY 1;