[데이터베이스개론] 7장 연습문제

[1] SQL은 데이터 정의어, 데이터 조작어, 데이터 제어어로 구분할 수 있다. 다음 중 성격이 다른 명령어는 무엇인가?

1. ALTER
2. DROP
3. CREATE
4. INSERT

[정답]④

정의어는 CREATE, ALTER, DROP이 있습니다.

INSERT는 조작어의 기능 입니다.

 

 

[2] 삽입 SQL에 대한 설명으로 옳지 않은 것은?

1. 프로그래밍 언어로 작성된 프로그램 안에 삽입하여 사용하는 SQL문이다
2. 프로그램 안에서 일반적인 명령문이 위치할 수 있는 곳이면 어디에나 삽입할 수 있다.
3. 프로그램 안에 선언된 일반 변수를 삽입 SQL문에서 사용할 수 있지만 테이블의 속성과 구별하기 위해 이름이 달라야 한다.
4. 프로그램 안에 선언된 일반 변수의 데이터 타입은 이에 대응하는 테이블의 속성과 데이터 타입이 일치해야 한다.

[정답]③

이름이 같아도 괜찮습니다.

 

 

[3] 오라클에서 학생 테이블을 참조하는 제약조건과 함께 학생 테이블을 제거하는 SQL 문에서 빈칸에 필요한 것은 무엇인가?

DROP TABLE 학생 ________;

1. ALL CONSTATINTS
2. CASCADE CONSTRAINTS
3. RESTRICT CONSTRAINTS
4. DELETE CONSTRAINTS

 

[정답]②

 

 

[4] 기본 테이블 R과 S를 조인해서 뷰 V1을 정의하고, 뷰 V1을 이용해 V2가 정의되었다. 이때 다음과 같은 SQL 문이 실행됐을 때 발생하는 결과를 올바르게 설명한 것은?

DROP VIEW V1;

1. V1만 삭제
2. V2만 삭제
3. V1,V2삭제
4. V1,V2 모두 삭제되지 않음

[정답]④

외래키가 있다면 삭제가 거절됩니다.

 

[5] 뷰에 대한 설명으로 옳지 않은 것은?

1. 뷰는 물리적으로 데이터를 저장하지 않는 가상의 테이블이다.
2. 뷰에 대한 삽입, 삭제, 수정 연산이 항상 허용되는 것은 아니다.
3. 뷰는 기본 테이블로부터 유도되지만, 검색 연산은 기본 테이블과 약간의 차이가 있다
4. 뷰에 대한 정의는 ALTER 문으로 변경할 수 없다.

[정답]③

 

 

 

[6] SQL 문을 이용해 테이블을 생성할 때 특정 속성에 대해 가능한 데이터 값의 범위를 지정하거나 제약조건을 지정하여 데이터 무결성을 유지하기 위해 사용되는 키워드는 무엇인가?

 

[정답]CHECK

 

 

 

[7]  다음 SQL 명령어를 보고 각 물음에 답하시오.

1. CREATE
2. SELECT
3. DROP
4. ALTER
5. DELETE
6. INSERT
7. UPDATE

 

1. DDL에 해당하는 SQL명령어를 모두 고르시오
2. DML에 해당하는 SQL명령어를 모두 고르시오

 

[정답]

1. 1, 4, 3

2. 2, 6, 5, 7

 

 

[10] 다음 두 테이블을 보고 각 물음에 답하시오

환자테이블

환자번호	환자이름	나이	담당의사
P001	오우진	31	D002
P002	채광주	50	D001
P003	김용욱	43	D003

의사 테이블

의사번호	의사이름	소속	근무연수
D001	정지영	내과	5
D002	김선주	피부과	10
D003	정성호	정형외과	15

 

(1) 환자 테이블을 생성하는 SQL 문을 작성하시오. 단, 이름 속성은 널 값이 허용되지 않도록 지정하고, 담당의사 속성을 의사 테이블의 의사번호 속성을 참조하는 외래키로 지정하시오.

 

 

(2) 의사 테이블을 생성하는 SQL 문을 작성하시오. 단, 소속 속성의 값을 입력하지 않으면 자동으로 내과가 지정되도록 하고, 근무연수는 1년 이상 40년 이하 범위의 값을 가지도록 지정하시오.

 

 

(3) D001 의사가 담당하고 나이가 30세 이상인 환자의 환자번호와 환자이름을 검색하는 SQL 문을 작성하시오.

 

(4) 소속별로 의사의 수와 평균 근무연수를 검색하는 SQL 문을 작성하시오.

 

(5) 김용욱 환자를 담당하는 의사의 의사이름과 소속, 근무연수를 검색하는 SQL 문을 작성하시오.

 

 

[정답]

(1)

CREATE TABLE 환자 (

           환자번호 VARCHAR(10) NOT NULL,

           환자이름 VARCHAR(10) NOT NULL,

           나이 INT,

           담당의사 VARCHAR(10),

           PRIMARY KEY(환자번호),

           FOREIGN KEY(담당의사) REFERENCES 의사(의사번호),

)

 

(2)

CREATE TABLE 의사 (

           의사번호 CHAR(4) NOT NULL,

           의사이름 VARCHAR(10),

           소속 VARCHAR(20) DEFAULT ‘내과’,

           근무연수 INT,

           PRIMARY KEY(의사번호),

           CONSTRAINT CHK_YEAR CHECK(근무연수 >= 1 AND 근무연수 <= 40)

)

 

