Home 실전문제) 2과목 1장 - SQL 기본 Part. 1
Post
Cancel

실전문제) 2과목 1장 - SQL 기본 Part. 1

Posted Aug 25, 2023
11 min read

Part. 2로 이어짐

1~5번 - 명령어 종류

DML(데이터 조작어)

  • 조회 및 검색
    SELECT

  • 테이블 데이터 변형
    INSERT, UPDATE, DELETE

DDL(데이터 정의어)

    CREATE, ALTER, DROP, RENAME

DCL(데이터 제어어)

    GRANT, REVOKE

TCL(트랜잭션 제어어)

    COMMIT, ROLLBACK


6번 - PK 제약조건

제약조건 설정

테이블을 추가할 때 같이 정의하는 경우

1
2
3
4
5
6

CREATE TABLE [테이블명] (
THIS_IS_STR         VARCHAR2(40)    NOT NULL,
THIS_IS_NUM         NUMBER(5)   NOT NULL,
THIS_IS_TRIVIAL_VAL NUMBER(3),
CONSTRAINT  [제약조건명]    PRIMARY KEY (칼럼명)
);

제약조건 추가

1
2

ALTER TABLE [테이블명]
ADD CONSTRAINT [제약조건명] [제약조건] (칼럼명);


7번 - 테이블 데이터 변경

Oracle

1
2

ALTER TABLE [테이블명]
MODIFY COLUMN [칼럼명] [타입] [제약조건];

SQLServer

1
2

ALTER TABLE [테이블명]
ALTER COLUMN [칼럼명] [타입] [제약조건];

오라클과 SQL서버가 각각 MODIFY, ALTER인 것에 주의!!


9번 - 제약조건 1

CASCADE란?

  : Parent 테이블에서 데이터 삭제 시
    참조한 Child 테이블 데이터도 함께 삭제.


10번 - 제약조건 2: 종류

UNIQUE

  • 해당 칼럼값은 항상 유일무이 값 가져야 함.
  • 중복 허용 X
  • But, NULL 중복까지는 아님.

NOT NULL

  • 해당 칼럼값은 NULL 허용하지 않음.
  • 입력받을 때 무조건 입력 받아야 함.

PRIMARY KEY(PK)

  • UNIQUE와 NOT NULL 조건 동시에 만족.
  • 한 테이블엔 단 한 개의 PK만 존재.

FOREIGN KEY(FK)

  • 해당 칼럼값이 다른 칼럼의 값 참조해야 함.
  • 참조가 되는 칼럼은 UNIQUE나 PRIMARY KEY 설정돼야 함.


12번 - INDEX, PRIMARY KEY

INDEX 생성

1

CREATE INDEX [인덱스명] ON [테이블명](칼럼1, 칼럼2, ...);

②가 틀린 이유

CREATE TABLE EMP ( EMP_NO VARCHAR2(10) PRIMARY KEY, EMP_NM VARCHAR2(30) NOT NULL, DEPT_CODE VARCHAR2(4) DEFAULT '0000', JOIN_DATE DATE NOT NULL, REGIST_DATE DATE ) CREATE INDEX IDX_EMP_01 ON EMP (JOIN_DATE);

문제에서 제시한 테이블 구조에서
DEPT_CODE는 제약조건이 NOT NULL로 정의되었으나,
2번 선지에서는 해당 제약조건이 정의되어 있지 않음.

④가 틀린 이유

CREATE TABLE EMP ( EMP_NO VARCHAR2(10) NOT NULL PRIMARY KEY, EMP_NM VARCHAR2(30) NOT NULL, DEPT_CODE VARCHAR2(4) DEFAULT '0000' NOT NULL, JOIN_DATE DATE NOT NULL, REGIST_DATE DATE NULL ); ALTER TABLE EMP ADD CONSTRAINT EMP_PK PRIMARY KEY (EMP_NO); CREATE INDEX IDX_EMP_01 ON EMP (JOIN_DATE);

EMP_NO가 테이블 생성 시에 PRIMARY KEY로 정의되었으나
이후에 EMP_NO에 PRIMARY KEY를 추가하려 하므로 중복됨.


13번 - 두 문장 실행 결과

두 SQL 문장의 실행결과가 같은 이유
=> 학번이 PK로 정의되어 있으므로 NULL 불가!!

직접 실행

아래의 코드로
세 번의 테이블 생성과 삭제, 그리고 조회를 번갈아 반복하며
직접 결과를 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

CREATE TABLE STUD (
STUD_ID VARCHAR2(8) PRIMARY KEY,
AWARD   NUMBER(15)
);

DROP TABLE STUD;

-- CASE 1 - 두 칼럼 모두 NOT NULL 값
INSERT INTO STUD VALUES ('12345', 500);
INSERT INTO STUD VALUES ('12346', 700);

-- CASE 2 - 칼럼 하나는 모두 NOT NULL 값,
--          다른 하나는 장학금이 NULL 값
INSERT INTO STUD VALUES ('12345', 500);
INSERT INTO STUD VALUES ('12346', NULL);

-- CASE 3 - 두 칼럼 모두 장학금이 NULL 값
INSERT INTO STUD VALUES ('12345', NULL);
INSERT INTO STUD VALUES ('12346', NULL);

-- 조회
SELECT COUNT(*) AS CNT_ALL,
       COUNT(STUD_ID) AS ID
FROM STUD;

실행 결과는 세 번 모두 위와 같이 동일하였다.


