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

sid bid day R1 Example Instances 22 101 10/10/96 58 103 11/12/96 ❖ We will use these sid sname rating age S1 SQL: Queries, Programming, instances of the 22 dustin 7 45.0 Sailors and Triggers Reserves relations 31 lubber 8 55.5 in our examples. 58 rusty 10 35.0 ❖ If the key for the Chapter 5 sid sname rating age Reserves relation S2 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 Database Management Systems, R. Ramakrishnan and J. Gehrke 1 Database Management Systems, R. Ramakrishnan and J. Gehrke 2 SELECT [DISTINCT] target-list Basic SQL Query Conceptual Evaluation Strategy relation-list FROM WHERE qualification ❖ Semantics of an SQL query defined in terms of the ❖ relation-list A list of relation names (possibly with a following conceptual evaluation strategy: range-variable after each name). – Compute the cross-product of relation-list . ❖ target-list A list of attributes of relations in relation-list – Discard resulting tuples if they fail qualifications . ❖ qualification Comparisons (Attr op const or Attr1 op – Delete attributes that are not in target-list . , , , , , < > = ≤ ≥ ≠ Attr2, where op is one of ) – If DISTINCT is specified, eliminate duplicate rows. combined using AND, OR and NOT . ❖ This strategy is probably the least efficient way to ❖ DISTINCT is an optional keyword indicating that the compute a query! An optimizer will find more answer should not contain duplicates. Default is that efficient strategies to compute the same answers . duplicates are not eliminated! Database Management Systems, R. Ramakrishnan and J. Gehrke 3 Database Management Systems, R. Ramakrishnan and J. Gehrke 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 WHERE S.sid=R.sid AND R.bid=103 appears twice in the FROM clause. The (sid) sname rating age (sid) bid day previous query can also be written as: 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 Database Management Systems, R. Ramakrishnan and J. Gehrke 5 Database Management Systems, R. Ramakrishnan and J. Gehrke 6

Expressions and Strings Find sailors who’ve reserved at least one 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: Find triples (of ages of sailors and ❖ Would adding DISTINCT to this query make a two fields defined by expressions) for sailors whose names difference? begin and end with B and contain at least three characters. ❖ What is the effect of replacing S.sid by S.sname in ❖ AS and = are two ways to name fields in result. the SELECT clause? Would adding DISTINCT to ❖ LIKE is used for string matching. `_’ stands for any this variant of the query make a difference? one character and `%’ stands for 0 or more arbitrary characters. Database Management Systems, R. Ramakrishnan and J. Gehrke 7 Database Management Systems, R. Ramakrishnan and J. Gehrke 8 Find sid’s of sailors who’ve reserved a red and a green boat Find sid’s of sailors who’ve reserved a red or a green boat SELECT S.sid ❖ UNION : Can be used to FROM Sailors S, Boats B1, Reserves R1, SELECT S.sid ❖ INTERSECT : Can be used to Boats B2, Reserves R2 FROM Sailors S, Boats B, Reserves R compute the union of any compute the intersection WHERE S.sid=R1.sid AND R1.bid=B1.bid WHERE S.sid=R.sid AND R.bid=B.bid two union-compatible sets of AND S.sid=R2.sid AND R2.bid=B2.bid AND (B.color=‘red’ OR B.color=‘green’) of any two union- tuples (which are AND (B1.color=‘red’ AND B2.color=‘green’) compatible sets of tuples. themselves the result of ❖ Included in the SQL/92 Key field! SQL queries). SELECT S.sid standard, but some SELECT S.sid ❖ If we replace OR by AND in FROM Sailors S, Boats B, Reserves R systems don’t support it. FROM Sailors S, Boats B, Reserves R the first version, what do WHERE S.sid=R.sid AND WHERE S.sid=R.sid AND ❖ Contrast symmetry of the we get? R.bid=B.bid R.bid=B.bid UNION and INTERSECT AND B.color=‘red’ ❖ Also available: EXCEPT AND B.color=‘red’ UNION queries with how much INTERSECT (What do we get if we SELECT S.sid the other versions differ. SELECT S.sid replace UNION by EXCEPT ?) FROM Sailors S, Boats B, Reserves R FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND WHERE S.sid=R.sid AND R.bid=B.bid Database Management Systems, R. Ramakrishnan and J. Gehrke 9 Database Management Systems, R. Ramakrishnan and J. Gehrke R.bid=B.bid 10 AND B.color=‘green’ AND B.color=‘green’ 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 EXISTS ( SELECT * WHERE S.sid IN ( SELECT R.sid FROM Reserves R FROM Reserves R WHERE R.bid=103 AND S.sid=R.sid) WHERE R.bid=103) ❖ A very powerful feature of SQL: a WHERE clause can ❖ EXISTS is another set comparison operator, like IN . itself contain an SQL query! (Actually, so can FROM ❖ If UNIQUE is used, and * is replaced by R.bid , finds and HAVING clauses.) sailors with at most one reservation for boat #103. ❖ To find sailors who’ve not reserved #103, use NOT IN . ( UNIQUE checks for duplicate tuples; * denotes all ❖ To understand semantics of nested queries, think of a attributes. Why do we have to replace * by R.bid ?) nested loops evaluation: For each Sailors tuple, check the ❖ Illustrates why, in general, subquery must be re- qualification by computing the subquery. computed for each Sailors tuple. Database Management Systems, R. Ramakrishnan and J. Gehrke 11 Database Management Systems, R. Ramakrishnan and J. Gehrke 12

