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' );
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';
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');
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
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);
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
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);
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 )
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
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
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
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 !
How to implement them?

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

Want to read all 149 pages?

• Spring '18
• From Sells