[자격증]/SQLD

[2과목] 관리 구문

에디터 윤슬 2025. 3. 3. 15:02

제1절 DML

1. 테이블 생성 시 주의해야 할 사항으로 가장 적절한 것은?

  • 테이블명은 객체를 의미할 수 있는 적절한 이름을 사용하고 가능한 복수형을 권고한다.
  • 한 테이블 내에서는 칼럼명을 중복되게 지정할 수 있다.
  • 칼럼 뒤에 데이터 유형은 꼭 지정되어야 한다.
  • 벤더에서 사전에 정의한 예약어(Reserved Word)도 쓸 수 있다.
테이블 생성의 주의사항

1. 테이블명은 객체를 의미할 수 있는 적절한 이름을 사용한다. 가능한 단수형을 권고한다.

2. 테이블 명은 다른 테이블의 이름과 중복되지 않아야 한다.

3. 한 테이블 내에서는 칼럼명이 중복되게 지정될 수 없다.

4. 테이블 이름을 지정하고 각 칼럼들은 괄호 "()"로 묶어 지정한다.

5. 각 칼럼들은 콤마 ","로 구분되고, 테이블 생성문의 끝은 항상 세미콜론 ";"으로 끝난다.

6. 칼럼에 대해서는 다른 테이블까지 고려하여 데이터베이스 내에서는 일관성 있게 사용하는 것이 좋다.(데이터 표준화 관점)

7. 칼럼 뒤에 데이터 유형은 꼭 지정되어야 한다.

8. 테이블명과 칼럼명은 반드시 문자로 시작해야 하고, 벤더별로 길이에 대한 한계가 있다.

9. 벤더에서 사전에 정의한 예약어(Reserved word)는 쓸 수 없다.

10. A-Z, a-z, 0-9, _, $, # 문자만 혀용된다.

 

2. 아래 SQL에서 RepName 속성이 가지는 키 특성으로 가장 적절한 것은?

CREATE TABLE SALESREP(
SALESREPNO INT NOT NULL,
REPNAME CHAR(35) NOT NULL,
HIREDATE DATE NOT NULL,

CONSTRAINT SALESREPPK PRIMARY KEY (DALESREPNO),
CONSTRAINT SALESREPAK1 UNIQUE (REPNAME)
);
  • 기본키(Primary Key)
  • 외래키(Foreign Key)
  • 후보키(Candidate Key)
  • 대리키(Surrogate Key)
해설

주요 특징:
1. SALESREPNO:
• `PRIMARY KEY`로 설정되어 있습니다.
• 기본 키는 테이블의 각 행을 고유하게 식별하며, `NOT NULL` 제약조건을 포함합니다.

 

2. REPNAME:
• `NOT NULL` 제약조건이 설정되어 있어 반드시 값이 입력되어야 합니다.
• `UNIQUE` 제약조건이 설정되어 있어, 중복된 값을 가질 수 없습니다.
• 따라서 `REPNAME`은 후보 키(Candidate Key)로 동작합니다.

 

3. HIREDATE:
• 단순히 `NOT NULL` 제약조건만 설정되어 있으며, 고유성을 요구하지 않습니다.

 

키 특성 설명
1. 기본 키(Primary Key):
• 테이블의 각 행을 고유하게 식별하는 데 사용됩니다.
• 기본 키는 반드시 고유하고, NULL 값을 허용하지 않습니다.
• 예: `SALESREPNO`.

 

2. 후보 키(Candidate Key):
• 기본 키로 선택될 수 있는 후보 중 하나입니다.
• 후보 키는 고유성과 최소성을 만족하며, 테이블 내에서 중복되지 않는 값을 가집니다.
• 예: `REPNAME`은 `UNIQUE`와 `NOT NULL` 제약조건을 만족하므로 후보 키입니다.

 

3. 대체 키(Alternate Key):
• 후보 키 중에서 기본 키로 선택되지 않은 나머지 키를 의미합니다.
• 예: `REPNAME`은 후보 키이자 대체 키입니다.

 

4. 외래 키(Foreign Key):
• 다른 테이블의 기본 키를 참조하여 테이블 간 관계를 정의합니다.
• 현재 SQL에서는 외래 키가 정의되지 않았습니다.

 

RepName 속성의 특성
• `REPNAME`은 다음과 같은 이유로 후보 키(Candidate Key)입니다:
1. `UNIQUE`: 값이 중복될 수 없습니다.
2. `NOT NULL`: 반드시 값이 입력되어야 합니다.
3. 고유성과 최소성을 만족하므로 후보 키로 작동합니다.
• 또한, 기본 키(`SALESREPNO`)가 이미 정의되어 있으므로, `REPNAME`은 후보 키 중에서 기본 키로 선택되지 않은 대체 키(Alternate Key)로도 간주됩니다.

 