Rewriting INTERSECT Queries Using IN More on Set-Comparison Operators Find sid’s of sailors who’ve reserved both a red and a green boat: SELECT S.sid ❖ We’ve already seen IN, EXISTS and UNIQUE . Can also FROM Sailors S, Boats B, Reserves R use NOT IN, NOT EXISTS and NOT UNIQUE . WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’ , , , , , ❖ Also available: op ANY , op ALL , op IN > < = ≥ ≤ ≠ AND S.sid IN ( SELECT S2.sid 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 * ❖ Similarly, EXCEPT queries re-written using NOT IN . FROM Sailors S WHERE S.rating > ANY ( SELECT S2.rating ❖ To find names (not sid ’s) of Sailors who’ve reserved FROM Sailors S2 both red and green boats, just replace S.sid by S.sname WHERE S2.sname=‘Horatio’) in SELECT clause. (What about INTERSECT query?) Database Management Systems, R. Ramakrishnan and J. Gehrke 13 Database Management Systems, R. Ramakrishnan and J. Gehrke 14 (1) COUNT (*) SELECT S.sname FROM Sailors S COUNT ( [ DISTINCT ] A) Division in SQL Aggregate Operators WHERE NOT EXISTS SUM ( [ DISTINCT ] A) (( SELECT B.bid AVG ( [ DISTINCT ] A) FROM Boats B) MAX (A) EXCEPT ❖ Significant extension of Find sailors who’ve reserved all boats. ( SELECT R.bid MIN (A) relational algebra. ❖ Let’s do it the hard FROM Reserves R single column WHERE R.sid=S.sid)) way, without EXCEPT : SELECT COUNT (*) SELECT S.sname (2) SELECT S.sname FROM Sailors S FROM Sailors S FROM Sailors S WHERE S.rating= ( SELECT MAX (S2.rating) SELECT AVG (S.age) WHERE NOT EXISTS ( SELECT B.bid FROM Sailors S2) FROM Sailors S FROM Boats B WHERE S.rating=10 WHERE NOT EXISTS ( SELECT R.bid Sailors S such that ... FROM Reserves R SELECT COUNT ( DISTINCT S.rating) WHERE R.bid=B.bid SELECT AVG ( DISTINCT S.age) there is no boat B without ... FROM Sailors S AND R.sid=S.sid)) FROM Sailors S WHERE S.sname=‘Bob’ WHERE S.rating=10 a Reserves tuple showing S reserved B Database Management Systems, R. Ramakrishnan and J. Gehrke 15 Database Management Systems, R. Ramakrishnan and J. Gehrke 16 GROUP BY and HAVING Find name and age of the oldest sailor(s) SELECT S.sname, MAX (S.age) ❖ So far, we’ve applied aggregate operators to all ❖ The first query is illegal! FROM Sailors S (qualifying) tuples. Sometimes, we want to apply (We’ll look into the SELECT S.sname, S.age them to each of several groups of tuples. reason a bit later, when FROM Sailors S we discuss GROUP BY .) ❖ Consider: Find the age of the youngest sailor for each WHERE S.age = rating level. ❖ The third query is ( SELECT MAX (S2.age) equivalent to the second FROM Sailors S2) – In general, we don’t know how many rating levels exist, and what the rating values for these levels are! query, and is allowed in SELECT S.sname, S.age – Suppose we know that rating values go from 1 to 10; the SQL/92 standard, FROM Sailors S we can write 10 queries that look like this (!): but is not supported in WHERE ( SELECT MAX (S2.age) SELECT MIN (S.age) some systems. FROM Sailors S2) For i = 1, 2, ... , 10: FROM Sailors S = S.age WHERE S.rating = i Database Management Systems, R. Ramakrishnan and J. Gehrke 17 Database Management Systems, R. Ramakrishnan and J. Gehrke 18