16.DML

/* 
    <DML(Date Manipulation Languae)>
        - 데이터 조작 언어로 테이블에 값을 삽입(INSERT)하거나, 수정(UPDATE), 삭제(DELETE)하는ㄱ ㅜ문
        
    <INSERT>
        - 테이블에 새로운 행을 추가하는 구문
        INSERT INTO 테이블명 (서브쿼리);

*/
DROP TABLE emp_01;
DROP TABLE emp_02;
DROP TABLE emp_03;
DROP TABLE emp_copy;

--  테이블 복사
CREATE TABLE emp_01
AS SELECT * FROM emp;
-- 테이블 구조 복사 (조건이 일치하지 않으니 데이터는 복사안됨)
CREATE TABLE EMP_02 AS SELECT * FROM emp WHERE 1<0;
-- 테이블 구조 복사2 - 원하는 컬럼만 볷
CREATE TABLE EMP_03 AS SELECT emp_id, emp_name FROM emp WHERE 1<0;
SELECT * FROM emp_03;

CREATE TABLE emp_copy (
    emp_id NUMBER,
    emp_name VARCHAR2(30),
    dept_title VARCHAR2(35)
);
-- 서브쿼리를 이용하여 데이터를 입력
INSERT INTO emp_copy(
    SELECT emp.emp_id, emp_name, dept_title
    FROM emp
    LEFT JOIN dept ON(dept_code=dept_id)
    );
    
CREATE TABLE emp_copy 
AS  SELECT emp_id, emp_name, dept_title
    FROM emp, dept
    WHERE dept_code = dept_id;

-- 사원번호, 사원명, 직급명, 부서명
DROP TABLE emp_info;

CREATE TABLE emp_info (
    emp_id VARCHAR2(3), 
    emp_name VARCHAR2(20), 
    job_name VARCHAR2(35), 
    dept_title VARCHAR2(35) 
    );

INSERT INTO emp_info (
    SELECT emp_id, emp_name, job_name, dept_title
    FROM emp
    LEFT JOIN dept ON(dept_code = dept_id)
    JOIN job USING(job_code)
    );
    
SELECT * FROM emp_info;
    
/*
컬럼명의 개수와 타입을 맞춰줘야 함
INSERT INTO 테이블명(컬럼명, ...)
        (서브쿼리)
*/
INSERT INTO EMP_INFO(emp_id, emp_name) 
    SELECT emp_id, emp_name FROM emp;
    
ROLLBACK;
    
/*
    <INSERT ALL>
        - 하나의 쿼리를 이용하여 두개 이상의 테이블에 INSERT
        INSERT ALL을 이용하여 여러개 테이블에 한번에 데이터를 삽입
        
        [표현법]
        1) INSERT ALL
            INTO 테이블명1[(컬럼, 컬럼,...)] VALUES(값,값...)
            INTO 테이블명2[(컬럼, 컬럼,...)] VALUES(값,값...)
            서브쿼리;
*/
-- emp테이블의 구조를 복사하여 테이블을 생성
-- emp_dept : id, name, code, date
-- emp_manager : id, name, manager_id

CREATE TABLE emp_dept 
AS  SELECT emp_id, emp_name, dept_code, dept_title, hire_date
    FROM emp
    JOIN dept ON(dept_code=dept_id)
    WHERE 1<0;
    
CREATE TABLE emp_manager 
AS  SELECT emp_id, emp_name, manager_id , job_code, job_name
    FROM emp
    JOIN job USING(job_code)
    WHERE 1<0;
    
INSERT ALL
    INTO emp_dept VALUES (emp_id, emp_name, dept_code,dept_title ,hire_date)
    INTO emp_manager VALUES (emp_id, emp_name, manager_id, job_code, job_name)
    SELECT emp_id, emp_name, dept_code, dept_title, hire_date, manager_id, job_code, job_name
    FROM emp
    LEFT JOIN dept ON(dept_code=dept_id)
    JOIN job USING(job_code);
    
SELECT * FROM emp_dept;
SELECT * FROM emp_manager;

SELECT * 
FROM emp_dept A ,emp_manager B
WHERE A.emp_id = B.emp_id;

DROP TABLE emp_dept;
DROP TABLE emp_manager;

DELETE emp_dept;
DELETE emp_manager;

INSERT ALL
WHEN DEPT_CODE = 'D1' THEN
    INTO emp_dept VALUES (emp_id, emp_name, dept_code,dept_title ,hire_date)
WHEN job_code = 'J1' THEN
    INTO emp_manager VALUES (emp_id, emp_name, manager_id, job_code, job_name)
    SELECT emp_id, emp_name, dept_code, dept_title, hire_date, manager_id, job_code, job_name
    FROM emp
    LEFT JOIN dept ON(dept_code=dept_id)
    JOIN job USING(job_code);
    
