1 / 79

Comp 231 Database Management Systems

Comp 231 Database Management Systems. 4. Structured Query Language. Basic Structure. SQL is based on set and relational operations with certain modifications and enhancements

adelle
Télécharger la présentation

Comp 231 Database Management Systems

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. Comp 231 Database Management Systems 4. Structured Query Language Department of Computer Science and Engineering, HKUST Slide 1

  2. Basic Structure • SQL is based on set and relational operations with certain modifications and enhancements • A typical SQL query has the form:select A1, A2, …, Anfrom R1, R2, …, Rmwhere P - Ai represent attributes - Ri represent relations - P is a predicate. • This query is equivalent to the relational algebra expression:A1, A2, …, An(P(R1  R2  …  Rm)) • The result of an SQL query is a relation. I.e., SQL statements can be nested. Department of Computer Science and Engineering, HKUST Slide 2

  3. Projection • The select clause corresponds to the projection operation of the relational algebra. It is used to list the attributes desired in the result of a query. • Find the names of all branches in the loan relationselect branch-namefrom loanEquivalent to: branch-name(loan)or { t[branch-name] | t  loan } • An asterisk in the select clause denotes “all attributes”select *from loan Department of Computer Science and Engineering, HKUST Slide 3

  4. Duplicate Removal • SQL allows duplicates in relations as well as in query results. Use select distinct to force the elimination of duplicates.Find the names of all branches in the loan relation, and remove duplicatesselectdistinct branch-namefrom loan • The keyword all specifies that duplicates not be removed.selectall branch-namefrom loan force theDBMS to remove duplicates force theDBMS not to remove duplicates Department of Computer Science and Engineering, HKUST Slide 4

  5. Arithmetic Operations on Retrieved Results • The select clause can contain arithmetic expressions involving the operators,,, and , and operating on constants or attributes of tuples. • The query:select branch-name, loan-number, amount * 100 from loanwould return a relation which is the same as the loan relations, except that the attribute amount is multiplied by 100 Department of Computer Science and Engineering, HKUST Slide 5

  6. The where Clause • The where clause specifies conditions that tuples in the relations in the fromclause must satisfy. • Find all loan numbers for loans made at the Perryridge branch with loan amounts greater than $1200.select loan-numberfromloanwherebranch-name=“Perryridge” and amount >1200 • SQL allows logical connectives and, or, and not. Arithmetic expressions can be used in the comparison operators. • Note: attributes used in a query (both select and where parts) must be defined in the relations in the from clause. Department of Computer Science and Engineering, HKUST Slide 6

  7. The where Clause (Cont.) • SQL includes the between operator for convenience. • Find the loan number of those loans with loan amounts between $90,000 and $100,000 (that is,  $90,000 and  $100,000)select loan-numberfrom loanwhere amount between 90000 and 100000 Department of Computer Science and Engineering, HKUST Slide 7

  8. The from Clause • The from clause corresponds to the Cartesian product operation of the relational algebra. • Find the Cartesian product borrower  loanselect *from borrower, loanIt is rarely used without a where clause. • Find the name and loan number of all customers having a loan at the Perryridge branch.select distinct customer-name, borrower.loan-number from borrower, loanwhere borrower.loan-number = loan.loan-number and branch-name = “Perryridge” Department of Computer Science and Engineering, HKUST Slide 8

  9. The Rename Operation • Renaming relations and attributes using the as clause: old-name as new-name • Find the name and loan number of all customers having a loan at the Perryridge branch; replace the column name loan-number with the name loan-id. selectdistinct customer-name, borrower.loan-number as loan-idfrom borrower, loanwhere borrower.loan-number = loan.loan-number and branch-name = “Perryridge” Department of Computer Science and Engineering, HKUST Slide 9

  10. Tuple Variables/Alias • Tuple variables are defined in the from clause via the use of the “as” clause. • Find the customer names and their loan numbers for all customers having a loan at some branch.selectdistinct customer-name, T.loan-numberfrom borrower as T, loan as Swhere T.loan-number = S.loan-number • Tuple variable/Alias can be used as short hand, but it is more than just a short hand (see next slide) Department of Computer Science and Engineering, HKUST Slide 10

  11. branches in Brooklyn Tuple Variables/Alias • Find the names of all branches that have greater assets than some branch located in Brooklyn. select distinct T.branch-name from branch as T, branch as S where T.assets > S.assets and S.branch-city=“Brooklyn” branch branch S: a branch in Brooklyn T Does it returns branches within Brooklyn? Department of Computer Science and Engineering, HKUST Slide 11

  12. String Operations • Character attributes can be compared to a pattern: % matches any substring._ matches any single character. • Find the name of all customers whose street includes the substring ‘Main’. (Eg Mainroad, Smallmain Road, AMainroad,…)select customer-namefrom customerwhere customer-street like “%Main%” • How to match the name “Main%”: (Eg abcMain%, MainMain%,…)where customer-street like “Main\%” Use the escape “\” to specify % as a single character Department of Computer Science and Engineering, HKUST Slide 12

  13. Ordering the Display of Tuples • List in alphabetic order the names of all customers having a loan at Perryridge branch select distinct customer-namefrom borrower, loanwhere borrower.loan-number = loan.loan-number and branch-name = “Perryridge”order by customer-name • order by customer-name desc, amount ascdesc for descending order; asc for ascending order (default) • SQL must perform a sort to fulfill an order by request. Since sorting a large number of tuples may be costly, it is desirable to sort only when necessary. Department of Computer Science and Engineering, HKUST Slide 13

  14. Set Operations • The set operation union, intersect, and except operate on relations and correspond to the relational algebra operations ,  and . • Each of the above operations automatically eliminates duplicate; to retain all duplicates use the corresponding multiset versions union all, intersect all and except all.Suppose a tuple occurs m times in r and n times in s, then, it occurs: • m + n times in r union all s • min(m,n) times in r intersect all s • max(0,m-n) times in r except all s Department of Computer Science and Engineering, HKUST Slide 14

  15. Set operations • Find all customers who have a loan, an account, or both: (select customer-name from depositor)union (select customer-name from borrower) • Find all customers who have both a loan and an account. (select customer-name from depositor)intersect (select customer-name from borrower) • Find all customers who have an account but no loan. (select customer-name from depositor)except (select customer-name from borrower) Department of Computer Science and Engineering, HKUST Slide 15

  16. SQLAggregate Functions Department of Computer Science and Engineering, HKUST Slide 16

  17. Aggregate Functions • Operates on a column of a relation, and return a valueavg: average valuemin: minimum valuemax: maximum valuesum: sum of valuescount: number of values Department of Computer Science and Engineering, HKUST Slide 17

  18. balance account select balance from account where branch-name =“Perryridge” Avg() 120,000 Aggregate Functions(cont.) • Find the average account balance at the Perryridge branch. select avg(balance)from accountwhere branch-name=“Perryridge” Department of Computer Science and Engineering, HKUST Slide 18

  19. Aggregate Functions(cont.) • Find the numbers of tuples in the customer relation.select count(*)from customer • remember * stands for all attributes • compare to: select count(customer-city) from customer • Find the number of depositors in the bankselectcount (distinct customer-name)from depositor • distinct is redundant if you know customer-name is a key Department of Computer Science and Engineering, HKUST Slide 19

  20. Aggregate functions - Group by • Find the number of accounts for each branch. select branch-name, count( distinct account-number)from accountgroup by branch-name • For each group of tuples with the same branch-name, applyaggregate function count and distinct to account-number account table Department of Computer Science and Engineering, HKUST Slide 20

  21. correct Aggregate functions - Group by • Relational Algebra: G1,G2, … Gn F1 A1, F2 A2,…, Fm Am(E)branch-name count ( distinct account-number )(account) • Attributes in select clause outside of aggregate functions must appear in group by list, why?select branch-name, balance, count( distinct account-number)from accountgroup by branch-name select … from account group by branch-name, balance OR select branch-name, sum(balance), count(…) from account group by branch-name Department of Computer Science and Engineering, HKUST Slide 21

  22. Aggregate/Group by with Join • Find the number of depositors for each branch.select branch-name, count( distinct customer-name)from depositor, accountwhere depositor.account-number = account.account-numbergroup by branch-name • Perform Join then group by then count ( distinct () )depositor (customer-name, account-number)account (branch-name, account-number, balance)Join  (customer-name, account-number, branch-name, balance) • Group by and aggregate functions apply to the Join result Department of Computer Science and Engineering, HKUST Slide 22

  23. branch-name branch-name cust-name Perryridge count branch-name cust-name Perryridge Perryridge Downtown Downtown Uptown Uptown Downtown Downtown Uptown Perryridge Downtown Downtown Perryridge Downtown Perryridge Perryridge Uptown Perryridge Uptown Perryridge Downtown Uptown Downtown John Wong John Wong May Cheung John Wong May Cheung Jacky Chan John Wong Pat Lee Pat Lee Mary Kwan John Wong John Wong Jacky Chan John Wong Mary Kwan John Wong John Wong May Cheung John Wong Pat Lee John Wong Jacky Chan Mary Kwan Uptown 2 Downtown 2 3 branch-name cust-name Example for Group by select branch-name, customer-name from depositor, account where depositor.account-number = account.account-number count distinct group by Department of Computer Science and Engineering, HKUST Slide 23

  24. Aggregate Functions -Having Clause (new) • Find the names of all branches where the average account balance is more than $700select branch-name, sum(balance)from accountgroupby branch-namehavingavg (balance) >700 • predicates in the having clause are applied to each group after the formation of groups Department of Computer Science and Engineering, HKUST Slide 24

  25. Modes of Interaction between Users and DBMS Department of Computer Science and Engineering, HKUST Slide 25

  26. user Ad hoc query result Stored procedures query Stored procedure result SQL result C/Java program Embedded SQL DBMS Example Department of Computer Science and Engineering, HKUST Slide 26

  27. Embedded and Dynamic SQL Department of Computer Science and Engineering, HKUST Slide 27

  28. Embedded SQL • The SQL standard defines embeddings of SQL in a variety of programming languages such as Pascal, PL/l, Fortran, C and Cobol. • A language in which SQL queries are embedded is referred to as a host language, and the SQL structures permitted in the host language comprise embedded SQL. • EXEC SQL statement is used to identify embedded SQL requests to the preprocessorEXEC SQL <embedded SQL statement > END EXEC Department of Computer Science and Engineering, HKUST Slide 28

  29. Example Embedded SQL From within a host language, find the names and account numbers of customers with more than the variable amount dollars in some account. • Specify the query in SQL and declare a cursor for it EXEC SQLdeclare c cursor for select customer-name, account-number from depositor, account where depositor.account-number = account.account-number and account.balance > :amountEND-EXEC Department of Computer Science and Engineering, HKUST Slide 29

  30. cursor A program variable can hold one value at a time Why is cursor needed? Embedded SQL host program DBMS result Cursor bridges the gap between value-oriented host program and set-oriented DBMS Department of Computer Science and Engineering, HKUST Slide 30

  31. Embedded SQL (cont.) • When a cursor is defined, the embedded SQL is not executed • The open statement causes the query to be evaluatedEXEC SQLopen c END-EXEC • The fetch statement causes the values of one tuple in the query result to be placed in host language variables.EXEC SQLfetch c into :cn :an END-EXECrepeated calls to fetch get successive tuples in the query result; a variable in the SQL communication area indicates when end-of-file is reached. • The close statement causes the database system to delete the temporary relation that holds the result of the query.EXEC SQLclose c END-EXEC Department of Computer Science and Engineering, HKUST Slide 31

  32. open c cursor result open c fetch c process data close c The Flow host program DBMS define c cursor PL/SQL JDBC Department of Computer Science and Engineering, HKUST Slide 32

  33. Dynamic SQL • Allows programs to construct and submit SQL queries at run time. • Example of the use of dynamic SQL within a C program.char* sqlcmd = “update account set balance = balance * 1.05 where account-number= ?”EXEC SQL prepare dynprog from :sqlcmd;char account[10] = “A-101”;EXEC SQL execute dynprog using :account; • The dynamic SQL program contains a ?, which is a place holder for a value that is provided when the SQL program is executed. Department of Computer Science and Engineering, HKUST Slide 33

  34. Other SQL features • Fourth-generation languages- special language to assist application programmers in creating templates on the screen for a user interface, and in formatting data for report generation; available in most commercial database products. E.g., Delphi, Microsoft Access • SQL sessions - provide the abstraction of a client and a server • client connects to a SQL server, establishing a session • executes a series of statements • disconnects the session • can commit or rollback the work carried out in the session • An SQL environment contains several components, including a user identifier, and a schema, which identifies which of several schemas a session is using. Department of Computer Science and Engineering, HKUST Slide 34

  35. Back to SQL … Set and Nested Queries Department of Computer Science and Engineering, HKUST Slide 35

  36. 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 result of any arithmetic expression involving null is null. • Roughly speaking, all comparisons involving null return false.More precisely, • Any comparison with null returns unknown (caution: Oracle treats it as false!) • (true or unknown) = true, (false or unknown) = unknown (unknown or unknown) = unknown,(true and unknown) = unknown, (false and unknown) = false (unknown and unknown) = unknown • Result of where clause predicate is treated as false if it evaluates to unknown • “P is unknown” evaluates to true if predicate P evaluates to unknown Department of Computer Science and Engineering, HKUST Slide 36

  37. Null Values (cont.) • Find all loan numbers which appear in the loan relation with null values for amount.select loan-numberfrom loanwhere amount is null • Total of all loan amountsselect sum(amount)from loanAbove statement ignores null amounts; result is null if there is no non-null amount. • All aggregate operations except count(*) ignore tuples with null values on the aggregated attributes. (Caution: count(*) may not be equal to count(R) in Oracle!) Department of Computer Science and Engineering, HKUST Slide 37

  38. SQL - Nested Subqueries • Every SQL statement returns a relation/set in the result; remember a relation could be null or merely contain a single atomic value • You can replace a value or set of values with a SQL statement (ie., a subquery)select * select *from loan from loanwhere amount > 1200 where amount > select avg(amount) from loan • Illegal if the subquery returns the wrong type for the comparison • Questions: What if the subquery becomes: • select amount from loan • select amount from loan where loan-number=12345 Department of Computer Science and Engineering, HKUST Slide 38

  39. (5 in ) returns true (5 in ) returns false (5 not in ) returns true Set Membership • f in r   t  r ( t = f ) f is typically an attribute name and the set of values is typically returned from a subquery. e.g., ProjNo in (… …) Department of Computer Science and Engineering, HKUST Slide 39

  40. Check for each borrower if he/she is also a depositor Return the set of depositors Example Nested Query • Find all customers who have both an account and a loan in the bank.selectdistinctcustomer-namefromborrowerwherecustomer-namein (selectcustomer-namefromdepositor) Can you formulate the query with “intersect”? Department of Computer Science and Engineering, HKUST Slide 40

  41. Example Query • Find all customers who have a loan at the bank but do not have an account at the bank.select distinctcustomer-namefromborrowerwhere customer-namenot in (selectcustomer-namefromdepositor) Department of Computer Science and Engineering, HKUST Slide 41

  42. Returns borrowers with loan at Perryridge Important link: (Perryridge, D Lee) Returns depositor information Set Membership Example • Find all customers who have both an account and a loan at the Perryridge branch.select distinctcustomer-namefromborrower, loanwhereborrower.loan-number = loan.loan-numberandbranch-name=“Perryridge” and (branch-name, customer-name) in (selectbranch-name, customer-namefromdepositor, accountwheredepositor.account-number = account.account-number) Department of Computer Science and Engineering, HKUST Slide 42

  43. Set Comparison • Find all branches that have greater assets than some branches located in Brooklyn.select distinct T.branch-namefrom branch as T, branch as Swhere T.assets > S.assets and S.branch-city = “Brooklyn” Department of Computer Science and Engineering, HKUST Slide 43

  44. The Some Clause • F <comp> some r   t ( t  r [ F <comp> t ] ) where <comp> can be : <, , >, , =, (5 < some ) returns true(read: 5 < some tuple in the relation) • (5 < some ) returns false Department of Computer Science and Engineering, HKUST Slide 44

  45. The Some Clause (cont.) • (5 = some ) = true(5  some ) = true (since 0  5) • (= some)  in • However, ( some)  not in Department of Computer Science and Engineering, HKUST Slide 45

  46. branches in Brooklyn Assets of all branches in Brooklyn Example Query • Find all branches that have greater assets than some branch located in Brooklyn.select branch-namefrom branchwhere assets > some (select assets from branchwhere branch-city = “Brooklyn”) More efficient: … assets > (select min(assets) from … … ) Department of Computer Science and Engineering, HKUST Slide 46

  47. The All Clause • F <comp> all r   t ( t  r  [ F <comp> t ] ) (5 < all ) = false(5 < all ) = true Department of Computer Science and Engineering, HKUST Slide 47

  48. The All Clause (cont.) • (5 = all ) = false(5  all ) = true (since 5  4 and 5  6) • ( all)  not in • However, (= all)  in Department of Computer Science and Engineering, HKUST Slide 48

  49. Assets of all branches in Brooklyn Example Query • Find the names of all branches that have greater assets than all branches located in Brooklyn.select branch-namefrom branchwhere assets > all (select assetsfrom branchwhere branch-city=“Brooklyn”) Department of Computer Science and Engineering, HKUST Slide 49

  50. Test for Empty Relations • exists returns true if the argument subquery is nonempty. • exists r  r   • not exists r  r =  select … from … where not exist ( select * from branch where assets < 0 ) and … … select … from … where exist ( select * from branch where assets > 1000000 ) and … … Department of Computer Science and Engineering, HKUST Slide 50

More Related