추가 학습 포인트
1. 후보 키와 대체 키의 관계:
• 모든 대체 키는 후보 키에 포함됩니다.
• 기본적으로 후보 키는 고유성과 최소성을 만족해야 합니다.

 

2. UNIQUE와 NOT NULL:
• UNIQUE 제약조건은 컬럼 값의 중복을 방지하며, NULL 값을 허용할 수 있습니다.
• 그러나 UNIQUE와 NOT NULL이 함께 사용되면 해당 컬럼은 고유성과 비어 있지 않은 값을 모두 만족해야 하므로 후보 키가 될 수 있습니다.

 

3. 기본 키와 외래 키의 차이:
• 기본 키는 테이블 내에서 각 행을 고유하게 식별하는 데 사용됩니다.
• 외래 키는 다른 테이블의 기본 키를 참조하여 테이블 간 관계를 정의합니다.

 

3. 관계형 데이터베이스에서 자식 테이블의 FK 데이터 생성시 부모 테이블에 PK가 없는 경우, 자식 테이블 데이터 입력을 허용하지 않는 참조동작(Referential Action)은?

  • CASCADE
  • RESTRICT
  • AUROMATIC
  • DEPENDENT
폴더 7

주어진 상황:
• 자식 테이블에서 외래 키(FK)를 통해 부모 테이블을 참조하려고 합니다.
• 부모 테이블에 기본 키(PK)가 없는 경우, 자식 테이블에 데이터를 입력할 수 없도록 설정하는 참조 동작(Referential Action)을 묻는 문제입니다.
참조 동작(Referential Action) 옵션 설명

 

1. CASCADE:
• 부모 테이블에서 데이터가 삭제되거나 수정될 때, 자식 테이블의 관련 데이터도 자동으로 삭제되거나 수정됩니다.
• 이 옵션은 부모-자식 관계를 동기화하는 데 사용되며, 부모 테이블에 PK가 없는 경우와는 관련이 없습니다.

 

2. RESTRICT:
• 부모 테이블에 관련된 데이터가 있는 경우, 부모 테이블의 데이터를 삭제하거나 수정하는 작업을 제한합니다.
• 그러나 이 옵션은 부모 테이블에 PK가 없는 경우를 처리하지 않습니다.

 

3. AUTOMATIC:
• 데이터베이스 표준 SQL에서는 `AUTOMATIC`이라는 참조 동작이 존재하지 않습니다.
• 이는 잘못된 옵션입니다.

 

4. DEPENDENT:
• `DEPENDENT` 옵션은 자식 테이블에서 외래 키(FK) 데이터를 생성하려고 할 때, 부모 테이블에 기본 키(PK)가 없으면 자식 테이블의 입력을 제한합니다.
• 즉, 자식 테이블의 입력 조건이 부모 테이블의 상태에 의존적입니다.

 


• 자식 테이블에서 FK 데이터 생성 시 부모 테이블에 PK가 없는 경우, 자식 테이블 데이터 입력을 허용하지 않는 참조 동작은:
DEPENDENT

 

추가 학습 포인트
1. 외래 키(FK)와 참조 무결성(Referential Integrity):
• 외래 키는 자식 테이블에서 부모 테이블의 기본 키(PK) 또는 고유 키(Unique Key)를 참조하여 관계를 정의합니다.
• 참조 무결성을 유지하기 위해, 자식 테이블의 외래 키 값은 반드시 부모 테이블의 기본 키 값과 일치해야 합니다.

 

2. 참조 동작(Referential Action)의 주요 옵션:
• CASCADE: 부모 데이터 변경 시 자식 데이터도 자동으로 변경.
• RESTRICT: 부모 데이터 변경을 제한.
• SET NULL: 부모 데이터 삭제 시 자식의 외래 키 값을 NULL로 설정.
• DEPENDENT: 부모 상태에 따라 자식 입력 제한.

 

3. PK와 FK 관계의 중요성:
• 부모-자식 관계를 정의하려면 부모 테이블에 반드시 기본 키(PK)나 고유 키(Unique Key)가 있어야 합니다.
• 이를 통해 참조 무결성을 유지할 수 있습니다.

 

 

제2절 TCL

1. 아래와 같은 상황에서 문제가 발생한 트랜잭션의 특성으로 가장 적절한 것은?

[Table A]

