[Database] DML ( Data Manipulation Language )

[Database] 서브쿼리 문제풀이

---

데이터 조작어 ( DML, Data Manipulation Language )

데이터베이스 내의 데이터를 조작하는 데 사용되는 SQL의 하위집합이다.
DML문은 데이터베이스에서 데이터의 삽입, 업데이트, 삭제를 수행하는 데 사용한다.

DML 문
● INSERT 
● UPDATE
● DELETE

DML 구문의 실행을 완전히 보장할 필요가 있어야한다. 이를 위해 트랜잭션이 필요로 하는데,
작업의 논리적인 단위 형태인 DML문장의 모음을 트랜잭션(Transaction)이라고 한다.

INSERT 

새로운 행을 데이터베이스에 추가하는 구문으로 아래 그림과 같이 가장 마지막 행에 데이터를 추가한다.

작성방법

• 각각의 열에 대한 값을 포함하는 새로운 행을 삽입한다.
• 테이블에 있는 열의 디폴트 순서로 값을 나열한다.
• INSERT 절에서 열을 선택적으로 나열한다.
• 문자와 날짜 값은 단일 따옴표 내에 둔다.

INSERT INTO DEPTS VALUES(99, 'HELLO', 'Y', SYSDATE, 3.14, 'HELLO WORLD, HI~~');

▶  INSERT 문에서 아래와 같이 서브쿼리를 활용하여 필요한 구문만 추가할 수 있다

---

UPDATE 문

● 기존의 행을 갱신한다. 즉, 기존의 데이터를 새로이 변경할 데이터로 수정한다.
● 필요하다면 하나 이상의 행을 갱신할 수 있다.

UPDATE DEPTS SET MANAGER_ID = 100 WHERE MANAGER_ID IS NULL;

▶ INSERT 문과 마찬가지로, UPDATE 문에서도 서브쿼리를 활용하여, 내용을 갱신할 수 있다.

---

DELETE 문

● DELETE문장을 사용하여 테이블로 부터 기존의 행을 제거할 수 있다.
● 참조 무결성 제약 조건을 주의해야 한다.
   ▶ 다른 테이블에서 참조되고 있는 레코드가 존재할 경우를 주의해야한다.

주의할 점!
DELETE문을 실행하기 전에는 반드시 다시 한 번 확인하는 습관을 가져야한다.
그렇지않으면, 실수로 오입력한 데이터가 삭제될 우려가 있습니다.

DELETE FROM DEPTS WHERE DEPARTMENT_NAME = 'NOC';

---

MERGE 문

MERGE문을 사용하면 변경할 테이블에 데이터가 존재하는지 체크하고, UPDATE, DELETE, INSERT를 한 번에 작업이 가능하다.
※ MERGE문을 사용하지 않을 경우 해당 조건으로 테이블을 SELECT 한 후 IF 조건을 사용하여 UPDATE나 INSERT로
    분기하는 로직을 작성해야 하는 번거로움이 있다.

MERGE문의 경우 한 개의 테이블에서 UPDATE 또는 INSERT를 하는 경우 많이 사용하지만, 두 개의 테이블을 
비교하거나 서브 쿼리의 결과에 따라 UPDATE, INSERT 작업이 가능하다. 

MERGE문 구문 형식

MERGE INTO DEPTS D1
USING (SELECT * FROM DEPARTMENTS) D2
ON ( D1.DEPARTMENT_ID = D2.DEPARTMENT_ID )
WHEN MATCHED THEN
    UPDATE SET 
    D1.DEPARTMENT_NAME = D2.DEPARTMENT_NAME,
    D1.MANAGER_ID = D2.MANAGER_ID,
    D1.LOCATION_ID = D2.LOCATION_ID
WHEN NOT MATCHED THEN
    INSERT (D1.DEPARTMENT_NAME, D1.MANAGER_ID, D1.LOCATION_ID) VALUES 
    (D2.DEPARTMENT_NAME, D2.MANAGER_ID, D2.LOCATION_ID);

---

DML 문장 실습

• 연습문제 01 
  DEPTS테이블의 다음을 추가하세요

CREATE TABLE DEPTS AS (SELECT * FROM DEPARTMENTS);