17번 - SQL 문장 실행 결과

제시된 두 테이블을 다이어그램으로 나타내면 다음과 같다.

그리고 제시된 A, B, C 각각을 실행했을 때,

A

1

SELECT COUNT(직원번호) FROM 직원;

직원 테이블의 직원번호 칼럼에는
세 개의 행 모두 NULL이 아닌 값들이 있으므로
해당 문장의 실행 결과는 3이다.

B

1
2

DELETE FROM 부서
WHERE 부서번호 = '20';

직원 테이블의 소속부서 칼럼은
부서 테이블의 부서번호를 참조하는 FK이자
DELETE에 대한 CASCADE가 설정되어 있으므로,
부서번호 칼럼 중 값이 '20'인 행을 삭제할 시
직원 테이블의 소속부서 칼럼 중 값이 '20'인 행 역시 전부 삭제된다.

C

1

SELECT COUNT(직원번호) FROM 직원;

B가 실행된 이후에 위 문장이 실행될 때,
직원 테이블에서 존재하는 행은
단 한 개밖에 없으므로 그 결과는 1이 된다.


19번 - FK(외래키) 제약조건

DELETE ACTION

 - CASCADE
  : Parent 테이블에서 데이터 삭제 시 참조한 Child 테이블 데이터도 함께 삭제.

 - SET NULL
  : Parent 테이블에서 데이터 삭제 시 참조한 Child 테이블 데이터가 NULL로 변경됨.

 - SET DEFAULT
  : Parent 테이블에서 데이터 삭제 시 참조한 child 테이블 데이터가 DEFAULT값으로 변경.

 - RESTRICT
  : Child 테이블에 PK값 없는 경우만 Parent 테이블에서 데이터 삭제 허용

 - NO ACTION
  : 참조무결성 위반하는 삭제/수정 action 취하지 않음.

INSERT ACTION

 - AUTOMATIC
  : Parent 테이블에 PK가 없는 경우,
    PK 값 생성 후 Child 테이블 데이터 입력.

 - SET NULL
  : Parent 테이블에 PK가 없는 경우,
    Child 테이블 외부키를 NULL 값으로 입력.

 - SET DEFAULT
  : Parent 테이블에 PK가 없는 경우,
    Child 테이블 외부키를 지정된 기본값으로 입력.

 - DEPENDENT
  : Parent 테이블 데이터에 PK 있을 때만,
    Child 테이블에서 데이터 입력 허용

 - NO ACTION
  : 참조무결성 위반하는 입력 action 취하지 않음.


22번 - FK 제약조건 2

②가 틀린 이유
주문 테이블의 고객ID 칼럼은 고객 테이블의 동일명 칼럼을 참조하고
고객 테이블의 고객ID 칼럼에 존재하는 값의 종류는 2가지이다.
이때, 해당 선지에서 삽입하고자 하는 고객ID 값은 'C003'이고
이는 고객 테이블에는 존재하지 않는 값이다.
이러한 값을 child 테이블인 주문에 삽입하고자 하므로 오류 발생.

④가 틀린 이유
고객ID 칼럼에 대하여 고객 테이블이 parent, 주문이 child이다.
이때, 고객 테이블에서 고객ID 칼럼의 특정 행을 삭제할 경우
해당 행의 값을 참조하는 다른 테이블의 값 역시 삭제된다.
그리고, 주문 테이블의 고객ID 참조에 대해 DELETE SET NULL이라는
제약조건이 설정되었으므로, 완전히 삭제되는 것이 아니라
해당 값은 NULL로 변경되어야 한다.
그러나, 고객ID의 다른 제약조건인 NOT NULL도 존재하므로 상충한다.


29번 - COMMIT, ROLLBACK

오라클에서는 DDL 문장의 경우 수행 후 자동으로 COMMIT이 수행되고,
SQL Server에서는 자동으로 수행되지는 않는다.
따라서, 제시된 SQL 문장에서,

1
2
3
4
5
6
7
8
9
10
11

-- 1st
UPDATE A SET VAL = 200
WHERE ID = '001';

-- 2nd
CREATE TABLE B (
ID  CHAR(3) PRIMARY KEY
);

-- 3rd
ROLLBACK;

오라클은 2nd 이후 자동으로 COMMIT이 되었으므로
테이블 A의 ID가 '001'인 행의 VAL 값이 200으로 변경되었고
테이블 B 역시 생성되었다.

그러나,
SQL Server는 1st, 2nd 이후에도 COMMIT이 이뤄지지 않았으므로
3rd 이후에는 1st와 2nd 내용이 수행되기 이전으로 돌아간다.

따라서,
오라클에서 B 테이블이 생성되지 않았다고 기술된
③번 선지가 부적절하다.


31번 - COMMIT, ROLLBACK 2

ROLLBACK은 COMMIT이 수행되지 않은
모든 트랜잭션의 수행에 대하여 그 이전으로 돌아가므로
아래의 두 문장 역시 그 실행 이전으로 돌아갔다고 할 수 있다.

1
2
3
4
5
6

DELETE 품목
WHERE 품목ID = '002';

UPDATE 품목
SET 단가=2000
WHERE 단가=1000;

따라서,

1
2
3

SELECT COUNT(품목ID)
FROM 품목
WHERE 단가=2000;

위 문장의 수행결과는 3이 된다.


Part. 2로 이어짐

SQLD
DATABASE SQLD SQLD_Probs
Contents