관리 구문 단원 모의 문제 · SQLD

5단원 관리 구문의 모의 문제 9개입니다. 문제마다 보기 네 개 가운데 답을 하나 고른 뒤, 정답을 펼쳐 해설과 다시 볼 레슨을 확인합니다.

해설은 정답의 까닭을 먼저 적고, 나머지 보기를 한 줄씩 짚습니다. 고른 보기의 줄부터 읽으면 어디서 생각이 갈렸는지 알 수 있습니다.

SQL 문제는 레슨과 같은 예제 데이터로 풉니다. SQL 블록 하나는 처음 데이터에서 시작해 한 세션에서 차례로 실행한 것입니다.

EMP

EMPNO	ENAME	SAL	DEPTNO	MGR
1001	김민준	520	10	NULL
1002	이서연	380	20	1006
1003	박지호	410	20	1006
1004	최유나	450	30	1001
1005	정하늘	300	NULL	1004
1006	강도윤	470	20	1001

DEPT

DEPTNO	DNAME	LOC
10	기획	서울
20	개발	판교
30	영업	부산
40	인사	서울

EMP의 EMPNO와 DEPT의 DEPTNO는 기본키입니다.
EMP의 ENAME·SAL과 DEPT의 DNAME은 NOT NULL입니다.
EMP의 DEPTNO는 DEPT를, MGR은 EMP를 가리키는 외래키입니다. ON DELETE 규칙은 정하지 않았습니다.

문제 1

다음 SQL을 차례로 실행한 뒤 EMP의 행 수로 알맞은 것을 고르세요.

INSERT INTO EMP
  (EMPNO, ENAME, SAL)
SELECT EMPNO + 100,
  ENAME,
  SAL
FROM EMP
WHERE DEPTNO = 20;
DELETE EMP
WHERE DEPTNO IS NULL
  AND SAL < 400;
SELECT COUNT(*) AS CNT
FROM EMP;

① 5
② 7
③ 8
④ 9

정답

② — VALUES 대신 서브쿼리를 쓴 INSERT가 20번 부서 3명을 사번에 100을 더해 한 번에 넣어 9행이 됩니다. 열 목록에 없는 DEPTNO는 NULL이라서, DELETE가 새로 넣은 1102 이서연(380)과 정하늘(300)을 지워 7행이 남습니다.

① INSERT에 VALUES가 없어 막힌다고 봤습니다(그러면 정하늘만 지워져 5행). VALUES 대신 서브쿼리를 쓰면 그 결과 행을 한 번에 넣어 3행이 더해집니다.
③ 새로 넣은 행의 DEPTNO가 원래 행의 20을 따라간다고 봤습니다. 열 목록에 없는 열에는 NULL이 들어가서, 새 1102 이서연도 DEPTNO가 NULL이고 SAL이 400보다 적어 함께 지워집니다.
④ FROM을 뺀 DELETE가 오류라고 봤습니다. Oracle에서는 DELETE EMP WHERE …처럼 FROM을 빼도 됩니다.

CNT
7

다시 볼 레슨: DML

문제 2

Oracle에서 다음 MERGE의 ㉠과 ㉡에 넣었을 때 오류 없이 실행되는 것을 고르세요.

MERGE INTO EMP E
USING (
  SELECT 1003 AS EMPNO,
    450 AS SAL
  FROM DUAL
) S
ON ㉠
WHEN MATCHED THEN
  UPDATE SET ㉡;

① ㉠ E.EMPNO = S.EMPNO, ㉡ E.SAL = S.SAL
② ㉠ E.EMPNO = S.EMPNO, ㉡ E.EMPNO = 1103
③ ㉠ (E.EMPNO = S.EMPNO), ㉡ E.SAL = S.SAL
④ ㉠ (E.EMPNO = S.EMPNO), ㉡ E.EMPNO = 1103

정답

③ — Oracle의 MERGE는 ON 조건을 괄호로 감싸야 합니다. 또 ON에 쓴 EMPNO는 짝을 찾는 기준이라 UPDATE SET에서 바꿀 수 없어서, 괄호를 두르고 SAL을 바꾸는 ③만 실행됩니다.