COL1 COL2
A 100
B 200

 

[트랜잭션]

시간 TX1 TX2
t1 update TableA set col2 = 200
where col1 = 'A';		  
t2   update TableA set col2 = 300
where col1 = 'A';
t3   commit;
t4 commit;  

 

[트랜잭션 수행결과]

COL1 COL2
A 300
B 200

 

  • 원자성(Atomicity)
  • 일관성(Consistency)
  • 고립성(Isolation)
  • 영속성(Durability)
해설

트랜잭션 흐름:
1. t1 (TX1):
• TX1이 `COL1 = 'A'`인 행의 `COL2` 값을 200으로 업데이트합니다.
• 이 시점에서 TX1은 아직 **커밋(commit)**하지 않았습니다.
2. t2 (TX2):
• TX2가 동일한 행(`COL1 = 'A'`)의 `COL2` 값을 300으로 업데이트합니다.
• TX1이 아직 커밋되지 않았음에도, TX2가 동일한 데이터를 수정했습니다.
3. t3 (TX2 Commit):
• TX2가 커밋되면서, `COL1 = 'A'`의 최종 값은 300으로 확정됩니다.
4. t4 (TX1 Commit):
• TX1이 뒤늦게 커밋되지만, TX2의 변경 사항(300)이 이미 반영된 상태입니다.

문제 상황

• TX1과 TX2가 동일한 데이터를 동시에 수정하려고 했으며, 이로 인해 트랜잭션 간의 충돌이 발생했습니다.
• 결과적으로, TX1이 의도한 변경(200)은 무시되었고, TX2의 변경(300)만 반영되었습니다.
• 이는 트랜잭션의 고립성(Isolation) 위반으로 인한 문제입니다.


트랜잭션 특성(ACID) 설명
1. 원자성(Atomicity):
• 트랜잭션 내 모든 작업이 성공하거나 모두 실패해야 함을 보장합니다.
• 예를 들어, 트랜잭션 도중 오류가 발생하면 모든 변경 사항이 롤백됩니다.
• 현재 문제는 원자성과 관련이 없습니다.

 

2. 일관성(Consistency):
• 트랜잭션 전후에 데이터베이스가 일관된 상태를 유지해야 함을 보장합니다.
• 일관성은 데이터 무결성과 관련된 특성이며, 현재 문제는 데이터 무결성 위반과는 관계가 없습니다.

 

3. 고립성(Isolation):
• 각 트랜잭션은 독립적으로 실행되어야 하며, 다른 트랜잭션의 영향을 받아서는 안 됩니다.
• 현재 상황에서는 TX1이 커밋되지 않은 상태에서 TX2가 동일한 데이터를 수정했으므로, 고립성이 위반되었습니다.
• 이로 인해 “더티 라이트(Dirty Write)” 문제가 발생했습니다.

 

4. 영속성(Durability):
• 트랜잭션이 커밋된 후에는 시스템 오류가 발생하더라도 변경 사항이 영구적으로 저장됨을 보장합니다.
• 현재 문제는 영속성과 관련이 없습니다.

 

추가 학습 포인트
1. 고립성(Isolation)과 더티 라이트(Dirty Write):
• 더티 라이트는 한 트랜잭션(TX1)이 아직 커밋되지 않은 데이터를 다른 트랜잭션(TX2)이 수정하는 경우 발생합니다.
• 이를 방지하기 위해 적절한 격리 수준(예: Serializable)을 설정해야 합니다.

 

2. 트랜잭션 격리 수준:
• Read Uncommitted: 커밋되지 않은 데이터를 읽을 수 있음(더티 리드 가능).
• Read Committed: 커밋된 데이터만 읽을 수 있음(더티 리드 방지).
• Repeatable Read: 동일한 쿼리에서 동일한 데이터를 읽음(팬텀 리드 방지).
• Serializable: 완전한 고립성을 보장하며, 가장 엄격하지만 성능 저하 가능성이 있음.

 

3. 해결 방법:
• `Serializable` 격리 수준을 사용하여 각 트랜잭션이 독립적으로 실행되도록 보장하거나,
• 적절한 잠금(Locking) 메커니즘을 통해 충돌을 방지해야 합니다.

 

