1 / 98

Chapter 4 SQL

Chapter 4 SQL. 4.1 Basic Structure 4.2 Set Operations 4.3 Aggregate Functions 4.4 Null Values 4.5 Nested Subqueries 4.6 Views 4.7 Complex Queries 4.8 Recursion in SQL 4.9 Modification of the Database 4.10 Joined Relations

bethany-russo
Download Presentation

Chapter 4 SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 4 SQL 4.1 Basic Structure 4.2 Set Operations 4.3 Aggregate Functions 4.4 Null Values 4.5 Nested Subqueries 4.6 Views 4.7 Complex Queries 4.8 Recursion in SQL 4.9 Modification of the Database 4.10 Joined Relations 4.11 Data-Definition Language 4.12 Embedded SQL 4.13 Exercises

  2. 4.1 Basic Structure The Basic Structure of an SQL expression consists of three clauses: select, from and where: ①The select clause corresponds to the projectionoperation of the relational algebra. It is used to list the attributes desired in the result of a query. ② The from clause corresponds to the Cartesian-product operation of the relational algebra. It lists the relations to be scanned in the evaluation of the expression. ③The where clause corresponds to the selection predicate of the relational algebra. It consists of a predicate involving attributes of the relations that appear in the from clause.

  3. 4.1 Basic Structure A typical SQL query has the form select A1,A2,……An fromr1,r2……rm wherep Each Ai represents an attribute and each ria relation. P is a predicate. The query is equivalent to the relational-algebra expression. ∏A1,A2,……An(p(r1r2……  rm))

  4. loan-number branch-name amount branch-name L-11 L-14 L-15 Perryridge Perryridge Mianus Perryridge Perryridge Mianus 900 1500 2000 loan 4.1.1 The Select Clause SQL allows duplicates in relations as well as in the results of SQL expressions. Examples: ① Find the names of all branches in the loan relation Loan-schema=( branch-name, loan-number ,amount) selectbranch-namefrom loan

  5. branch-name loan-number branch-name amount Perryridge Mianus L-11 L-14 L-15 Perryridge Perryridge Mianus 900 1500 2000 loan 4.1.1 The Select Clause ② If you want to force the elimination of duplicates, you can insert the keyword distinct after select. selectdistinct branch-name from loan

  6. loan loan-number branch-name amount loan-number branch-name amount L-11 L-14 L-15 L-11 L-14 L-15 Perryridge Perryridge Mianus Perryridge Perryridge Mianus 900 1500 2000 900 1500 2000 4.1.1 The Select Clause ③The asterisk symbol “” can be used to denote “all attributes”. Loan-schema=(branch-name, loan-number ,amount) select from loan selectbranch-name,loan-number,amount from loan

  7. loan loan branch-name loan-numberamount loan-number branch-name amount L-11 L-14 L-15 Perryridge Perryridge Mianus 9000 15000 20000 L-11 L-14 L-15 Perryridge Perryridge Mianus 900 1500 2000 4.1.1 The Select Clause ④ The select clause can also contain arithmeticexpressions involving the operators,+, -, ,and /, and operating on constants or attributes of tuples. select branch-name,loan-number,amount 10 from loan

  8. 4.1.2 The Where Clause SQL uses the logical connectives and, or, and not. The operands of the logical connectives can be expressions involving the comparison operators <, <=, >, >=, =, and <>. Examples: ① Find all loan numbers for loans made at the Perryride branch with loan amounts greater that $1200. Loan-schema=( branch-name, loan-number ,amount) select loan-number fromloan wherebranch-name=“Perryridge” and amount>1200

  9. 4.1.2 The Where Clause Between: ② Find the loan number of those loans with loan amounts between $90,000 and $100,000. Loan-schema=( branch-name, loan-number ,amount) select loan-number from loan whereamountbetween 90000 and 100000 whereamount>= 90000 andamount<= 100000 Similarly, we can use the not between comparison operator.

  10. 4.1.3 The from Clause Thefrom clauseby itself defines aCartesian productof the relations in the clause. Examples: ①For all customers who have a loan form the bank, find their names and loan number and loan amount. Borrower-schema=(customer-name,loan-number) Loan-schema=( branch-name,loan-number,amount) selectdistinct customer-name, borrower.loan-number,amount from borrower, loan whereborrower.loan-number=loan.loan-number

  11. loan borrower customer-name loan-number branch-name amount loan-number L-10 L-14 L-15 L-11 L-14 L-15 Perryridge Perryridge Mianus 900 1500 2000 Hayes Johnson Smith borrower.loan-number customer-name loan.loan-number branch-name amount L-14 L-15 Perryridge Mianus Johnson Smith 1500 2000 L-14 L-15 4.1.3 The from Clause whereborrower.loan-number=loan.loan-number selectdistinct customer-name, borrower.loan-number,amount

  12. 4.1.3 The from Clause ②Find the names and loan numbers of all customers who have a loan at the Perryridge branch. Borrower-schema=(customer-name,loan-number) Loan-schema=( branch-name,loan-number,amount) selectdistinct customer-name, borrower.loan-number from borrower, loan whereborrower.loan-number=loan.loan-number and branch-name=“Perryridge”

  13. loan-number amount loan-number amount loan loan d namount 4.1.4 The Rename Operation Problems: ①Two relations in the from clause may have attributes with the same name, in which case a attribute name is duplicated in the result. Example: ②If we used an arithmetic expression in the select clause the resultant attribute does not have a name. Example: select branch-name, amount 100 ③Even if a attribute name can be derived from the base relations as in the preceding example, we may want to change the attribute name in the result.

  14. 4.1.4 The Rename Operation SQL provides a way of renaming the attributes of a result relation. Example: ① If we want the attribute name loan-number to be replaced with the name loan-id. Then we can write. selectdistinct customer-name, borrower.loan-number as loan-id from borrower, loan whereborrower.loan-number=loan.loan-number and branch-name=“Perryridge”

  15. Brighton 5100 assets Brooklyn 5000 Redwood 6800 6200 Rye 4800 4900 … 4.1.5 Tuple Variables Tuple variables are defined in the from clause via the use of the as clause. Tuple variables are most useful for comparing two tuples in the same relation. Example: ① Find the names of all branches that have assets greater than at least one branch located in Brooklyn. Branch-schema=(branch-name, branch-city,assets) >

  16. branch-name branch-city assets branch-name branch-city assets 5000 4900 6200 5100 Brooklyn Rye Brooklyn Palo Pownal Redwood Brighton Mianus 5000 4900 6200 5100 Brooklyn Rye Brooklyn Palo Pownal Redwood Brighton Mianus branch T branch S selectdistinctT.branch-name frombranch as T, branch as S whereT.assets＞S.assets and S.branch-city=“Brooklyn” 4.1.5 Tuple Variables >

  17. 4.1.6 String Operations like: ⑴ Percent(%): The % character matches any substring. ⑵ Underscore( _ ): The _ character matches any character. Examples: ① “Perry%” matches any string beginning with “Perry”. ② “_ _ _” matches any string of exactly three characters.

  18. customer-name customer-street customer-city customer-name Pittsfield Brooklyn Rye Stamford Adams Brooks Curry Hayes Spmain main Nmainh Park Adams Brooks Curry 4.1.6 String Operations ③ Find the names of all customers whose street address includes the substring “main” Customer-schema=(customer-name,customer-street,customer-city) selectcustomer-name from customer where customer-street like“%main%”

  19. 4.1.6 String Operations ⑶ The escape character is used immediately before a special pattern character to indicate that the special pattern character is to be treated like a normal character. Examples: using a backslash (\) as the escape character ① like “ab\%cd%” escape “\” matches all string beginning with “ab%cd” ② like “ab\\cd%” escape “\”matches all strings beginning with “ab\cd”.

  20. 4.1.7 Ordering the Display of Tuples Order by clause: descasc Examples: ① To list in alphabetic order all customers who have a loan at the Perryridge branch. Borrower-schema=(customer-name,loan-number) Loan-schema=( branch-name,loan-number,amount) selectdistinct customer-name from borrower, loan whereborrower.loan-number=loan.loan-number and branch-name=“Perryridge” order by customer-name (asc)

  21. loan-number branch-name amount loan-number branch-name amount L-11 L-15 L-14 Perryridge Perryridge Mianus L-14 L-15 L-11 Mianus Perryridge Perryridge 900 1500 1500 1500 1500 900 asc desc 4.1.7 Ordering the Display of Tuples Ordering can be performed on multiple attributes ② List the entire loan relation in descending order of amount. If several loans have the same amount, we order than in ascending order by loan number. Loan-schema=( branch-name,loan-number ,amount) select﹡ from loan order by amount desc, loan-number asc

  22. customers who have a loan customers who have an account 4.2 Set Operations union, intersect, except：∪, ∩, － 1. The Union Operation Example: ①Find all customers having a loan, an account or both at the bank. (select customer-name from depositor) union (select customer-name from borrower) Union operation automatically eliminates duplicates. If we want to retain all duplicates, we must write union all in place of union.

  23. 4.2 Set Operations 2. The Intersect Operation Example: ①Find all customers who have both a loan and an account at the bank (select customer-name from depositor) intersect (select customer-name from borrower) If we want to retain all duplicates, we must write intersect all in place of intersect.

  24. 4.2 Set Operations 3. The Except Operation Example: ①Find all customers who have an account but no loan at the bank (select customer-name from depositor) except (select customer-name from borrower) If we want to retain all duplicates, we must write except all in place of except.

  25. account-number branch-name balance Perryridge Redwood Perryridge L-16 L-93 L-98 500 700 900 select avg(balance) from account where branch-name=“Perryridge” 700 4.3 Aggregate Functions 1.Aggregate functions are functions that take a collection of values as input and return a single value. SQL offers five built-in aggregate functions. Average: avg Minimum: min Maximum: max Total: sum Count: count Example: ①Find the average account balance at the Perryridge branch.

  26. branch-name ebalance account-number branch-name balance Perryridge Redwood Perryridge Redwood Perryridge L-16 L-18 L-21 L-25 L-33 600 700 800 900 700 Perryridge Redwood 700 800 4.3 Aggregate Functions 2. group by clause. Examples: ①Find the average account balance at each branch. select branch-name, avg(balance) as ebalance group bybranch-name from account

  27. 4.3 Aggregate Functions ② Find the number of depositor for each branch. Depositor-schema=(customer-name,account-number) Account-schema=(branch-name,account-number, balance) select branch-name, count(distinctcustomer-name) from depositor,account where depositor.account-number=account.account-number group bybranch-name

  28. branch-name ebalance account-number branch-name balance Perryridge Redwood Perryridge Redwood Perryridge L-16 L-18 L-21 L-25 L-33 600 700 800 900 700 Perryridge Redwood 700 800 branch-name ebalance Redwood 800 4.3 Aggregate Functions 3.having clause Example: ①we might be interested in only those branches where the average account balances is more than $750. ebalance> $750

  29. ebalance> $750 branch-name ebalance Perryridge Redwood 700 800 branch-name ebalance Redwood 800 4.3 Aggregate Functions select branch-name, avg(balance) from account group bybranch-name having avg(balance)>750

  30. customer-name customer-street customer-city Pittsfield Brooklyn Rye Stamford Adams Brooks Curry Hayes Spmain main Nmainh Park 4.3 Aggregate Functions 4. At times, we wish to treat the entire relation as a single group, in such cases, we do not use a group by clause. Example: ①Find the average balance for all account selectavg(balance) from account 5.count (﹡) Example:①Find the number of tuples in the customer relation selectcount(﹡) from customer 4

  31. 4.3 Aggregate Functions 6. A where clause and a having clause appear in the same query Example:①Find the average balance for each customer who lives in Harrison and has at least three accounts. Account-schema=(branch-name,account-number, balance) Depositor-schema=(customer-name,account-number) Customer-schema=(customer-name,customer-street,customer-city) ①innerjion depositor, account, customer ② customer-city=“Harrison” (where) ③ group bydepositor.customer-name (having) ④ count(distinct depositor.account-number)>=3

  32. 4.3 Aggregate Functions ② Find the average balance for each customer who has at least three accounts. (having) ① Find the average balance for each customer who lives in Harrison. (where) select depositor.customer-name, avg(balance) from depositor, account, customer where depositor.account-number=account.account-number and depositor.customer-name=customer.customer-name and customer-city=“Harrison” group bydepositor.customer-name having count(distinct depositor.account-number)>=3

  33. 4.4 Null Valus SQL allows the use of null values to indicate absence of information about the value of an attribute. We use the special keyword null in a predicate to test for a null value. Example: ① Find all loan numbers that appear in the loan relation with null value for amount. select loan-number from loan where amount isnull select sum(amount) from loan

  34. 4.5 Nested Subqueries A subquery is a select-from-where expression that is nested within another query. 1. Set Membership inandnot in Examples: ① Find all customers who have both a loan and an account at the bank. Borrower-schema=(customer-name, loan-number) Depositor-schema=(customer-name,account-number) select distinct customer-name from borrower wherecustomer-namein (selectcustomer-name fromdepositor)

  35. loan-number customer-name customer-name account-number Hayes Johnson A-102 A-101 Hayes Curry L-16 L-93 depositor borrower customer-name Hayes Curry ② select distinct customer-name from borrower where customer-name in ③ select customer-name Hayes 4.5 Nested Subqueries ①selectcustomer-name fromdepositor in Hayes not in Johnson

  36. 4.5 Nested Subqueries ② Find all customers who have both an account and a loan at the perryridge branch. selectdistinct customer-name from borrower, loan whereborrower.loan-number=loan.loan-number and branch-name=“perryridge” and (“perryridge” ,customer-name) (branch-name,customer-name)in (selectbranch-name,customer-name fromdepositor, account wheredepositor.account-number=account.account-number)

  37. (selectbranch-name,customer-name fromdepositor, account wheredepositor.account-number=account.account-number) account-number branch-name balance customer-name account-number Perryridge Redwood Mianus L-16 L-93 L-98 500 700 900 Hayes Curry Smith L-16 L-93 L-95 account depositor branch-name customer-name Perryridge Redwood Hayes Curry 4.5 Nested Subqueries

  38. loan-number branch-name amount customer-name loan-number Perryridge Redwood Mianus Hayes Smith Curry 900 1500 800 L-11 L-14 L-15 L-11 L-14 L-16 loan borrower branch-name customer-name Perryridge Redwood Hayes Curry 4.5 Nested Subqueries (branch-name,customer-name) (Perryridge, Hayes) Hayes have a loan at the Perryridge branch in Hayes and Curry have a account Hayes have both an account and a loan at the perryridge branch

  39. 4.5 Nested Subqueries ③ Find all customer who do have a loan at the bank but do nothave an account at the bank. select distinct customer-name fromborrower wherecustomer-name not in (select customer-name from depositor)

  40. 4.5 Nested Subqueries ④ Select the names of customers who have a loan at the bank, and whose names are neither “smith” nor “jones” selectdistinct customer-name fromborrower wherecustomer-name not in (“smith”, “jones”)

  41. 4.5 Nested Subqueries 2. Test for Empty Relations exist and not exist Examples: ① Find all customers who have both an account and a loan at the bank Borrower-schema=(customer-name, loan-number) Depositor-schema=(customer-name,account-number) selectdistinct customer-name from borrower whereexists (select * from depositor Where depositor.customer-name=borrower.customer-name)

  42. customer-name loan-number customer-name account-number Hayes Johnson Hayes Curry A-102 A-101 L-16 L-93 depositor borrower customer-name ② (select * Hayes from depositor Where depositor.customer-name=‘Hayes’) 4.5 Nested Subqueries ①borrower(Hayes,A-102) (Johnson,A-101) ③whereexists(‘Hayes’, ‘L-16’) True ④select customer-name form (Hayes,A-102)

  43. Brighton Downtown B Smith brighton downtown Brighton Downtown curry brighton Redwood brighton downtown A all the branches in brooklyn … smith not exists true 4.5 Nested Subqueries ②Find all customers who have an account at all the branches located in brooklyn. contain except Null relation A contains relation B not exists(B except A) smith

  44. B Brighton Downtown B A Brighton Downtown A smith 4.5 Nested Subqueries Depositor-schema=(customer-name,account-number) Account-schema=(branch-name,account-number, balance) Branch-schema=(branch-name,branch-city,assets) not exists (select branch-name from branch where branch-city=“brooklyn”) ( except (selectcustomer-name,branch-name fromdepositor asT, account asR whereT.account-number=R.account-number) )

  45. 4.5 Nested Subqueries Depositor-schema=(customer-name, account-number) Account-schema=(branch-name, account-number, balance) select distinct S.customer-name from depositor asS ((select branch-name from branch where branch-city=“brooklyn”) Where not exists except (select R.branch-name from depositor asT, account asR whereT.account-number=R.account-numberand S.customer-name=T.customer-name))

  46. Smith c-11 C-10 C-11 curry c-11 c-10 all the courses number … 4.5 Nested Subqueries ③ find the names of all students who have not chosen all courses.(who choose all courses?) S-schema=(sno,sname,ssex,sage) PK =sno C-schema=(cno,cname,teacher) PK=cno SC-schema=(sno,cno,score) PK =(sno,cno) not contain Smith

  47. 4.5 Nested Subqueries S-schema=(sno,sname,ssex,sage) PK =sno C-schema=(cno,cname,teacher) PK=cno SC-schema=(sno,cno,score) PK =(sno,cno) select sname from s whereexists false (select * true from c false wherenot exists true (select * from sc wheresc.cno=c.cnoandsc.sno=s.sno))

  48. Smith c-11 C-10 C-11 Curry c-10 c-11 NO.1 … 4.5 Nested Subqueries ④ find the names of all students who have chosen the courses includingone course which the NO.1 student has chosen at lease. S-schema=(sno,sname,ssex,sage) PK =sno C-schema=(cno,cname,teacher) PK=cno SC-schema=(sno,cno,score) PK =(sno,cno) contain Smith Curry

  49. sy sx sno cno score sno cno score C-10 C-11 C-10 C-11 C-11 90 89 78 86 89 No.1 No.1 No.2 No.2 No.3 C-10 C-11 C-10 C-11 C-11 90 89 78 86 89 No.1 No.1 No.2 No.2 No.3 4.5 Nested Subqueries S-schema=(sno,sname,ssex,sage) PK =sno C-schema=(cno,cname,teacher) PK=cno SC-schema=(sno,cno,score) PK =(sno,cno) select sname from s true whereexists false (select * from sc assx where sno=“NO.1” true and exists (select * from sc assy false wheresy.sno=s.snoandsx.cno=sy.cno))

  50. sy sx sno cno score sno cno score C-10 C-11 C-10 C-11 C-11 90 89 78 86 89 No.1 No.1 No.2 No.2 No.3 C-10 C-11 C-10 C-11 C-11 90 89 78 86 89 No.1 No.1 No.2 No.2 No.3 4.5 Nested Subqueries ⑤ Find the names of all students who choose the courses include all courses which NO.1 students has chosen at least. select sname from s true wherenotexists false (select * from sc assx where sno=“NO.1” false and not exists (select * true from sc assy wheresy.sno=s.snoandsx.cno=sy.cno))

More Related