① 조인의 ON처럼 괄호 없이 써도 된다고 봤습니다. Oracle의 MERGE는 ON 조건에 괄호가 없으면 ORA-00969 오류입니다.
② 두 오해가 겹쳤습니다. ON 조건에 괄호가 없어 ORA-00969이고, 괄호를 둘러도 ON에 쓴 EMPNO는 SET에서 바꿀 수 없습니다.
④ ON에 쓴 열도 바꿀 수 있다고 봤습니다. 짝을 찾는 기준인 EMPNO를 SET에서 바꾸려 하면 ORA-38104 오류입니다.

MERGE INTO EMP E
USING (
  SELECT 1003 AS EMPNO,
    450 AS SAL
  FROM DUAL
) S
ON (E.EMPNO = S.EMPNO)
WHEN MATCHED THEN
  UPDATE SET
    E.SAL = S.SAL;
SELECT EMPNO,
  ENAME,
  SAL
FROM EMP
WHERE EMPNO = 1003;

EMPNO	ENAME	SAL
1003	박지호	450

다시 볼 레슨: DML

문제 3

다음 SQL을 차례로 실행했습니다. 마지막 SELECT의 결과로 알맞은 것을 고르세요.

UPDATE EMP
SET SAL = SAL + 30
WHERE DEPTNO = 30;
SAVEPOINT S1;
DELETE FROM EMP
WHERE MGR = 1004;
ROLLBACK TO S1;
ROLLBACK;
SELECT ENAME,
  SAL
FROM EMP
WHERE EMPNO IN
    (1004, 1005)
ORDER BY EMPNO;

① 최유나 450, 정하늘 300
② 최유나 450, 정하늘 행 없음
③ 최유나 480, 정하늘 300
④ 최유나 480, 정하늘 행 없음

정답

① — ROLLBACK TO S1은 S1 뒤의 DELETE만 되돌리고 트랜잭션은 끝내지 않습니다. 그래서 마지막 ROLLBACK이 S1 앞의 UPDATE까지 트랜잭션 전체를 되돌려 두 사람 모두 처음 값이 남습니다.

② 지운 행은 되돌릴 수 없다고 봤습니다. DELETE는 DML이라 ROLLBACK으로 되돌릴 수 있고, ROLLBACK이 안 되는 것은 TRUNCATE·DROP 같은 DDL입니다.
③ 저장점 앞의 변경은 ROLLBACK에도 남는다고 봤습니다. ROLLBACK TO는 트랜잭션을 끝내지 않으므로, 마지막 ROLLBACK이 트랜잭션 처음까지 되돌려 최유나도 450으로 돌아옵니다.
④ 문장마다 바로 확정된다고 봤습니다. 그것은 SQL Server의 기본 설정이고, Oracle에서는 COMMIT하기 전의 변경을 ROLLBACK으로 모두 되돌립니다.

ENAME	SAL
최유나	450
정하늘	300

다시 볼 레슨: TCL

문제 4

Oracle에서 다음 SQL을 차례로 실행했습니다. 마지막 SELECT의 결과로 알맞은 것을 고르세요.

UPDATE EMP
SET SAL = 490
WHERE EMPNO = 1004;
SAVEPOINT SP1;
UPDATE EMP
SET SAL = 430
WHERE EMPNO = 1003;
ALTER TABLE DEPT
  ADD (TEL VARCHAR2(15));
ROLLBACK TO SP1;
SELECT EMPNO,
  ENAME,
  SAL
FROM EMP
WHERE EMPNO IN
    (1003, 1004)
ORDER BY EMPNO;

① 박지호 410, 최유나 450
② 박지호 410, 최유나 490
③ 박지호 430, 최유나 450
④ 박지호 430, 최유나 490

정답

④ — ALTER TABLE은 DDL이라 Oracle이 실행 전후에 자동으로 COMMIT합니다. 이때 두 UPDATE가 확정되고 트랜잭션이 끝나 저장점 SP1도 사라져서, ROLLBACK TO SP1은 ORA-01086 오류를 내고 아무것도 되돌리지 않습니다.

① DDL이 앞의 변경을 자동으로 되돌린다고 봤습니다. DDL 앞뒤에 일어나는 것은 자동 COMMIT이라 두 UPDATE가 모두 확정됩니다.
② DDL이 트랜잭션에 영향을 주지 않는다고 봤습니다. 그랬다면 ROLLBACK TO SP1이 박지호만 410으로 되돌렸겠지만, 자동 COMMIT으로 저장점이 사라져 되돌릴 수 없습니다.
③ 저장점 앞뒤를 거꾸로 봤습니다. ROLLBACK TO SP1이 되돌리는 것은 SP1 뒤의 변경이고, 여기서는 그마저 자동 COMMIT으로 이미 확정되었습니다.

