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' );
Image of page 64
Example 65
Image of page 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';
Image of page 66
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');
Image of page 67
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
Image of page 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);
Image of page 69
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
Image of page 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);
Image of page 71
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
Image of page 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
Image of page 73
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
Image of page 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 )
Image of page 75
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.
Image of page 76
77 Motivation: Efficiency When doing projection, it is easier to avoid eliminating duplicates.
Image of page 77
Image of page 78

You've reached the end of your free preview.

Want to read all 167 pages?

  • Fall '14
  • From Sells

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

Stuck? We have tutors online 24/7 who can help you get unstuck.
A+ icon
Ask Expert Tutors You can ask You can ask You can ask (will expire )
Answers in as fast as 15 minutes