INSERT INTO DEPTS (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
VALUES (280, '개발' , NULL, 1800);
INSERT INTO DEPTS (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
VALUES (290, '회계부', NULL, 1800);
INSERT INTO DEPTS (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
VALUES (300, '재정', 301, 1800);
INSERT INTO DEPTS (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
VALUES (310, '인사', 302, 1800);
INSERT INTO DEPTS (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
VALUES (320, '영업', 303, 1700);

---

• 연습문제 02
• DEPTS테이블의 데이터를 수정합니다

1. department_name 이 IT Support 인 데이터의 department_name을 IT bank로 변경
2. department_id가 290인 데이터의 manager_id를 301로 변경
3. department_name이 IT Helpdesk인 데이터의 부서명을 IT Help로 , 매니저아이디를 303으로, 지역아이디를 1800으로 변경하세요.

UPDATE DEPTS
SET DEPARTMENT_NAME = 'IT BANK'
WHERE DEPARTMENT_NAME = 'IT Support';

UPDATE DEPTS 
SET DEPARTMENT_ID = 301
WHERE DEPARTMENT_ID = 290;
 

UPDATE DEPTS
SET DEPARTMENT_NAME = 'IT Help', MANAGER_ID = 303, LOCATION_ID = 1800
WHERE DEPARTMENT_NAME = 'IT Helpdesk';

---

• 연습문제 03
• 삭제의 조건은 항상 primary key로 합니다, 여기서 primary key는 department_id라고 가정합니다.

1. 부서명 영업부를 삭제 하세요
2. 부서명 NOC를 삭제하세요

DELETE FROM DEPTS
WHERE DEPARTMENT_NAME = '영업';

DELETE FROM DEPTS
WHERE DEPARTMENT_NAME = 'NOC';

---

• 연습문재 04

1. Depts 사본테이블에서 department_id 가 200보다 큰 데이터를 삭제하세요.
2. Depts 사본테이블의 manager_id가 null이 아닌 데이터의 manager_id를 전부 100으로 변경하세요.
3. Departments테이블은 매번 수정이 일어나는 테이블이라고 가정하고
   Depts와 비교하여 일치하는 경우 Depts의 부서명, 매니저ID, 지역ID를 업데이트 하고
   새로유입된 데이터는 그대로 추가해주는 merge문을 작성하세요.

DELETE FROM DEPTS
WHERE DEPARTMENT_ID > 200;

UPDATE DEPTS
SET MANAGER_ID = 100
WHERE MANAGER_ID IS NULL;

MERGE INTO DEPTS D1
USING (SELECT * FROM DEPARTMENTS) D2
ON ( D1.DEPARTMENT_ID = D2.DEPARTMENT_ID )
WHEN MATCHED THEN
    UPDATE SET 
    D1.DEPARTMENT_NAME = D2.DEPARTMENT_NAME,
    D1.MANAGER_ID = D2.MANAGER_ID,
    D1.LOCATION_ID = D2.LOCATION_ID
WHEN NOT MATCHED THEN
    INSERT (D1.DEPARTMENT_NAME, D1.MANAGER_ID, D1.LOCATION_ID) VALUES 
    (D2.DEPARTMENT_NAME, D2.MANAGER_ID, D2.LOCATION_ID);

---

• 연습문제 05

1. jobs_it 사본 테이블을 생성하세요 (조건은 min_salary가 6000보다 큰 데이터만 복사합니다)
2. jobs_it 테이블에 다음 데이터를 추가하세요

    3. jobs테이블은 매번 수정이 일어나는 테이블이라고 가정하고
        jobs_it과 비교하여 min_salary컬럼이 0보다 큰 경우 기존의 데이터는
        min_salary, max_salary를 업데이트 하고 새로 유입된 데이터는 그대로 추가해주는 merge문을 작성하세요

CREATE TABLE JOBS_IT AS (SELECT * FROM JOBS WHERE MIN_SALARY > 6000);
SELECT * FROM JOBS_IT;

INSERT INTO JOBS_IT (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY)
VALUES ('IT_DEV', '아이티개발팀', 6000, 20000);
INSERT INTO JOBS_IT (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY)
VALUES ('NET_DEV', '네트워크개발팀', 5000, 20000);
INSERT INTO JOBS_IT (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY)
VALUES ('SEC_DEV', '보안개발팀', 6000, 19000);

SELECT * FROM JOBS_IT;

MERGE INTO JOBS_IT J1
USING (SELECT * FROM JOBS WHERE MIN_SALARY >= 0) J
ON ( J1.JOB_ID = J.JOB_ID )
WHEN MATCHED THEN
    UPDATE SET
    J1.MIN_SALARY = J.MIN_SALARY,
    J1.MAX_SALARY = J.MAX_SALARY
WHEN NOT MATCHED THEN
    INSERT VALUES 
    (J.JOB_ID, J.JOB_TITLE, J.MIN_SALARY, J.MAX_SALARY);