[한국은행 전산학 기출][2010] DB SQL

문제

은행 데이터베이스 테이블이 아래와 같을 때 물음에 답하시오.

고객(성명, 주소, 나이) 지점(지점명, 주소, 자산) 계좌(계좌번호, 지점명, 잔고, 구분) 예금(성명, 계좌번호) ----------------- 이하 내 풀이를 위한 심볼 -------------- customer(name, addr, age) bank_branch(name, addr, asset) account(number, branch_name, balance, class) deposit(customer_name, account_number)

 

가. 아래와 같은 조건을 가지고 있는 "계좌" 테이블을 생성하는 SQL을 작성하시오.

- 계좌번호는 문자 10자리, 지점명은 문자 20자리, 잔고는 정수, 구분은 문자 10자리이다.

- '지점' 테이블의 지점명이 바뀌면 '계좌' 테이블의 지점명도 자동으로 바뀐다.

- 잔고는 0 이상이고, 구분의 기본값은 '예금'이다.

 

나. 성이 'kim' 이고 나이가 20대이며 최소한 2개의 계좌를 가진 고객들의 성명과 잔고의 평균을 구하는 SQL을 작성하시오.

나의 답

가.

create table account (
	number varchar(10) primary key,
    branch_name varchar(20),
    balance int,
    class varchar(10) default 'deposit',
    foreign key (branch_name) references bank_branch (name) on update cascade,
    check (balance >= 0)
);

나.

select deposit.customer_name, avg(account.balance)
from deposit, account, customer
where deposit.customer_name like "kim%"
	and customer.age between 20 and 29
    and deposit.customer_name = customer.name
    and deposit.account_number = account.number
group by deposit.customer_name
having count(*) >= 2;

 

풀이

SQL 테스트는 macOS m1 local mysql에서 진행하였다.

 

1. 테이블 만들기

마지막에 show tables; 명령어를 입력하면 bok_example db 내부에 4개의 table이 만들어진 모습을 확인할 수 있다.

 

2. 예제 tuple 삽입

 

(1) customer (고객)

 

4명을 넣었다.

(2) bank_branch (지점)

 

내가 알고있는 지역........을 응용하여 두 곳을 생성하였다.

(3) account (계좌)

 

account table에 tuple들을 insert 하기 전에 생각해 봐야 할 것이 있다.

우리의 account.branch_name은 bank_branch의 PK인 name(branch name)을 FK로 사용한다.

다시 말해, 하나의 account가 어떤 지점에 만들어지면 당연히 그 지점의 asset도 update되기를 바라는 것은 당연할 것이다.

 

그런데 지금 DB에는 그러한 설정이 되어 있지 않다.

따라서, account table에 tuple이 insert, delete, update 될 때 마다 bank_branch table의 해당 branch의 asset 또한 update 되어야 할 것이다.

우리는 이것을 DB의 trigger 문을 이용하여 해결할 수 있다.

 

Trigger는 어떤 테이블에 insert/delete/update 가 일어나기 전/후 에 특정 작업을 수행할 것이다!를 정의해놓는 것이다.

고로 정리하여 생각해 보면

1) account table에 insert가 된 후

=> bank_branch table의 해당 지점 asset = asset + (새로운 계좌 balance) 로 update

2) account table에 delete가 된 후

=> bank_branch table의 해당 지점 asset = asset - (삭제된 계좌 balance) 로 update

3) account table에 update가 된 후

=> bank_branch table의 해당 지점 asset = asset - (update 이전 계좌 balance) + (update 이후 계좌 balance) 로 update

로 정리할 수 있을 것이다.

 

이를 그대로 trigger 문으로 옮기면 아래와 같다.

trigger가 잘 작동하는지를 account table에 tuple을 insert하며 테스트해보자.

 

먼저, 새로운 계좌를 하나 등록했을 때 asset도 자동으로 update가 잘 되는 것을 볼 수 있다.

insert때 잘 되는 것을 확인했으니 와장창 넣었다. 여전히 trigger는 잘 동작한다.

그렇다면 delete 할 때는 잘 될까? 아래와 같이 잘 된다.

 

update도 물론 잘 된다. 아싸. 뿌듯하다!!!!

(4) deposit (예금)

 

이제 customer table과 account table을 참조하여 deposit tuple들을 insert 했다.

 

 

3. 문제에서 원하는 쿼리 수행

 

(1) 가. 문제

 

제약조건을 잘 만족하는지를 봐야 한다.

 

일단 아래와 같이 balance가 0보다 작은 것을 넣으려고 하면 안되는 것을 확인하여 check 절이 잘 동작함을 확인할 수 있다.

또한 아래의 예제로 하남 지점을 여의도 지점으로 바꾸었을 때 account의 모든 계좌의 지점명도 변경됨을 볼 수 있다.

 

(2) 나. 문제

 

일단 deposit에 존재하는 모든 사람의 이름, 나이, 계좌번호, 잔액을 나타내면 다음과 같다.

 

 

성이 kim 이고, 나이는 20대이며 계좌가 2개 이상인 사람은 kima 뿐이므로 우리가 원하는 답은 kima, 150 의 튜플을 리턴하는 sql일 것이다.

 

답으로 적어둔 sql을 실행하면 아래와 같이 잘 나온다.