Queries, Constraints, Triggers Ramakrishnan & Gehrke, Chapters - PowerPoint PPT Presentation

SQL: Queries, Constraints, Triggers Ramakrishnan & Gehrke, Chapters 4 & 5 320302 Databases & WebApplications (P. Baumann)

Example Instances Sailors Boats Reserves sid sname rating age bid color sid bid day ----------------------- ---------------- ---------------- 22 Dustin 7 45.0 101 red 22 101 10/10/96 31 Lubber 8 55.5 102 blue 58 103 11/12/96 58 Rusty 10 35.0 103 green 320302 Databases & WebApplications (P. Baumann) 2

Basic SQL Query Structure SELECT [DISTINCT] target-list relation-list FROM WHERE qualification  relation-list • list of relation names (possibly with a range-variable after each name)  target-list • A list of attributes of relations in relation-list, possibly using range variables  qualification • Attr op const or Attr1 op Attr2 where op one of , , , , , combined using AND, OR, NOT  DISTINCT is optional for suppressing duplicates • By default duplicates not eliminated! …so tables actually are multisets, not sets 320302 Databases & WebApplications (P. Baumann) 3

Conceptual Evaluation Strategy SELECT [DISTINCT] target-list relation-list FROM WHERE qualification  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 qualification • 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 320302 Databases & WebApplications (P. Baumann) 4

Example of Conceptual Evaluation SELECT S.sname FROM Sailors S, Reserves R WHERE S.sid=R.sid AND R.bid=103 (sid) sname rating age (sid) bid day ------------------------------------------- 22 Dustin 7 45.0 22 101 10/10/96  cardinality? 22 Dustin 1 45.0 58 103 11/12/96 31 Lubber 8 55.5 22 101 10/10/96 31 Lubber 8 55.5 58 103 11/12/96 58 Rusty 10 35.0 22 101 10/10/96 58 Rusty 7 35.0 58 103 11/12/96 320302 Databases & WebApplications (P. Baumann) 5

A Note on Range Variables  Really needed only if the same relation appears twice in the FROM clause  previous query can also be written as: SELECT S.sname FROM Sailors S, Reserves R WHERE S.sid=R.sid AND bid=103 It is good style, however, to use  Or: range variables SELECT sname always! FROM Sailors, Reserves WHERE Sailors.sid=Reserves.sid AND bid=103 320302 Databases & WebApplications (P. Baumann) 6

Join  Join = several tables addressed in one query SELECT target-list FROM Relation1 R1, Relation2 R2, … WHERE qualification  List of relations in FROM clause determine cross product  Frequently cross-relation conditions on attribute values to restrict results  Most common: R1.attr1 = R2.attr2 SELECT S.sid • ex: FROM Sailors S, Reserves R WHERE S.sid=R.sid 320302 Databases & WebApplications (P. Baumann) 7

"Sailors who’ve reserved at least 1 boat" (sid) sname rating age (sid) bid day SELECT S.sid ------------------------------------------- 22 Dustin 7 45.0 22 101 10/10/96 FROM Sailors S, Reserves R 22 Dustin 7 45.0 58 103 11/12/96 WHERE S.sid=R.sid 31 Lubber 8 55.5 22 101 10/10/96 31 Lubber 8 55.5 58 103 11/12/96 58 Rusty 10 35.0 22 101 10/10/96 58 Rusty 10 35.0 58 103 11/12/96  Would adding DISTINCT to this query make a difference?  What is the effect of replacing S.sid by S.sname in the SELECT clause? Would adding DISTINCT to this variant of the query make a difference? 320302 Databases & WebApplications (P. Baumann) 8

Expressions and Strings SELECT S.age, age1=S.age-5, 2*S.age AS age2 FROM Sailors S WHERE S.sname LIKE „B_% B‟  Illustrates use of arithmetic expressions and string pattern matching: • Find triples (of ages of sailors and two fields defined by expressions) for sailors whose names begin and end with B and contain at least three characters  AS and = are two ways to name fields in result  LIKE is used for string matching • `_‟ stands for any one character • `%‟ stands for 0 or more arbitrary characters 320302 Databases & WebApplications (P. Baumann) 9

"sid’s of sailors who have reserved a red or a green boat" UNION: Can be used to compute the  SELECT S.sid union of any two union-compatible sets of FROM Sailors S, Boats B, Reserves R tuples WHERE S.sid=R.sid AND R.bid=B.bid AND (B.color=„red‟ OR B.color=„green‟) • which themselves are the result of SQL queries SELECT S.sid FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid If we replace OR by AND in the first  AND B.color=„red‟ version, what do we get? UNION SELECT S.sid Also available: EXCEPT  FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid • What do we get if we replace UNION AND B.color=„green‟ by EXCEPT? 320302 Databases & WebApplications (P. Baumann) 10

