::WinLux-Compeople::sadbird98@gmail.com

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	외래키(publisher의 pub_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 등과 같은 함수는 같이 사용할수 있다.