EMPNO	ENAME	SAL
1003	박지호	430
1004	최유나	490

다시 볼 레슨: TCL

문제 5

다음 SQL을 차례로 실행한 뒤 마지막 SELECT의 결과로 알맞은 것을 고르세요.

CREATE TABLE EMP_BAK AS
SELECT *
FROM EMP;
INSERT INTO EMP_BAK
SELECT *
FROM EMP
WHERE EMPNO = 1006;
INSERT INTO EMP_BAK
  (EMPNO, ENAME)
SELECT EMPNO + 10,
  ENAME
FROM EMP
WHERE MGR = 1006;
SELECT COUNT(*) AS CNT
FROM EMP_BAK;

① 6
② 7
③ 8
④ 9

정답

② — CREATE TABLE … AS SELECT⁠는 EMP의 열과 6행을 복사하지만, 제약 조건은 NOT NULL만 가져오고 기본키는 가져오지 않습니다. 그래서 사번 1006이 겹치는 첫 INSERT는 들어가고, SAL을 비운 둘째 INSERT는 NOT NULL에 막혀(ORA-01400) 7행입니다.

① 기본키도 복사된다고 봤습니다. EMP_BAK에는 기본키가 없어서 사번 1006이 두 번 들어가도 막히지 않습니다.
③ 두 제약 조건을 거꾸로 봤습니다. 기본키는 복사되지 않아 1006이 들어가고, NOT NULL은 복사되어 SAL이 빈 두 행은 들어가지 못합니다.
④ 제약 조건이 하나도 복사되지 않는다고 봤습니다. NOT NULL은 복사되어서, SAL이 빈 이서연·박지호 두 행은 ORA-01400으로 막힙니다.

CNT
7

다시 볼 레슨: DDL · DML

문제 6

처음 데이터에서 다음 문장을 하나씩 따로 실행합니다. 오류 없이 실행되는 것을 고르세요.

① DELETE FROM DEPT WHERE DNAME = '인사';
② DELETE FROM DEPT WHERE DEPTNO = 30;
③ TRUNCATE TABLE DEPT;
④ DROP TABLE DEPT;

정답

① — 인사 부서(40번)를 가리키는 사원이 없어서 그 행을 지울 수 있습니다. 나머지 셋은 EMP의 외래키가 DEPT를 가리키고 있어 막힙니다.

② 부모 행은 자식 행과 상관없이 지울 수 있다고 봤습니다. 30번은 최유나가 가리키고 있어 ORA-02292 오류입니다.
③ TRUNCATE는 행만 비우니 외래키와 상관없다고 봤습니다. 외래키가 가리키는 부모 테이블은 TRUNCATE할 수 없어 ORA-02266 오류입니다.
④ DROP은 테이블째 지우니 막히지 않는다고 봤습니다. 외래키가 가리키는 부모 테이블은 DROP TABLE만으로 지울 수 없어 ORA-02449 오류이고, CASCADE CONSTRAINTS를 붙여야 그 외래키까지 지웁니다.

다시 볼 레슨: DDL

문제 7

게시판의 댓글 테이블에서 글번호는 게시글 테이블의 기본키를 가리키는 외래키입니다(게시글이 부모, 댓글이 자식). 두 테이블 사이에 다음 규칙 (가)·(나)를 두려 합니다. (가)와 (나)에 알맞은 참조 동작의 이름을 고르세요.

(가) 댓글이 달린 게시글은 지우려 해도 지워지지 않고, 댓글이 하나도 없는 게시글만 지워집니다.

(나) 아직 없는 글번호로 댓글을 넣으면, 그 번호의 게시글을 먼저 만든 뒤 댓글을 넣습니다.

① (가) DEPENDENT, (나) AUTOMATIC
② (가) DEPENDENT, (나) CASCADE
③ (가) RESTRICT, (나) AUTOMATIC
④ (가) RESTRICT, (나) CASCADE

정답

③ — (가)는 부모인 게시글을 지울 때의 규칙이고, 자식인 댓글이 없을 때만 지우므로 RESTRICT입니다. (나)는 자식인 댓글을 넣을 때의 규칙이고, 없는 부모를 만든 뒤 넣으므로 AUTOMATIC입니다.

