SQL DML 실습 코드

참고도서: 모두의 SQL(길벗) 8장 http://www.yes24.com/Product/Goods/64434562?OzSrank=1

모두의 SQL

 

DEPARTMENT 테이블의 DEPARTMENT_NAME이 'Sample_Dept'인 행을 삽입/갱신/삭제하는 과정

---- 모두의 SQL 8장 DML: 데이터의 삽입/갱신/삭제 ----
--- 1) INSERT: 행 삽입하기
-- 예제 8-1
INSERT INTO DEPARTMENTS(DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
VALUES (271, 'Sample_Dept', 200, 1700);

-- 예제 8-2
INSERT INTO DEPARTMENTS
VALUES (272, 'Sample_Dept', 200, 1700);

commit;   -- 커밋하기 전까지 실행한 모든 DML은 데이터베이스에 반영되지 않음

--- 2) UPDATE: 행 갱신하기
-- 예제 8-3: 'Sample_Dept' 부서의 ID를 업데이트
UPDATE DEPARTMENTS SET MANAGER_ID = 201, LOCATION_ID = 1800
WHERE DEPARTMENT_NAME = 'Sample_Dept';

SELECT * FROM DEPARTMENTS;

COMMIT;

/* -- 예제 8-4: 서브쿼리를 사용하여 대량의 데이터를 변경
UPDATE DEPARTMENTS
SET (MANAGER_ID, LOCATION_ID) = (SELECT MANAGER_ID, LOCATION_ID
                                 FROM DEPARTMENTS
                                 WHERE DEPARTMENT_ID = 40)
WHERE DEPARTMENT_NAME = 'Sample_Dept'; */

--- 3) DELETE: 행 삭제하기 (대부분 강제로 막혀있음)
-- 예제 8-5
DELETE FROM DEPARTMENTS
WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID
                        FROM DEPARTMENTS
                        WHERE DEPARTMENT_NAME = 'Sample_Dept');
Commit;

SELECT * FROM DEPARTMENTS;