[Oracle SQL Developer] 기초 XIV - SUBQUERY

1. SUBQUERIES

---

  - 쿼리 안에 포함된 또 다른 쿼리를 칭한다.
  - JOIN 연산을 줄이기 위해서 사용하나 QUERY 의 구조가 복잡해진다.
  - 서브쿼리는 '( SubqueriesCONTENTSBLBLBLBLAAAA )' 안에 기술한다. 
  - SELECT, FROM, WHERE 절에 나올 수 있다.
  - 연산자 오른쪽에 기술한다.
  - FROM 절에 사용되는 서브쿼리를 INLINE VIEW 라고 하며 독립실행 가능해야 한다.
  - 서브쿼리를 포함하고 있는 쿼리를 메인쿼리라고 한다.
  - 메인쿼리에 사용하는 테이블과 서브쿼리에서 사용하는 테이블과의 관계
    조인되는 경우 : 연관성 있는 서브쿼리 
    조인연산이 발생되지 않은 경우 : 연관성없는 서브쿼리라고 한다.
  - 반환되는 행과 열에 따라 '단일행/단일열, 단일행/다중열, 다중행/단일열, 다중행/다중열' 로 구분된다.
    : 서버쿼리의 구분은 사용되는 연산자에 의해서 구분된다.


  1.1. 단일행 서브쿼리
    - 하나의 행만 결과로 반환하는 서브쿼리
    - 단일행 연산자 : >, <, =, >=, <=, <>, !=
     
  
  예) 사원테이블에서 사원들의 평균급여보다 많이 급여를 받는 사원을 조회하시오. 
      Alias는 사원번호, 사원명, 부서명, 급여
      
  SELECT A.EMPLOYEE_ID AS 사원번호, 
         A.EMP_NAME AS 사원명, 
         B.DEPARTMENT_NAME AS 부서명, 
         A.SALARY AS 급여    
    FROM EMPLOYEES A, DEPARTMENTS B, (SELECT ROUND(NVL(AVG(SALARY),0)) AS AVGSA
                                       FROM EMPLOYEES) C
   WHERE B.DEPARTMENT_ID = A.DEPARTMENT_ID
     AND A.SALARY >= C.AVGSA; --(SELECT ROUND(AVG(SALARY)) FROM EMPLOYEES);
   
   
   
  1.2. 다중행 서브쿼리  
    - 서브쿼리 수행결과 복수의 행을 메인쿼리에 전달하는 형태이다.
    - 하나 이상의 행을 반환하는 서브쿼리
    - 사용 연산자 : IN, ANY, SOME, ALL, EXISTS, ...
    
  예) 각 부서별로 가장 급여를 많이 받는 사원 정보를 조회하시오.
      Alias는 사원번호, 사원명, 부서명, 급여
   
  SELECT A.EMPLOYEE_ID AS 사원번호, 
         A.EMP_NAME AS 사원명, 
         A.DEPARTMENT_ID AS 부서코드,
         B.DEPARTMENT_NAME AS 부서명, 
         A.SALARY AS 급여    
    FROM EMPLOYEES A, DEPARTMENTS B
   WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
     AND (A.EMPLOYEE_ID, A.SALARY) IN 
         (SELECT EMPLOYEE_ID, E.MAMT
            FROM EMPLOYEES D, (SELECT DEPARTMENT_ID AS DN, MAX(SALARY) AS MAMT
                                 FROM EMPLOYEES
                               GROUP BY DEPARTMENT_ID) E
           WHERE E.DN = D.DEPARTMENT_ID
             AND D.SALARY = E.MAMT);
      
  SELECT MAX(A.SALARY) AS SA,
         B.DEPARTMENT_NAME AS DN
    FROM EMPLOYEES A, DEPARTMENTS B
   WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
   GROUP BY B.DEPARTMENT_NAME;
         
      
      
  예) 사원테이블의 배송부(50)의 모든 사원의급여보다 더 많은 급여를 받는 사원정보를 조회하시오.
      사원명, 급여
  
  SELECT 
    FROM EMPLOYEES A, DEPARTMENT B;
  
  -- 홍보부의 모든 합계    
  SELECT SALARY AS HS,
         EMP_NAME AS 사원명
    FROM EMPLOYEES
   WHERE DEPARTMENT_ID=50; 
      
      
      
  SELECT A.EMPLOYEE_ID AS 사원번호, 
         A.EMP_NAME AS 사원명, 
         A.DEPARTMENT_ID AS 부서코드,
         B.DEPARTMENT_NAME AS 부서명, 
         A.SALARY AS 급여    
    FROM EMPLOYEES A, DEPARTMENTS B      
   WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
     AND A.SALARY > ALL ( SELECT SALARY AS HS
                            FROM EMPLOYEES
                           WHERE DEPARTMENT_ID=50); -- 50번부서의 MAX값이랑 동일한 의미이다.
                           
                           
                           
  ** SELECT 절에 사용되는 서브쿼리 
     
    예) 상품테이블과 분류테이블를 이용하여 , 상품코드, 상품명, 분류코드, 분류명을 조회하시오.
   
  --   
    SELECT A.PROD_ID AS 상품코드, 
           A.PROD_NAME AS 상품명, 
           A.PROD_LGU AS 분류코드,
           (SELECT LPROD_NM AS 분류명
              FROM LPROD
             WHERE LPROD_GU = PROD_LGU) AS 분류명
      FROM PROD A;--, LPROD B
     WHERE A.PROD_LGU = B.LPROD_GU; 
       
      
      
      
      
  예)  2005년 5월 매입수량을 조회하시오.
      Alias 상품코드, 상품명, 매입수량합계
  
  SELECT A.PROD_ID AS 상품코드, 
         A.PROD_NAME AS 상품명, 
         NVL((SELECT SUM(B.BUY_QTY)
            FROM BUYPROD B
           WHERE BUY_DATE BETWEEN '20050501' AND '20050531'
             AND A.PROD_ID = B.BUY_PROD),0)
         AS 매입수량합계 
    FROM PROD A;
   
   --GROUP BY A.PROD_ID, A.PROD_NAME;
      
      
  1.3. 연관성없는 서브쿼리
    - 메인테이블과 조인으로 연결되지 않은 서브쿼리
    
  예) JOB_HISTORY 테이블과 사원테이블에서 사원번호와 직책코드가 모두 같은 사원을 조회하시오.
      : 서브쿼리는 ? 
      Alias 사원번호, 사원명, 직책코드 
      
      
  SELECT A.EMPLOYEE_ID AS 사원번호, 
         A.EMP_NAME AS 사원명, 
         JOB_ID AS 직책코드
    FROM EMPLOYEES A 
   WHERE (A.EMPLOYEE_ID, JOB_ID) IN (SELECT EMPLOYEE_ID, JOB_ID
                                       FROM JOB_HISTORY); 
   
   
  예) 회원들의 평균 마일리지보다 많은 마일리지를 보유한 회원정보를 조회하시오.
      Alias 회원번호, 회원명, 마일리지
      
  SELECT MEM_ID AS 회원번호,
         MEM_NAME AS 회원명,
         MEM_MILEAGE AS 마일리지,
         B.AVGM AS 평균마일리지
    FROM MEMBER, (SELECT ROUND(AVG(MEM_MILEAGE)) AS AVGM
                    FROM MEMBER) B
   WHERE MEM_MILEAGE >= B.AVGM
   ORDER BY MEM_MILEAGE;
      
  SELECT ROUND(AVG(MEM_MILEAGE)) AS AVGM
    FROM MEMBER;
      
  
  예) 장바구니테이블에서 회원별 최고 구매수량의 자료를 조회하시오
      Alias 회원번호, 장바구니번호, 수량
  
  SELECT A.CART_MEMBER AS 회원번호,
         A.CART_PROD AS 상품번호,
         MAX(CART_QTY) AS 수량
    FROM CART A
   GROUP BY A.CART_MEMBER, A.CART_PROD;
  
  
  SELECT *
    FROM CART A
   WHERE A.CART_QTY = (SELECT MAX(B.CART_QTY) AS MAXQ
                        FROM CART B
                       WHERE A.CART_MEMBER = B.CART_MEMBER)
   ORDER BY 1; 
  

  예) 모든 거래처의 2005년 거래처별 매입금액 합계를 서브쿼리를 이용하여 작성하시오
      Alias 거래처코드, 거래처명, 매입금액합계
      
      
  SELECT A.BUYER_ID AS 거래처코드, 
         A.BUYER_NAME AS 거래처명, 
         NVL(D.AMT,0) AS 매입금액합계
    FROM BUYER A 
   LEFT OUTER JOIN (  SELECT AA.BUYER_ID AS BID,
                            NVL(SUM(B.BUY_QTY * B.BUY_COST),0) AS AMT
                       FROM BUYPROD B, PROD C, BUYER AA
                      WHERE B.BUY_DATE LIKE '2005%'
                        AND C.PROD_ID = B.BUY_PROD
                        AND AA.BUYER_ID = C.PROD_BUYER
                     --HAVING NVL(SUM(B.BUY_QTY * B.BUY_COST),0) > 100000000  
                      GROUP BY AA.BUYER_ID) D ON (A.BUYER_ID = D.BID)
   ORDER BY 1;
  
  
  SELECT AA.BUYER_ID AS BID,
         NVL(SUM(B.BUY_QTY * B.BUY_COST),0) AS AMT
    FROM BUYPROD B, PROD C, BUYER AA
   WHERE B.BUY_DATE LIKE '2005%'
     AND C.PROD_ID = B.BUY_PROD
     AND AA.BUYER_ID = C.PROD_BUYER
   GROUP BY AA.BUYER_ID;
  
  
  예) 상품테이블에서 상품들의 평균판매가보다 큰 상품을 조회하시오.
      상품번호, 상품명, 판매가
  
  SELECT A.PROD_ID AS 상품번호, 
         A.PROD_NAME AS 상품명, 
         A.PROD_PRICE AS 판매가
    FROM PROD A, (SELECT ROUND(AVG(PROD_PRICE)) AS AVGP FROM PROD) B
   WHERE A.PROD_PRICE > B.AVGP;                 
  
  -- 상품들의 평균판매가
  SELECT ROUND(AVG(PROD_PRICE)) AS AVGP
    FROM PROD;
    
  
  
  
  예) 2005년도 5월 거래처별 매입금액 합계를 구한 후 평균 매입금액보다 적은 매입금액을  갖고있는 거래처 정보를 조회하시오
      (거래처코드, 거래처명, 매입금액합계);
 
   
  -- 평균 매입금액
  SELECT ROUND(AVG(A.BUY_COST * A.BUY_QTY)) AS AVGB
    FROM BUYPROD A
   WHERE A.BUY_DATE BETWEEN '20050501' AND '20050531';
     
  -- 2005년 거래처별 매입금액 합계
   SELECT BUYER_ID,
          SUM(A.BUY_COST * A.BUY_QTY) AS SUMB
     FROM BUYPROD A, PROD B, BUYER C
    WHERE A.BUY_DATE BETWEEN '20050501' AND '20050531'
      AND B.PROD_ID = A.BUY_PROD
      AND B.PROD_BUYER = C.BUYER_ID
    GROUP BY BUYER_ID; 
    
  -- 2005년 거래처별 평균 매입금액 
     SELECT AVG(SUM(A.BUY_COST * A.BUY_QTY)) AS AVGC
     FROM BUYPROD A, PROD B, BUYER C
    WHERE A.BUY_DATE BETWEEN '20050501' AND '20050531'
      AND B.PROD_ID = A.BUY_PROD
      AND B.PROD_BUYER = C.BUYER_ID
    GROUP BY BUYER_ID; 
    
    
    
  SELECT A.BUYER_ID AS 거래처코드,
         A.BUYER_NAME AS 거래처명,
         B.SUMB AS 매입금액합계,
         C.AVGC AS 평균매입금액
    FROM BUYER A, (SELECT BUYER_ID AS BID,
                         BUYER_NAME,
                         SUM(A.BUY_COST * A.BUY_QTY) AS SUMB
                    FROM BUYPROD A, PROD B, BUYER C
                   WHERE A.BUY_DATE BETWEEN '20050501' AND '20050531'
                     AND B.PROD_ID = A.BUY_PROD
                     AND B.PROD_BUYER = C.BUYER_ID
                   GROUP BY BUYER_ID, BUYER_NAME) B, ( SELECT AVG(SUM(A.BUY_COST * A.BUY_QTY)) AS AVGC
                                                         FROM BUYPROD A, PROD B, BUYER C
                                                        WHERE A.BUY_DATE BETWEEN '20050501' AND '20050531'
                                                          AND B.PROD_ID = A.BUY_PROD
                                                          AND B.PROD_BUYER = C.BUYER_ID
                                                        GROUP BY BUYER_ID) C
   WHERE A.BUYER_ID = B.BID
     AND C.AVGC > B.SUMB;
  
    

  
  예) 2005년 월별 구매실적이 가장 많은 회원의 정보와 구매금액합계를 조회하시오.  
      일자, 구매금액합계, 회원번호, 회원명, 직업 
  
  
  -- 4개 나와야댐 04/05/06/07 
  
  SELECT D.MON AS 일자, 
         C.MEM_ID AS 회원번호,
         MAX(D.COUCQ)
    FROM MEMBER C, (  SELECT SUBSTR(CART_NO,5,2) AS MON,
                             A.CART_MEMBER AS MEMID,
                             SUM(A.CART_QTY * C.PROD_PRICE) AS COUCQ
                        FROM CART A, PROD C
                       WHERE CART_NO LIKE '2005%'
                         AND A.CART_PROD = C.PROD_ID
                       GROUP BY SUBSTR(CART_NO,5,2), A.CART_MEMBER) D
   WHERE D.MEMID = C.MEM_ID
   GROUP BY D.MON, C.MEM_ID
   ORDER BY 3 DESC;
   
   
   
   SELECT 
     FROM ;
   
   -- MEMBER A
   -- 월별 최고금액합계 B
   -- 월별, 회원별 최고구매금액 C
   -- JOING ?
   -- 조건1. B.최고구매금액 = C.최고구매금액
   -- 조건2. C.멤버ID = A.멤버ID
   
   
   -- 2005년 월별 최고 구매금액 합계
   
   SELECT SUBSTR(A.CART_NO,1,6) AS MON,
          MAX(A.CART_QTY * B.PROD_PRICE) AS MAXP
     FROM CART A, PROD B
    WHERE A.CART_PROD = B.PROD_ID
    GROUP BY SUBSTR(A.CART_NO,1,6); 
   
   
   SELECT SUBSTR(A.CART_NO,1,6) AS MON,
          MEM_ID AS MID,
          MAX(A.CART_QTY * B.PROD_PRICE) AS MAXP
     FROM CART A, PROD B, MEMBER C
    WHERE A.CART_PROD = B.PROD_ID
      AND C.MEM_ID = A.CART_MEMBER
    GROUP BY SUBSTR(A.CART_NO,1,6), MEM_ID
    ORDER BY 1; 
    
    
  SELECT DISTINCT A.MEM_ID AS 회원번호,
         SUBSTR(B.MON,5,2) || '월' AS 월,
         A.MEM_NAME AS 회원명,
         A.MEM_JOB AS 직업,
         TO_CHAR(B.MAXP) || '원' AS 구매금액합계
    FROM MEMBER A, (SELECT SUBSTR(A.CART_NO,1,6) AS MON,
                          MEM_ID AS MID,
                          MAX(A.CART_QTY * B.PROD_PRICE) AS MAXP
                     FROM CART A, PROD B, MEMBER C
                    WHERE A.CART_PROD = B.PROD_ID
                      AND C.MEM_ID = A.CART_MEMBER
                    GROUP BY SUBSTR(A.CART_NO,1,6), MEM_ID
                    ORDER BY 1) B, (SELECT SUBSTR(A.CART_NO,1,6) AS MON,
                                          MAX(A.CART_QTY * B.PROD_PRICE) AS MAXP
                                     FROM CART A, PROD B
                                    WHERE A.CART_PROD = B.PROD_ID
                                    GROUP BY SUBSTR(A.CART_NO,1,6)) C     
   WHERE B.MAXP = C.MAXP
     AND B.MID = A.MEM_ID
   ORDER BY 2;
   
   
   
   
   
   
  -- 2005년 월별 최대 구매실적
  SELECT SUBSTR(CART_NO,5,2) AS MON,
         A.CART_MEMBER AS MEMID,
         SUM(A.CART_QTY * C.PROD_PRICE) AS COUCQ
    FROM CART A, MEMBER B, PROD C
   WHERE CART_NO LIKE '2005%'
     AND A.CART_MEMBER = B.MEM_ID
     AND A.CART_PROD = C.PROD_ID
   GROUP BY SUBSTR(CART_NO,5,2), A.CART_MEMBER;
   
  
  SELECT SUBSTR(CART_NO,1,6),
         MAX(A.CART_QTY * C.PROD_PRICE) AS COUCQ
    FROM CART A, MEMBER B, PROD C
   WHERE A.CART_NO LIKE '2005%'
     AND A.CART_PROD = C.PROD_ID
     AND A.CART_MEMBER = B.MEM_ID
   GROUP BY SUBSTR(CART_NO,1,6);
  
     
  
  
  
  
  SELECT B.MEM_ID,
         B.MEM_NAME,
         SUM(MAX(A.CART_QTY * C.PROD_PRICE)) AS TOTAL
    FROM CART A, MEMBER B, PROD C
   WHERE A.CART_PROD = C.PROD_ID
     AND B.MEM_ID = A.CART_MEMBER
   GROUP BY B.MEM_ID, B.MEM_NAME;   
  
  
  
  
  
  EX) 사원테이블에서 전체 사원의 평균 급여보다 많은 급여를 받는 사원정보를 조회하시오.
      (사원번호, 사원명, 부서번호, 부서명, 급여)
  SELECT 
    FROM EMPLOYEES;
    
    
  EX) 사원테이블에서 사원이름 중 LAST NAME 이 'Hunold'인 직원과 급여가 같은 직원 정보를 조회하시오.
 -- 메인 -> 사원테이블에서 사원번호, 사원명, 부서명, 급여 
 -- 서브 -> last name이 Hunold인 직원
  
  SELECT EMP_NAME AS NAME,
         SALARY AS NS
    FROM EMPLOYEES
   WHERE EMP_NAME LIKE '%Hunold';
   
  SELECT A.EMPLOYEE_ID AS 사원번호,
         A.EMP_NAME AS 사원명, 
         B.DEPARTMENT_NAME AS 부서명, 
         A.SALARY AS 급여 
    FROM EMPLOYEES A, DEPARTMENTS B
   WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
     AND A.SALARY = (SELECT SALARY AS NS
                       FROM EMPLOYEES
                      WHERE EMP_NAME LIKE '%Hunold');
   
  
  SELECT A.EMPLOYEE_ID AS 사원번호,
         A.EMP_NAME AS 사원명, 
         B.DEPARTMENT_NAME AS 부서명, 
         A.SALARY AS 급여 
    FROM EMPLOYEES A, DEPARTMENTS B
   WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
     AND A.SALARY IN (SELECT SALARY AS NS
                       FROM EMPLOYEES
                      WHERE EMP_NAME LIKE '%Taylor');  
  
  
  
  EX) 상품테이블에서 분류별 최저판매가격의 상품의 정보를 조회하시오.
      상품번호, 상품명, 분류명, 거래처명, 가격
  
  SELECT A.PROD_ID AS 상품번호, 
         A.PROD_NAME AS 상품명, 
         C.LPROD_NM AS 분류명, 
         B.BUYER_NAME AS 거래처명, 
         A.PROD_PRICE AS 가격
    FROM PROD A, BUYER B, LPROD C
   WHERE A.PROD_BUYER = B.BUYER_ID
     AND A.PROD_LGU = C.LPROD_GU
     AND (A.PROD_LGU, A.PROD_PRICE) IN (SELECT PROD_LGU AS PL,
                                               MIN(PROD_PRICE) AS MPR
                                             FROM PROD
                                            GROUP BY PROD_LGU);
  
  
  -- 서브쿼리 : 분류별 최저판매가격의 상품 정보
  SELECT /*PROD_LGU AS PL,*/
         MIN(PROD_PRICE) AS MPR
    FROM PROD
   GROUP BY PROD_LGU; 
         
  EX) 사원테이블에서 각부서별 최저급여를 수령하는 사원 정보를 조회하시오.
        사원번호, 사원명, 부서명, 급여
  
  SELECT A.EMPLOYEE_ID AS 사원번호, 
         A.EMP_NAME AS 사원명, 
         B.DEPARTMENT_NAME AS 부서명, 
         A.SALARY AS 급여
    FROM EMPLOYEES A
   FULL OUTER JOIN  DEPARTMENTS B ON (A.DEPARTMENT_ID = B.DEPARTMENT_ID)
   RIGHT OUTER JOIN (SELECT B.DEPARTMENT_ID AS DI,
                         MIN(B.SALARY) AS MS
                    FROM EMPLOYEES B
                   GROUP BY B.DEPARTMENT_ID) C ON (A.DEPARTMENT_ID = C.DI AND A.SALARY = C.MS);
 

  
  -- 각부서별 최저급여를 수령하는 사원  
  SELECT B.DEPARTMENT_ID AS DI,
         MIN(B.SALARY) AS MS
    FROM EMPLOYEES B
   GROUP BY B.DEPARTMENT_ID;     
  
  
  1.4. FROM 절의 서브쿼리
    - FROM 절의 서브쿼리는 INLINE VIEW 라고도 한다.
    - VIEW 는 쿼리가 시작된 후 보이는 가상의 테이블이다.
    - 커서의 대상이 된다.
  
  EX) 2005년 월별 가장 많이 판매된 제품정보를 조회하시오.
      일자, 상품번호, 상품명, 판매수량
      
  2005년 월별 
  
  -- 메인 : 서브쿼리 결과로 주어진 상품번호와 일치하는 상품정보 출력 cart, prod, subq
  
  -- 서브 : 2005년 월별 최고 판매수량의 제품번호와 일자
  SELECT SUBSTR(A.CART_NO,1,6), 
           MAX(CART_QTY) AS AMTCQ
    FROM CART A
   WHERE CART_NO LIKE '2005%'
   GROUP BY (SUBSTR(A.CART_NO,1,6))
   ORDER BY 1;
  
  
  
  -- 2005년 월별, 제품별 판매수량 합계
  SELECT SUBSTR(A.CART_NO,1,6),
         CART_PROD,
         SUM(CART_QTY) AS AMTCQ
    FROM CART A
   WHERE CART_NO LIKE '2005%'
   GROUP BY (SUBSTR(A.CART_NO,1,6), CART_PROD)
   ORDER BY 1;
   
  -- 월별 최고 판매수량
  SELECT B.DAY1,
         MAX(B.AMTCQ) AS AMT
    FROM CART A, (SELECT SUBSTR(A.CART_NO,1,6) AS DAY1,
                         CART_PROD AS CID,
                         SUM(CART_QTY) AS AMTCQ
                   FROM CART A
                  WHERE CART_NO LIKE '2005%'
                  GROUP BY (SUBSTR(A.CART_NO,1,6), CART_PROD)
                  ORDER BY 1) B
   WHERE A.CART_PROD = B.CID
   GROUP BY B.DAY1
   ORDER BY 1;  
   
  -- 날짜가 서로 같고, 판매수량이 같은  테이블
  
  SELECT 
    FROM PROD D, (SELECT B.DAY1,
         MAX(B.AMTCQ) AS AMT
    FROM CART A, (SELECT SUBSTR(A.CART_NO,1,6) AS DAY1,
                         CART_PROD AS CID,
                         SUM(CART_QTY) AS AMTCQ
                   FROM CART A
                  WHERE CART_NO LIKE '2005%'
                  GROUP BY (SUBSTR(A.CART_NO,1,6), CART_PROD)
                  ORDER BY 1) B
   WHERE A.CART_PROD = B.CID
   GROUP BY B.DAY1
   ORDER BY 1) B, (  SELECT SUBSTR(A.CART_NO,1,6) AS CDAY,
         CART_PROD,
         SUM(CART_QTY) AS AMTCQ
    FROM CART A
   WHERE CART_NO LIKE '2005%'
   GROUP BY (SUBSTR(A.CART_NO,1,6), CART_PROD)
   ORDER BY 1) C
   
   WHERE C.CDAY = B.DAY1
     AND C.AMTCQ = B.AMTCQ;