230728 SQL 강의

--사원번호가 7499인 사원보다 급여가 많은 사원을 표시하시오. 사원이름과 담당 업무
SELECT * FROM EMP;

SELECT E2.ENAME 사원이름,
       E2.JOB 담당업무,
       E2.SAL 급여
  FROM EMP E1, EMP E2
 WHERE E1.EMPNO = '7499'
   AND E1.SAL < E2.SAL;

---

-- 최소급여를 받는 사원의 이름, 담당업무 및 급여를 표시하시오. 
SELECT MIN(SAL) FROM EMP;

SELECT ENAME 사원이름
     , JOB 담당업무
     , SAL 급여
  FROM EMP
 WHERE SAL = (SELECT MIN(SAL) 
                FROM EMP);

---

--평균급여가 가장 적은 직급의 직급 이름과 직급의 평균을 구하시오.
SELECT * FROM EMP;

SELECT JOB 직급명
     , AVG 직급평균
  FROM 
  (
    SELECT JOB
     , AVG(SAL) AVG
      FROM EMP
     GROUP BY JOB
     ORDER BY AVG(SAL) ASC
  )
 WHERE ROWNUM = 1;

---

--각 부서의 최소 급여를 받는 사원의 이름, 급여, 부서번호를 표시하시오.
SELECT * FROM EMP;
SELECT DEPTNO, MIN(SAL) MIS FROM EMP GROUP BY DEPTNO ORDER BY DEPTNO ASC;

SELECT A.ENAME 사원이름
     , A.SAL 급여
     , A.DEPTNO 부서번호
  FROM EMP A,
  (
    SELECT DEPTNO
         , MIN(SAL) SAL 
      FROM EMP 
     GROUP BY DEPTNO
  ) B
 WHERE A.SAL = B.SAL
 ORDER BY A.DEPTNO ASC;

---

--평균 영업사원 급여보다 급여가 적으면서 업무가 영업사원이 아닌 사원들을 표시(사원번호, 이름, 담당 업무, 급여)하시오.
SELECT * FROM EMP;

SELECT AVG(SAL) SAL
  FROM EMP;
  
SELECT EMPNO 사원번호
     , ENAME 이름
     , JOB 담당업무
     , SAL 급여
  FROM EMP
 WHERE 1=1
   AND SAL < (SELECT AVG(SAL) SAL FROM EMP)
   AND JOB <> '영업사원';

---

--평균 영업사원 급여보다 급여가 적으면서 업무가 영업사원이 아닌 사원들을 표시(사원번호, 이름, 담당 업무, 급여)하시오.
SELECT * FROM EMP;

SELECT AVG(SAL) SAL
  FROM EMP
 WHERE JOB = '영업사원';
  
SELECT A.EMPNO 사원번호
     , A.ENAME 이름
     , A.JOB 담당업무
     , A.SAL 급여
  FROM EMP A,
       (
        SELECT AVG(SAL) SAL
          FROM EMP
         WHERE JOB = '영업사원'
       ) B
 WHERE 1=1
   AND A.SAL < B.SAL
   AND A.JOB <> '영업사원';

---

--부하직원이 없는 사원의 이름을 표시하시오. 
--(사원 번호가 매니저로 저장되어 있진 않은 사람을 조회해야 함)
SELECT * FROM EMP;

SELECT E.ENAME 사원이름
  FROM EMP E, EMP E1
 WHERE E.EMPNO = E1.MGR;
 
SELECT DISTINCT(ENAME) 사원명
  FROM EMP
 WHERE ENAME NOT IN (
    SELECT E.ENAME 사원이름
      FROM EMP E, EMP E1
     WHERE E.EMPNO = E1.MGR);

---

---

--단일 행(Sing-Row) 서브쿼리 
--오직 한개의 행(값)을 반환 합니다.(Return only one row)
--단일 행 연산자(=,>, >=, <, <=, <>, !=) 만 사용 할 수 있습니다. 
SELECT ename,job
  FROM emp
 WHERE job = (SELECT job 
                FROM emp 
               WHERE empno = 7369);

--다중 행(Multiple-Row) 서브쿼리 란 ?
--하나 이상의 행을 RETURN하는 SUBQUERY를 다중 행 SUBQUERY라고 합니다. 
--복수 행 연산자(IN, NOT IN, ANY, ALL, EXISTS)를 사용 할 수 있습니다. 

--IN 연산자의 사용 예제
--예제)부서별로 가장 급여를 많이 받는 사원의 정보를 출력하는 예제 입니다.
SELECT empno, ename, sal, deptno  
  FROM emp
 WHERE sal IN (SELECT MAX(sal)
                 FROM emp
                GROUP BY deptno);

