/*- 07_테이블_DML.sql -*/

/*
 * # INSERT
 * - 테이블에 새로운 데이터를 추가할 때 사용.
 *   INSERT INTO table_name
 *   (column_name, .....)
 *   VALUES ( column_value , ..... );
 */

SELECT * FROM DEPT01;

-- 새로운 데이터를 추가하기 위해서 사용하는 명령어 INSERT INTo VALUES는
-- 괄호안의 컬럼명에 있는 목록수와 VALUES 다음에 오는 괄호에 작성한 값을 갯수가 일치해야 함.
INSERT INTO DEPT01 (DEPTNO, DNAME, LOC)
VALUES
(10, 'accounting', 'new york');

SELECT * FROM DEPT01; 

-- 컬럼 수와 VALUES 다음에 작성한 값의 수가 다르면 ERROR
--INSERT INTO DEPT01 (DEPTNO, DNAME, LOC)
--VALUES
--(10, 'accounting');

-- 컬럼명이 다르면 ERROR
--
--INSERT INTO DEPT01 (DEPTNO, DNAME, LOK)
--VALUES
--(10, 'accounting', 'new york');

-- 컬럼에 입력할 값의 데이터 타입이 다르면 ERROR
--INSERT INTO DEPT01 (DEPTNO, DNAME, LOC)
--VALUES
--(10, 'accounting', seoul);

-- 모든 컬럼에 데이터를 입력하는 경우에는 컬럼 목록을 작성하지 않아도 됨.
-- 컬럼 목록이 생략되면 VALUES 다음에 값들이 테이블의 컬럼 순서대로 저장.
INSERT INTO DEPT01
VALUES
(10, 'James', 'Canada');

INSERT INTO DEPT01 (deptno, dname)
VALUES (60,'AI');

INSERT INTO DEPT01 (loc, dname, deptno) VALUES ('busan', 'developer', 70 );

SELECT * FROM DEPT01;

/*
 * # NULL 값 추가
 * - NULL 또는 ''사용
 */

INSERT INTO DEPT01 VALUES (40, 'operations', NULL );

INSERT INTO DEPT01 VALUES (80, '', NULL );

/*
 * # 서브쿼리를 사용해서 ROW 복사
 */

CREATE TABLE DEPT02
AS
SELECT * FROM DEPT WHERE 1=0;

INSERT INTO DEPT02
SELECT * FROM DEPT;

SELECT * FROM DEPT02;

/*
 * # INSERT ALL 을 사용한 다중행 입력.
 */

-- 사원번호, 사원명, 입사일자를 관리하는 EMP_HIRE 테이블 생성.
CREATE TABLE EMP_HIRE
AS
SELECT EMPNO, ENAME, HIREDATE FROM EMP WHERE 1=0;

SELECT * FROM EMP_HIRE;

-- 사원번호, 사원명, 상관을 관리하는 EMP_MGR 테이블 생성.
CREATE TABLE EMP_MGR
AS
SELECT EMPNO, ENAME, MGR FROM EMP WHERE 1=0;

SELECT * FROM EMP_MGR;

INSERT ALL
INTO EMP_HIRE VALUES (EMPNO, ENAME, HIREDATE)
INTO EMP_MGR VALUES (EMPNO, ENAME, MGR)
SELECT EMPNO, ENAME, HIREDATE, MGR FROM EMP WHERE DEPTNO=20;

SELECT * FROM EMP_HIRE;
SELECT * FROM EMP_MGR;

/*
 * # INSERT ALL ~ WHEN(조건) 으로 다중 테이블에 다중 행 입력
 * 
 */

-- 사원번호, 사원명, 입사일자를 관리하는 EMP_HIRE2 테이블 생성
CREATE TABLE EMP_HIRE2
AS
SELECT EMPNO, ENAME, HIREDATE FROM EMP WHERE 1=0;

SELECT * FROM EMP_HIRE2;

-- 사원번호, 사원명, 급여를 관리하는 EMP_SAL 테이블 생성
CREATE TABLE EMP_SAL
AS
SELECT EMPNO, ENAME, SAL FROM EMP WHERE 1=0;

SELECT * FROM EMP_SAL;

-- EMP_HIRE2 테이블에는 1982년 1월 1일 이후에 입사한 사원의 정보 추가.
-- EMP_SAL 테이블에는 급여가 2000 이상인 사원의 정보 추가.
INSERT ALL
WHEN HIREDATE > '1982/01/01' THEN
INTO EMP_HIRE2 VALUES (EMPNO, ENAME, HIREDATE)
WHEN SAL >= 2000 THEN
INTO EMP_SAL VALUES (EMPNO,ENAME,SAL)
SELECT EMPNO,ENAME,HIREDATE,SAL FROM EMP;

SELECT * FROM EMP_HIRE2;
SELECT * FROM EMP_SAL;

