4. Advanced SQL (Structured Query Language)

1. 4. Advanced SQL (Structured Query Language) 서울시립대학교 전자전기컴퓨터공학부 김한준

2. Aggregate Functions 다시 보기 • 이들 함수는 relation의 행의 다중 집합 값에 연산하여 단일 값을 돌려준다. avg: 평균 값 min: 최소 값 max: 최대 값 sum: 총 계 count: 값의 개수

3. Aggregate Functions 다시 보기 • Perryridge지점의 평균 예금 잔고를 찾아라. select avg(balance) from account where branch-name = “Perryridge” • customer relation의 tuple수를 찾아라. select count(*) from customer • 은행의 예금자 수를 찾아라. select count(distinct customer-name) from depositor

4. Aggregate Functions- Group By • 각 지점의 예금자 수를 찾아라. • select branch-name, count(distinct customer-name) • from depositor, account • wheredepositor.account-number = account.account-number • group by branch-name • 유의 : aggregate function외부의 select절에 있는 attribute는 group by리스트 내에 나타나야 한다.

5. Aggregate Functions- Having 절 • 평균 예금 잔고가 1,200불을 초과하는 모든 지점명을 찾아라. select branch-name, avg(balance) from account group by branch-name having avg(balance) > 1200 • 유의 : having절의 술어는 그룹이 이루어진 후에 적용 • 된다.

6. NULL이 있는 질의 • loan relation내의 amount에 널 값이 있는 모든 대출 번호를 찾아라. select loan-number from loan where amount is null • 모든 대출액의 총계 select sum (amount) from loan • 위의 문장은 널 값은 무시한다. 널이 아닌 금액이 없으면 결과는 널이다. • count(*)를 제외한 모든 집계 연산은 집계 attribute상에 널 값을 가진 tuple은 무시한다.

7. Nested Query및 Set 활용 질의 • 은행에 예금과 대출이 모두 있는 고객을 찾아라. select distinct customer-name from borrower where customer-name in (select customer-name from depositor) • 은행에 대출은 있으나 예금은 없는 모든 고객을 찾아라. select distinct customer-name from borrower where customer-name not in ( select customer-name from depositor)

8. Self-join • Brooklyn에 위치한 어떤 지점보다 더 많은 자산을 가진 모든 지점을 찾아라. select distinctT.branch-name frombranch as T, branch as S whereT.assets > S.assetsand S.branch-city = “Brooklyn”

9. SOME, ALL 의 사용 • Brooklyn에 위치한 어떤 지점보다 더 많은 자산을 가진 모든 지점을 찾아라. select branch-name from branch where assets > some (select assets from branch where branch-city = “Brooklyn”)

10. SOME, ALL 의 사용 • Brooklyn에 위치한 모든 지점보다 더 많은 자산을 가진 모든 지점을 찾아라. select branch-name from branch where assets > all (select assets from branch where branch-city = “Brooklyn”)

11. Derived Relation • 평균 예금 잔고가 1,200불을 초과하는 지점들의 평균 예금 잔고를 찾아라. select branch-name, avg-balance from (select branch-name, avg(balance) from account group by branch-name) as result (branch-name, avg-balance) where avg-balance > 1200 • from절 내 temporaryrelationresult를 계산한후, 관련 attribute가 where 절에서 직접 사용될 수 있으므로, having절을 사용할 필요없음

12. 데이터베이스의 수정 : 삭제 • Perryridge지점의 모든 예금 레코드를 삭제하라. delete from account where branch-name = “Perryridge” • Needham에 위치한 각 지점의 모든 예금 계좌를 삭제하라. delete from account where branch-name in (select branch-name from branch where branch-city = “Needham”) delete from depositor where account-number in (select account-number from branch, account where branch-city = “Needham” andbranch.branch-name = account.branch-name)

13. 데이터베이스의 수정 : 삭제 • 은행의 평균에 미달하는 잔고를 가진 모든 예금 계좌 레코드를 삭제하라. delete from account where balance < (select avg (balance) from account) - 문제점: deposit에서 tuple들을 삭제하므로 평균 잔고가 변한다. - SQL에서 사용되는 해결책: 1. avg balance를 계산하고 삭제할 모든 tuple을 찾는다. 2. avg를 다시 계산하거나 tuple을 재 검사하지 않고 위에서 찾은 모든 tuple을 삭제한다.

14. 데이터베이스의 수정:삽입 • account 에 새로운 tuple을 삽입하라. insert into account values(“Perryridge”, A-9732, 1200) 또는, insert into account (branch-name, balance, account-number) values(“Perryridge”, 1200, A-9732) • account에 balance 값이 널로 지정된 tuple을 삽입하라. insert into account values (“Perryridge”, A-777, null)

15. 데이터베이스의 수정:삽입 • Perryridge지점의 모든 대출 고객에게 200불의 저축 예금 계좌를 제공한다. 새로운 저축 예금의 계좌 번호는 대출 번호로 한다. insert into account select branch-name, loan-number, 200 from loan where branch-name = “Perryridge” insert into depositor select customer-name, loan-number from loan, borrower where branch-name = “Perryridge” and loan.account-number = borrower.account-number