--ANY 연산자의 사용 예제
--ANY연산자는 서브쿼리의 결과값중 어느 하나의 값이라도 만족이 되면 결과값을 반환 합니다. 
SELECT * FROM EMP;

SELECT ename, sal 
  FROM emp 
 WHERE sal > ANY(SELECT sal FROM emp WHERE job='영업사원');

--ALL 연산자의 사용 예제
--ALL연산자는 서브쿼리의 결과값중 모든 결과 값이 만족 되야만 결과값을 반환 합니다.
SELECT ename, sal 
  FROM emp 
 WHERE sal > ALL(SELECT sal FROM emp WHERE job='영업사원');

--EXISTS 연산자의 사용 예제
--EXISTS 연산자를 사용하면 서브쿼리의 데이터가 존재하는가의 여부를 먼저 따져 존재하는 값들만을 결과로 반환해 줍니다. 
--SUBQUERY에서 적어도 1개의 행을 RETURN하면 논리식은 참이고 그렇지 않으면 거짓 입니다.

--예제)사원을 관리할 수 있는 사원의 정보를 보여 줍니다. 
SELECT empno, ename, sal
FROM emp e
WHERE EXISTS (SELECT empno FROM emp WHERE e.empno = mgr);

--Pairwise(쌍비교) Subquery 
--서브쿼리가 한번 실행되면서 모든 조건을 검색해서 주 쿼리로 넘겨 줍니다.
SELECT empno, sal, deptno
  FROM emp
 WHERE (sal, deptno) IN ( SELECT sal, deptno
                            FROM emp
                           WHERE deptno = 30
                             AND comm is NOT NULL );

--Nonpairwise(비쌍비교) Subquery 
--서브쿼리가 여러 조건별로 사용 되어서 결과값을 주 쿼리로 넘겨 줍니다.
SELECT empno, sal, deptno
  FROM emp
 WHERE sal IN ( SELECT sal
                  FROM emp
                 WHERE deptno = 30
                   AND comm is NOT NULL )
   AND deptno IN ( SELECT deptno
                     FROM emp
                    WHERE deptno = 30 
                      AND comm is NOT NULL );

--Null Values in a Subquery 
--서브쿼리에서 null값이 반환되면 주 쿼리 에서는 어떠한 행도 반환되지 않습니다.

---

--------------------------------------------------------------------

--FROM절상의 서브쿼리(INLINE VIEW)와 상관관계 서브쿼리 

--FROM절상의 서브쿼리(INLINE VIEW) 란 ?
--SUBQUERY는 FROM절에서도 사용이 가능 합니다. 

--INLINE VIEW란 FROM절상에 오는 서브쿼리로 VIEW처럼 작용 합니다.
--예제)급여가 20부서의 평균 급여보다 크고 사원을 관리하는 사원으로서 20부서에 속하지 않은 사원의 정보를 보여주는 SQL문 입니다. 
SELECT b.empno, b.ename, b.job, b.sal, b.deptno
  FROM (SELECT empno FROM emp WHERE sal > (SELECT AVG(sal) FROM emp WHERE deptno = 20)) a, emp b
 WHERE a.empno = b.empno
   AND b.mgr IS NOT NULL
   AND b.deptno != 20;

--상관관계 서브쿼리
--상관관계 서브쿼리란 바깥쪽 쿼리의 컬럼 중의 하나가 안쪽 서브쿼리의 조건에 이용되는 처리 방식 입니다.
--이는 주 쿼리에서 서브 쿼리를 참조하고 이 값을 다시 주 쿼리로 반환한다는 것입니다.

--예제) 사원을 관리할 수 있는 사원의 평균급여보다 급여를 많이 받는 사원의 정보를 출력
SELECT empno, ename, sal
FROM emp e
WHERE sal > (SELECT AVG(sal) sal FROM emp WHERE e.empno = mgr);

---

--집합 쿼리(UNION, INTERSECT, MINUS) 
--집합 연산자를 사용시 집합을 구성할 컬러의 데이터 타입이 동일해야 합니다. 
--◈ UNION :합집합       ◈ UNION ALL:공통원소 두번씩 다 포함한 합집합 
--◈ INTERSECT:교집합    ◈ MINUS:차집합

--UNION
--UNION은 두 테이블의 결합을 나타내며, 결합시키는 두 테이블의 중복되지 않은 값들을 반환 합니다. 
SELECT deptno FROM emp
UNION
SELECT deptno FROM dept;

