[-문구-Compeople-문구-]

++ETC/Study2013. 6. 19. 21:46

1. 다음 용어에 대해 설명하라.

데이터 정의 언어(DDL)

=> 객체의 생성,변경,삭제 명령어 (CREATE , ALTER , DROP,RENAME )

SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 정의하거나 변경 또는 삭제할 때 사용하는 언어

데이터베이스 관리자나 데이터베이스 설계자가 사용함

데이터 정의어(DDL)3가지 유형

데이터 조작 언어(DML)

=> 레코드 제어 명령어 (SELECT, INSERT, UPDATE ,DELETE )

데이터베이스 사용자가 응용 프로그램이나 질의어를 통하여 저장된 데이터를 실질적으로 처리하는데 사용하는 언어

데이터베이스 사용자와 데이터베이스 관리 시스템 간의 인터페이스 제공

데이터 조작어(DML)4가지 유형

 

2. 다음과 같은 스키마를 갖는 테이블 publisher를 생성하는 SQL문을 작성하라.

필드이름

설명

데이터타입

길이

기타

pub_code

출판사 코드

문자열

10

기본키

pname

출판사 이름

문자열

20

 

paddress

출판사 주소

문자열

50

 

pphone

출판사 전화번호

문자열

20

 

## SQL Query..

CREATE TABLE `publisher` (

`pub_code` varchar(10) NOT NULL default '' COMMENT '출판사 코드',

`pname` varchar(20) default NULL COMMENT '출판사 이름',

`paddress` varchar(50) default NULL COMMENT '출판사 주소',

`pphone` varchar(20) default NULL COMMENT '춮판사 전화번호',

PRIMARY KEY (`pub_code`)

);

 

3. 다음과 같은 스키마를 작는 테이블 book을 생성하는 SQL 문을 작성하라.

필드이름

설명

데이터타입

길이

기타

isbn

ISBN 번호

문자열

20

기본키

title

제목

문자열

50

 

author

저자

문자열

20

 

pub_code

출판사코드

문자열

10

외래키(publisherpub_code를 참조)

price

가격

정수

 

 

## SQL Query..

CREATE TABLE `book` (

`isbn` varchar(20) NOT NULL default '' COMMENT 'ISBN 번호',

`title` varchar(50) default NULL COMMENT '제목',

`author` varchar(20) default NULL COMMENT '저자',

`pub_code` varchar(10) default NULL COMMENT '출판사 코드',

`price` int(11) default NULL COMMENT '가격',

PRIMARY KEY (`isbn`),

Foreign Key (`pub_code`) REFERENCES publisher(`pub_code`)

);

 

4. (문제2)(문제3)에서 만든 테이블 publisher, book에 대해서 publisher 테이블을 삭제할 때 어떤 문제가 발생할 수 있는지 외래키와 관련지어 설명하라.

=> “publisher”테이블의 “pub_code”필드의 경우, book테이블에서 외래키로 지정하였기 book테이블의 부모테이블이 된다, 기본제약조건으로 자식테이블(book)에 데이터가 존재할 경우, 부모테이블(publisher) 삭제가 불가능하다. 물론 DBMS에 종류에 따라, 강제 삭제할 수도 있다.

 

5. 아래는 은행에서 고객의 예금정보를 저장하기 위한 테이블 스키마이다. 이 테이블들에 대해서 주어진 물음에 답할 수 있는 select문을 작성하라. 

client(ssn, name, address, phone)

ssn

name

address

phone

970102-1234123

김기식

서울

010-2121-1231

890302-1273121

홍순태

서울

010-3242-2352

870423-2312593

강지선

부산

010-5223-3214

910502-2123121

황현희

부산

010-5394-0909

970306-1298101

정선태

대구

010-4392-3241

910931-2109211

박선희

서울

010-7984-1383

890902-1248311

이기상

대전

010-5335-9786

900402-1235721

박지성

서울

010-5910-2312

910328-2212123

이영순

부산

010-9876-2323

951012-1234123

차두리

제주

010-5920-2312

## 고객테이블 / Source for table "client"

CREATE TABLE `client` (

`ssn` varchar(14) NOT NULL default '',

`name` varchar(255) default NULL,

`address` varchar(255) default NULL,

`phone` varchar(255) default NULL,

PRIMARY KEY (`ssn`)

);

 

deposit(deposit_num, ssn, balance, branch_name)

deposit_num

ssn

balance

branch_name

100

970102-1234123

330000

서울지점

101

870423-2312593

120000

