SQL (2)

Modification of the Database

Deletion

• relation에서 특정 튜플 삭제

Deletion in Relational Algebra
$r \leftarrow r - E$
$account \leftarrow account - \rho_{branch\_name = "Perryridge"}(account)$

Deletion in Database

delete from r
where P

r: relation, P: predicate

ex) Brooklyn 시에 위치한 각 지점에서 개설된 모든 계좌 정보를 삭제하시오.

delete from account
where branch_name in (select branch_name
					  from branch
    				  where branch_city = 'Brooklyn')

delete vs truncate vs drop
truncate & drop table → data definition 작업
delete → data modification 작업

delete vs truncate

• trucate r: relation r에 있는 모든 튜플 정보를 삭제
• delete from r과 유사한 결과
• 차이) delete는 순차적 삭제, truncate은 일괄 삭제

Insertion

• 특정 튜플을 relation에 추가

Insertion in Relational Algebra
$r \leftarrow r \cup E$
$depositor \leftarrow depositor \cup \{(``Smith", A-973)\}$

Insertion in Database

insert into r
	values (A1, A2, A3)

r: relation, (A1, A2, A3) : inserted values

ex) account relation에 새로운 튜플('A-777', 'Perryridge')을 추가하시오. 이 튜플의 balance는 null로 저장하시오.

insert into account
	values ('A-777', 'Perryridge', null)

Updating

• relation의 특정 속성의 값을 변경

Updating in Relational Algebra
$r \leftarrow \Pi_{F_1, F_2, ..., F_n}(r)$
$account \leftarrow \Pi_{account\_number,\ branch\_number,\ balance * 1.05}(account)$

Updating in Database

update r	# 어떤 테이블을 update?
set S		# 어떻게 update?
where P		# 어떤 것을 update? (조건)

r: relation, S: arithmetic expression, P: predicate

ex) 잔액이 $10,000 이상인 계좌에 6%의 이자를 지급하고, 다른 계좌에는 5% 이자를 지급하시오.

update account
set balance = balance * 1.06
where balance >= 10000

update account
set balance = balance * 1.05
where balance < 10000

Null Values

Null Values

• null: unknown value (or) 값이 존재하지 않는 경우

• is null: 값이 null인지 확인

ex) amount의 값이 null인 모든 대출 계좌 번호를 loan relation에서 찾으시오.

select loan_number
from loan
where amount is null

Null Values & Aggregates

ex) 모든 대출 계좌의 총 금액을 계산하시오.

select sum(amount)
from loan

• null 값을 무시한 채 계산하므로 문제되지 않음.
• 모든 amount가 null이면? result = null

count(*)를 제외한 모든 aggregate operation은 null값을 갖는 튜플을 무시하고 연산 수행

• sum, avg, max, min → null 값을 가지고 있는 튜플 무시 O
• count → null 값을 가지고 있는 튜플 무시 X

Nested Sub-queries

Nested Sub-queries

• 다른 query문 안에 포함되어 있는 sub-query

Set memberhsip

• 각 data가 어떤 집합에 포함되어 있는지 확인
• in, not in

Set comparison

• 각 data와 어떤 집합의 data를 비교할때 사용
• some, all

Set Membership

Set Membership

• 각 data가 어떤 집합에 포함되어 있는지 확인

ex) 예금 계좌와 대출 계좌를 모두 가지고 있는 모든 고객의 이름은?

(select customer_name from depositor)
intersect
(select customer_name from borrower)

select distinct customer_name
from borrower
where customer_name in (select customer_name from depositor)

ex) 예금 계좌 없이 대출 계좌만 가지고 있는 모든 고객의 이름은?

(select customer_name from depositor)
except
(select customer_name from borrower)

select distinct customer_name
from borrower
where customer_name not in (select customer_name from depositor)

Set Comparison

• 각 data와 어떤 집합의 data를 비교할때 사용
  - some: 집합 원소 중 조건을 만족하는 원소가 적어도 한개가 있음
  - all: 집합에 속한 모든 원소가 조건을 만족함

Set Comparison
ex) Brooklyn 시에 위치한 어떤 지점보다 많은 자산을 가지고 있는 모든 지점의 이름을 찾으시오.

select distinct T.branch_name
from branch as T.branch as S
where T.assets > S.assets and S.brand_city = 'Brooklyn'

select branch_name
from branch
where assets > some
	(select assets
    from branch
    where branch_city = 'Brooklyn')

Tuple Variables (remind)
ex) 대전의 어떤 지점보다 많은 자산을 보유하고 있는 모든 지점 (대전을 제외한 지역)의 이름을 찾으시오.

select distinct T.branch_name
from branch as T.branch as S
where T.assets > S.assets and S.branch_city = '대전' and T.brand_city <> '대전'

Set Comparison II
ex) Brooklyn 시에 위치한 모든 지점보다 많은 자산을 가지고 있는
지점의 이름을 찾으시오.

select branch_name
from branch
where assets > all
	(select assets
	from branch
	where branch_city = ‘Brooklyn’)

Example of 'Some' & 'All' Clause
Some
( 5 < some [0, 5, 6] ) = True
( 5 < some [0, 5] ) = False
( 5 = some [0, 5] ) = True
( 5 <> some [0, 5] ) = True
all
( 5 < all [0, 5, 6] ) = False
( 5 < all [6, 10] ) = True
( 5 = all [4, 5] ) = False
( 5 <> all [4, 6] ) = True

Views

Views

• 특정 사용자에게 공개되는 데이터의 집합 (다른 data는 모두 hidden)

View Definition

View

• 일종의 가상 relation

• 실제 relation으로부터 새로운 view 생성 가능

• 실제 relation처럼 query문 처리 가능

  • 검색(select): relation과 동일하게 수행
  • 삽입, 삭제, 갱식: 가능은 하나 제약이 있음 (ex. 원래 relation에 not null 속성이 있으면 view를 통한 삽입 불가능 / 두 테이블로부터 구성된 view는 삽입 불가능)
  • 원래 relation에 반영됨
  • 원래 relation에서 삽입, 삭제, 갱신 시 view에도 반영됨

View의 생성 방법
create view V as \<query expression>

View의 삭제 방법
drop view V

Example Queries
ex) 대출 계좌 또는 예금 계좌를 가지고 있는 모든 고객과 지점으로 구성된 'all_customer'라는 이름의 view를 정의하시오.

create view all_customer as

		(select branch_name, customer_name
        from depositor, account
        where depositor.account_number = account_number)

        union

        (select branch_name, customer_name
        from borrower, loan
        where borrower.loan_number = loan.loan_number)