2. DELETE와 TRUNCATE, DROP 명령어에 대해 비교한 설명으로 가장 적절하지 않은 것은?

  • 특정 테이블에 대하여 WHERE 조건절이 없는 DELETE 명령을 수행하면 DROP TABLE 명령을 수행했을 때와 똑같은 결과를 얻을 수 있다.
  • DROP 명령어는 테이블 정의 자체를 삭제하고, TRUNCATE 명령어는 테이블을 초기상태로 만든다.
  • TRUNCATE 명령어는 UNDO를 위한 데이터를 생성하지 않기 때문에 동일 데이터량 삭제 시 DELETE보다 빠르다.
  • DROP과 TRUNCATE는 Auto Commit되고, DELETE는 사용자 COMMIT으로 수행된다.
해설

주어진 SQL 명령어 설명
1. DELETE:
• 특정 조건에 따라 테이블의 데이터를 삭제합니다.
• 테이블 구조는 유지됩니다.
• 트랜잭션 로그에 각 행 삭제가 기록되므로 롤백이 가능합니다.
• 속도는 TRUNCATE와 DROP보다 느립니다.

 

2. TRUNCATE:
• 테이블의 모든 데이터를 삭제하지만, 테이블 구조는 유지됩니다.
• 트랜잭션 로그에 개별 행 삭제가 기록되지 않으므로 DELETE보다 빠릅니다.
• 롤백이 불가능하며, 테이블의 자동 증가 값(AUTO_INCREMENT)이 초기화됩니다.

 

3. DROP:
• 테이블의 데이터뿐만 아니라 테이블 구조 자체를 삭제합니다.
• 트랜잭션 로그에 기록되지 않으며, 복구가 불가능합니다.
• 테이블과 관련된 인덱스, 제약 조건 등도 함께 삭제됩니다.

 

각 선택지 분석
1. 특정 테이블에 대하여 WHERE 조건절이 없는 DELETE 명령을 수행하면 DROP TABLE 명령을 수행했을 때와 똑같은 결과를 얻을 수 있다.
• DELETE 명령은 데이터를 삭제하지만 테이블 구조는 유지됩니다.
• 반면, DROP 명령은 테이블 데이터와 구조를 모두 삭제합니다.
• 따라서 WHERE 조건절 없이 DELETE를 수행하더라도 DROP과 동일한 결과를 얻을 수 없습니다.
• 적절성: 부적절함.

 

2. DROP 명령어는 테이블 정의 자체를 삭제하고, TRUNCATE 명령어는 테이블을 초기 상태로 만든다.
• DROP은 테이블 정의(구조)와 데이터를 모두 삭제합니다.
• TRUNCATE는 데이터를 모두 삭제하지만, 테이블 구조는 유지됩니다.
• 이 설명은 정확합니다.
• 적절성: 적절함.

 

3. TRUNCATE 명령어는 UNDO를 위한 데이터를 생성하지 않기 때문에 동일 데이터량 삭제 시 DELETE보다 빠르다.
• TRUNCATE는 트랜잭션 로그에 개별 행 삭제 정보를 기록하지 않으므로 DELETE보다 빠릅니다.
• 이는 대량 데이터 삭제 시 TRUNCATE가 DELETE보다 효율적인 이유입니다.
• 적절성: 적절함.

 

4. DROP과 TRUNCATE는 Auto Commit되고, DELETE는 사용자 COMMIT으로 수행된다.
• DROP과 TRUNCATE는 DDL(데이터 정의 언어) 명령어로, 실행 즉시 Auto Commit이 발생합니다.
• DELETE는 DML(데이터 조작 언어) 명령어로, 사용자가 COMMIT 또는 ROLLBACK을 수행해야 변경 사항이 확정됩니다.
• 이 설명은 정확합니다.
• 적절성: 적절함.

 

• 가장 적절하지 않은 설명은:
1번: 특정 테이블에 대하여 WHERE 조건절이 없는 DELETE 명령을 수행하면 DROP TABLE 명령을 수행했을 때와 똑같은 결과를 얻을 수 있다.

 

3. 데이터베이스 트랜잭션에 대한 설명으로 가장 적절하지 않은 것은?

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

