[데이터베이스] SQL 연습문제

Chapter 4 - SQL 연습문제

---

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

① 데이터 정의 언어(DDL)

• 데이터의 전체적인 구조나 골격을 결정하는 역할 (create, drop, alter … )

② 데이터 조작 언어(DML)

• 테이블 안에 있는 데이터를 조작하는 역할 (insert, select, update set … )

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

create table publisher (
	pub_code	varchar(10),
    pname		varchar(20),
    paddress	varchar(50),
    pphone		varchar(20),
    constraint pk_pcode primary key (pub_code)
);

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

create tabe book (
	isbn 		varchar(20),
    title		varchar(50),
    author		varcahr(20),
    pub_code	varchar(10),
    price		int,
    constraint pk_isbn primary key (isbn)
    constraint fg_pcode foreign key (pub_code) references publisher(pub_code)
);

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

• book은 외래키 pub_code를 통해 publisher를 참조하고 있다. 이는 무결성 제약 위반이기에 외래키를 통한 참조 관계에서 참조 되는 테이블을 먼저 삭제할 수 없다.

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

client(ssn, name, address, phone)	deposit(deposit_num, ssn, balance, branch_name)	branch(branch_name, branch_head, address)
![

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

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

select d.deposit_num, c.name, d.balance
from client c, deposit d
where c.ssn = d.ssn;

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

select phone, ssn
from client
where name = '박지성';

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

select balance
from deposit
where branch_name = '성남지점';

@ 지점장 이름이 ‘고희경’인 지점의 이름과 주소를 검색하라.

select branch_name, address
from branch
where branch_head = '고희경';

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

select branch_head, address
from branch
where branch_name = '광주지점';

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

select d.deposit_num, d.branch_name, d.balance
from client c, deposit d
where c.ssn = d.ssn and c.name = '김기식';

select deposit_num, branch_name, balance
from deposit
where ssn in (select ssn
             from client
             where name = '김기식');

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

select c.name, c.address, d.balance
from client c, deposit d
where c.ssn = d.ssn and d.branch_name = '성남지점';

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

select c.name, d.balance
from client c, deposit d
where c.ssn = d.ssn and d.branch_name = '성남지점' and c.name like '김%';

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

select name
from client
where ssn in (select ssn
             from deposit
             where balance >= 100000);

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

select branch_name, branch_head
from branch
where branch_name in (select branch_name
                     from deposit
                     where balance >= 100000);

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

select deposit_num, balance, branch_name
from deposit
where ssn in (select ssn
             from client c, branch b
             where c.name = b.branch_head);

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

select c.name, d.balance
from client c, deposit d
where c.ssn = d.ssn and branch_name = '서울지점' and c.ssn like '%-1%' or c.ssn like '%-3%';

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

select c.name, d.deposit_num
from client c, deposit d
where c.ssn = d.ssn and c.ssn like '__03%';

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

select c.name, d.balance
from client c, deposit d
where c.ssn = d.ssn and d.branch_name like address || '%';

select c.name, d.balance
from client c, deposit d, branch b
where c.ssn = d.ssn and d.branch_name = b.branch_name and c.address = b.address;

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

select count(distinct c.ssn)
from client c, deposit d
where c.ssn = d.ssn and branch_name = '성남지점';

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

select branch_name, sum(balance)
from deposit
group by branch_name;

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

select c.name, sum(d.balance)
from client c, deposit d
where c.ssn = d.ssn
group by c.name;

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

select branch_name, sum(balance)
from deposit
group by branch_name
having sum(balance) >= 1000000;

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

select d.branch_name, count(distinct c.ssn)
from client c, deposit d
where c.ssn = d.ssn and d.balance >= 1000000
group by d.branch_name;

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

select name, phone
from client
where ssn not in (select ssn
                 from deposit);

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

employee(emp_id, name, period_emp, dept)	department(dept, phone, office)

project(project_name, address, period)	assign(emp_id, project name)
	![

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

select e.name, a.project_name
from employee e, assign a
where e.emp_id = a.emp_id and e.period_emp >= 10;

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

select name, dept
from employee
where emp_id in (select emp_id
                from assign
                where project_name = 'A');

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

select name, emp_id
from employee
where emp_id in (select emp_id
                 from project p, assign a
                 where p.project_name = a.project_name and p.address = '서울');

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

select name, dept
from employee
where emp_id in (select emp_id
                from project p, assign a
                where p.project_name = a.project_name and p.period >= 2);

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

select project_name, period
from project
where project_name in (select a.project_name
                      from assign a, employee e
                      where a.emp_id = e.emp_id and e.dept = '총무부');

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

select dept, count(*), avg(period_emp)
from employee
group by dept;

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

select e.dept, avg(p.period)
from employee e, project p, assign a
where e.emp_id = a.emp_id and p.project_name = a.project_name
group by e.dept;

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

select p.project_name, count(*), avg(e.period_emp)
from employee e, assign a, project p
where e.emp_id = a.emp_id and p.project_name = a.project_name
group by p.project_name;

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

select count(*)
from assign
where emp_id in (select emp_id
                from employee
                where period_emp >= 10);

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

select name, dept
from employee
where emp_id not in (select a.emp_id
                    from assign a, project p
                    where a.project_name = p.project_name);

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

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

update client 
set address = '서울'
where name = '차두리';

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

update deposit 
set balance = balance * 1.05
where branch_name = '광주지점';

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

update deposit 
set balance = balance * 1.05
where balance >= 100000;

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

delete from deposit
where balance < 10000;

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

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

select 문에 집계 함수가 사용되면 group by를 통해 묶어서 표현해야 하지만 (deposit_num, branch_name) 이 아닌 branch_name 에 대해서만 group by를 진행하여 해당 질의는 잘못된 질의이며 올바르게 고친다면 다음과 같다.

select branch_name, avg(balance)
from deposit
where balance > 10000
group by branch_name

select branch_name, avg(balance)
from deposit
where balance > 10000
group by branch_name