(3)

SELECT 환자번호, 환자이름

FROM 환자

WHERE 담당의사 = 'D001' AND 나이>=30;

 

(4)

SELECT 소속, COUNT(*) AS ‘의사 수’, AVG(근무연수) AS ‘평균 근무연수’

FROM 의사

GROUP BY 소속;

 

(5)

 

SELECT 의사.의사이름, 의사.소속, 의사.근무연수

FROM 의사, 환자

WHERE 환자.환자이름 = ‘김용욱’ AND 의사.의사번호 = 환자.담당의사;

 

 

 

 

[11]  다음 3개의 테이블 스키마를 보고 각 물음에 답하시오.

학생(학번, 이름, 학년) 과목(과목번호, 과목이름) 수강(학번, 과목번호, 중간성적, 기말성적, 학점)

(1) 수강 테이블에서 L로 시작하는 과목의 중간성적이 90점 이상인 학생의 이름과 기말성적을 검색하는 SQL 문을 작성하시오. 단, 이름을 기준으로 오름차순 정렬하고, 만약 이름이 같으면 기말성적을 기준으로 내림차순 정렬하시오.

 

(2) 수강 테이블에서 2명 이상의 학생이 수강하는 과목에 대해 과목별 등록 학생의 수와 중간성적의 평균을 구하는 SQL 문을 작성하시오. 이때, 등록한 학생의 총 수는 ‘학생수’로, 중간성적의 평균은 ‘성적평균’으로 속성 이름을 새로 부여하시오.

 

(3) 수강 테이블에서 개설된 과목의 수를 검색하는 SQL 문을 작성하시오.

 

(4) L001 과목을 수강하지 않는 학생의 이름과 학년을 검색하는 SQL 문을 작성하시오. 단, IN 연산자를 이용해 작성하시오.

 

(5) L001 과목을 수강하지 않는 학생의 이름과 학년을 검색하는 SQL 문을 작성하시오. 단, EXISTS 연산자를 이용해 작성하시오

 

[정답]

(1)

SELECT 학생.이름, 수강.기말성적

FROM 학생, 수강

WHERE 수강.과목번호 LIKE 'L%' AND 수강.중간성적 >= 90 AND 학생.학번 = 수강.학번

ORDER BY 학생.이름 ASC, 수강.기말성적 DESC;

 

(2)

SELECT 과목번호, COUNT(*) AS 학생수, AVG(중간성적) AS 성적평균

FROM 수강

GROUP BY 과목번호 HAVING COUNT(*) >= 2;

 

(3)

SELECT COUNT(DISTINCT 과목번호)

FROM 수강;

 

(4)

SELECT 이름, 학년

FROM 학생

WHERE 학번 NOT IN (SELECT 학번 FROM 수강 WHERE 과목번호 = ‘L001');

 

(5)

SELECT 이름, 학년

FROM 학생

WHERE NOT EXISTS

(SELECT * FROM 수강 WHERE 과목번호 = ‘L001' AND 학생.학번 = 수강.학번);

 

 

 

 

[12] 다음 4개의 테이블 스키마를 보고 각 물음에 답하시오.

고객(고객번호, 이름, 거주도시, 할인율) 판매자(판매자번호, 이름, 수수료) 제품(제품번호, 제품명, 재고량, 가격) 주문(주문번호, 고객번호, 제품번호, 판매자번호, 주문수량)

 

(1) 고객 테이블에 고객번호가 C002, 이름이 채희성, 거주도시가 대전이고, 할인율은 아직 결정되지 않은 고객의 정보를 삽입하는 SQL 문을 작성하시오.

 

(2) 방지호 고객의 주문수량을 10% 증가시키는 SQL 문을 작성하시오.

 

(3) 재고량이 100개 이하인 제품을 모두 삭제하는 SQL 문을 작성하시오.

 

(4) 가격이 가장 최소인 제품의 제품명을 중복 없이 검색하는 SQL 문을 작성하시오.

 

(5) 제품명에 ‘위’가 포함된 제품을 주문한 고객의 이름을 검색하는 SQL 문을 작성하시오.

 

(6) 주문 수량의 총계가 300개 이상인 고객에 대해 고객별 주문 횟수와 주문수량의 총계를 검색하는 SQL 문을 작성하시오.

 

 

[정답]

 

(1)

INSERT INTO 고객 VALUES ('C002', '홍길동', '대전', NULL);

 

(2)

UPDATE 주문

SET 주문수량 = 주문수량 * 1.1

WHERE 고객번호 IN (SELECT 고객번호 FROM 고객 WHERE 고객명 = ‘방지호’);

 

(3)

DELETE

FROM 제품

WHERE 재고량 <= 100

 

(4)

SELECT DISTINCT 제품명

FROM 제품

WHERE 가격 = (SELECT MIN(가격) FROM 제품)

 

(5)

SELECT 고객.이름

FROM 고객, 제품, 주문

WHERE 제품.제품명 LIKE ‘%위%’ AND 고객.고객번호 = 주문.고객번호 AND 제품.제품번호 = 주문.제품번호;

 

(6)

SELECT 고객번호, COUNT(주문번호) AS 주문횟수, SUM(주문수량) AS 주문수량총계

FROM 주문

GROUP BY 고객번호 HAVING SUM(주문수량) >= 300;