ACID 특성 설명
1. 원자성 (Atomicity)
• 트랜잭션은 “모두 성공(All)” 또는 “모두 실패(Nothing)“의 원칙을 따릅니다.
• 트랜잭션 내의 작업이 일부만 실행되거나 중간 상태로 남아서는 안 됩니다.
• 예: 은행 계좌 이체에서 송금이 성공했지만 수취가 실패하면 전체 트랜잭션이 롤백됩니다.
2. 일관성 (Consistency)
• 트랜잭션이 실행되기 전후에 데이터베이스는 항상 유효한 상태를 유지해야 합니다.
• 데이터 무결성과 비즈니스 규칙을 준수해야 하며, 잘못된 데이터가 기록되지 않아야 합니다.
3. 고립성 (Isolation)
• 여러 트랜잭션이 동시에 실행될 때, 서로 간섭하지 않도록 보장합니다.
• 한 트랜잭션의 중간 결과는 다른 트랜잭션에서 볼 수 없습니다.
• 고립성 수준은 다양한 격리 수준(Read Uncommitted, Read Committed 등)에 따라 달라질 수 있습니다.
4. 지속성 (Durability)
• 트랜잭션이 커밋된 후에는 시스템 오류나 장애가 발생하더라도 변경 사항이 영구적으로 유지됩니다.
• 이는 로그 파일이나 백업 메커니즘을 통해 보장됩니다.
각 선택지 분석
1. “원자성: 트랜잭션에서 정의된 연산들은 모두 성공적으로 실행되든지 아니면 전혀 실행되지 않은 상태로 남아 있어야 한다.”
• 분석:
• 원자성의 정의를 정확히 설명하고 있습니다.
• 트랜잭션 내 모든 작업은 하나의 단위로 처리되며, 일부만 성공하거나 실패하는 일이 없어야 합니다.
• 적절성: 적절함.
2. “일관성: 트랜잭션이 실행 되기 전의 데이터베이스 내용이 잘못 되어 있지 않다면 트랜잭션이 실행된 이후에도 데이터베이스의 내용에 잘못이 있으면 안 된다.”
• 분석:
• 일관성은 데이터 무결성을 유지하는 것을 의미합니다.
• 그러나 이 설명에서 “트랜잭션 실행 후에도 데이터베이스 내용에 잘못이 있으면 안 된다”는 표현은 부정확합니다.
• 일관성은 트랜잭션 자체가 데이터 무결성을 유지하도록 보장하는 것이며, 외부 요인으로 인해 데이터베이스가 잘못될 가능성을 다루지는 않습니다.
• 적절성: 부적절함.
3. “고립성: 트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안 된다.”
• 분석:
• 고립성의 정의를 정확히 설명하고 있습니다.
• 각 트랜잭션은 독립적으로 실행되어야 하며, 다른 트랜잭션과 간섭하지 않아야 합니다.
• 적절성: 적절함.
4. “지속성: 트랜잭션을 취소하더라도 트랜잭션이 갱신한 내용이 데이터베이스에 저장되어야 한다.”
• 분석:
• 지속성은 “트랜잭션이 커밋된 후”에 변경 사항이 영구적으로 저장되는 것을 보장합니다.
• 그러나 설명에서 “트랜잭션을 취소하더라도”라는 표현은 지속성과 맞지 않습니다.
• 취소(ROLLBACK)된 경우에는 변경 사항이 저장되지 않아야 합니다.

 

BEGIN TRAN

BEGIN TRANSACTION으로 트랜잭션을 시작하고 COMMIT TRANSACTION(TRANSACTION은 생략 가능) 또는 ROLLBACK TRANSACTION(TRANSACTION은 생략 가능)으로 트랜잭션을 종료한다. ROLLBACK 구문을 만나면 최초의 BEGIN TRANSACTION 시점까지 모두 ROLLBACK이 수행된다.

저장점(SAVEPOINT)

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

TOP() 예제

사원 테이블에서 급여가 높은 2명을 내림차순으로 출력하는데 같은 급여를 받는 사원이 있으면 같이 출력한다.

 

SELECT TOP(2) WITH TIES ENAME, SAL

FROM EMP

ORDER BY SAL DESC;

 

 

제3절 DDL

1. 아래 데이터 모델과 같은 테이블 및 PK 제약조건을 생성하는 DDL 문장으로 가장 적절한 것은?(단, DBMS는 오라클로 가정)

[Product()]

# PROD_ID VARCHAR2(10)

* PROD_NM VARCHAR2(100)

* REG_DT DATE

0 REGR_NO NUMBER(10)

1. 
CREATE TABLE PRODUCT
(PROD_ID VARCHAR2(10),
PROD_NM VARCHAR2(100),
REG_DT DATE,
REGR_NO NUMBER(10) );
ALTER TABLE PRODUCT ADD CONSTRAINT PRODUCT_PK PRIMARY_KEY (PROD_ID);

	•	분석:
	•	테이블 생성 후, ALTER TABLE 문을 사용하여 기본 키를 추가합니다.
	•	그러나 `PROD_NM`과 `REG_DT`에 대한 NOT NULL 제약조건이 누락되었습니다.
    
