1 / 65

SQL: Queries, Programming, Triggers

SQL: Queries, Programming, Triggers. Chapter 5. Example Schema. We will use these table definitions in our examples. Sailors ( sid : integer , sname : string , rating : integer , age : real ) Boats ( bid : integer , bname : string , color : string )

napua
Télécharger la présentation

SQL: Queries, Programming, Triggers

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. SQL: Queries, Programming, Triggers Chapter 5

  2. Example Schema We will use these table definitions in our examples. Sailors(sid: integer, sname: string, rating: integer, age: real) Boats(bid: integer, bname: string, color: string) Reserves(sid: integer, bid: integer, day: date) Make reservations

  3. Example Instances R1 • We will use these instances of the Sailors and Reserves relations in our examples. • If the key for the Reserves relation contained only the attributes sid and bid, how would the semantics differ? S1 S2

  4. Projection S2 Projection on sname and rating

  5. Cross-Product S1 R1 R1 × S1 Each row of R1 is paired with each row of S1.

  6. Basic SQL Query SELECT [DISTINCT] target-list FROM relation-list WHERE qualification • relation-list A list of relation names • target-list A list of attributes of relations in relation-list • qualification Comparisons (“Attrop const” or “Attr1 op Attr2,” where op is one of ˂, ˃, ≤, ≥, =, ≠ ) combined using AND, OR, and NOT. • DISTINCT is an optional keyword indicating that the answer should not contain duplicates. (Default: noteliminated)

  7. Semantics of SQL QUERY PROCESSOR SELECT [DISTINCT] target-list FROM relation-list WHERE qualification target-list Define search space R1 × R2 × R3 × · · · relation-list qualification ˂, ˃, ≤, ≥, =, ≠ Select rows Select columns Query Result Projection

  8. Conceptual Evaluation Strategy • Semantics of an SQL query defined in terms of the following conceptual evaluation strategy: • Compute the cross-product of relation-list. • Discard resulting tuples if they fail qualifications. • Delete attributes that are not in target-list. • If DISTINCT is specified, eliminate duplicate rows. • This strategy is probably the least efficient way to compute a query! An optimizer will find more efficient strategies to compute the same answers.

  9. Example of Conceptual Evaluation SELECTS.sname FROM Sailors S, Reserves R WHERE S.sid=R.sid AND R.bid=103 S×R Remove Irrelevant columns Disqualified Answer

  10. Example of Conceptual Evaluation SELECT S.sname FROM Sailors S, Reserves R WHERE S.sid=R.sid AND R.bid=103 S×R

  11. A Note on Range Variables Really needed only if the same relation appears twice in the FROM clause. The previous query can be written in two ways: Range variable SELECT S.sname FROM Sailors S, Reserves R WHERE S.sid=R.sid AND bid=103 It is good style, however, to use range variables always! SELECT sname FROM Sailors, Reserves WHERE Sailors.sid=Reserves.sid AND bid=103 OR

  12. Find sailors who’ve reserved at least one boat SELECT S.sid FROM Sailors S, Reserves R WHERE S.sid = R.sid • Would adding DISTINCT to this query make a difference? Remove duplicate sid • What is the effect of replacing S.sid by S.sname in the SELECT clause? Since two sailors may have the same name, some sailor may have no reservation even his/her name is in the output

  13. Find sailors who’ve reserved at least one boat Sailors(sid, sname, rating, age) Reserves(sid, bid, day) sid has a reservation SELECT S.sid FROM Sailors S, Reserves R WHERE S.sid = R.sid Given a sailor sid sid has a reservation

  14. Arithmetic Expressions and Strings AS and = are two ways to name fields in result LIKE is used for string matching. ‘_’ stands for any one character and ‘%’ stands for 0 or more arbitrary characters. SELECT S.age, age1 = S.age-5, 2*S.ageAS age2 FROM Sailors S WHERE S.sname LIKE ‘B_%B’ Name begins and ends with ‘B’ and contains at least three characters

  15. Find names of sailors who’ve reserved a red or a green boat Sailors(sid, sname, rating, age) Reserves(sid, bid, day) Boats(bid, bname, color) sid has a reservation for bid color = ‘red’ OR color = ‘green’ SELECT S.name FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND (B.color=‘red’ ORB.color=‘green’) bid is a boat

  16. Find names of sailors who’ve reserved a red or a green boat SELECT S.name FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’ UNION SELECT S.name FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘green’ Name of sailors who’ve reserved red boats • UNION: Compute the union of any two union-compatible sets of tuples (which are themselves the result of SQL queries). Name of sailors who’ve reserved green boats

  17. EXCEPT SELECT S.name FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’ EXCEPT SELECT S.name FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘green’ Name of sailors who’ve reserved red boats • What do we get if we replace UNION by EXCEPT? Find the sids of all sailors who have reserved red boats but not green boats Name of sailors who’ve reserved green boats

  18. Find names of sailors who’ve reserved a red and a green boat SELECT S.name FROM Sailors S, Boats B1, Reserves R1, Boats B2, Reserves R2 WHERE S.sid=R1.sid AND R1.bid=B1.bid The sailor reserves 1st boat AND S.sid=R2.sid AND R2.bid=B2.bid The same sailor reserves 2nd boat AND (B1.color=‘red’ AND B2.color=‘green’) The 2nd boat is green The 1st boat is red

  19. Find names of sailors who’ve reserved a red and a green boat Result is empty ! Can’t we say: SELECT S.name FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND (B.color=‘red’ AND B.color=‘green’)

  20. Find names of sailors who’ve reserved a red and a green boat SELECT S.name FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid ANDB.color=‘red’ INTERSECT SELECT S.name FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid ANDB.color=‘green’ Name of sailors who’ve reserved red boats • INTERSECT: Can be used to compute the intersection of any two union-compatible sets of tuples. • Included in the SQL/92 standard, but some systems don’t support it. Name of sailors who’ve reserved green boats

  21. Nested Queries Find names of sailors who’ve reserved boat #103 SELECT S.sname FROM Sailors S WHERE S.sid IN (SELECT R.sid FROM Reserves R WHERE R.bid=103) • A very powerful feature of SQL: a WHERE clause can itself contain an SQL query! (Actually, so can FROM and HAVING clauses.) • To understand semantics of nested queries, think of a nested loops evaluation: For each Sailors tuple, check the qualification by computing the subquery. • To find sailors who’ve not reserved #103, use NOT IN. All “boat 103” reservations Sailor S has at least one of these reservations

  22. Nested Queries with Correlation (1) Query: Find names of sailors who’ve reserved boat #103 EXISTStests whether a set is nonempty. SELECT S.sname FROM Sailors S WHERE EXISTS(SELECT * FROM Reserves R WHERE S.sid=R.sidAND R.bid=103) S Sailor S reserves boat 103

  23. NOT EXIST • Use NOT EXIST to find the names of sailors who have not reserved a red boat SELECT S.sname FROM Sailors S WHERE NOT EXISTS (SELECT * FROM Reserves R WHERE R.bid=103 AND S.sid=R.sid)

  24. Nested Queries with Correlation (2) Query: Find names of sailors who reserve boat 103 at most once. • UNIQUE returns true if no row appears more than once. (Note: returns true if answer is empty) • Can we replace “SELECT R.bid” by “ SELECT * ” ? No, A sailor may reserve boat 103 on different days; and UNIQUE would return true SELECT S.sname FROM Sailors S WHERE UNIQUE (SELECT R.bid FROM Reserves R WHERE R.bid=103 AND S.sid=R.sid)

  25. More on Set-Comparison Operators • Also available: opANY,opALL , where op: ˃, ˂, =, ≠, ≥, ≤ • Find sailors whose rating is greater than that of some sailor called Horatio: SELECT * FROM Sailors S WHERE S.rating > ANY (SELECT S2.rating FROM Sailors S2 WHERE S2.sname=‘Horatio’) The subquery must return a row that makes the comparison true, in order for S.rating > ANY … to return true

  26. Rewriting INTERSECT Queries Using IN Find sid’s of sailors who’ve reserved both a red and a green boat: sid of sailors who’ve reserved red boats SELECT S.sid FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid ANDB.color=‘red’ (SELECT S2.sid FROM Sailors S2, Boats B2, Reserves R2 WHERE S2.sid=R2.sid AND R2.bid=B2.bid AND B2.color=‘green’) Similarly, we can rewrite EXCEPT queries using NOT IN. sid of sailors who’ve reserved green boats AND S.sid IN

  27. Division B A/B A X X Y Y Useful for expressing queries like: Find sailors who have reserved all boats No yellow nor green No red nor green Division

  28. Division Operations in SQL (1) Find names of sailors who’ve reserved all boat: SELECT S.sname FROM Sailors S WHERE NOT EXIST ((SELECT B.bid FROM Boats B) EXCEPT (SELECT R.bid FROM Reserves R WHERE R.sid = S.sid )) The sailor reserved all boats All boats Boats not reserved by the sailor All boats reserved by the sailor

  29. Division Operations in SQL (2) Find names of sailors who’ve reserved all boat: Sailor S SELECT S.sname FROM Sailors S WHERE NOT EXIST ((SELECT B.bid FROM Boats B WHERE NOT EXISTS (SELECT R.bid FROM Reserves R WHERE R.bid = B.bid AND R.sid = S.sid)) such that there is no boat B without a reservation showing Sailor S such that … there is no boat B without … a Reserves tuple showing S reserved B. Sailor S reserved boat B

  30. Aggregate Operators Significant extension of relational algebra

  31. Aggregate Operators Find the name of sailors with the highest rating Count the number of sailors SELECT COUNT (*) FROM Sailors S SELECTS.sname FROM Sailors S WHERES.rating= (SELECT MAX(S2.rating) FROM Sailors S2) Find the average age of sailors with a rating of 10 Find the average of the distinct ages of sailors with a rating of 10 SELECT AVG (S.age) FROM Sailors S WHERE S.rating=10 Count the number of distinct ratings of sailors called “Bob” SELECT AVG (DISTINCT S.age) FROM Sailors S WHERES.rating=10 SELECT COUNT (DISTINCTS.rating) FROM Sailors S WHERE S.sname=‘Bob’

  32. Aggregate Operators SELECT COUNT (*) FROM Sailors S SELECTS.sname FROM Sailors S WHERES.rating= (SELECT MAX(S2.rating) FROM Sailors S2) SELECT AVG (S.age) FROM Sailors S WHERE S.rating=10 SELECT AVG (DISTINCT S.age) FROM Sailors S WHERE S.rating=10 SELECT COUNT (DISTINCTS.rating) FROM Sailors S WHERE S.sname=‘Bob’

  33. Find name and age of the oldest sailor(s) Comparing a number with a relation is allowed here SELECT S.sname FROM Sailors S WHERE S.rating = (SELECT MAX(S2.rating) FROM Sailors S2) Allowed in SQL/92 standard, but is not supported in some systems SELECT S.sname FROM Sailors S WHERE ( SELECT MAX (S2.rating) FROM Sailors S2 ) = S.rating

  34. Find name and age of the oldest sailor(s) Only aggregate operations allowed If the SELECT clause uses an aggregate operation, then it must use only aggregate operations unless the query contains a GROUP BY clause (aggregate value for each group – discussed later.) Illegal SELECT S.sname, MAX (S.age) FROM Sailors S

  35. GROUP BY and HAVING (1) • So far, we’ve applied aggregate operators to all (qualifying) tuples. • Sometimes, we want to apply them to each of several groups of tuples. Relation Aggregator Qualifier 32 12 Aggregator Group 1 Relation 9 Group 2 Aggregator 11 Aggregator Group 3

  36. GROUP BY and HAVING (2) Consider: Find the age of the youngest sailor for each rating level. /* Min(age) for multiple groups • If we know that rating values go from 1 to 10, we can write 10 queries that look like this: • In general, we don’t know how many rating levels exist, and what the rating values for these levels are ! SELECT MIN (S.age) FROM Sailors S WHERE S.rating = i For i = 1, 2, ... , 10:

  37. Queries With GROUP BYand HAVING SELECT [DISTINCT] target-list FROM relation-list WHERE qualification GROUP BY grouping-list HAVING group-qualification MIN(Attribute) GROUP BY Output a table HAVING 12 Qualifier selecting groups SELECT FROM WHERE Aggregator Group 1 9 Aggregator Group 2 Group 3

  38. Queries With GROUP BYand HAVING SELECT [DISTINCT] target-list FROM relation-list WHERE qualification GROUP BY grouping-list HAVING group-qualification The target-list contains: (i) attribute names,(ii) terms with aggregate operations (e.g., MIN (S.age)). • Each answer tuplebelongs to a group. • The attribute list must be a subset of grouping-list. • A group is a set of tuples that have the same value for all attributes in grouping-list.

  39. SELECT [DISTINCT] target-list FROM relation-list WHERE qualification GROUP BY grouping-list HAVING group-qualification Conceptual Evaluation • The cross-product of relation-list is computed • Tuples that fail qualification are discarded • `Unnecessary’ fields are deleted • The remaining tuples are partitioned into groups by the value of attributes in grouping-list. • The group-qualification is then applied to eliminate some groups • One answer tuple is generated per qualifying group

  40. Find the age of the youngest sailor with age ≥ 18, for each rating with at least 2 such sailors Input relation SELECT S.rating, MIN (S.age) FROM Sailors S WHERE S.age>= 18 GROUP BY S.rating HAVING COUNT (*) > 1 Sailors age Answer Disqualify Only one group satisfies HAVING 4rating groups Only S.rating and S.age are mentioned in SELECT

  41. “GROUP BYand HAVING” Examples Find the age of the youngest sailor with age ≥ 18 SELECT MIN (S.age) FROM Sailors S WHERE S.age >= 18 SELECT S.rating, MIN (S.age) FROM Sailors S WHERE S.age >= 18 GROUP BY S.rating Find the age of the youngest sailor with age ≥ 18, for each rating SELECT S.rating, MIN (S.age) FROM Sailors S WHERE S.age >= 18 GROUP BY S.rating HAVING COUNT (*) > 1 Find the age of the youngest sailor with age ≥ 18, for each ratingwith at least 2 such sailors

  42. For each red boat, find the number of reservations for this boat 3) Count the number of reservations for each red-boat group SELECT B.bid, COUNT (*) AS scount FROM Boats B, Reserves R WHERE R.bid=B.bid AND B.color=‘red’ GROUP BY B.bid 1) Find all reservations for red boats 2) Group the reservations for red boats according to bid

  43. Number of reservation for each boat Illegal Having Clause SELECT B.bid, COUNT (*) AS scount FROM Boats B, Reserves R WHERE R.bid=B.bid GROUP BY B.bid HAVING B.color=‘red’ B.Coloris not in the grouping-list • HAVING B.color=‘red’ Illegal ! Note: HAVING clause is to select groups ! GROUP BY “bid” Output a table HAVING “red” ? ? Qualifier selecting groups Aggregator Group 1 SELECT FROM WHERE ? Aggregator Group 2 Group 3

  44. 4 1 Find the age of the youngest sailor older than 18, for each rating with at least 2 sailors 2 3 Sailors Rating 1 3 Age>18 HAVING 1 ˂ (SELECT COUNT (*) FROM Sailors S2 WHERE S.rating = S2.rating) Size>1 Group S.rating 2 WHERE S.age > 18 26 Min. age 22 4 GROUP BY S.rating

  45. Find the age of the youngest sailor older than 18, for each rating with at least 2 sailors 4) Find youngest age for each qualified group 1) Find all sailors older than 18 SELECTS.rating, MIN (S.age) FROM Sailors S WHERE S.age > 18 GROUP BY S.rating HAVING 1 ˂ (SELECT COUNT (*) FROM Sailors S2 WHERES.rating = S2.rating) 2) Group qualified sailors according to rating 3.2) Discard groups with less than two sailors 3.1) Count the number of sailors in a group Number of sailors with this rating

  46. Find the age of the youngest sailor older than 18, for each rating level that has at least 2 sailors SELECTS.rating, MIN (S.age) FROM Sailors S WHERES.age > 18 GROUP BY S.rating HAVING 1 ˂ (SELECT COUNT (*) FROM Sailors S2 WHERES.rating= S2.rating) • Shows HAVING clause can also contain a subquery. • We can use S.rating inside the nested subquery because it has a single value for the current group of sailors. • What if HAVING clause is replaced by “HAVING COUNT(*) >1” • Find the age of the youngest sailor older than 18, for each rating level that has at least two such sailors. If that rating group has more than 1 sailor

  47. Find the age of the youngest sailor older than 18, for each rating level that has at least 2 sailors Counting including sailors younger than 18 SELECTS.rating, MIN (S.age) FROM Sailors S WHERES.age > 18 GROUP BY S.rating HAVING 1 ˂ (SELECT COUNT (*) FROM Sailors S2 WHERES.rating= S2.rating) “age” is notmentioned in this subquery CAUTIOUS At least 2 sailors SELECTS.rating, MIN (S.age) FROM Sailors S WHERES.age > 18 GROUP BY S.rating HAVINGCOUNT (*) › 1 Counting include only adult sailors At least 2 such sailors, i.e., older than 18

  48. Find those ratings for which the average age is the minimum over all ratings Aggregate operations cannot be nested SELECT S.rating FROM Sailors S WHERE S.age = (SELECT MIN (AVG (S2.age)) FROM Sailors S2)

  49. Find those ratings for which the average age is the minimum over all ratings Correct solution (in SQL/92): Find average age for each rating group SELECT Temp.rating, Temp.avgage FROM (SELECT S.rating, AVG (S.age) AS avgage FROM Sailors S GROUP BY S.rating) AS Temp WHERE Temp.avgage = (SELECT MIN (Temp.avgage) FROM Temp) Average age for some rating group Minimum over all ratings

  50. Null Values • Field values in a tuple are sometimes • unknown(e.g., a rating has not been assigned), or • inapplicable(e.g., no spouse’s name). • SQL provides a special value null for such situations.

More Related