① 둘 다 막는 동작이라 RESTRICT와 DEPENDENT를 바꿔 봤습니다. 부모를 지울 때 자식이 없어야 지우는 것이 RESTRICT이고, DEPENDENT는 자식을 넣을 때 부모가 있어야 넣는 이름입니다.
② 지울 때와 넣을 때의 이름을 서로 바꿔 봤습니다. DEPENDENT는 넣을 때의 이름이고 CASCADE는 넣을 때는 없는 이름이라, 게시글을 지우는 (가)는 RESTRICT, 댓글을 넣는 (나)는 AUTOMATIC입니다.
④ 부모와 자식을 함께 처리하는 동작을 모두 CASCADE로 봤습니다. CASCADE는 부모를 지울 때 자식도 지우는 이름이고, 자식을 넣을 때 없는 부모를 만들고 넣는 것은 AUTOMATIC입니다.

다시 볼 레슨: DDL

문제 8

다음 문장을 차례로 실행했습니다. -- 줄은 실행한 사용자이고, OH는 이 블록에서 처음 만든 사용자입니다. 마지막 SELECT의 결과로 알맞은 것을 고르세요.

-- 관리자
CREATE USER OH
  IDENTIFIED BY oh1234;
CREATE ROLE HR_VIEW;
GRANT CREATE SESSION
  TO HR_VIEW;
GRANT SELECT ON SQLD.EMP
  TO HR_VIEW;
GRANT HR_VIEW TO OH;
REVOKE SELECT ON SQLD.EMP
  FROM HR_VIEW;
-- OH
SELECT COUNT(*) AS CNT
FROM SQLD.EMP;

① 0
② 6
③ 테이블이 없다는 오류(ORA-00942)
④ 접속할 수 없다는 오류(ORA-01045)

정답

③ — OH는 EMP의 SELECT 권한을 역할 HR_VIEW를 거쳐 받았습니다. 역할에서 권한을 거두면 그 역할을 받은 사용자 모두에게 반영되어, OH는 접속은 하지만 EMP를 읽지 못하고 Oracle은 테이블이 없다고 답합니다.

① 권한이 없으면 행이 0개로 보인다고 봤습니다. 권한이 없는 테이블은 행을 세지 못하고, 테이블이 없다는 오류(ORA-00942)가 납니다.
② 역할에서 거둬도 이미 역할을 받은 OH에게는 남는다고 봤습니다. 역할에 준 권한을 바꾸면 그 역할을 받은 사용자 모두에게 반영됩니다.
④ SELECT를 거두면 역할에 든 다른 권한도 함께 사라진다고 봤습니다. REVOKE는 적은 권한만 거두어서, 역할의 CREATE SESSION이 남아 OH는 접속할 수 있습니다.

다시 볼 레슨: DCL

문제 9

관리자가 CREATE SESSION만 가진 사용자 YUN에게 다음 문장을 하나씩 실행합니다. Oracle에서 오류가 나는 것을 고르세요.

① GRANT SELECT, INSERT, UPDATE ON SQLD.EMP TO YUN;
② GRANT CONNECT TO YUN;
③ GRANT CREATE TABLE TO YUN WITH ADMIN OPTION;
④ GRANT SELECT ON SQLD.EMP TO YUN WITH ADMIN OPTION;

정답

④ — SELECT는 EMP 하나에 대한 객체 권한이라, 받은 사람이 다시 줄 수 있게 하는 옵션은 WITH GRANT OPTION입니다. 객체 권한에 WITH ADMIN OPTION을 붙이면 ORA-00993 오류입니다.

① 권한은 한 번에 하나씩만 줄 수 있다고 봤습니다. 객체 권한은 쉼표로 여럿을 한 번에 줄 수 있습니다.
② CONNECT를 다른 사용자로 접속하는 SQL*Plus 명령으로만 봤습니다. CONNECT는 Oracle에 미리 있는 역할 이름이기도 해서 GRANT로 줄 수 있습니다.
③ WITH ADMIN OPTION은 역할에만 붙인다고 봤습니다. CREATE TABLE 같은 시스템 권한에도 WITH ADMIN OPTION을 붙입니다.

다시 볼 레슨: DCL