2. 
CREATE TABLE PRODUCT
(PROD_ID VARCHAR2(10) NOT NULL,
PROD_NM VARCHAR2(100) NOT NULL,
REG_DT DATE NOT NULL,
REGR_NO NUMBER(10) NULL,
ADD CONSTRAINT PRIMARY KEY (PROD_ID) );

	•	분석:
	•	`ADD CONSTRAINT PRIMARY KEY (PROD_ID)` 구문이 잘못되었습니다.
	•	오라클에서는 테이블 정의 내부에서 제약조건을 추가할 때는 `CONSTRAINT constraint_name PRIMARY KEY` 형식으로 작성해야 합니다.
	•	또한, `ADD CONSTRAINT`는 테이블 정의 외부에서 사용됩니다.
    
3.
CREATE TABLE PRODUCT
(PROD_ID VARCHAR2(10) NOT NULL,
PROD_NM VARCHAR2(100) NOT NULL,
REG_DT DATE NOT NULL,
REGR_NO NUMBER(10),
CONSTRAINT PRODUCT_PK PRIMARY KEY (PROD_ID) );

	•	분석:
	•	테이블 정의 내에서 기본 키 제약조건을 정확히 설정했습니다.
	•	`PROD_ID`는 기본 키로 설정되었으며, `NOT NULL` 제약조건도 포함되었습니다.
	•	`PROD_NM`과 `REG_DT`에 대한 NOT NULL 제약조건도 명시적으로 설정되었습니다.
테이블 컬럼 정의 변경

[Oracle]

ALTER TABLE 테이블명

MODIFY (칼럼명1 데이터 유형 [DEFAUT 식] [NOT NULL], 칼럼명2 데이터 유형...);

 

[SQL Server]

ALTER TABLE 테이블명

ALTER BOLUMN 칼럼명 데이터 유형 [DEFAULT 식] [NOT NULL];

NULL

NULL(ASCII 코드 00번)은 공백(BLANK, ASCII 코드 32번)이나 숫자 0(ZERO, ASCII 48)과는 전혀 다른 값이며, 조건에 맞는 데이터가 없을 때의 공집합과도 다르다. 'NULL'은 '아직 정의되지 않은 미지의 값'이거나 '현재 데이터를 입력하지 못하는 경우'를 의미한다.

 

제4절 DCL

1. 아래를 참고할 때 오류가 발생하는 SQL은?

[BOARD]

BOARD_ID: VARCHAR2(10) NOT NULL

BOARD_NM: VARCHAR2(50) NOT NULL

USE_YN: VARCHAR2(1) NOT NULL

REG_DATE: DATE NOT NULL

BOARD_DESC: VARCHAR2(100) NULL

 

  • INSET INTO BOARD VALUES (1, 'Q&A', 'Y', SYSDATE, 'Q&A 게시판');
  • INSER INTO BOARD (BOARD_ID, BOARD_NM, USE_YN, BOARD_DESC) VALUES ('100', 'FAQ', 'Y', 'FAQ 게시판');
  • UPDATE BOARD SET USE_YN = 'N' WHERE BOARD_ID = '1';
  • UPDATE BOARD SET BOARD_ID = 200 WHERE BOARD_ID = '100';
해설

1. `INSERT INTO BOARD VALUES (1, 'Q&A', 'Y', SYSDATE, 'Q&A 게시판');`
• `BOARD_ID`의 데이터 타입은 `VARCHAR2(10)`인데, 숫자형 값 `1`이 입력되었습니다.
• 오라클에서는 데이터 타입이 일치하지 않으면 오류가 발생합니다.
• 오류: 데이터 타입 불일치로 인해 오류 발생.

 

2. `INSERT INTO BOARD (BOARD_ID, BOARD_NM, USE_YN, BOARD_DESC) VALUES ('100', 'FAQ', 'Y', 'FAQ 게시판');`
• `REG_DATE` 컬럼은 `NOT NULL` 제약 조건이 있지만, 해당 컬럼에 값이 입력되지 않았습니다.
• 오라클에서는 `NOT NULL` 제약 조건이 있는 컬럼에 값을 입력하지 않으면 오류가 발생합니다.
• 오류: `REG_DATE`에 값이 입력되지 않아 오류 발생.

 

3. `UPDATE BOARD SET USE_YN = 'N' WHERE BOARD_ID = '1';`
• 이 문장은 단순히 `USE_YN` 값을 `'N'`으로 업데이트합니다.
• 모든 제약 조건을 만족하므로 오류가 발생하지 않습니다.