대전지점

102

890902-1248311

2300000

성남지점

103

890302-1273121

560000

광주지점

104

900402-1235721

870000

성남지점

105

951012-1234123

9000

대구지점

106

970102-1234123

110000

대구지점

107

910502-2123121

1900000

서울지점

108

890302-1273121

320000

광주지점

109

910931-2109211

560000

성남지점

110

890302-1273121

1200000

서울지점

111

970306-1298101

900000

제주지점

## 예금테이블 / Source for table "deposit"

CREATE TABLE `deposit` (

`deposit_num` int(11) NOT NULL auto_increment,

`ssn` varchar(255) default NULL,

`balance` varchar(255) default NULL,

`branch_name` varchar(255) default NULL,

PRIMARY KEY (`deposit_num`),

Foreign Key (`ssn`) REFERENCES client(`ssn`),

Foreign Key (`branch_name`) REFERENCES branch(`branch_name`)

);

 

branch(branch_name, branch_head, address)

branch_name

branch_head

address

광주지점

김기백

광주

대구지점

김기식

대구

대전지점

이연희

대전

부산지점

홍상순

부산

서울지점

강동희

서울

성남지점

박찬주

성남

제주지점

고희경

제주

# 개설지점 테이블 / Source for table "branch"

CREATE TABLE `branch` (

`branch_name` varchar(20) NOT NULL default '',

`branch_head` varchar(255) default NULL,

`address` varchar(255) default NULL,

PRIMARY KEY (`branch_name`)

);

 

밑줄 친 필드는 각 테이블의 기본키이며 예금 테이블의 주민등록번호 필드(ssn)는 고객 테이블을 참조하는 외래키, 개설지점 필드(branch_name)는 지점 테이블을 참조하는 외래키이다.

 

모든 고객의 계좌번호, 이름, 그리고 예금 잔액을 검색하라.

## SQL Query..

select de.deposit_num, cl.name, de.balance

from client cl, deposit de

where cl.ssn = de.ssn

## 질의결과

deposit_num

name

balance

100

김기식

330000

101

강지선

120000

102

이기상

2300000

103

홍순태

560000

104

박지성

870000

105

차두리

9000

106

김기식

110000

107

황현희

1900000

108

홍순태

320000

109

박선희

560000

110

홍순태

1200000

111

정선태

900000

 

이름이 박지성인 고객의 전화번호와 주민등록번호를 검색하라.

## SQL Query..

select ssn, phone

from client

where name = '박지성

 

지점 이름이 성남지점인 지점을 통해 개설된 모든 예금의 잔액을 검색하라.

## SQL Query..

select balance from deposit

where branch_name = '성남지점

 

지점장 이름이 고소영인 지점의 이름과 주소를 검색하라.

## SQL Query..

select branch_name, address

from branch

where branch_head = '고소영'

 

지점 이름이 광주지점인 지점의 지점장 이름과 주소를 검색하라.

## SQL Query..

select branch_head, address

from branch

where branch_name = '광주지점'

 

이름이 김광식인 고객이 소유한 예금의 계좌번호, 개설지점의 이름, 잔액을 검색하라.

## SQL Query..

select de.deposit_num, de.branch_name, de.balance

from client cl, deposit de

where cl.ssn = de.ssn and cl.name = '김광식

 

성남지점에서 계좌를 개설한 고객의 이름과 주소, 그리고 예금 잔액을 검색하라.

## SQL Query..

select cl.address, cl.name, de.balance

from client cl, deposit de

where cl.ssn = de.ssn and de.branch_name = '성남지점'

 

성남지점에서 계좌를 개설한 고객중 김씨 성을 가진 고객의 이름과 예금 잔액을 검색하라.

## SQL Query..

select cl.name, de.balance

from client cl, deposit de

where cl.ssn = de.ssn and de.branch_name = '성남지점' and cl.name like '%’

 

예금 잔액이 10만원 이상인 계좌를 소유한 고객의 이름을 검색하라.

## SQL Query..

select cl.name

from client cl, deposit de

where cl.ssn = de.ssn and de.balance >= 100000

group by name

 

예금 잔액이 10만원 이상인 계좌가 개설된 지점의 이름과 지점장 이름을 검색하라.

## SQL Query..

select br.branch_name, br.branch_head

from deposit de, branch br

where de.branch_name = br.branch_name and de.balance >= 100000

group by br.branch_name, br.branch_head

 

예금을 개설한 지점의 지점장과 이름이 같은 고객이 소유한 예금의 계좌번호, 잔액, 그리고 개설지점 이름을 검색하라.

