[Database] SQLD 자격검정 실전문제 개념 정리 6편(p109-p130)

이번 포스팅에서는 이전 포스팅에 이어서 SQL 자격검정 실전문제 책을 보며 익힌 내용들을 정리하는 시간을 가져보도록 하겠습니다.

Oracle에서 테이블 생성 방법

오라클에서 테이블을 만들기 위해서는 CREATE TABLE을 사용하여 컬럼명, 데이터 타입을 정의하여 테이블을 생성하면 됩니다.

CREATE TABLE emp 
( 
    empno       NUMBER(4)	NOT NULL,
    ename       VARCHAR2(10),
    job         VARCHAR2(9),
    mgr         NUMBER(4),
    hiredate    DATE,
    sal         NUMBER(7,2),
    comm        NUMBER(7,2),
    deptno      NUMBER(2)
);

PK 생성

ALTER TABLE [테이블명] ADD CONSTRAINT [PK명] PRIMARY KEY ([컬럼명,컬럼명...])

ALTER TABLE emp ADD CONSTRAINT emp_pk PRIMARY KEY (empno);

-- 테이블에 생성에 포함
CREATE TABLE emp 
( 
    empno       NUMBER(4)	NOT NULL,
    ename       VARCHAR2(10),
    job         VARCHAR2(9),
    mgr         NUMBER(4),
    hiredate    DATE,
    sal         NUMBER(7,2),
    comm        NUMBER(7,2),
    deptno      NUMBER(2),	
    CONSTRAINT emp_pk PRIMARY KEY (empno)
);

테이블 컬럼에 대한 정의 변경

Oracle

ALTER TABLE 테이블명 MODIFY (컬럼명1 데이터 유형 [DEFAULT 식] [NOT NULL], 컬럼명2 데이터 유형 ...);

SQL Server

ALTER TABLE 테이블명 ALTER (컬럼명1 데이터 유형 [DEFAULT 식] [NOT NULL], 컬럼명2 데이터 유형 ...);

REFERENCES TABLE(COLUMN) ON DELETE [CASCADE|SET NULL]

REFERENCES TABLE(COLUMN) ON DELETE [CASCADE|SET NULL]는 데이터베이스에서 외래 키를 설정할 때 사용되는 제약 조건입니다. 이 제약 조건은 참조 무결성을 유지하기 위해 부모 테이블의 행이 삭제될 때 자식 테이블에 있는 관련 행들이 어떻게 처리될지를 정의합니다.

ON DELETE CASCADE

부모 테이블의 행이 삭제될 때, 자식 테이블에서 해당 외래 키를 참조하는 모든 행도 자동으로 삭제됩니다.

CREATE TABLE parent (
    id INT PRIMARY KEY
);

CREATE TABLE child (
    id INT PRIMARY KEY,
    parent_id INT,
    FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE
);

ON DELETE SET NULL

부모 테이블의 행이 삭제될 때, 자식 테이블에서 해당 외래 키를 참조하는 컬럼이 NULL로 설정됩니다.

CREATE TABLE parent (
    id INT PRIMARY KEY
);

CREATE TABLE child (
    id INT PRIMARY KEY,
    parent_id INT,
    FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE SET NULL
);

MERGE 구문 해석

MERGE 구문은 SQL에서 주로 두 테이블을 병합할 때 사용됩니다.

MERGE 구문은 두 테이블을 합치는 것이 아니라, 특정 조건에 따라 업데이트, 삭제, 또는 삽입 작업을 수행하여 하나의 테이블을 다른 테이블의 데이터와 동기화하는 것입니다. 결과적으로, MERGE 작업 후 테이블 A는 B 테이블의 데이터를 반영하여 변경됩니다.

MERGE INTO T1 A
USING T2 B
ON (B.EMPNO = A.EMPNO)
WHEN MATCHED THEN
    UPDATE SET A.SAL = B.SAL - 500 WHERE A.JOB = 'CLERK'
    DELETE WHERE A.SAL < 2000
WHEN NOT MATCHED THEN
    INSERT (A.EMPNO, A.ENAME, A.JOB) VALUES (B.EMPNO, B.ENAME, B.JOB) WHERE B.JOB = 'CLERK';

1. T1 테이블의 EMPNO와 T2 테이블의 EMPNO가 일치하는 행이 있는 경우

   T1 테이블에서 JOB이 'CLERK'인 행의 SAL 값을 T2 테이블의 SAL 값에서 500을 뺀 값으로 업데이트합니다.

   업데이트 후, SAL 값이 2000보다 작은 행을 삭제합니다.

