[Oracle] 서브쿼리

2020년 모든 상품별 매입/매출 집계를 조회하시오
Alias는 상품번호, 상품명, 매입수량, 매입금액, 매출수량, 매출금액

서브쿼리

  SELECT A.PROD_ID AS 상품번호,
                A.PROD_NAME AS 상품명,
                NVL(B.BQTY,0) AS 매입수량,
                NVL(B.BSUM,0) AS 매입금액,
                NVL(C.CQTY,0) AS 매출수량,
                NVL(C.CSUM,0) AS 매출금액
    FROM PROD A, (SELECT A.PROD_ID AS BID,
                         SUM(B.BUY_QTY) AS BQTY,
                         SUM(B.BUY_QTY*A.PROD_COST) AS BSUM
                    FROM PROD A, BUYPROD B      
                   WHERE A.PROD_ID=B.BUY_PROD 
                     AND B.BUY_DATE BETWEEN TO_DATE('20200101') AND LAST_DAY(TO_DATE('20201201'))
                   GROUP BY A.PROD_ID ) B,
                  ( SELECT A.PROD_ID AS CID,
                           SUM(C.CART_QTY) AS CQTY,
                           SUM(C.CART_QTY*A.PROD_PRICE) AS CSUM
                      FROM PROD A, CART C     
                     WHERE A.PROD_ID=C.CART_PROD
                       AND SUBSTR(C.CART_NO,1,4)='2020'
                     GROUP BY A.PROD_ID) C
 WHERE A.PROD_ID=BID(+)
 AND A.PROD_ID=CID(+)
 ORDER BY 1;​

 ANSI FORMAT

   SELECT A.PROD_ID AS 상품번호,
                A.PROD_NAME AS 상품명,
                NVL(B.BQTY,0) AS 매입수량,
                NVL(B.BSUM,0) AS 매입금액,
                NVL(C.CQTY,0) AS 매출수량,
                NVL(C.CSUM,0) AS 매출금액
    FROM PROD A
    LEFT OUTER JOIN (SELECT A.PROD_ID AS BID,
                            SUM(B.BUY_QTY) AS BQTY,
                            SUM(B.BUY_QTY*A.PROD_COST) AS BSUM
                       FROM PROD A, BUYPROD B      
                      WHERE A.PROD_ID=B.BUY_PROD 
                        AND B.BUY_DATE BETWEEN TO_DATE('20200101') AND LAST_DAY(TO_DATE('20201201'))
                      GROUP BY A.PROD_ID ) B 
                  ON (A.PROD_ID=B.BID)
  LEFT OUTER JOIN (SELECT A.PROD_ID AS CID,
                          SUM(C.CART_QTY) AS CQTY,
                          SUM(C.CART_QTY*A.PROD_PRICE) AS CSUM
                     FROM PROD A, CART C     
                    WHERE A.PROD_ID=C.CART_PROD
                      AND SUBSTR(C.CART_NO,1,4)='2020'
                    GROUP BY A.PROD_ID) C 
                  ON(A.PROD_ID=C.CID)
 ORDER BY 1;

 서브 쿼리

• 서브쿼리는 SQL구문 안에 또다른 SQL 구문이 포함된 형태
• 알려지지않은 조건에 근거하여 값들을 조회하는 쿼리가 필요한 경우
• 서브쿼리는 SELECT, INSERT, UPDATE, DELETE 문에서 사용됨
• 서브쿼리는 ' ( ) '로 묶어야 함 (예외 : INSERT와 CREATE 문에 사용되는 서브쿼리)
• 조건절에 서브쿼리가 사용될 경우 서브쿼리는 반드시 연산자 오른쪽에 기술 되어야 함
• 서브쿼리는 SELECT절(일반 서브쿼리) , FROM절(인라인 서브쿼리), WHERE절(중첩 서브쿼리)에 사용 가능
• 실행 순서는 해당 절이 수행될 때 서브 쿼리가 가장 먼저 수행 됨

서브쿼리의 분류

• 단일행/복수행 
   - 사용되는 연산자에 의한 구분
• 연관성 없는 서브쿼리/연관성 있는 서브 쿼리
   - 메인쿼리와 서브쿼리에 사용된 테이블이 조인연산을 사용하는 여부에 따른 분류

단일행 서브쿼리

• 오직 한개의 행만을 반환하는 서브쿼리
• 단일행 연산자 ( =, >, >=, <, <=, != )만 사용 가능

---

사원테이블에서 평균임금보다 더 적은 급여를 받는 사원들을 조회하시오.
Alias는 사원번호, 사원명, 급여, 사원들의 평균급여

(WHERE절)

SELECT EMPLOYEE_ID AS 사원번호,
       EMP_NAME AS 사원명,
       SALARY AS 급여,
       (SELECT ROUND(AVG(SALARY))
          FROM HR.EMPLOYEES) AS "사원들의 평균급여"
        -- WHERE절에 만족하는 56명 수행
