Example drinker bar drinker beer tv1 tv2 Sally Bud Sally Joe's check for Joe Likes Frequents check these to output are equal
Example drinker bar drinker beer tv1 tv2 Sally Bud Sally Joe’s Likes Frequents to output check these are equal check for Joe
Explicit Tuple-Variables Sometimes,a query needs to use two copies of the same relation. Distinguish copies by following the relation name by the name of a tuple- variable,in the FROM clause. It's always an option to rename relations this way,even when not essential
Explicit Tuple-Variables zSometimes, a query needs to use two copies of the same relation. zDistinguish copies by following the relation name by the name of a tuplevariable, in the FROM clause. zIt ’s always an option to rename relations this way, even when not essential
Example:Self-Join From Beers(name,manf),find all pairs of beers by the same manufacturer. Do not produce pairs like(Bud,Bud) Produce pairs in alphabetic order,e.g. (Bud,Miller),not (Miller,Bud). SELECT b1.name,b2.name FROM Beers bl,Beers b2 WHERE bl.manf b2.manf AND b1.name b2.name
Example: Self-Join zFrom Beers(name, manf), find all pairs of beers by the same manufacturer. – Do not produce pairs like (Bud, Bud). – Produce pairs in alphabetic order, e.g. (Bud, Miller), not (Miller, Bud). SELECT b1.name, b2.name FROM Beers b1, Beers b2 WHERE b1.manf = b2.manf AND b1.name < b2.name;
Subqueries Result of a select-from-where query can be used in the where- clause of another query. Simplest Case:Subquery Returns a Single,Unary Tuple Find bars that serve Miller at the same price Joe charges for Bud. Sells(bar,beer,price) SELECT bar FROM Sells WHERE beer ='Miller'AND price (SELECT price FROM Sells WHERE bar ='Joe''s Bar'AND beer ='Bud'); Notice the scoping rule:an attribute refers to the most closely nested relation with that attribute. Parentheses around subquery are essential
Subqueries Result of a select-from-where query can be used in the whereclause of another query. Simplest Case: Subquery Returns a Single, Unary Tuple Find bars that serve Miller at the same price Joe charges for Bud. Sells(bar, beer, price) SELECT bar FRO M Sells W HERE beer= 'Miller' AND price = (SELECT price FRO M Sells W HERE bar ='Joe''s Bar' AND beer = 'Bud'); z Notice the scoping rule: an attribute refers to the most closely nested relation with that attribute. z Parentheses around subquery are essential
TheIN Operator "Tuple I relation"is true iff the tuple is in the relation. Example Find the name and manufacturer of beers that Fred likes. Beers(name,manf) Likes (drinker,beer) SELECT FROM Beers WHERE name I (SELECT beer FROM Likes WHERE drinker ='Fred) Also:NOT IN
The IN Operator “TupleIN relation” is true iff the tuple is in the relation. Example Find the name and manufacturer of beers that Fred likes. Beers(name, manf) Likes(drinker,beer) SELECT * FROM Beers W HERE name IN (SELECT beer FROM Likes W HERE drinker = 'Fred’); z Also: NOT IN