## SQL Query..

select de.deposit_num, de.balance, de.branch_name

from client cl, deposit de, branch br

where cl.ssn = de.ssn and cl.name = br.branch_head

 

서울지점에서 계좌를 개설한 고객들 중에서 남자 고객의 이름과 예금 잔액을 검색하라.

## SQL Query..

select cl.name, de.balance

from client cl, deposit de

where cl.ssn = de.ssn

and de.ssn in(

select ssn

from (select ssn, substr(ssn,8,1) s_ssn from client cl) a

where a.s_ssn = '1' or a.s_ssn = '3'

)

 

주민등록번호상의 생일이 3월인 모든 고객의 이름과 소유한 예금의 계좌번호를 검색하라.

## SQL Query..

select de.deposit_num

from client cl, deposit de

where cl.ssn = de.ssn

and de.ssn in (

select ssn

from (

select ssn, substr(ssn,3,2) s_ssn

from client cl

) a

where a.s_ssn = '03'

)

 

자신의 주소와 같은 지점에 계좌를 소유하고 있는 고객의 이름과 예금 잔액을 검색하라.

## SQL Query..

select cl.name, de.balance

from client cl, deposit de, branch br

where cl.ssn = de.ssn

and de.branch_name = br.branch_name

and cl.address = br.address

 

성남지점과 거래하고 있는 고객의 숫자를 검색하라.

## SQL Query..

select count(*)

from client cl, deposit de

where cl.ssn = de.ssn

and branch_name = '성남지점

 

각 지점별 잔액의 총합을 검색하라.

## SQL Query..

select branch_name,sum(balance)

from deposit

group by branch_name

 

고객 이름별 예금 전액의 총합을 검색하라.

## SQL Query..

select cl.name, b.s_balance

from client cl, (

select ssn, sum(balance) s_balance

from deposit

group by ssn

)b

where cl.ssn = b.ssn

 

잔액의 합이 100만원 이상인 지점 이름과 잔액의 합을 검색하라.

## SQL Query..

select branch_name, s_balance from (

select branch_name, sum(balance) s_balance

from deposit

group by branch_name

) a

where a.s_balance >= 1000000

 

지점별로 예금 잔액이 100만원 이상인 고객의 숫자를 검색하라.

## SQL Query..

select count(*) from (

select ssn , sum(balance) s_balance

from deposit

group by ssn

) a

where a.s_balance > 1000000

 

예금 계좌를 소유하고 있지 않은 고객의 이름과 전화번호를 검색하라.

## SQL Query..

select cl.name, cl.phone

from client cl left outer join deposit de on de.ssn = cl.ssn

where deposit_num is null

 

6. 아래는 회사에 대한 정보를 담고 있는 테이블 스키마이다.(2장 연습문제 10번 참조) 이 테이블들에 대해서 주어진 물음에 답할 수 있는 select문을 작성하라. 각 사원(employee)은 하나의 부서(department)에 속해있고, 하나 이상의 프로젝트(project)에 배정(assign)될 수 있다. 밑줄이 있는 필드는 기본키를 나타낸다.

employee(emp_id, name, period_emp, dept)

emp_id

name

period_emp

dept

100

김경호

10

총무부

101

자우림

12

관리부

102

김범수

8

인사부

103

윤민수

8

인사부

104

박정현

9

총무부

105

박완규

12

관리부

# 사원 테이블 / Source for table "employee"

CREATE TABLE `employee` (

`emp_id` char(1) NOT NULL default '0',

`name` varchar(255) NOT NULL default '',

`period_emp` int(11) default NULL,

`dept` char(1) default NULL,

PRIMARY KEY (`emp_id`)

);

 

department(dept, phone, office)

dept

phone

office

총무부

02-201-2343

301

관리부

02-201-4367

103

인사부

02-201-0932

201

# 부서 테이블 / Source for table "deptment"

CREATE TABLE `department` (

`dept` char(1) NOT NULL default '',

`phone` varchar(255) default NULL,

`office` varchar(255) default NULL,

PRIMARY KEY (`dept`)

);

 

project(project_name, address, period)

project_name

address

period

A

서울

2

B

부산

4

C

대전

1

# 프로젝트 테이블 / Source for table "project"

CREATE TABLE `project` (

`project_name` varchar(11) NOT NULL default '',

`address` varchar(255) default NULL,

`period` int(11) default NULL,

PRIMARY KEY (`project_name`)

);

 