FROM HR.EMPLOYEES
WHERE SALARY < (SELECT AVG(SALARY) 
                  FROM HR.EMPLOYEES)
                --전체 107명 수행
ORDER BY 1;
--서브쿼리는 총 163번 수행되어짐

(FROM절)

-- 독립실행되어야함 FROM절 안에있는 부분을 실행하면 반드시 실행되어야함

 SELECT A.EMPLOYEE_ID AS 사원번호,
        A.EMP_NAME AS 사원명,
        A.SALARY AS 급여,
        B.ASAL AS "사원들의 평균급여"
    FROM HR.EMPLOYEES A, (SELECT ROUND(AVG(SALARY)) AS ASAL
                            FROM HR.EMPLOYEES) B
  WHERE A.SALARY<B.ASAL
  ORDER BY 1;
  -- 비동등조인
  -- 서브쿼리 1번 수행

---

2020년 5월 회원별 구매액을 계산하여 구매액이 많은 5명의 회원정보를 조회하시오
Alias는 회원번호, 회원명, 주소, 직업, 마일리지, 구매액

SELECT A.MEM_ID AS 회원번호,
               A.MEM_NAME AS 회원명,
               A.MEM_ADD1 || ' ' || A.MEM_ADD2 AS 주소,
               A.MEM_JOB AS 직업,
               A.MEM_MILEAGE AS 마일리지,
               CSUM AS 구매액 
FROM MEMBER A, ( SELECT CART_MEMBER AS CID,
                        SUM(CART_QTY * PROD_PRICE) AS CSUM
                   FROM CART A, PROD B
                  WHERE A.CART_PROD=B.PROD_ID
                  AND SUBSTR(CART_NO,1,6)='202005'
                  GROUP BY CART_MEMBER
                  ORDER BY 2 DESC) B
 WHERE A.MEM_ID=B.CID
   AND ROWNUM<=5;

연관성있는 쿼리 / JOIN으로 이루어져있는 쿼리 ( 알려지지않은 조건을 근거하여 값을 조회한다)

서브쿼리를 사용하는 INSERT

INSERT INTO 테이블명 [(컬럼list)]      서브쿼리;

서브쿼리의 SELECT절의 컬럼의 갯수, 순서와 INTO절에 사용된 (컬럼list)의 갯수, 순서와 반드시 일치

---

생성된 재고수불테이블에 다음자료를 삽입하시오
년도 : 2020
상품번호 : PROD 테이블의 상품번호
기초재고 : PROD 테이블의 PROD_PROPERSTOCK
입고/출고 수량 : 없음
현재고 : 기초재고와 같음
갱신일 : 2020년 1월 1일

INSERT INTO REMAIN(REMAIN_YEAR,PROD_ID,REMAIN_J_00,
                   REMAIN_J_99,REMAIN_DATE)
     SELECT '2020', PROD_ID, PROD_PROPERSTOCK, PROD_PROPERSTOCK, TO_DATE('20200101')
       FROM PROD;

COMMIT;

---

HR계정의 사원테이블에서 부서의 위치가 미국에 위치한 부서에 속한 사원의 평균 급여보다 더 많은 급여를 받는 미국 이외의 부서에 근무하는 사원정보를 조회하시오
Alias는 사원번호, 사원명, 부서명, 급여

SELECT A.EMPLOYEE_ID AS 사원번호,
              A.EMP_NAME AS 사원명,
              B.DEPARTMENT_NAME AS 부서명,
              ROUND(BAVG) AS 평균급여,
              A.SALARY AS 급여
FROM HR.EMPLOYEES A, HR.DEPARTMENTS B, HR.LOCATIONS C,
            (SELECT AVG(A.SALARY) AS BAVG
               FROM HR.EMPLOYEES A, HR.DEPARTMENTS B, HR.LOCATIONS C
            WHERE A.DEPARTMENT_ID=B.DEPARTMENT_ID
              AND B.LOCATION_ID=C.LOCATION_ID
              AND C.COUNTRY_ID='US') D
WHERE A.DEPARTMENT_ID=B.DEPARTMENT_ID
AND B.LOCATION_ID=C.LOCATION_ID
AND C.COUNTRY_ID!='US'
AND A.SALARY>D.BAVG
ORDER BY A.DEPARTMENT_ID, A.SALARY;

---

회원테이블에서 마일리지가 많은 5명의 회원이 2020년 4월 구매한 정보를 조회하시오
Alias는 회원번호, 회원명, 구매금액합계

SELECT A.MEM_ID AS 회원번호,
              A.MEM_NAME AS 회원명,
              SUM(B.CART_QTY*C.PROD_PRICE) AS 구매금액합계
FROM MEMBER A, CART B, PROD C
WHERE A.MEM_ID=B.CART_MEMBER
AND B.CART_PROD = C.PROD_ID
AND SUBSTR(CART_NO,1,6)='202004'
AND A.MEM_ID IN(SELECT DID
                  FROM (SELECT MEM_ID AS DID, MEM_MILEAGE
                          FROM MEMBER
                         WHERE MEM_MILEAGE IS NOT NULL
                         ORDER BY MEM_MILEAGE DESC)
                   WHERE ROWNUM<=5) 
