The set of beers Steve or Bill likes SELECT name manf FROM Beers WHERE name IN

# The set of beers steve or bill likes select name manf

This preview shows page 64 - 78 out of 167 pages.

The set of beers Steve or Bill likes SELECT name, manf FROM Beers WHERE name IN ( SELECT beer FROM Likes WHERE drinker = 'Steve' or drinker = 'Bill' );
Example 65
Without subquery Does this query produce the same result? 66 SELECT name, manf FROM Beers b, Likes l WHERE b.name = l.beer and l.drinker = 'Steve' or l.drinker = 'Bill';
Correct equivalent subquery Note the "distinct" and grouping (using parentheses) of two conditions on drinker 67 SELECT distinct name, manf FROM Beers b, Likes l WHERE b.name = l.beer and (l.drinker = 'Steve' or l.drinker = 'Bill');
Introduced by comparison operators <comparison operator> <any/all> (subquery) – Comparison operators: =, !=, <, >, <=, >=, <> • Examples – x >= all (subquery) – x <= all (subquery) – x = any (subquery) // equivalent to "x in (subquery)" – x != all (subquery) // equivalent to "x not in (subquery)" Is "x != any (subquery)" equivalent to "x not in (subquery)"? 68
69 Example From Sells(bar, beer, price), find the beer(s) sold for the highest price. What about beers with "the lowest price"? price from the outer Sells must not be less than any price. SELECT beer FROM Sells WHERE price >= ALL ( SELECT price FROM Sells WHERE price is not NULL);
Introduced by "exists" or "not exists" Both form a boolean expression exists (subquery) – Evaluated to true if subquery has at least one result not exists (subquery) – Evaluated to true if subquery has no results 70
71 Example Query with EXISTS What does this query do? select name from Beers b1 where not exists ( select name from Beers b2 where b2.name <> b1.name and b2.manf = b1.manf);
Agenda SQL DML (Data Manipulation Language) – SQL query – Relations as bags – Joins – Grouping and aggregation – Database modification SQL DDL (Data Definition Language) – Define schema 72
73 Bag Semantics for SFW Queries The SELECT-FROM-WHERE statement uses bag semantics – Selection: preserve the number of occurrences – Projection: preserve the number of occurrences (no duplicate elimination) – Cartesian product, join: no duplicate elimination
Set Operations on Bags • Union: {a,b,b,c} U {a,b,b,b,e,f,f} = {a,a,b,b,b,b,b,c,e,f,f} add the number of occurrences Difference: {a,b,b,b,c,c} - {b,c,c,c,d} = {a,b,b} – subtract the number of occurrences Intersection: {a,b,b,b,c,c} {b,b,c,c,c,c,d} = {b,b,c,c} – minimum of the two numbers of occurrences 74
75 Union, Intersection, and Difference Union, intersection, and difference of relations are expressed by the following forms, each involving subqueries: – ( subquery ) UNION ( subquery ) – ( subquery ) INTERSECT ( subquery ) – ( subquery ) EXCEPT ( subquery )
76 Set Semantics for Set Operations Although the SELECT-FROM-WHERE statement uses bag semantics, the default for union, intersection, and difference is set semantics. – That is, duplicates are eliminated as the operation is applied.
77 Motivation: Efficiency When doing projection, it is easier to avoid eliminating duplicates.

#### You've reached the end of your free preview.

Want to read all 167 pages?

• Fall '14
• From Sells
Stuck? We have tutors online 24/7 who can help you get unstuck.
Ask Expert Tutors You can ask You can ask You can ask (will expire )
Answers in as fast as 15 minutes