/*
 * # UPDATE
 * - 테이블에 저장된 데이터를 수정할 때 사용.
 *   UPDATE table_name
 *   SET column_name = value , .....
 *   WHERE conditions;
 * - WHERE 절을 지정하면 특정 행의 값이 수정되고, 사용하지 않으며 모든 행이 수정됨.
 */
 
-- 연습 테이블
DROP TABLE EMP01 PURGE;

CREATE TABLE EMP01 AS SELECT * FROM EMP;

SELECT * FROM EMP01;

-- 모든 사원의 부서번호를 30번으로 수정
UPDATE EMP01
SET DEPTNO=30;

SELECT * FROM EMP01;

-- 모든 사원의 급여를 10%인상
UPDATE EMP01
SET SAL=SAL*1.1;

SELECT * FROM EMP01;

-- 모든 사원의 입사일을 오늘로 변경.
UPDATE EMP01
SET HIREDATE=SYSDATE;

SELECT * FROM EMP01;

/* 
 * # 특정 행만 수정
 */

-- 연습 테이블
DROP TABLE EMP01 PURGE;

CREATE TABLE EMP01 AS SELECT * FROM EMP;

SELECT * FROM EMP01;

-- 부서번호가 10번인 사원의 부서번호를 30번으로 수정
UPDATE EMP01
SET DEPTNO=30
WHERE DEPTNO=10;

SELECT * FROM EMP01;

-- 급여가 3000이상인 사원의 급여를 10%인상
UPDATE EMP01
SET SAL=SAL*1.1
WHERE SAL>=3000;

SELECT * FROM EMP01;

-- 1982년도 입사한 사원 입사일을 오늘로 변경
UPDATE EMP01
SET HIREDATE = SYSDATE
WHERE SUBSTR(HIREDATE,1,2) = '82';

SELECT * FROM EMP01;

/* 
 * # 2개 이상의 컬럼값 변경
 * - 기존 SET 절에 쉼표로 구분해서 '컬럼=값'을 작성.
 */

UPDATE EMP01
SET DEPTNO=40, JOB = 'MANAGER'
WHERE ENAME='SMITH';

SELECT * FROM EMP01;

/*
 * # 서브 쿼리를 사용한 데이터 수정
 * - UPDATE 문의 SET절에서 서브쿼리를 작성하면, 서브 쿼리를 실행한 결과로 내용이 변경.
 * 
 */

DROP TABLE DEPT01 PURGE;

CREATE TABLE DEPT01 AS SELECT * FROM DEPT;

SELECT * FROM DEPT01;

-- 20번 부서의 지역명을 40번 부서의 지역명으로 설정.
UPDATE DEPT01
SET LOC=(SELECT LOC FROM DEPT01 WHERE DEPTNO=40)
WHERE DEPTNO=20;

-- 부서번호 20번인 부서의 부서명과 지역명을 부서번호 30번과 동일하게 변경.
UPDATE DEPT01
SET (DNAME, LOC)=(SELECT DNAME, LOC FROM DEPT01 WHERE DEPTNO=30)
WHERE DEPTNO=20;

SELECT * FROM DEPT01;

/*
 * # DELETE
 * - 테이블에 저장되어 있는 데이터 삭제.
 *   DELETE FROM table_name
 *   WHERE conditions;
 * - 특정 행(row)를 삭제하기 위해서는 WHERE 절을 사용해서 조건을 지정.
 *   WHERE 절을 지정하지 않으면 모든 행이 삭제.
 */

-- DEPT01 테이블 생성.
DROP TABLE DEPT01 PURGE;

CREATE TABLE DEPT01 AS SELECT * FROM DEPT;

SELECT * FROM DEPT01;

-- DEPT01 테이블의 모든 데이터 삭제.
DELETE FROM DEPT01;

SELECT * FROM DEPT01;

-- DEPT01 테이블의 30번 부서번호 삭제

DELETE FROM DEPT01 WHERE DEPTNO=30;
SELECT * FROM DEPT01;

-- EMP01 테이블 생성.
DROP TABLE EMP01 PURGE;

CREATE TABLE EMP01 AS SELECT * FROM EMP;

SELECT * FROM EMP01;

-- 서브쿼리를 사용한 데이터 삭제
-- 부서명이 SALES인 사원을 모두 삭제
SELECT * FROM EMP01;
DELETE FROM EMP01 WHERE DEPTNO=(SELECT DEPTNO FROM DEPT01 WHERE DNAME LIKE ('%SALES%'));

/*
 * # MERGE
 * - 구조가 같은 두 개의 테이블을 하나의 테이블로 합치는 기능.
 *   > 기존에 존재하는 행이 있으면 새로운 값으로 갱신(UPDATE) 되고,
 *     존재하지 않으면 새로운 행으로 추가(INSERT)
 */

