Example Instances 22 101 10/10/96 58 103 11/12/96 We will use - PDF document

R1 sid bid day Example Instances 22 101 10/10/96 58 103 11/12/96  We will use these S1 sid sname rating age instances of the 22 dustin 7 45.0 SQL: Queries, Constraints, Triggers Sailors and Reserves relations in our 31 lubber 8 55.5 examples. 58 rusty 10 35.0  If the key for the Chapter 5 S2 sid sname rating age Reserves relation contained only the 28 yuppy 9 35.0 attributes sid and 31 lubber 8 55.5 bid, how would the 44 guppy 5 35.0 semantics differ? 58 rusty 10 35.0 1 2 Basic SQL Query Conceptual Evaluation Strategy SELECT [DISTINCT] target-list relation-list FROM  Semantics of an SQL query defined in terms of the WHERE qualification following conceptual evaluation strategy:  relation-list: List of relation names (possibly with a range- 1. Compute the cross-product of relation-list. variable after each name). 2. Discard resulting tuples if they fail qualifications.  target-list: List of attributes of relations in relation-list 3. Delete attributes that are not in target-list.  qualification: Comparisons ( Attr op const or Attr1 op 4. If DISTINCT is specified, eliminate duplicate rows. Attr2 , where op is one of  ,  ,  ,  ,  ,  ) combined using  This strategy is probably the least efficient way to AND, OR and NOT. compute a query…  DISTINCT: Optional keyword indicating that the answer  Optimizer should find more efficient strategies to should not contain duplicates. compute the same answers.  Default = duplicates are not eliminated 3 4 Example of Conceptual Evaluation A Note on Range Variables SELECT S.sname FROM Sailors S, Reserves R  Really needed only if the same relation appears WHERE S.sid=R.sid AND R.bid=103 twice in the FROM clause. The previous query can also be written as: (sid) sname rating age (sid) bid day 22 dustin 7 45.0 22 101 10/10/96 SELECT S.sname 22 dustin 7 45.0 58 103 11/12/96 It is good style, FROM Sailors S, Reserves R however, to use 31 lubber 8 55.5 22 101 10/10/96 WHERE S.sid=R.sid AND bid=103 range variables 31 lubber 8 55.5 58 103 11/12/96 OR SELECT sname always! 58 rusty 10 35.0 22 101 10/10/96 FROM Sailors, Reserves WHERE Sailors.sid=Reserves.sid 58 rusty 10 35.0 58 103 11/12/96 AND bid=103 5 6

Find sailors who’ve reserved at least one Expressions and Strings boat SELECT S.age, age1=S.age-5, 2*S.age AS age2 SELECT S.sid FROM Sailors S FROM Sailors S, Reserves R WHERE S.sname LIKE ‘B_%B’ WHERE S.sid=R.sid  Illustrates use of arithmetic expressions and string pattern matching  Would adding DISTINCT to this query make a  Find triples (age of sailor and two fields defined by expressions) difference, i.e., could a sailor returned by the original for sailors whose names begin and end with B and contain at least three characters. version disappear or could a new sailor appear?  AS and = are two ways to name fields in the result.  What is the effect of replacing S.sid by S.sname in  LIKE is used for string matching the SELECT clause? Would adding DISTINCT to this  `_’ stands for any one character variant of the query make a difference?  `%’ stands for 0 or more arbitrary characters. 7 8 Find sid’s of sailors who’ve reserved a Find sid’s of sailors who’ve reserved a red or a green boat red and a green boat SELECT S.sid SELECT S.sid  UNION: Computes the  INTERSECT: Computes FROM Sailors S, Boats B, Reserves R FROM Sailors S, Boats B1, Reserves R1, intersection of any two union of any two union- WHERE S.sid=R.sid AND R.bid=B.bid Boats B2, Reserves R2 union-compatible sets compatible sets (which AND (B.color=‘red’ OR B.color=‘green’) WHERE S.sid=R1.sid AND R1.bid=B1.bid of tuples. AND S.sid=R2.sid AND R2.bid=B2.bid can themselves be the AND (B1.color=‘red’ AND B2.color=‘green’)  Included in the SQL/92 result of SQL queries). standard, but some  If we replace OR by AND SELECT S.sid Key field! SELECT S.sid systems do not FROM Sailors S, Boats B, Reserves R in the first version, what FROM Sailors S, Boats B, Reserves R support it. WHERE S.sid=R.sid AND R.bid=B.bid do we get? WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’  Contrast symmetry of AND B.color =‘red’  Also available: EXCEPT UNION the UNION and INTERSECT SELECT S.sid (What do we get if we INTERSECT queries SELECT S.sid FROM Sailors S, Boats B, Reserves R replace UNION by with how much the FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid WHERE S.sid=R.sid AND R.bid=B.bid EXCEPT?) other versions differ. AND B.color=‘green’ AND B.color =‘green’ 9 10 Nested Queries Nested Queries with Correlation Find names of sailors who’ve reserved boat #103: Find names of sailors who’ve reserved boat #103: SELECT S.sname SELECT S.sname FROM Sailors S FROM Sailors S WHERE S.sid IN ( SELECT R.sid WHERE EXISTS ( SELECT * FROM Reserves R FROM Reserves R WHERE R.bid=103) WHERE R.bid=103 AND S.sid=R.sid)  Very powerful feature of SQL: WHERE clause can itself contain  EXISTS tests if the set is empty. an SQL query  If UNIQUE is used, and * is replaced by R.bid, finds sailors  And so can FROM and HAVING clauses. with at most one reservation for boat #103.  To find sailors who have not reserved #103, use NOT IN.  UNIQUE returns true if there are no duplicates in the result set.  To understand semantics of nested queries, think of a nested  Why do we have to replace * by R.bid for that query version? loops evaluation:  Illustrates why, in general, subquery must be re-  For each Sailors tuple, check the qualification by computing the computed for each Sailors tuple. subquery. 11 12