4. `UPDATE BOARD SET BOARD_ID = 200 WHERE BOARD_ID = '100';`
• `BOARD_ID`의 데이터 타입은 `VARCHAR2(10)`인데, 숫자형 값 `200`이 입력되었습니다.
• 오라클에서는 데이터 타입이 일치하지 않으면 오류가 발생합니다.
• 오류: 데이터 타입 불일치로 인해 오류 발생.

 

정답
다음 SQL 문장에서 오류가 발생합니다:
1. `INSERT INTO BOARD VALUES (1, 'Q&A', 'Y', SYSDATE, 'Q&A 게시판');`
2. `INSERT INTO BOARD (BOARD_ID, BOARD_NM, USE_YN, BOARD_DESC) VALUES ('100', 'FAQ', 'Y', 'FAQ 게시판');`
3. `UPDATE BOARD SET BOARD_ID = 200 WHERE BOARD_ID = '100';`

 

추가 학습 포인트
1. 데이터 타입 확인:
• 테이블 정의에서 각 컬럼의 데이터 타입을 확인하고, 입력 값이 해당 데이터 타입과 일치하도록 해야 합니다.
2. NOT NULL 제약 조건:
• `NOT NULL` 제약 조건이 있는 컬럼에는 반드시 값을 입력해야 합니다.
3. SQL INSERT와 UPDATE 사용 시 주의사항:
• INSERT 시에는 모든 필수 컬럼에 값을 제공해야 하며,
• UPDATE 시에는 데이터 타입과 제약 조건을 준수해야 합니다.
4. 오류 메시지 예시:
• 데이터 타입 불일치: `ORA-01722: invalid number`
• NOT NULL 위반: `ORA-01400: cannot insert NULL into ("TABLE"."COLUMN")`

 

2. 아래를 참고할 때 오류가 발생하는 INSERT문으로 가장 적절한 것은?

CREATE TABLE 주문 (
	주문번호 NUMBER PRIMARY KEY,
    주문일자 DATE NOT NULL,
    회원번호 NUMBER,
    주문상태코드 VARCHAR2(3) DEFAULT '000'
);
  • INSERT INTO 주문(주문번호, 주문일자, 회원번호, 주문상태코드) VALUES(1, SYSDATE, 1900123, '002');
  • INSERT INTO 주문(주문번호, 주문일자, 회원번호, 주문상태코드) VALUES(2, '20190301', 1900124, '001');
  • INSERT INTO 주문(주문번호, 주문일자, 회원번호, 주문상태코드) VALUES(3, SYSDATE-1, 1900125, '001');
  • INSERT INTO 주문(주문번호, 주문일자, 회원번호, 주문상태코드) VALUES(4, 20190302, 1900126, '001');
해설

주요 제약 조건:
1. 주문번호:
• `NUMBER` 데이터 타입이며, 기본 키(PK)로 설정되어 중복 불가 및 `NULL` 값 허용되지 않음.
2. 주문일자:
• `DATE` 데이터 타입이며, `NOT NULL` 제약 조건이 있어 반드시 값이 입력되어야 함.
3. 회원번호:
• `NUMBER` 데이터 타입이며, NULL 값 허용.
4. 주문상태코드:
• `VARCHAR2(3)` 데이터 타입이며, 기본값은 `'000'`.
주어진 INSERT 문
1. INSERT INTO 주문(주문번호, 주문일자, 회원번호, 주문상태코드) VALUES(1, SYSDATE, 1900123, ‘002’);
• 분석:
• 모든 컬럼에 적합한 값이 입력되었습니다.
• `SYSDATE`는 현재 날짜를 반환하며, `DATE` 데이터 타입과 일치합니다.
• 오류 없음.
2. INSERT INTO 주문(주문번호, 주문일자, 회원번호, 주문상태코드) VALUES(2, ‘20190301’, 1900124, ‘001’);
• 분석:
• `'20190301'`은 문자열로 입력되었지만, `DATE` 데이터 타입에 맞지 않습니다.
• 오라클에서는 문자열을 날짜로 변환하려면 `TO_DATE` 함수를 사용해야 합니다.
• 예: `TO_DATE('20190301', 'YYYYMMDD')`.
• 오류 발생: 날짜 형식 오류(`ORA-01843: not a valid month`).
3. INSERT INTO 주문(주문번호, 주문일자, 회원번호, 주문상태코드) VALUES(3, SYSDATE-1, 1900125, ‘001’);
• 분석:
• `SYSDATE-1`은 현재 날짜에서 하루 전 날짜를 계산하며, 유효한 `DATE` 값입니다.
• 모든 컬럼에 적합한 값이 입력되었습니다.
• 오류 없음.
4. INSERT INTO 주문(주문번호, 주문일자, 회원번호, 주문상태코드) VALUES(4, 20190302, 1900126, ‘001’);
• 분석:
• `20190302`는 숫자형으로 입력되었으며, 이는 `DATE` 데이터 타입과 일치하지 않습니다.
• 숫자를 날짜로 변환하려면 역시 `TO_DATE` 함수를 사용해야 합니다.
• 예: `TO_DATE('20190302', 'YYYYMMDD')`.
• 오류 발생: 날짜 형식 오류(`ORA-01861: literal does not match format string`).

 