16. 데이터베이스의 수정:갱신 • 10,000 불을 초과하는 모든 예금 계좌에는 6%를 다른 계좌에는 5%의 이자를 지급하라. - 두 개의 update문으로 작성하라. update account set balance = balance * 1.06 where balance > 10000 update account set balance = balance * 1.05 where balance 10000

17. branch-name loan-number amount Downtown L-170 3000 Redwood L-230 4000 Perryridge L-260 1700 loan-number customer-name Jones L-170 Smith L-230 Hayes L-155 Outer Join (1/4) • relationloan • relationborrower

18. branch-name loan-number amount customer-name loan-number Downtown L-170 3000 Jones L-170 Redwood L-230 4000 Smith L-230 branch-name loan-number amount customer-name loan-number Downtown L-170 3000 Jones L-170 Redwood L-230 4000 Smith L-230 Perryridge L-260 1700 null null Outer Join (2/4) • loan inner join borrower on • loan.loan-number = borrower.loan-number • loan left outer join borrower on • loan.loan-number = borrower.loan-number

19. branch-name loan-number amount customer-name Downtown L-170 3000 Jones Redwood L-230 4000 Smith branch-name loan-number amount customer-name Downtown L-170 3000 Jones Redwood L-230 4000 Smith null L-155 null Hayes Outer Join (3/4) • loan natural inner join borrower • loan natural right outer join borrower

20. branch-name loan-number amount customer-name Downtown L-170 3000 Jones Redwood L-230 4000 Smith Perryridge L-260 1700 null null L-155 null Hayes Outer Join (4/4) • loan full outer join borrower using(loan-number) • 은행에 예금이 있거나 대출이 있는 모든 고객을 찾아라. selectcustomer-name from(depositor natural full outer join borrower) whereaccount-number is null or loan-number is null

21. Data Definition Language (DDL) • Relation 자체와 아래와 같은 relation 메타 정보의 조작을 지원 • Relation의 스키마 • Attribute에 관련된 값들의 domain • Integrity constraint조건 • Relation에 유지되어야 할 인덱스 집합 • Relation에 대한 정보 보안과 인증 • 디스크 상의 relation의 물리적 저장 구조

22. SQL에서의 도메인 유형 (1/2) • char(n):사용자가 지정한 길이 n을 가진 고정길이 문자열 • varchar(n):사용자가 지정한 최대 길이 n을 가진 가변길이 문자열 • int:정수 (보통 4bytes, machine-dependent) • smallint:작은 정수 (보통 2bytes, machine-dependent) • numeric(p,d): 유효자리수p개, 소수점 이하 n 개를 가지는 floating-point number • real, double precision: 부동 소수점 • float(n): 최소 n자리수의 사용자가 지정한 유효자리수를 가진 부동 소수점 수 • date: 4자리의 연, 월, 일을 가지는 날짜 • time: 시, 분, 초로 이루어진 시간

23. SQL에서의 도메인 유형 (2/2) • NULL값은 모든 도메인 유형에서 허용 • attribute를 not null로 선언하면 그 attribute에 널 값이 금지됨 • SQL-92의 create domain구조는 사용자가 정의한 도메인 유형을 생성 create domain person-name char(20) not null

24. Domain Constraints • Column 값들의 범위를 제한 • 일반 computerprogramming language의 data type과 유사 • User-defined domains 의 예제 • CREATEDOMAIN hourly-wage NUMERIC(5,2) CONSTRAINTwage_value_testCHECK(VALUE >= 4.00) • CREATEDOMAIN account-type CHAR(10) CHECK(VALUEIN (“checking”, “savings”))

25. CREATE TABLE 문장구조 • SQL relation은 create table 명령을 사용해 정의한다. create table r (A1 D1, A2 D2, . . ., AnDn, < integrity-constraint1>, . . ., < integrity-constraintk>) - r 은 relation명이다. - Ai는 relationr의 스키마내의 attribute명 - Di 는 attributeAi의 도메인내 값들의 데이터 타입 • 예: create table branch (branch-name char(15) not null, branch-city char(30), assets integer)

26. CREATE TABLE 문장구조: 무결성제약 조건 • NOT NULL • PRIMARY KEY(A1, . . ., An) • check(P), 여기서 P는 predicate (술어) • 예)branch-name을 branch의 주 키로 하고 assets의 값은 음수가 안되도록 하라. create table branch (branch-name char(15) not null branch-city char(30), assets integer, primary key (branch-name), check(assets >= 0)) • SQL-92에서는 attribute상의 primary key선언에 의해 자동으로 not null을 보장한다.