More on Set-Comparison Operators Rewriting INTERSECT Queries Using IN Find sid’s of sailors who’ve reserved both a red and a green boat:  Seen so far: IN, EXISTS, UNIQUE SELECT S.sid  Can also use NOT IN, NOT EXISTS, NOT UNIQUE.  Also available: op ANY, op ALL, where op is  ,  ,  ,  ,  , FROM Sailors S, Boats B, Reserves R or  WHERE S.sid=R.sid AND R.bid=B.bid AND B.color =‘red’ AND S.sid IN ( SELECT S2.sid  Note: IN same as  ANY, NOT IN same as  ALL FROM Sailors S2, Boats B2, Reserves R2  Find sailors whose rating is greater than that of some WHERE S2.sid=R2.sid AND R2.bid=B2.bid sailor called Horatio: AND B2.color=‘green’) SELECT * FROM Sailors S  Similarly, EXCEPT queries re-written using NOT IN. WHERE S.rating > ANY ( SELECT S2.rating  To find names (not sid’s) of Sailors who’ve reserved both FROM Sailors S2 red and green boats, just replace S.sid by S.sname in WHERE S2.sname=‘Horatio’) SELECT clause. (What about INTERSECT query?) 13 14 (1) SELECT S.sname FROM Sailors S Review: Division Operator Division in SQL WHERE NOT EXISTS (( SELECT B.bid FROM Boats B) Find sailors who’ve reserved all boats. sno pno EXCEPT pno pno pno ( SELECT R.bid s1 p1  The hard way, without p2 p2 p1 FROM Reserves R s1 p2 EXCEPT: WHERE R.sid=S.sid)) p4 p2 B1 s1 p3 p4 B2 (2) SELECT S.sname s1 p4 FROM Sailors S B3 sno s2 p1 WHERE NOT EXISTS ( SELECT B.bid s1 s2 p2 FROM Boats B sno s2 Sailors S such that ... WHERE NOT EXISTS ( SELECT R.bid s3 p2 sno s1 FROM Reserves R s3 s4 p2 s1 WHERE R.bid=B.bid s4 s4 there is no boat B without ... s4 p4 AND R.sid=S.sid)) A/B1 A/B2 A/B3 A a Reserves tuple showing S reserved B 15 16 COUNT (*) Find name and age of the oldest Aggregate Operators COUNT ( [ DISTINCT ] A) sailor(s) SUM ( [ DISTINCT ] A) SELECT S.sname, MAX (S.age) AVG ( [ DISTINCT ] A)  First query is illegal.  Significant extension of FROM Sailors S MAX (A) (Discussed in more depth relational algebra. MIN (A) SELECT S.sname, S.age later for GROUP BY.) SELECT COUNT (*) FROM Sailors S single column  Second query has implicit FROM Sailors S WHERE S.age = SELECT S.sname type cast (Which?) ( SELECT MAX (S2.age) FROM Sailors S SELECT AVG (S.age)  Third query is equivalent FROM Sailors S2) WHERE S.rating= ( SELECT MAX (S2.rating) FROM Sailors S to second query FROM Sailors S2) WHERE S.rating=10 SELECT S.sname, S.age  Allowed in the SQL/92 FROM Sailors S standard SELECT COUNT ( DISTINCT S.rating) SELECT AVG ( DISTINCT S.age) WHERE ( SELECT MAX (S2.age)  But not supported in some FROM Sailors S FROM Sailors S FROM Sailors S2) systems WHERE S.sname=‘Bob’ WHERE S.rating=10 = S.age 17 18