assign(emp_id, project_name)

emp_id

project_name

100

A

100

C

101

A

103

B

104

B

104

A

# 배정 테이블 / Source for table "assign"

CREATE TABLE `assign` (

`emp_id` char(1) NOT NULL default '',

`project_name` varchar(255) NOT NULL default '',

PRIMARY KEY (`emp_id`,`project_name`)

);

 

근무기간(period_emp)10년 이상인 사원의 이름과 그들이 참여하고 있는 프로젝트명을 검색하라.

## SQL Query..

select em.name, ass.project_name

from employee em, assign ass

where em.emp_id = ass.emp_id

and em.period_emp >= 10

 

프로젝트명이 ‘A’이 프로젝트에 참여하고 있는 사원의 이름과 부서명(dept)을 검색하라.

## SQL Query..

select em.name, em.dept

from employee em, assign ass

where em.emp_id = ass.emp_id

and ass.project_name = 'A'

 

주소가 서울인 곳에서 진행 중인 프로젝트에 참여하는 사원의 이름과 사번(emp_id)을 검색하라.

## SQL Query..

select em.name, em.emp_id

from employee em, assign ass , project pr

where em.emp_id = ass.emp_id

and ass.project_name = pr.project_name

and pr.address = '서울'

 

기간(period)2년 이상인 프로젝트에 참여하는 사원의 이름과 부서명을 검색하라.

## SQL Query..

select em.name, em.dept

from employee em, assign ass , project pr

where em.emp_id = ass.emp_id

and ass.project_name = pr.project_name

and pr.period >= 2

group by em.name, em.dept

 

총무부사원들이 참여하고 있는 프로젝트 이름과 기간을 검색하라.

## SQL Query..

select pr.project_name, pr.period

from employee em, assign ass , project pr

where em.emp_id = ass.emp_id

and ass.project_name = pr.project_name

and em.dept = '총무부'

group by pr.project_name, pr.period

 

부서별 사원 수와 평균 근무기간을 검색하라.

## SQL Query..

select dept, AVG(period_emp)

from employee

group by dept

 

부서별 사원들이 참여하고 있는 프로젝트의 평균 기간을 검색하라.

## SQL Query..

select dept, avg(pr.period)

from employee em, assign ass , project pr

where em.emp_id = ass.emp_id

and ass.project_name = pr.project_name

group by dept

 

프로젝트별 참여하고 있는 사원 수와 평균 근무기간을 검색하라.

## SQL Query..

select ass.project_name, avg(em.period_emp), count(*)

from employee em, assign ass

where em.emp_id = ass.emp_id

group by ass.project_name

 

근무기간이 10년 이상인 사원들이 참여하고 있는 프로젝트 수를 검색하라.

## SQL Query..

select count(*)

from employee em, assign ass

where em.emp_id = ass.emp_id

and em.period_emp >= 10

 

프로젝트에 참여하고 있지 않은 사원의 이름과 부서명을 검색하라.

## SQL Query..

select em.name, em.dept

from employee em left outer join assign ass on em.emp_id = ass.emp_id

where ass.emp_id is null

 

7. (문제5)의 테이블 스키마에 대해서 다음에 해당하는 질의를 SQL문으로 작성하라.

이름이 차두리인 고객의 주소를 서울로 수정하라

## SQL Query..

update client

set address = '서울'

where name = '차두리'

 

광주지점에 개설된 모든 예금에 대해 잔액을 5% 증가시키라.

## SQL Query..

update deposit

set balance = balance * 1.05

where branch_name = '광주지점'

 

잔액이 10만원 이상인 모든 예금에 대해 잔액을 5% 증가시키라.

## SQL Query..

update deposit

set balance = balance * 1.05

where balance >= 100000

 

잔액이 만원 미만인 모든 예금을 삭제하라.

## SQL Query..

delete from deposit

where balance <= 100000

 

주소가 서울인 모든 고객이 소유한 예금 계좌를 삭제하라.

## SQL Query..

delete from deposit

where ssn in (select ssn from client where address = '서울')

 

8. (문제5)의 테이블 스키마에 대해 다음의 질의는 잘못되었다. 그이유가 무엇인지 설명하라.

select deposit_num, branch_name, avg(balance)

from deposit

where balance > 10000

group by branch_name

설명 : 그룹 함수를 사용한 branch_name 만 컬럼명 리스트에서 사용해야 한다.

avg , count , sum, mix, min 등과 같은 함수는 같이 사용할수 있다.

Posted by Compeople