-- EMP01 & EMP02 테이블 생성.
DROP TABLE EMP01 PURGE;

CREATE TABLE EMP01 AS SELECT * FROM EMP;

SELECT * FROM EMP01;

DROP TABLE EMP02 PURGE;

CREATE TABLE EMP02 AS SELECT * FROM EMP WHERE JOB LIKE '%MANAGER%';

SELECT * FROM EMP02;

-- EMP02 테이블의 job을 test로 변경
UPDATE EMP02 SET JOB='TEST';
SELECT * FROM EMP02;

-- EMP02 테이블에 데이터 추가
INSERT INTO EMP02 VALUES (9242, 'JAMES', 'BLACK', 7698, '80/12/23', 4000, null, 50);
SELECT * FROM EMP02;

-- EMP01 테이블에 EMP02 테이블 병합
MERGE INTO EMP01 USING EMP02 ON (EMP01.EMPNO=EMP02.EMPNO) 
WHEN MATCHED THEN 
UPDATE SET EMP01.ENAME=EMP02.ENAME,
           EMP01.JOB=EMP02.JOB,
           EMP01.MGR=EMP02.MGR,
           EMP01.HIREDATE=EMP02.HIREDATE,
           EMP01.SAL=EMP02.SAL,
           EMP01.COMM=EMP02.COMM,
           EMP01.DEPTNO=EMP02.DEPTNO
WHEN NOT MATCHED THEN
INSERT VALUES (EMP02.EMPNO,
               EMP02.ENAME,
               EMP02.JOB,
               EMP02.MGR,
               EMP02.HIREDATE,
               EMP02.SAL,
               EMP02.COMM,
               EMP02.DEPTNO);

SELECT * FROM EMP01;
SELECT * FROM EMP02;

/* quiz */
DROP TABLE SAM01 PURGE;
DROP TABLE SAM02 PURGE;
SELECT * FROM SAM01;
SELECT * FROM SAM02;
SELECT * FROM DEPT;
-- EMP 테이블의 empno, ename, job, sal 컬럼 이름만 적용된 SAM01 테이블을 생성하세요
-- 이미 있는 테이블이면 삭제후에 생성하세요
CREATE TABLE SAM01 AS SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE 1=0; 
-- SAM01 테이블에 데이터를 3개 추가하세요
INSERT INTO SAM01 (EMPNO,ENAME,JOB,SAL) VALUES (9202, 'munsan', 'BLACK', 6000);
INSERT INTO SAM01 (EMPNO,ENAME,JOB,SAL) VALUES(9539, 'guryoengpo', 'BLACK', 4500);
INSERT INTO SAM01 (EMPNO,ENAME,JOB,SAL) VALUES(9429, 'jincheun', 'BLACK', 4300);
-- SAM01 테이블에 job 은 null 값을 가지는 데이터 2개를 추가하세요
INSERT INTO SAM01 (EMPNO,ENAME,JOB,SAL) VALUES (9535, 'NULLMEM1', NULL, 2300);
INSERT INTO SAM01 (EMPNO,ENAME,JOB,SAL) VALUES (9525, 'NULLMEM2', NULL, 2400);
-- SAM01 테이블에 EMP 테이블의 부서번호 10번의 사원 정보를 추가하세요
INSERT INTO SAM01 (EMPNO,ENAME,JOB,SAL) (SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE DEPTNO=10);
-- SAM01 테이블의 사원 중 급여가 5000 이상인 사원들의 급여를 3000씩 감소 시키세요
UPDATE SAM01 SET SAL=SAL-3000 WHERE SAL>=5000;
-- 서브쿼리를 사용해서 EMP 테이블의 저장된 ename, sal, hiredate, deptno 컬럼을 적용한 SAM02 테이블을 생성하세요
CREATE TABLE SAM02 AS SELECT ENAME,SAL,HIREDATE,DEPTNO FROM EMP;
-- SAM02 테이블의 DALLAS 에 위치한 부서 소속의 사원들 급여를 1000 씩 인상하세요
UPDATE SAM02 SET SAL=SAL+1000 WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE LOC='DALLAS');
-- 서브쿼리 문을 사용해서 SAM02 테이블의 모든 사원의 급여와 입사일을 KING 인 사원의 급여와 입사일로 변경하세요
UPDATE SAM02 SET (SAL,HIREDATE)=(SELECT SAL,HIREDATE FROM SAM02 WHERE ENAME='KING');

-- SAM01 테이블에서 직급이 정해지지 않은 사원을 삭제하세요
DELETE FROM SAM01 WHERE JOB IS NULL;
-- SAM02 테이블에서 RESEARCH 부서 소속 사원들만 삭제하세요
DELETE FROM SAM02 WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE DNAME='RESEARCH');