/*
    2) INSERT ALL
        WHEN 조건1 THEN
            INTO 테이블명1[(컬럼, 컬럼, ...)] VALUES(값, 값,...)
        WHEN 조건2 THEN
            INTO 테이블명2[(컬럼, 컬럼, ...)] VALUES(값, 값,...)
        서브쿼리;
*/
-- emp 테이블의 입사일 기준으로
-- emp_id, emp_name, hire_date, salary
-- 2000/01/01 이전 입사 emp_old
-- 2000/01/01 이후 입사 emp_new

CREATE TABLE emp_old
AS  SELECT emp_id, emp_name, hire_date, salary
    FROM emp
    WHERE 1=2;
CREATE TABLE emp_new
AS  SELECT emp_id, emp_name, hire_date, salary
    FROM emp
    WHERE 1=2;
    
INSERT ALL
    WHEN hire_date<'2000/01/01' THEN
        INTO emp_old VALUES(emp_id, emp_name, hire_date, salary)
    WHEN hire_date>='2000/01/01' THEN
        INTO emp_new VALUES(emp_id, emp_name, hire_date, salary)
    SELECT emp_id, emp_name, hire_date, salary
    FROM emp;
    
DROP TABLE emp_old;
DROP TABLE emp_new;
DROP TABLE emp_copy;
DROP TABLE emp_info;
DROP TABLE emp_dept;
DROP TABLE emp_manager;

/*
    <UPDATE>
        - 테이블에 기록된 데이터를 수정하는 구문
        
        [표현법]
            UPDATE 테이블명
            SET 컬럼명 = 변경하려는 값,
                컬럼명 = 변경하려는 값,
                ...
                [WHERE 조건];
        - SET절에서 여러개의 컬럼을 콤마(,)로 나열해서 값을 동시에 변경할 수 있다
        - WHERE절을 생략하면 모든 행의 데이터가 변경된다.
*/
CREATE TABLE dept_copy
AS SELECT * FROM dept;
-- DEPT_COPY테이블에서 dept_id가 'D9'인 부서명을 '전략기획팀'으로 수정
UPDATE dept_copy
SET dept_title = '전략기획팀'
WHERE dept_id = 'D9';

-- emp_salary 테이블에서 노옹철 사원의 급여를 1000000원으로 변경
CREATE TABLE emp_salary 
AS SELECT * FROM emp;

UPDATE emp_salary
SET salary = 1000000
WHERE emp_name = '노옹철';

-- 모든 사원의 급여를 기존 급여에서 10프로 인상한 급액(기존 급여 * 1.1)으로 변경
UPDATE emp_salary 
SET salary=salary*1.1;

SELECT salary 인상급여, (SELECT salary FROM emp WHERE emp_id = emp_salary.emp_id)
FROM emp_salary;

-- 참조키생성
-- emp 테이블에 참조키 추가, job_code, dept_code
ALTER TABLE emp ADD CONSTRAINT emp_dept_code_fk
    FOREIGN KEY(dept_code) REFERENCES DEPT(dept_id);
ALTER TABLE emp ADD CONSTRAINT emp_job_code_fr
    FOREIGN KEY(job_code) REFERENCES JOB(job_code);
    
-- 노옹철 사원의 부서코드를 D0으로 업데이트
-- FK삽입제한 - 업데이트시에도 적용
UPDATE emp
SET dept_code = 'D0'
WHERE emp_name = '노옹철';
-- PK제약조건
UPDATE emp SET emp_id = NULL;

-- 방명수 사원의 급여와 보너스를 유재식 사원과 동일하게 변경 (3400000, 0.2)
-- emp_salary
-- 1) 유재식 사원의 급여와 보너스 조회
SELECT salary, bonus
FROM emp_salary
WHERE emp_name = '유재식';
-- 2) 단일행 서브쿼리를 각각의 컬럼에 적용
UPDATE emp_salary
SET salary= (SELECT salary
    FROM emp_salary
    WHERE emp_name = '유재식' ),
    bonus= (SELECT bonus
    FROM emp_salary
    WHERE emp_name = '유재식' )
WHERE emp_name = '방명수';
-- 3) 다중열 서브쿼리를 사용하여 한번에 적용
UPDATE emp_salary
SET (salary, bonus) = (SELECT salary, bonus
    FROM emp_salary
    WHERE emp_name = '유재식' )
WHERE emp_name = '방명수';

-- emp_salary 노옹철, 전형돈, 정중하, 하동운 사원의 급여와 부서코드를 유재식 사원과 동일하게 변경
UPDATE emp_salary
SET (salary, dept_code) = (SELECT salary, dept_code
    FROM emp_salary
    WHERE emp_name = '유재식' )
WHERE emp_name IN ('노옹철', '전형돈','정중하', '하동운' );

SELECT * FROM emp_salary WHERE emp_name IN ('노옹철', '전형돈','정중하', '하동운' );

-- emp_salary 테이블에서 아시아 지역에 근무하는 직원들의 보너스를 0.3으로 변경
-- 1) 아시아 지역에 근무하는 사원들 조회
SELECT emp_name, local_name
FROM emp
LEFT JOIN dept ON(dept_code=dept_id)
LEFT JOIN location ON(location_id=local_code)
WHERE local_name LIKE 'ASIA%';

UPDATE emp_salary
SET bonus = 0.3
WHERE emp_name IN (
    SELECT emp_name
    FROM emp_salary
    LEFT JOIN dept ON(dept_code=dept_id)
    LEFT JOIN location ON(location_id=local_code)
    WHERE local_name LIKE 'ASIA%'
);

SELECT emp_name, bonus, local_name
FROM emp_salary
LEFT JOIN dept ON(dept_code=dept_id)
LEFT JOIN location ON(location_id=local_code);

/*
<DELETE>
    - 테이블에 기록 된 데이터를 삭제하는 구문(행 단위로 삭제)
    
    [표현법]
        DELETE [FROM] 테이블명
        [WHERE] 조건식;
        
    - WHERE절을 제시하지 않으면 전체 행이 삭제됨 
    
<TRUNCATE>
    - 테이블의 전체 행을 삭제할 때 사용하는 구문으로 DELETE보다 수행 속도가 더 빠름
    - 별도의 조건 제시가 불가능하고 ROLLBACK이 불가능하다
    
    [표현법]
        TRUNCATE TABLE 테이블명;
*/
-- emp_salary 공유 사원의 데이터 지우기
DELETE emp_salary WHERE emp_name='임시환';
ROLLBACK;
-- 외래키 제약조건의 삭제 제약
-- 자식테이블에서 사용중인 코드는 삭제 불가
DELETE FROM dept WHERE dept_id='D1';

CREATE TABLE emp_copy
AS SELECT * FROM emp WHERE 1=2;

-- emp테이블을 복사해서 emp_m01 테이블 생성
-- 모든 사원
CREATE TABLE emp_m01 AS SELECT * FROM emp;
-- emp 테이블을 복사해서 emp_m02 테이블 생성
-- 직급코드가 j4
CREATE TABLE emp_m02 
AS
    SELECT * 
    FROM emp
    WHERE job_code = 'J4';
-- INSERT 신규사원 추가 UPDATE SALARY = 0
DESC emp_m02;
INSERT INTO emp_m02 VALUES(999, '공유', '888888-1888888', 'AAA@BBB.com', '0102223333', 'D1', 'J1', 5000000, 100, 200, SYSDATE,'',N);
UPDATE emp_m02
SET salary=0;
SELECT * FROM emp_m02;
commit;

/*
<MERGE>
    - 구조가 같은 두개의 테이블을 하나의 테이블로 합치는 기능
    - 두 테이블에서 지정하는 조건의 값이 존재하면 UPDATE 존재하지 않으면 INSERT
*/
MERGE INTO emp_m01
-- 조건 : 사원번호가 존재하는지 확인
USING emp_m02 ON(emp_m01.emp_id = emp_m02.emp_id)
WHEN MATCHED THEN UPDATE SET
-- 일치하면 업데이트
    emp_m01.emp_name = emp_m02.emp_name, 
    emp_m01.emp_no = emp_m02.emp_no, 
    emp_m01.email = emp_m02.email, 
    emp_m01.phone = emp_m02.phone,
    emp_m01.salary = emp_m02.salary
WHEN NOT MATCHED THEN
-- 일치하지 않으면 인서트
    INSERT VALUES(emp_m02.emp_id, emp_m02.emp_name, emp_m02.emp_no, emp_m02.email, emp_m02.phone, 
        emp_m02.dept_code, emp_m02.job_code, emp_m02.salary, emp_m02.bonus, emp_m02.manager_id, emp_m02.hire_date,emp_m02.ent_date, emp_m02.ent_yn);
        
CREATE TABLE emp1
AS SELECT emp_id, emp_name, salary, bonus FROM emp;

CREATE TABLE emp2
AS SELECT emp_id, emp_name, salary, bonus FROM emp WHERE 1=2;

MERGE INTO emp1
USING emp2 ON(emp1.emp_id = emp2.emp_id)
WHEN MATCHED THEN UPDATE SET
    emp1.emp_name = emp2.emp_name,
    emp1.salary = emp2.salary,
    emp1.bonus = emp2.bonus
WHEN NOT MATCHED THEN
    INSERT VALUES(emp2.emp_id, emp2.emp_name, emp2.salary, emp2.bonus);
    
SELECT * FROM emp1;