DB: DML(INSERT, UPDATE, DELETE) <- CUD, DECODE, CASE WHEN 사용법

// table 복사
-> CREATE TABLE DEPT01
    AS SELECT * FROM departments;

- INSERT문
->    INSERT INTO DEPT01 VALUES(300, 'Developer', 100, 10);
      insert into dept01 (DEPARTMENT_ID, DEPARTMENT_NAME)
            values(310, 'Innovation');

- UPDATE 문 / DELETE         <- 반드시 WHERE 문 추가
-> UPDATE 테이블명 SET 컬럼명 = 수정값, 컬럼명 = 수정값
    WHERE 수정대상

-> UPDATE DEPT01 SET department_name = 'IT Service'
    WHERE department_id =10;


퀴즈 > emp00테이블에서 salary 3000 이상인 대상자 salary 10% 인상 하라.

- DELETE 문
-> DELETE FROM 테이블명 WHERE 삭제 대상


  select * from dept01;
--퀴즈 > dept01 테이블에서 부서이름이 'IT Service' 값을 가진 row 를 삭제하기
delete from dept01
where DEPARTMENT_NAME = 'IT Service';

- DECODE() <- SWITCH 랑 유사

-> SELECT job_id, DECODE(job_id, 'SA_MAN', 'Sales Dept',
    'SH_CLERK', 'Sales Dept', 'Another')
    FROM employees;

- CASE WHEN
-> SELECT job_id,
    CASE job_id
    WHEN 'SA_MAN' THEN 'Sales Dept'
    WHEN 'SH_CLERK' THEN 'Sales Dept'
    ELSE 'Another2'
    END "CASE"
    FROM employees;


// DECODE 혹은 CASE WHEN 사용하여 출력
Decode( expr, search1, result1, search2, result2, …… , default_result)

퀴즈 hr> departments 부서테이블
    10 -> Accounting
    20 -> Sales
    30 -> Innovation
    Another

SELECT DEPARTMENT_ID,DEPARTMENT_NAME,
  DECODE(DEPARTMENT_ID, 10, 'sales_Dept',
                        20,'marketting_Dept',
                        'Another3') as ant3
  FROM DEPARTMENTS;

SELECT DEPARTMENT_id, DEPARTMENT_NAME,
  CASE DEPARTMENT_ID
  WHEN 10 THEN '10_DEPT'
  WHEN 20 THEN '20_DEPT'
  ELSE 'ANother4'
  END "ANT4"
  FROM DEPARTMENTS;