Databases : SQL Multi-Relations

1. Databases : SQL Multi-Relations 2007, Fall Pusan National University Ki-Joune Li These slides are made from the materials that Prof. Jeffrey D. Ullman distributes via his course web page (http://infolab.stanford.edu/~ullman/dscb/gslides.html)

2. Multi-Relation Queries • More than one relation. • Several relations in the FROM clause. • Distinguish attributes of the same name • “<relation>.<attribute>” • Example: • Using Likes(drinker, beer) and Frequents(drinker, bar), find the beers liked by at least one person who frequents Joe’s Bar. SELECT beer FROM Likes, Frequents WHERE bar = ‘Joe’’s Bar’ ANDFrequents.drinker = Likes.drinker;

3. Formal Semantics • Almost the same as for single-relation queries: • Start with the product of all the relations in the FROM clause. • Apply the selection condition from the WHERE clause. • Project onto the list of attributes and expressions in the SELECT clause.

4. Operational Semantics • Imagine one tuple-variable for each relation in the FROM clause. • These tuple-variables visit each combination of tuples, one from each relation. • If the tuple-variables are pointing to tuples that satisfy the WHERE clause, send these tuples to the SELECT clause. • Nested Algorithm For each tuple r1 in R1 For each tuple r2 in R2 if the condition(r1, r2) in WHERE clause is true then print the attributes in SELECT clause • Problem of this nested Algorithm SELECT R.AFROM R, S, SWHERE R.A = S.A OR R.A = T.Awhen T is empty set

5. to output check for Joe check these are equal Example drinker bar drinker beer b1 b2 Sally Bud Sally Joe’s Likes Frequents

6. Explicit Tuple-Variables • Sometimes, a query needs to use two copies of the same relation. • Distinguish copies by tuple-variables in FROM clause. • Example: From Beers(name, manf), • find all pairs of beers by the same manf. • 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;

7. Union, Intersection, Difference • Example • MovieStar(name, address, gender, birthdate)MovieExec(name, address, cert#, netWorth) • Find names and addresses of all female movie stars who are also movie executives with a net worth over $10M(SELECT name, address FROM MovieStar WHERE gender=‘F’) INTERSECT (SELECT name, address FROM MovieExec WHERE netWorth > 10000000)

8. Subqueries • Parenthesized SFW statement (subquery) can be used • as a value: returns ONE tuple • as tuples: returns a set of tuples • related with relations: returns Boolean value • in FROM clause

9. Subqueries That Return One Tuple • If a subquery is guaranteed to produce one tuple, then the subquery can be used as a value. • Usually, the tuple has one component. • Also typically, a single tuple is guaranteed by “keyness” of attributes. • A run-time error occurs if there is no tuple or more than one tuple.

10. Example • From Sells(bar, beer, price), find the bars that serve Miller for the same price Joe charges for Miller. • Two queries would surely work: • Find the price Joe charges for Miller. • Find the bars that serve Miller at that price.

11. Query + Subquery Solution The price at which Joe sells Bud SELECT bar FROM Sells WHERE beer = ‘Miller’ AND price = ( SELECT price FROM Sells WHERE bar = ‘Joe’’s Bar’ AND beer = ‘Miller’); SELECT s1.bar FROM Sells s1,Sells s2 WHERE s1.beer = ‘Miller’ AND s1.price=s2.price AND s2.bar=‘Joe’’s Bar’ AND s2.beer = ‘Miller’;

12. Subqueries That Return More than one Tuple • If a subquery may produce more than one tuple, • then the subquery can be used as a set of values • Set operator are used: IN and NOT IN operators • IN operator • <tuple> IN <relation> is true if and only if the tuple is a member of the relation. • <tuple> NOT IN <relation> means the opposite. • IN-expressions can appear in WHERE clauses. • The <relation> is often a subquery.

13. Example • From Beers(name, manf) and Likes(drinker, beer), find the name and manufacturer of each beer that Fred likes. SELECT * FROM Beers WHERE name IN (SELECT beer FROM Likes WHERE drinker = ‘Fred’); The set of beers Fred likes SELECT beer.* FROM Beers beer, Likes like WHERE beer.name=Likes.beer AND Likes.drinker=‘Fred’;

14. The Exists Operator • EXISTS( <relation> ) is true if and only if the <relation> is not empty. • Being a boolean-valued operator, EXISTS can appear in WHERE clauses. • Example: • From Beers(name, manf), • find those beers that are the unique beer by their manufacturer.

15. Example Query with EXISTS SELECT name FROM Beers b1 WHERE NOT EXISTS( SELECT * FROM Beers WHERE manf = b1.manf AND name <> b1.name); Notice scope rule: manf refers to closest nested FROM with a relation having that attribute. Set of beers with the same manf as b1, but not the same beer

16. The Operator ANY • x = ANY( <relation> ) is a boolean condition meaning that x equals at least one tuple in the relation. • Similarly, = can be replaced by any of the comparison operators. • Example: x >= ANY( <relation> ) means x is not smaller than all tuples in the relation. • Note tuples must have one component only.

17. The Operator ALL • Similarly, x <> ALL( <relation> ) is true if and only if for every tuple t in the relation, x is not equal to t. • That is, x is not a member of the relation. • The <> can be replaced by any comparison operator. • Example: x >= ALL( <relation> ) means there is no tuple larger than x in the relation.

18. Example • From Sells(bar, beer, price), • find the beer(s) sold for the highest price. price from the outer Sells must not be less than any price. SELECT beer FROM Sells WHERE price >= ALL( SELECT price FROM Sells);

19. Example • From Movie(title, year, length, inColor, studioName, producerC#), • find movie titles used for more than one film year of movie production with the same title. SELECT title FROM Movie Old WHERE year < ANY( SELECT year FROM Movie title=Old.title);

20. Subqueries in FROM Clause • Subqueries can be used in FROM Clause • StarsIn(movieTitle, movieYear, starName)MovieExec(name, address, cert#, netWorth)Movie(title, year, length, inColor, studioName, producerC#) • Find producer the names of Harrison Ford’s movies Foreign Key SELECT name FROM MovieExec, (SELECT producerC# FROM Movie, StarsIN WHERE title=movieTitle AND year=movieYear AND starName=‘Harrison Ford’ ) ProdWHERE cert#=Prod.producerC#; Set of producerC# of Harrison Ford’s movies

21. JOIN • Example • MovieStar(name, address, gender, birthdate)MovieExec(name, address, cert#, netWorth) • CROSS JOIN: Cartisan Product • Movie CROSS JOIN StarsIN • JOIN: Theta Join • Movie JOIN StarsIN on title=movieTitle AND year movieYear • NATURAL JOIN: Natural Join • MovieStar NATURAL JOIN MovieExec Condition of Theta Join Join on (name, address)