Why Bud appears twice 47 Formal Semantics Almost the same as for

Why bud appears twice 47 formal semantics almost the

This preview shows page 47 - 65 out of 676 pages.

Why "Bud" appears twice? 47
Image of page 47
Reason 48
Image of page 48
49 Formal Semantics Almost the same as for single-relation queries: 1. Start with the product of all the relations in the FROM clause. 2. Apply the selection condition from the WHERE clause. 3. Project onto the list of attributes and expressions in the SELECT clause.
Image of page 49
50 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.
Image of page 50
51 Example drinker bar drinker beer tv1 tv2 Jennifer Bud Jennifer Joe's Likes Frequents to output check these are equal check for Joe • Find beers liked by drinkers who frequent Joe's bar
Image of page 51
52 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.
Image of page 52
53 Example 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 b1, Beers b2 WHERE b1.manf = b2.manf AND b1.name < b2.name;
Image of page 53
Example 54
Image of page 54
55 Subqueries
Image of page 55
56 Subquery in the from clause A parenthesized SELECT-FROM-WHERE statement ( subquery ) can be used in FROM clause • Example: – select * from (select * from Beers) as b – Note tuple variable needed to name the relation generated by the subquery
Image of page 56
Subquery in the where clause Introduced by '=' (or '!=') – x = (subquery) – x can be an attribute or a tuple of attributes – Subquery needs to return exactly one result Introduced by 'in' (or 'not in') – x in (subquery) – Subquery may return multiple results 57
Image of page 57
Subquery introduced by '=' Subquery needs to return exactly one result! 58 Return > 1 tuple select * from Beers where (name, manf) = (select name, manf from Beers where name = 'Bud'); select * from Beers where (name, manf) = (select name, manf from Beers where manf = 'Anheuser-Busch');
Image of page 58
Subquery introduced by 'in' Subquery may return multiple results 59 select * from Beers where (name, manf) in (select name, manf from Beers where manf = 'Anheuser-Busch');
Image of page 59
60 Example From Beers(name, manf) and Likes(drinker, beer), find the name and manufacturer of each beer that Steve or Bill likes. 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 60
Example 61
Image of page 61
Without subquery Does this query produce the same result? 62 SELECT name, manf FROM Beers b, Likes l WHERE b.name = l.beer and l.drinker = 'Steve' or l.drinker = 'Bill';
Image of page 62
Correct equivalent subquery Note the "distinct" and grouping (using parentheses) of two conditions on drinker 63 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 63
Introduced by comparison operators <comparison operator> <any/all> (subquery) – Comparison operators: =, !=, <, >, <=, >=, <> • Examples – x >= all (subquery) – x <= all (subquery)
Image of page 64
Image of page 65

You've reached the end of your free preview.

Want to read all 676 pages?

  • Fall '14

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture