1 / 21

Databases : SQL Multi-Relations

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 ). Multi-Relation Queries.

gema
Download Presentation

Databases : SQL Multi-Relations

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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)

More Related