27. DROP/ALTER TABLE 문의구조 • DROP TABLE명령 • 데이터베이스로부터 제거될 relation에 관한 모든 정보를 삭제 • ALTER TABLE명령 • 기존 relation에 attribute를 추가하는데 사용 • relation내의 모든 tuple에는 새로운 attribute의 값으로 널이 할당된다. • ALTER TABLE명령의 형식 alter table r add A D 여기서 A는 relationr에 추가될 attribute명, D는 A의 도메인 • alter table명령은 relation의 attribute를 제거하는데도 사용 alter table r drop column A 여기서 A는 relationr의 attribute명

28. Dynamic SQL • 실행시 프로그램이 구축되어 SQL 질의를 수행 • C 프로그램 내에서 동적 SQL의 사용 예 char * sqlprog = “update account set balance = balance  1.05 where account-number = ?” EXEC SQL prepare dynprog from :sqlprog; char account[10] = “A-101”; EXEC SQL execute dynprog using :account; • ? 의 의미: SQL 문장이 run-time시에 할당되는 값을 보관하는 holder

29. Integrity Constraints를 위한 SQL • Integrity Constraints (IC) are rules that the data in the DB must abide by • IC defines the semantics of the DB • X = Y + 1 ; /*syntactically correct • but if X is float and Y is string, semantically wrong • 예) 직원 이름 나이 월급 홍길동 35 7000 김철수 2 -5 /* syntactically correct /* semantically wrong

30. Integrity Constraints • DB의 변경에 대해서 • 데이터의 일관성을 잃지 않도록 보장 • 데이터베이스를 우발적인 손상으로부터 보호 • 종류 • Domain constraints • Referential constraints => foreign key를 의미 • Assertions • Triggers • Functional dependency => 이를 지원하는 SQL 문법은 없음

31. Integrity Constraints: referential integrity • Informal Definition • Referential Integrity (참조성 제한) = Foreign Key Constraint (외래키 제한) • 한 table의 attribute(set)가 다른 table의 primary key를 reference 하는 것 • Formal definition • r1(R1)과 r2(R2)를 각각 주키K1과 K2를 가진 relation • r2내 각각의 t2에 대해 t1[K1] = t2[]인 r1내에 tuplet1이 존재해야 한다면, R2의 부분 집합 는 relationr1의 K1을 참조하는 외래 키. • 참조 무결성 제약 조건: (r2)  K1(r1)

32. Integrity Constraints: referential integrity • Referential Integrity 수행 (r2)  k1(r1) ① Insert t1 into r1 → no problem ② Insert t2 into r2 → make sure t2r2 such that t1[]=t2[k2]. ③ Delete t1 from r1→ =t1[k1](r2) must be empty or reject or cascade delete or put NULL* ④ Delete t2 from r2 → no problem *Exception : primary key 는 NULL이 될 수 없으므로 참조 무결성의 예외

33. Referential integrity 지원 SQL 예제 • create table account • (branch-name char(15), • account-number char(10)not null, • balance integer, • primary key (account-number), • foreign key (branch-name) referencesbranch on update cascade ) • create table depositor • (customer-name char(20)not null, • account-number char(10)not null, • primary key (customer-name, account-number), • foreign key (account-number) referencesaccount on delete restrict, • foreign key (customer-name) references customer) create table customer (customer-name char(20)not null, customer-street char(30), customer-city char(30), primary key(customer-name)) create table branch (branch –name char(15)not null, branch-city char(30), assetsinteger, primary key (branch-name))

34. Assertions: Something that you say or write that you strongly believe Integrity Constraints: assertions • CREATEASSERTION <name> CHECK <predicate> • general constraint 표현방법 중 하나 • 예) for all x, P(x)의 표현  in SQL x, P(x) (즉, x P(x) = x P(x) = x P(x) • CREATE ASSERTION sum-constraint CHECK (NOT EXISTS ( SELECT * FROM branch WHERE (SELECT SUM(amount) FROM loan WHERE loan.b_name=branch.b_name) >= (SELECT SUM(amount) FROM account WHERE account.b_name=branch.b_name)))

35. Integrity Constraints: assertions • DB states • DB schema = { R1, … , Rn} • DB instance = { r1, … . rn} • DB state = DB instance • DB is in a valid state if it satisfies all integrity constraints • All assertions are tested when created • Modification to DB is only allowed if it does not cause violation • Assertion 정의  시스템은 그 타당성을 테스트  계산 cost가 매우 큼 • 그래서, 꼭 필요한 경우에 한하여 작성함 DB state 1 DB state 2 Update

36. Integrity Constraints: triggers • 데이터베이스에 특정 연산 (insert/delete/update)이 수행되었을 때, 기정의된 조건이 만족이 되었을 때 자동 수행되는 procedure • DEFINE TRIGGER overdraft ON UPDATE OF accountT (IF NEW T.balance< 0 /* Condition */ THEN (INSERT INTO loan VALUES /* Action */ (T.branch_name, T.account#, - NEW T.balance) INSERT INTO borrower (SELECT customer_name, account# FROM depositor WHERE T.account# = depositor.account#) UPDATE accountS SET S.balance= 0 WHERE S.account# = T.account#))