2. T1 테이블에 T2 테이블과 EMPNO가 일치하는 행이 없는 경우

   T2 테이블에서 JOB이 'CLERK'인 행을 T1 테이블에 삽입합니다.

Referential Action

1. Delete(/Modify) Action : Casecade, Set Null, Set Default, Restrict

1) Cascade : Master 삭제 시 Child 같이 삭제

2) Set Null : Master 삭제 시 Child 해당 필드 Null

3) Set Default : Master 삭제 시 Child Default 값으로 설정

4) Restrict : Child 테이블에 PK 값이 없는 경우만 Master 삭제 허용

5) No Action : 참조 무결성을 위반하는 삭제/수정 액션을 취하지 않음

2. Insert Action : Automatic, Set Null, Set Default, Dependent

1) Automatic : Master 테이블에 PK가 없는 경우 Master PK를 생성 후 Child 입력

2) Set Null : Master 테이블에 PK가 없는 경우 Child 외부키를 Null 값으로 처리

3) Set Default : Master 테이블에 PK가 없는 경우 Child 외부키를 지정된 기본값으로 입력

4) Dependent : Master 테이블에 PK가 존재할 경우에만 Child 입력 허용

5) No Action : 참조 무결성을 위반하는 입력 액션을 취하지 않음

DROP/TRUNCATE/DELETE

DROP	TRUNCATE	DELETE
DDL	DDL(일부 DML 성격 가짐)	DML
Rollback 불가능	Rollback 불가능	Commit 이전 Rollback 가능
Auto Commit	Auto Commit	사용자 Commit
테이블이 사용했던 Storage를 모두 Release	테이블이 사용했던 Storage 중 최초 테이블 생성시 할당된 Storage만 남기고 Release	데이터를 모두 Delete해도 사용했던 Storage는 Release되지 않음
테이블의 정의 자체를 완전히 삭제함	테이블을 최초 생성된 초기상태로 만듦	데이터만 삭제

데이터베이스 트랜잭션의 4가지 특성

특성	설명
원자성(Atomicity)	트랜잭션에서 정의된 연산들은 모두 성공적으로 실행되든지 아니면 전혀 실행되지 않은 상태로 남아 있어야 한다.
일관성(Consistency)	트랜잭션이 실행 되기 전의 데이터베이스 내용이 잘못되어 있지 않다면 트랜잭션이 실행된 이후에도 데이터베이스의 내용에 잘못이 있으면 안된다.
고립성(Isolation)	트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안된다.
지속성(Durability)	트랜잭션이 성공적으로 수행되면 그 트랜잭션이 갱신한 데이터베이스의 내용은 영구적으로 저장된다.

DB 종류별 COMMIT 수행

오라클에서는 DDL 문장 수행 후 자동으로 COMMIT을 수행합니다.

오라클에서는 DDL 문장의 수행은 내부적으로 트랜잭션을 종료시킵니다.

SQL Server에서는 DDL 문장 수행 후 자동으로 COMMIT을 수행하지 않습니다.

SQL Server에서는 CREATE TABLE 문장도 TRANSACTION의 범주에 포함됩니다.

저장점(SAVEPOINT)

저장점(SAVEPOINT)를 정의하면 롤백(ROLLBACK)할 때 트랜잭션에 포함된 전체 작업을 롤백하는 것이 아니라 현 시점에서 SAVEPOINT까지 트랜잭션의 일부만 롤백할 수 있습니다.

ORACLE

SAVEPOINT SVPT1;
...
ROLLBACK TO SVPT1;

SQL SERVER

SAVE TRANSACTION SVTR1;
...
ROLLBACK TRANSACTION SVTR1;

TOP N 구문(WITH TIES)

TOP N WITH TIES 쿼리는 특정 기준으로 상위 N개의 레코드를 선택할 때, N번째 레코드와 동일한 값을 가진 추가 레코드들도 함께 선택하기 위해 사용됩니다. 이는 동일한 순위의 레코드를 모두 포함시키는 기능입니다. 주로 Microsoft SQL Server에서 사용됩니다.

SELECT TOP 5 WITH TIES *
FROM employees
ORDER BY salary DESC;

해당 쿼리는 employees 테이블에서 salary 기준으로 상위 5명의 직원 정보를 선택합니다. 만약 5번째 직원과 동일한 급여를 가진 추가 직원들이 있다면, 그들도 결과에 포함됩니다.