--UNION ALL 
--UNION과 같으나 두 테이블의 중복되는 값까지 반환 합니다. 
SELECT deptno FROM emp
UNION ALL
SELECT deptno FROM dept;

--INTERSECT
--INTERSECT는 두 행의 집합중 공통된 행을 반환 합니다. 
SELECT deptno FROM emp
INTERSECT
SELECT deptno FROM dept;

--MINUS
--MINUS는 첫번째 SELECT문에 의해 반환되는 행중에서 두번째 SELECT문에 의해 반환되는 행에 존재하지 않는 행들을 보여 줍니다. 
SELECT deptno FROM dept
MINUS
SELECT deptno FROM emp;

---

-- 재고 테이블
CREATE TABLE  REMAIN
(  
    REMAIN_YEAR      CHAR(04)      NOT NULL,  -- 해당 년도   
    REMAIN_PROD      VARCHAR2(10)  NOT NULL,  -- 상품 코드   
    REMAIN_J_00      NUMBER(5),               -- 전년 재고   
    REMAIN_I         NUMBER(5),               -- 입고
    REMAIN_O         NUMBER(5),               -- 출고
    REMAIN_J_99      NUMBER(5),               -- 현재고  
    REMAIN_DATE      DATE,                    -- 처리일자
    CONSTRAINT PK_REMAIN      PRIMARY KEY(REMAIN_YEAR, REMAIN_PROD),
    CONSTRAINT FR_REMAIN_PROD FOREIGN KEY(REMAIN_PROD) 
                              REFERENCES PROD(PROD_ID)
);

INSERT INTO remain (remain_year, remain_prod, remain_j_00, remain_i, remain_o, remain_j_99, remain_date) 
     VALUES ('2003', 'P101000001', 20, 10, 12, 18, '2004-01-01');
INSERT INTO remain (remain_year, remain_prod, remain_j_00, remain_i, remain_o, remain_j_99, remain_date) 
     VALUES ('2003', 'P101000002', 11, 7, 6, 12, '2004-01-02');
INSERT INTO REMAIN (REMAIN_YEAR, REMAIN_PROD, REMAIN_I)
     VALUES ('2003', 'P102000007', 10);
INSERT INTO REMAIN(REMAIN_YEAR, REMAIN_PROD, remain_j_00,REMAIN_I, REMAIN_J_99, REMAIN_DATE)
     VALUES ('2003', 'P102000001', 31, 21, 41, '2003-12-31');
INSERT INTO REMAIN(REMAIN_YEAR,REMAIN_PROD,remain_j_00,REMAIN_I,remain_o,REMAIN_J_99,REMAIN_DATE)
     VALUES ('2003', 'P102000002', 31, 21, NULL, 41, '2003-12-31');
INSERT INTO REMAIN(REMAIN_YEAR,REMAIN_PROD,remain_j_00,REMAIN_I,remain_o,REMAIN_J_99,REMAIN_DATE)
     VALUES ('2003', 'P102000003', 31, 21, 11, 41, SYSDATE);

SELECT * FROM PROD;
SELECT * FROM REMAIN;

DELETE FROM REMAIN;
SELECT * FROM REMAIN;

DELETE 
  FROM remain
 WHERE REMAIN_YEAR = '2003'
   AND NVL(REMAIN_J_00, 0) + NVL(REMAIN_I, 0) - NVL(REMAIN_O, 0) != NVL(REMAIN_J_99, 0);

--모든 상품에 대한 재고 수불 파일 생성 ( 2003년도 재고 수불 마감)
--- 해당년도 : 2004년     
--- 상품코드 : 전 상품
--- 기초(전년)재고 : 상품코드의 우측 2자리를 숫자로 Conversion하여 
--                             처리 ( 원칙은 전년도말 재고가 되어야 함 )
--- 입고 : 10으로 일괄 처리
--- 출고 : 7으로 일괄 처리
--- 현재고 : 전년재고 + 입고 - 출고

INSERT INTO REMAIN (REMAIN_YEAR, REMAIN_PROD, REMAIN_J_00, REMAIN_I, REMAIN_O, REMAIN_J_99, REMAIN_DATE)
SELECT '2004', PROD_ID, TO_NUMBER(SUBSTR(PROD_ID, -2)), 10, 7, TO_NUMBER(SUBSTR(PROD_ID, -2))+10-7, SYSDATE FROM PROD;

SELECT '2004', PROD_ID, TO_NUMBER(SUBSTR(PROD_ID, -2)), 10, 7, TO_NUMBER(SUBSTR(PROD_ID, -2))+10-7, SYSDATE FROM PROD;