"Find sid’s of sailors who have reserved a red and a green boat" INTERSECT: Can be used to compute the  SELECT S.sid FROM Sailors S, Boats B1, Reserves R1, intersection of any two union-compatible Boats B2, Reserves R2 sets of tuples WHERE S.sid=R1.sid AND R1.bid=B1.bid AND S.sid=R2.sid AND R2.bid=B2.bid AND (B1.color=„red‟ AND B2.color=„green‟) Included in the SQL/92 standard,  Key field! SELECT S.sid but some systems don‟t support it FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid Contrast symmetry of the UNION and  AND B.color =„red‟ INTERSECT queries with how much the INTERSECT SELECT S.sid other versions differ! FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND B.color =„green‟ 320302 Databases & WebApplications (P. Baumann) 11

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)  WHERE clause can itself contain an SQL query! • Actually, so can FROM and HAVING clauses  To find sailors who‟ve not reserved #103, use NOT IN  To understand semantics of nested queries, think of a nested loops evaluation • For each Sailors tuple, check the qualification by computing the subquery 320302 Databases & WebApplications (P. Baumann) 12

Nested Queries with Correlation  Find names of sailors who‟ve reserved boat #103: SELECT S.sname FROM Sailors S WHERE EXISTS (SELECT * FROM Reserves R WHERE R.bid=103 AND S.sid=R.sid)  EXISTS: another set operator, like IN  If UNIQUE is used, and * is replaced by R.bid: finds sailors with at most one reservation for boat #103 • Why do we have to replace * by R.bid?  Illustrates why, in general, subquery must be re-computed for each Sailors tuple 320302 Databases & WebApplications (P. Baumann) 13

More on Set-Comparison Operators  We have already seen IN, EXISTS and UNIQUE • Can also use NOT IN, NOT EXISTS and NOT UNIQUE  Also available: op ANY, op ALL, op one of , , , , ,  "sailors whose rating is greater than that of sailor Horatio" SELECT * FROM Sailors S WHERE S.rating > ANY (SELECT S2.rating FROM Sailors S2 WHERE S2.sname = „Horatio‟) 320302 Databases & WebApplications (P. Baumann) 14

Rewriting INTERSECT Queries Using IN SELECT S.sid FROM Sailors S, Boats B, Reserves R  "sailors who‟ve reserved both red & green boat": WHERE S.sid=R.sid AND R.bid=B.bid AND B.color =„red‟ INTERSECT SELECT S.sid SELECT S.sid FROM Sailors S, Boats B, Reserves R FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=„red‟ WHERE S.sid=R.sid AND R.bid=B.bid AND B.color =„green‟ AND S.sid IN (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, EXCEPT queries re-written using NOT IN  names of Sailors? replace SELECT S.sid  SELECT S.sname What about INTERSECT query?  320302 Databases & WebApplications (P. Baumann) 15

Division in SQL (1) SELECT S.sname FROM Sailors S  "sailors who have reserved WHERE NOT EXISTS all boats" ( (SELECT B.bid FROM Boats B) EXCEPT ( SELECT R.bid  Let‟s do it the hard way, FROM Reserves R without EXCEPT : WHERE R.sid=S.sid ) ) (2) SELECT S.sname FROM Sailors S Sailors S such that ... WHERE NOT EXISTS (SELECT B.bid FROM Boats B there is no boat B without ... WHERE NOT EXISTS (SELECT R.bid FROM Reserves R a Reserves tuple showing S reserved B WHERE R.bid=B.bid AND R.sid=S.sid ) ) 320302 Databases & WebApplications (P. Baumann) 16

Aggregate Operators  Summary information instead of value list SELECT COUNT (*) FROM Sailors S SELECT COUNT (DISTINCT S.rating) COUNT (*) FROM Sailors S WHERE S.sname=„Bob‟ COUNT ( [ DISTINCT ] A ) SUM ( [ DISTINCT ] A ) SELECT AVG (S.age) FROM Sailors S AVG ( [ DISTINCT ] A ) WHERE S.rating=10 MAX ( A ) SELECT AVG ( DISTINCT S.age) MIN ( A ) FROM Sailors S WHERE S.rating=10 A: single column SELECT S.sname FROM Sailors S WHERE S.rating= (SELECT MAX(S2.rating) FROM Sailors S2) 320302 Databases & WebApplications (P. Baumann) 17