PART 2

1. PART 2 RELATIONAL DATABASES

2. Chapter 3 SQL

3. Three Parts in Chapter 3 • Functions and Usage of SQL • data definition: §3.2, DDL • data query/retrieve: §3.3——§3.8, Select clause • data manipulation: § 3.9 Modification of the Database Database System Concepts - Chapter3 SQL -

4. §3.1 Background • Database query languages • commercial implementation of relational operations in DBS • interactive tools for users to develop , use/access, and maintain DBS • DBS access: create, retrieval, insert, delete, update, etc • “Query” • generalized definition • define, retrieve, modify, control etc. on DB Database System Concepts - Chapter3 SQL -

5. 3.1 Background (cont.) • SQL • Structured Query Language • on the basis of the relational algebra and the tuplerelationalcalculus • first implemented inSystem Rby IBM in 1970s • standardized by ANSI and ISO, SQL-86, SQL-89, SQL- 92, SQL-99, OO-SQL, the most recent version is SQL-2003 • widely used for commercial DBS Database System Concepts - Chapter3 SQL -

6. 3.1 Background (cont.) • Parts/functions of SQL • data definition language (DDL) • define, delete, modify on schemas, views and index • interactive data-manipulation language (DML) • query/retrieve, insert , delete, update on tuples in DB • data control • integrity constraints specifying • authorization: security control, specifying access rights • transaction management • embedded and dynamic SQL Database System Concepts - Chapter3 SQL -

7. 3.1 Background (cont.) • 9basic operators in SQL Database System Concepts - Chapter3 SQL -

8. 3.1 Background (cont.) • Case study used in this chapter Fig. 3.1 Schema of banking enterprise Database System Concepts - Chapter3 SQL - return

9. §3.2 Data Definition • DDL in SQL allows the specification of information about each relation, including • the schema for each relation or view -----create table/view • the domain of values associated with each attribute • integrity constraints • the set of indices to be maintained for each relations • security and authorization information for each relation • the physical storage structure of each relation on disk 3.2.1 Basic Domain Types • char(n), varchar(n), int, smallint, numeric(p, d), real, double precision, float(n), date, time, timestamp, interval Database System Concepts - Chapter3 SQL -

10. 3.2.2 Schema Definition (cont.) • An SQL relation is defined using the create table command create table r(A1D1, A2D2, ..., An Dn,(integrity-constraint1), ..., (integrity-constraintk)) • r is the name of the relation • each Ai is an attribute name in the schema of relation r • Di is the data type of values in the domain of attribute Ai • The most important integrity constraint is the primary-key specification • primary key (Aj1, Aj2, …, Ajm) Database System Concepts - Chapter3 SQL -