3. 아래와 같은 테이블에서 Department의 did가 '1'인 행이 삭제될 때, Employee의 did가 '1'인 행도 같이 삭제하도록 하는 방법으로 가장 적절한 것은? (단, Employee의 did는 Department에서 가져온 외래키이다.)

 

Employee(eid, ename, did)

Department(did, dname, budget)

 

  • Employee 릴레이션을 생성할 때 FOREIGN KEY(did) REFERENCES DEPARTMENT(did) ON DELETE RESTRICT 명령어를 추가한다.
  • Department 릴레이션을 생성할 때 FOREIGN KEY(did) REFERENCES DEPARTMENT(did) ON DELETE CASCADE 명령어를 추가한다.
  • Employee 릴레이션을 생성할 때 FOREIGN KEY(did) REFERENCES DEPARTMENT(did) ON DELETE CASCADE 명령어를 추가한다.
  • DEPARTMENT 릴레이션을 생성할 때 FOREIGN KEY(did) REFERENCES EMPLOYEE(did) ON DELETE RESTRICT 명령어를 추가한다.
해설

1. CASCADE:
• 부모 테이블(Department)의 행이 삭제되면, 자식 테이블(Employee)의 해당 외래 키를 참조하는 모든 행도 자동으로 삭제됩니다.
• 이 옵션은 부모-자식 관계에서 데이터를 동기화하며, 문제의 요구사항에 적합합니다.

 

2. RESTRICT:
• 부모 테이블의 행을 삭제하려고 할 때, 자식 테이블에서 해당 외래 키를 참조하는 데이터가 있으면 삭제가 제한됩니다.
• 이 옵션은 문제의 요구사항과 맞지 않습니다.

 

3. SET NULL:
• 부모 테이블의 행이 삭제되면, 자식 테이블의 외래 키 값이 NULL로 설정됩니다.
• 이 옵션은 문제에서 요구하는 “자식 데이터 삭제”와 맞지 않습니다.

 

4. NO ACTION:
• 기본 동작으로, 부모 테이블의 데이터를 삭제하려고 하면 참조 무결성 제약 조건을 위반하여 오류가 발생합니다.
• 문제의 요구사항과 맞지 않습니다.

 

각 선택지 분석
1. `FOREIGN KEY(did) REFERENCES DEPARTMENT(did) ON DELETE RESTRICT`
• `ON DELETE RESTRICT`는 부모 테이블(Department)의 데이터가 자식 테이블(Employee)에서 참조되고 있을 경우 삭제를 제한합니다.
• 따라서, Department에서 `did = '1'`인 행을 삭제할 수 없습니다.
• 적합성: 부적절함.

 

2. `FOREIGN KEY(did) REFERENCES DEPARTMENT(did) ON DELETE CASCADE` (Department 릴레이션에서 설정)
• 이 문장은 Department 테이블에 외래 키를 설정하는 것으로 잘못된 문법입니다.
• 외래 키는 자식 테이블(Employee)에 설정해야 하므로, 이 문장은 적합하지 않습니다.
• 적합성: 부적절함.

 

3. `FOREIGN KEY(did) REFERENCES DEPARTMENT(did) ON DELETE CASCADE` (Employee 릴레이션에서 설정)
• Employee 테이블에서 외래 키(`did`)를 정의하며, `ON DELETE CASCADE` 옵션을 추가합니다.
• 이 옵션은 부모 테이블(Department)의 행이 삭제될 때, 자식 테이블(Employee)의 해당 외래 키를 참조하는 모든 행도 자동으로 삭제되도록 합니다.
• 문제의 요구사항과 정확히 일치합니다.
• 적합성: 적절함.

 

4. `FOREIGN KEY(did) REFERENCES EMPLOYEE(did) ON DELETE RESTRICT`
• 이 문장은 Department가 Employee를 참조하는 것으로 잘못된 관계를 정의하고 있습니다.
• 문제의 관계에서는 Employee가 Department를 참조하므로, 이 문장은 적합하지 않습니다.
• 적합성: 부적절함.