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 57 - 70 out of 149 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 57
Without subquery Does this query produce the same result? 58 SELECT name, manf FROM Beers b, Likes l WHERE b.name = l.beer and l.drinker = 'Steve' or l.drinker = 'Bill';
Image of page 58
Correct equivalent subquery Note the "distinct" and grouping (using parentheses) of two conditions on drinker 59 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 59
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)"? 60
Image of page 60
61 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);
Image of page 61
Introduced by "exists" or ”in" Exists exists (subquery): evaluated to true if subquery has at least one result IN X in (subquery): evaluated to true if element X is in table resulting from subquery. NOT EXISTS and NOT IN also possible 62
Image of page 62
63 Example Query with EXISTS Where 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 63
Often Interchangable… 64 -- IN SELECT * FROM Employee WHERE DeptName IN ( SELECT DeptName FROM Dept ) -- EXISTS SELECT * FROM Employee WHERE EXISTS ( SELECT 1 FROM Dept WHERE Employee.DeptName = Dept.DeptName )
Image of page 64
IN vs. Exists While there is no general rule as to whether you should prefer IN or EXISTS, but: IN predicates tend to be more readable than EXISTS predicates EXISTS predicates tend to be more expressive than IN predicates (i.e. it is easier to express very complex SEMI JOIN) There is no formal difference in performance. There may, however, be a huge performance difference on some databases . 65
Image of page 65
Agenda SQL DML (Data Manipulation Language) SQL query Relations as bags Joins Grouping and aggregation Database modification SQL DDL (Data Definition Language) Define schema 66
Image of page 66
67 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 67
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 68 !
Image of page 68
How to implement them?
Image of page 69
Image of page 70

You've reached the end of your free preview.

Want to read all 149 pages?

  • Spring '18
  • From Sells

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture