시나공 정보처리기사 실기 8장 SQL 응용 문제 풀이

SECTION 118 SQL - DDL

문제 1 다음 <속성 정의서>를 참고하여 <학생> 테이블에 20자의 가변 길이를 가진 '주소' 속성을 추가하는 <SQL문>을 완성하시오. (단, SQL문은 ISO/IEC 9075 표준을 기반으로 작성하시오.)

<속성 정의서>

속성명	데이터타입	제약조건	테이블명
학번	CHAR(10)	UNIQUE	학생
이름	VARCHAR(8)	NOT NULL	학생
주민번호	CHAR(13)		학생
학과	VARCAHR(16)	FOREIGN KEY	학생
학년	INT		학생

<SQL문>

( 1 ) TABLE 학생 ( 2 ) 주소 VARCHAR(20);

답 1. ALTER 2. ADD

 

문제 2 다음 <student> 테이블을 참고하여 'name' 속성으로 'idx_name'이라는 인덱스를 생성하는 SQL문을 작성하시오.

<student>

stid	name	score	deptid
2001	brown	85	PE01
2002	white	45	EF03
2003	black	67	UW11

답 : CREATE INDEX idx_name ON STUDENT(name);

 

문제 3 아래의 <요구사항>을 만족하는 테이블 <patient>를 정의하는 SQL문을 작성하시오.

<요구사항>

'id(문자 5)', 'name(문자 10)', 'sex(문자 1)', 'phone(문자 20)' 속성을 가진다. 'id' 속성은 기본키이다. 'sex' 속성은 'f' 또는 'm' 값만 갖도록 한다.(제약 조건명 : sex_ck). 'id'는 <doctor> 테이블에 있는 'doc_id'를 참조한다(제약조건명 : id_fk).

답 :

CREATE TABLE patient

       ( id CHAR(5) PRIMARY KEY,

             name CHAR(10),

             sex CHAR(1),

             phone CHAR(20),

             CONSTRAINT sex_ck CHECK (sex='f' or sex='m'),

             CONSTRAINT id_fk FOREIGN KEY(id) REFERENCES doctor(doc_id) );

 

문제 4. 아래의 <요구사항>을 만족하는 테이블 <Instructor>를 정의하는 SQL문을 작성하시오.

<요구사항>

'id(문자 5)', 'name(문자 15)', 'dept(문자 15)' 속성을 가진다. 'id' 속성은 기본키이다. 'name' 속성은 Null이 올 수 없다. 'dept' 속성은 <Department> 테이블의 'dept' 속성을 참조하는 외래키이다. - <Department> 테이블에서 튜플이 삭제되면 관련된 모든 튜플의 'dept' 속성의 값은 NULL로 변경되어야 한다. - <Department> 테이블의 'dept' 속성이 변경되면 <Instructor> 테이블의 관련된 모든 속성 값도 같은 값으로 변경되어야 한다.

답 : 

CREATE TABLE Instructor

      ( id CHAR(5)

            name CHAR(15) NOT NULL,

            dept CHAR(15),

            PRIMARY KEY(id),
            FOREIGN KEY(dept) REFERENCES Department(name)

 

                 ON DELETE SET NULL

 

                 ON UPDATE CASCADE

);

 

문제 5. <patient> 테이블에 데이터 타입이 문자 20자리인 'job' 속성을 추가하는 SQL문을 작성하시오.

문제 : ALTER TABLE patient ADD job CHAR(20);

 

문제 6. 아래의 <요구사항>을 만족하는 뷰 <CC>를 정의하는 SQL문을 작성하시오.

<요구사항>

<Course>와 <Instructor> 릴레이션을 이용한다. <Course>의 'instructor' 속성 값과 <Instructor>의 'id' 속성이 같은 자료에 대한 view를 정의한다. <cc> 뷰는 'ccid', 'ccname', instname' 속성을 가진다. <cc> 뷰는 <Course> 테이블의 'id', 'name', <instructor> 테이블의 'name' 속성을 사용한다.

답 : 

CREATE VIEW CC(ccid, ccname, instname) AS

       SELECT Course.id, Course.name, Instructor.name

       FROME Course, Instructor

       WHERE Course.instructor = Instructor.id;

 

문제 7. <사원> 테이블을 정의하는 SQL 문이다. 아래의 <요구사항>을 만족하도록 괄호(1, 2)에 적합한 명령을 넣어 SQL문을 완성하시오.