11. 3.2.2 Schema Definition (cont.) • E.g. create table branch (branch-name char(15)branch-city char(30),assets numeric(16,2) primary key (branch-name) • A newly created table is empty initially, we can use insert command to load data into the table • insert intoaccount values (‘A-9372’, ‘Perryridge’, 1200) • The delete command removes tuples from the table • delete from account Database System Concepts - Chapter3 SQL -

12. §3.2.2 Schema Definition (cont.) • The drop table command deletes all information (tuples and schema) about the dropped relation from the database • drop tabler • compared to delete fromr • The alter table command is used to add or delete/drop attributes to an existing relation • alter table r add A D • where A is the name of the attribute to be added to relation r and D is the domain of A • all tuples in the relation are assignednull as the value for the new attribute • e.g. alter table branch add branch-street char(30) Database System Concepts - Chapter3 SQL -

13. §3.2.2 Schema Definition (cont.) • alter table r drop A • where A is the name of an attribute of relation r • e.g. alter table branch drop branch-street • dropping of attributes not supported by many databases Database System Concepts - Chapter3 SQL -

14. §3.3 Basic structure— Select clause for data query query result : • A typical SQL query has the formselect A1, A2, ..., Anfromr1, r2, ..., rmwhere P • Ais represent attributes • ris represent relations • P is a predicate • This query is equivalent to the relational algebra expression  A1, A2, ..., An(P (r1 x r2 x ... x rm)) • the result of an SQL query is a relation • For more details about complete syntax definition for Select clause, refer to Appendix A. Syntax Definition of Select Clause t1 t2 tk Database System Concepts - Chapter3 SQL -

15. 3.3.1 The select Clause • The select clause list the attributes desired in the result of a query • corresponding to the projection operation of the relational algebra • E.g. Find the names of all branches in the loan relationselect branch-namefrom loan • in the “pure” relational algebra syntax, the query would be  branch-name(loan) Database System Concepts - Chapter3 SQL -

16. 3.3.1 The select Clause (cont.) • SQL allows duplicates in relations as well as in query results. • To force the elimination of duplicates, insert the keyword distinct after select. • E.g. Find the names of all branches in the loan relations, and remove duplicates select distinct branch-namefrom loan Database System Concepts - Chapter3 SQL -

17. 3.3.1 The select Clause (cont.) • The keyword all specifies that duplicates not be removed select allbranch-namefrom loan • An asterisk * in the select clause denotes “all attributes” select *from loan • The select clause can contain arithmetic expressions involving the operation, +, –, , and /, and operating on constants or attributes of tuples • corresponding to extended projection operation Database System Concepts - Chapter3 SQL -

18. 3.3.1 The select Clause (cont.) • E.g. The query: selectloan-number, branch-name, amount *100from loan would return a relation which is the same as the loan relations, except that the attribute amount is multiplied by 100 Database System Concepts - Chapter3 SQL -

19. 3.3.2 The where Clause • The where clause specifies conditions that the result must satisfy • corresponding to the selection predicate p in the relational algebra operation p • E.g. To find all loan number for loans made at the Perryridgebranch with loan amounts greater than $1200.select loan-numberfrom loanwhere branch-name=‘Perryridge’ and amount > 1200 Database System Concepts - Chapter3 SQL -

20. 3.3.2 The where Clause (cont.) • In where clause • comparison results can be combined using the logical connectives and, or, and not • comparisons can be applied to results of arithmetic expressions • SQL includes between and not between comparison operator • E.g. Find the loan number of those loans with loan amounts between $90,000 and $100,000 • select loan-numberfrom loanwhere amountbetween 90,000 and 100,000 Database System Concepts - Chapter3 SQL -

21. borrower loan 3.3.3 The from Clause • The fromclause lists the relations involved in the query • corresponding tothe Cartesian product operation of the relational algebra • E.g. !!!find the Cartesian productborrower × loanselect *from borrower, loan • As a simple representation of “natural join” • !!!e.g. corresponds to select * from borrower, loanwhere borrower.loan-number = loan.loan-number Database System Concepts - Chapter3 SQL -

22. 3.3.3 The from Clause (cont.) • E.g. Find the name, loan number and loan amount of all customers having a loan at the Perryridge branch • select customer-name, borrower.loan-number, amountfrom borrower, loanwhere borrower.loan-number = loan.loan-number and branch-name = ‘Perryridge’ Database System Concepts - Chapter3 SQL -

23. 3.3.4 The Rename Operation • The SQL allows renaming relations and attributes using the asclause to rename the attribute name, to avoid the same names for different attributes in the relations as query results old-name as new-name • as clause can appear in selectand fromclauses • E.g. Find the name, loan number, and loan amount of all customers; rename the column name loan-numberas loan-id select customer-name, borrower.loan-numberas loan-id, amountfrom borrower, loanwhere borrower.loan-number = loan.loan-number Database System Concepts - Chapter3 SQL -

24. 3.3.5 Tuple Variables • Tuple variables are defined in the from clause via the use of the as clause • E.g.1 Find the name, loan number and loan amount of all customers having a loan at some branches • E.g.2 Find the names of all branches that have greater assets than some branch located in Brooklyn • /*涉及到对branch关系中属性assets的不同值的比较 select customer-name, T.loan-number, S.amountfrom borrower as T, loan as Swhere T.loan-number = S.loan-number Database System Concepts - Chapter3 SQL -

25. 3.3.5 Tuple Variables (cont.) • select distinct T.branch-namefrom branch as T, branch as Swhere T.assets > S.assets and S.branch-city = ‘Brooklyn’ • /*利用T和S区分不同的branch!!!!!, 实现了对同一属性的不同值的比较 Database System Concepts - Chapter3 SQL -

26. 3.3.6 String Operations in where Subclause • SQL specifies strings by enclosing them in single quotes, e.g. ‘Perryridge’ • SQL introduces a series of operations on strings, of which the most commonly used operation is pattern-matching operator for comparisons on character strings select customer-namefrom customerwherecustomer-street like ‘%Main%’ • For more details, refer to Appendix B String Operations in SQL Database System Concepts - Chapter3 SQL -

27. 3.3.7 Ordering the Display of Tuples • The order by clause causes the tuples in the result of query to appear in sorted order, i.e. ascending order or descending order, denoted by keywords asc or desc respectively • ascendingorder is the default • E.g. List in alphabetic descending order the names of all customers having a loan in Perryridgebranch select distinct customer-namefrom borrower, loanwhere borrower loan-number - loan.loan-number and branch-name = ‘Perryridge’order bycustomer-name desc Database System Concepts - Chapter3 SQL -

28. 3.3.8 Duplicates • Viewing relations with duplicates as multisets, SQL can define not only what tuples will appear in the result of a query, but also how many copies of these tuples appear in the result • The duplicate semantics of select clause is based on multiset versions of the relational algebra operators • For more details, refer to Appendix C Duplicates in Select Clause Database System Concepts - Chapter3 SQL -

29. §3.4 Set Operations • In SQL, the set operations union, intersect, and except operate on relations and correspond to the relational algebra operators and  • each of the above operations automatically eliminates duplicates • to retain all duplicates use the correspondingmultiset versions union all, intersect all and except all • E.g.1 Find all customers who have a loan, an account, or both • (selectcustomer-name from depositor)union(selectcustomer-name from borrower) Database System Concepts - Chapter3 SQL -

30. 3.4 Set Operations (cont.) • E.g2. Find all customers who have both a loan and an account • (selectcustomer-name from depositor)intersect(selectcustomer-name from borrower) • E.g3. Find all customers who have an account but no loan • (selectcustomer-name from depositor)except(selectcustomer-name from borrower) Database System Concepts - Chapter3 SQL -

31. §3.5 Aggregate Functions • The following aggregate functions operate on the set (or multiset) of values of a column of a relation, and return a value • avg: average value, min: minimum value, max: maximum value, sum: sum of values, count: number of values • these functions corresponds to G1, G2, …, GngF1( A1), F2( A2),…, Fn( An) (E) Database System Concepts - Chapter3 SQL -

32. 3.5 Aggregate Functions (cont.) • How to conduct group by subclause and aggregate function • SelectA1, A2, ., ag_fun(Ai),.., Aj, .., Ak,…, An • From r1, r2, ..., rm • Where P1 • Group by Aj • havingP2 • notes: • P1 is defined on all attributes of r1, r2, ..., rm • Aj must be listed in select subclause • P2 is defined on A1, A2, ., Ai,..,Aj ,… , Ak, …, An, as the constraints on the group formed by Group byAj, so ag_fun (Ak) appear in P2 Database System Concepts - Chapter3 SQL -

33. 3.5 Aggregate Functions (cont.) • step1. temporal relation T1 is derived from SelectA1, A2,…., Ai,..., Aj, .., Ak,…, An From r1, r2, ..., rm Where P1 • step2. T1 is grouped on Aj, resulting in temporal relation T2 • step3. if having subclause exists, then the group, which is not in accordance with P2, is filtered out of T2 • step4. SelectA1, A2, ., ag_fun(Ai),.., Aj, .., Ak,…, An From T2 Database System Concepts - Chapter3 SQL -

34. 3.5 Aggregate Functions (cont.) • E.g.1 Find the average account balance at the Perryridge branch ( in accountrelation) • select avg (balance) from accountwhere branch-name = ‘Perryridge’ /*定义在多重集合上的计算 avg Database System Concepts - Chapter3 SQL -

35. 3.5 Aggregate Functions (cont.) • E.g.2 Find the number of tuples in the customer relation • select count (*)from customer • E.g.3 Find the number of depositors in the bank • select count (distinct customer-name) /*删除重复元组from depositor Database System Concepts - Chapter3 SQL -

36. 3.5 Aggregate Functions (cont.) • In Select clause, group by subclause is used to grouping tuples in the relations according to some attributes • E.g.4 Find the number of depositors for each branch • select branch-name, count (distinctcustomer-name)from depositor, accountwhere depositor.account-number = account.account-numbergroup by branch-name • note: attributes in select clause outside of aggregate functions must appear in group by list, e.g. branch-name Database System Concepts - Chapter3 SQL -

37. result relation , bycount on T temporal relation T(branch-name, customer-name) deriving from depositorandaccount

38. 3.5 Aggregate Functions (cont.) • Having clause is used to state a condition that applies to groups rather than tuples • predicates in the having clause are appliedafter the formation of groups whereas predicates in the where clause are applied before forming groups • E.g.5 Find the names of all branches where the average account balance is more than $1,200 • select branch-name, avg(balance)from accountgroup by branch-namehaving avg (balance) >= 1200 Database System Concepts - Chapter3 SQL -

39. result relation , byavg on T.balance temporal relation T(branch-name,balance) deriving from account

40. 3.5 Aggregate Functions (cont.) • E.g.6 Find the names of all branches where the average account balance is more than $1,200 • select branch-name, sum(balance)from accountgroup by branch-namehaving avg (balance) >= 1200 Database System Concepts - Chapter3 SQL -

41. result relation , byavg on T.balance temporal relation T(branch-name,balance) deriving from account

42. §3.6 Null Values • It is possible for tuples to have a null value, denoted by null, for some of their attributes • null signifies an unknown value or that a value does not exist. • The predicate is null can be used in Select clause to check for null values • E.g. Find all loan number which appear in the loan relation with null values for amount • select loan-numberfrom loanwhere amount is null • The result of any arithmetic expression involving null is null • e.g. 5 + null returns null Database System Concepts - Chapter3 SQL -

43. 3.6 Null Values (cont.) • Any comparison with null returns unknown • e.g. 5 < null, null <> null , null = null • In where clause, three-valued logic using the truth value unknown (i.e. null, introduced in SQL 1999) • OR • (unknown or true) = true, (unknown or false) = unknown(unknown or unknown) = unknown • AND • (true and unknown) = unknown, (false and unknown) = false,(unknown and unknown) = unknown Database System Concepts - Chapter3 SQL -

44. 3.6 Null Values (cont.) • NOT • (not unknown) = unknown • “P is unknown” evaluates to true if predicate P evaluates to unknown • Result of where clause predicate P is treated as false if it evaluates to unknown • Null values and aggregates • all aggregate operations except count(*) ignore tuples with null values on the aggregated attributes Database System Concepts - Chapter3 SQL -

45. 3.6 Null Values (cont.) • E.g. Total all loanamounts select sum (amount)from loan • if there are null values in amount attributes, above statement ignores null amounts • the result is null if there is no non-nullamount Database System Concepts - Chapter3 SQL -

46. §3.7 Nested (嵌套) Subqueries • SQL provides a mechanism for the nesting of subqueries, to implement more complex query • A subquery is a select-from-group expression that is nested within another query • the subquery is often nested in the where clause, and the having clause of another query • A common use of subqueries is to perform tests for set membership, set comparisons, and set cardinality 3.7.1 Set Membership • Using in or not in connective to test membership or absence membership in where clause Database System Concepts - Chapter3 SQL -

47. 3.7.1 Set Membership • E.g.1 Find all customers who have both an account and a loan at the bank • select distinct customer-namefrom borrowerwhere customer-name in (select customer-namefromdepositor) • E.g.2 Find all customers who have a loan at the bank but do not have an account at the bank • select distinct customer-namefrom borrowerwhere customer-name not in (select customer-namefrom depositor) Database System Concepts - Chapter3 SQL -

48. 3.7.1 Set Membership (cont.) • E.g.3 Find the names of all customers who have both an account and a loan at the Perryridge branch • select distinctcustomer-namefrom borrower, loanwhere borrower.loan-number = loan.loan-number andbranch-name = “Perryridge” and(branch-name, customer-name) in (select branch-name, customer-namefrom depositor, accountwhere depositor.account-number = account.account-number) 对应于关系customer Database System Concepts - Chapter3 SQL -

49. 3.7.2 Set Comparison • E.g.1 Find the names of branches that have assets greater than those of at least onebranch located in Brooklyn.( …至少比位于Brooklyn的某一家支行…） • select branch-namefrom branchwhere assets > some (select assetsfrom branchwhere branch-city = ‘Brooklyn’) • > some : 至少比1个大 Brooklyn各支行的资产值集合 Database System Concepts - Chapter3 SQL -

50. 3.7.2 Set Comparison (cont.) • compared with the example in §3.3.5 select distinct T.branch-namefrom branch as T, branch as Swhere T.assets > S.assets andS.branch-city = ‘Brooklyn’ • SQL allows < some/all, <= some/all, >= some/all, =some/all, and <>some/all comparisons • for more details, refer to Appendix D Some/All Set Comparisons Database System Concepts - Chapter3 SQL -