GROUP BY A.MEM_ID, A.MEM_NAME, A.MEM_MILEAGE
ORDER BY 3 DESC;

---

부서테이블에서 부서관리사원의 사원번호가 100인 부서에 속한 사원정보를 조회하시오

WHERE 조건의 IN

SELECT B.DEPARTMENT_NAME AS 부서이름,
              A.EMPLOYEE_ID AS 사원번호,
              A.EMP_NAME AS 사원이름
FROM HR.EMPLOYEES A, HR.DEPARTMENTS B
WHERE A.DEPARTMENT_ID=B.DEPARTMENT_ID
AND A.DEPARTMENT_ID IN ( SELECT DEPARTMENT_ID AS BDID
                           FROM HR.DEPARTMENTS
                          WHERE MANAGER_ID=100 );

FROM

SELECT B.DEPARTMENT_NAME AS 부서이름,
              A.EMPLOYEE_ID AS 사원번호,
              A.EMP_NAME AS 사원이름
FROM HR.EMPLOYEES A, HR.DEPARTMENTS B, 
        ( SELECT DEPARTMENT_ID AS BDID
            FROM HR.DEPARTMENTS
           WHERE MANAGER_ID=100 ) C
WHERE A.DEPARTMENT_ID=B.DEPARTMENT_ID
AND B.DEPARTMENT_ID=C.BDID;

EXISTS 연산자

EXISTS 뒤에는 반드시 서브쿼리가 나와야함

메인테이블에 사용된 컬럼과 서브컬럼 사이에 조인이 되어야한다.

결과가 하나라도 있으면 참 / 하나도 없으면 거짓

SELECT DEPARTMENT_ID AS 부서번호,
       EMPLOYEE_ID AS 사원번호,
       EMP_NAME AS 사원이름
FROM HR.EMPLOYEES A
WHERE EXISTS ( SELECT 1   --WHERE절이 참이면 1을 출력 >> 결과 존재
                 FROM HR.DEPARTMENTS B
                WHERE MANAGER_ID=100 -- 늘 참이므로 전체 107명 출력
                  AND A.DEPARTMENT_ID=B.DEPARTMENT_ID); --100번인 부서번호와 부서번호 조건

---

사원테이블에서 각 부서별 평균급여보다 더 많은 급여를 받는 사원정보를 조회하시오
Alias는 사원번호, 사원명, 부서번호, 부서평균급여, 급여

SELECT A.EMPLOYEE_ID AS 사원번호,
              A.EMP_NAME AS 사원명,
              A.DEPARTMENT_ID AS 부서번호,
              B.BAVG AS 부서평균급여,
              A.SALARY AS 급여
FROM HR.EMPLOYEES A, (SELECT DEPARTMENT_ID AS BID,
                             ROUND(AVG(SALARY)) AS BAVG
                        FROM HR.EMPLOYEES
                       GROUP BY DEPARTMENT_ID) B
WHERE A.DEPARTMENT_ID=B.BID
AND A.SALARY>B.BAVG;

---

SELECT A.EMPLOYEE_ID AS 사원번호,
       A.EMP_NAME AS 사원명,
       A.DEPARTMENT_ID AS 부서번호,
       (SELECT ROUND(AVG(SALARY)) AS BAVG
          FROM HR.EMPLOYEES B
         WHERE A.DEPARTMENT_ID=B.DEPARTMENT_ID) AS 부서평균급여,
       A.SALARY AS 급여
FROM HR.EMPLOYEES A
WHERE A.SALARY>(SELECT ROUND(AVG(SALARY)) AS BAVG
                 FROM HR.EMPLOYEES B
                WHERE A.DEPARTMENT_ID=B.DEPARTMENT_ID)
ORDER BY 3, 5 DESC;

EXISTS 연산자 사용

SELECT A.EMPLOYEE_ID AS 사원번호,
       A.EMP_NAME AS 사원명,
       A.DEPARTMENT_ID AS 부서번호,
       (SELECT ROUND(AVG(SALARY)) AS BAVG
          FROM HR.EMPLOYEES B
         WHERE A.DEPARTMENT_ID=B.DEPARTMENT_ID) AS 부서평균급여,
       A.SALARY AS 급여
FROM HR.EMPLOYEES A
WHERE EXISTS( SELECT  1
               FROM HR.EMPLOYEES B
              WHERE A.DEPARTMENT_ID=B.DEPARTMENT_ID
                AND A.SALARY>(SELECT ROUND(AVG(SALARY)) AS BAVG
                                FROM HR.EMPLOYEES B
                               WHERE A.DEPARTMENT_ID=B.DEPARTMENT_ID))
 ORDER BY 3, 5 DESC;