<요구사항>

'근무지번호'는 <근무지> 테이블의 '근무지번호'를 참조하는 외래키이다. <근무지> 테이블에서 '근무지번호'가 삭제되면 <사원> 테이블의 '근무지번호'도 삭제된다.

<SQL문>

CREATE TABLE 사원     ( 사원번호 NUMBER(4) PRIMARY KEY,       사원명 VARCHAR2(10),       근무지번호 NUMBER(2) ( (1) ) 근무지       ON DELETE ( (2) )     );

답 :

1 : FOREIGN KEY (근무지번호) REFERENCES

2 : CASCADE

 

문제 8. <Student> 테이블의 ssn 속성에 대해, 중복을 허용하지 않도록 'Stud_idx'라는 이름으로 오름차순 인덱스를 정의하는 SQL문을 작성하시오.

답 : 

CREATE UNIQUE INDEX Stud_idx

      ON Student(ssn ASC);

 

문제 9. 다음은 기본키인 '직위' 속성의 값으로 "사원", "대리", "과장", "부장", "이사", "사장"만을 허용하고, 기본 값으로 "사원"을 취하는 도메인 무결성 제약 조건을 설정하기 위한 SQL 문이다. 괄호를 채워 SQL문을 완성하시오.

<SQL 문>

CREATE DOMAIN 직위 VARCHAR2(10) ( (1) ) ( (2) ) VALID-직위 ( (3) );

답 : 

1. DEFAULT '사원' 2. CONSTRAINT 3. CHECK( VALUE IN('사원', '대리', '과장', '부장', '이사', '사장')

 

문제 10. <직원> 테이블에 대해 '이름' 속성으로 '직원_name'이라는 인덱스를 정의하는 SQL문을 작성하시오.

답 : 

 

문제 11. 다음 <처리 조건>에 부합하는 SQL문이 완성되도록 괄호에 적합한 옵션을 쓰시오.

<처리 조건>

<학생> 테이블을 제거한다. <학생> 테이블을 참조하는 모든 데이터도 함께 제거한다.

<SQL문>

DROP TABLE 학생 (     );

답 : CASCADE

 

문제 12. 데이터 언어는 데이터베이스를 구축하고 이용하기 위한 데이터베이스 관리 시스템과의 통신 수단이다. 데이터 언어는 데이터베이스의 구조를 정의 및 변경하는 DDL, 데이터를 조작하는 DML, 보안 및 무결성, 병행 제어 등을 위한 DCL로 구분된다. 다음 <보기>에서 DDL에 속하는 명령어들을 골라 적으시오.

Commit, Table, Insert, Domain, Update, Query, Create, Trigger, Alter, Rollback, Procedure, Drop, Select, Grant, Delete, Revoke, View

답 : Create, Alter, Drop

 

문제 13. 다음 <사원> 테이블의 구조를 참고하여 미완성된 SQL문을 완성하시오.

<사원>

직원코드	성명	직책	연봉
161353	김미나	대리	2300
181323	최영락	사원	1900
151453	홍진호	과장	2800
135485	구준표	과장	3000
104895	강나래	팀장	3600
165484	김하늘	대리	2400

<SQL문>

CREATE TABLE 사원       (직원코드 NUMBER NOT NULL,        성명 CHAR(10) UNIQUE,        직책 CHAR(10) (   (1)   )(직책 ( (2) )('사원', '대리', '과장', '팀장')),        연봉 NUMBER);

답 : 

1 : check

2 : in

 

SECTION 119 SQL -DCL

문제 1. 데이터를 제어하는 DCL의 하나인 ROLLBACK에 대해 간략히 서술하시오.

답 : 변경된 내용을 데이터베이스에 반영하지 않고, 모든 내용들을 취소하고 DB 반영 이전 상태로 되돌리는 명령어이다.

 

문제 2. DBA가 사용자 PARK에게 테이블 [STUDENT]의 데이터를 갱신 할 수 있는 시스템 권한을 부여하는 SQL문을 작성하려고 한다. 다음 <SQL 문>을 작성하시오.

<SQL 문>

( (1) ) UPDATE ( (2) ) STUDENT TO PARK;

답 : 

1 : Grant

2 : On

※ DCL을 아용하여 다음 요구 사항에 맞는 SQL을 작성하시오(3~6번)

<학사관리 시스템 스키마>

학생(학번, 주민등록번호, 이름, 학년, 전화번호, 주소) 강좌(강좌번호, 장좌명, 학점, 수강인원, 강의실, 학기, 연도, 교수번호) 수강(학번, 강좌번호, 성적) 교수(교수번호, 주민등록번호, 이름, 직위, 임용년도)

 

문제 3. 김하늘에게 <학생> 테이블에 대한 접근 및 조작에 관한 모든 권한을 부여하는 SQL문을 작성하시오.

답 : GRANT ALL ON 학생 TO 김하늘;

 

문제 4. 김하늘에게 <강좌> 테이블에 대해 삭제하는 권한을 부여하고, <강좌> 테이블에 대해 삭제하는 권한을 다른 사람에게 부여할 수 있는 권한을 부여하는 SQL문을 작성하시오.

답 : GRANT DELETE ON 강좌 TO 김하늘 WITH GRANT OPTION;

 

문제 5. 임꺽정에게 부여된 <교수> 테이블에 대한 SELECT, INSERT, DELETE 권한을 취소하는 SQL문을 작성하시오.

답 : REVOKE SELECT, INSERT, DELETE ON 교수 FROM 임꺽정;

 

문제 6. <수강> 테이블에 대해 임꺽정에게 부여된 UPDATE 권한과 임꺽정이 다른 사람에게 UPDATE 권한을 부여할 수 있는 권한, 그리고 임꺽정이 다른 사람에게 부여한 UPDATE 권한도 모두 취소하는 SQL문을 작성하시오.

답 : REVOKE UPDATE ON 수강 FROM 임꺽정 CASCADE;

 

문제 7. 트랜잭션은 데이터베이스에서 하나의 논리적 기능을 수행하기 위한 일련의 연산 집합으로서 작업의 단위가 된다. 트랜잭션의 연산 ROLLBACK과 COMMIT 중 COMMIT의 개념을 간략히 서술하시오.

답 : COMMIT은 트랜잭션 처리가 정상적으로 완료된 후 트랜잭션이 수행한 내용을 데이터베이스에 반영하는 명령이다.

 

문제 8. <학생> 테이블에 대해 다음 SQL문을 순차적으로 수행하였으나 작업 중 문제가 생겨 <학생> 테이블의 상태를 학번이 '2001'과 '2002'인 학생의 정보가 남아있는 상태로 복원하려고 한다. 적합한 ROLLBACK 명령을 작성하시오.

<학생>

학번	이름	학과
2001	한서현	생명공학
2002	김영현	인공지능
2003	명호준	유전공학
2004	유연우	컴퓨터공학

<SQL 문>

DELETE * FROM 학생 WHERE 학번 = 2004; COMMIT; DELETE * FROM 학생 WHERE 학번 = 2003; SAVEPOINT P1; DELETE * FROM 학생 WHERE 학번 = 2002; SAVEPOINT P2; DELETE * FROM 학생 WHERE 학번 = 2001;

복원하려는 <학생> 테이블의 상태

학번	이름	학과
2001	한서현	생명공학
2002	김영현	인공지능

답 : ROLLBACK TO R1;

 

SECTION 120 SQL - DML

문제 1. <학생> 테이블에서 '이름'이 "민수"인 튜플을 삭제하고자 한다. 다음 <처리 조건>을 참고하여 SQL문을 작성하시오.

<처리 조건>

명령문 마지막의 세미콜론(;)은 생략이 가능하다. 인용 부호가 필요한 경우 작은 따옴표(' ')를 사용한다.

답 : 

DELETE FROM 학생 WHERE 이름 = '민수';

 

문제 2. 다음 <처리 조건>에 부합하는 SQL문을 작성하시오.

<처리조건>

1. 테이블명은 학생으로 정의한다. 2. 학생 테이블의 구조는 다음과 같다. 3. 학생 테이블에 학번이 98170823, 성명이 '한국산', 학년이 3, 과목이 '경영학개론', 연락처가 '?-1234-1234'인 학생의 정보를 입력하시오. 4. 명령문 마지막의 세미콜론(;)은 생략이 가능하다. 5. 인용 부호가 필요한 경우 작은 따옴표(' ')를 사용한다.

답 : 

INSERT INTO 학생(학번, 성명, 학년, 과목, 연락처) VALUES ('98170823', '한국산', '3', '경영학개론', '?-1234-1234');