# slides7 - Schedule Today Jan 24(TH x x Subqueries Grouping...

This preview shows pages 1–6. Sign up to view the full content.

Winter 2002 Arthur Keller – CS 180 7–1 Schedule Today: Jan. 24 (TH) Subqueries, Grouping and Aggregation. Read Sections 6.3-6.4. Project Part 2 due. Jan. 29 (T) Modifications, Schemas, Views. Read Sections 6.5-6.7. Assignment 3 due. Jan. 31 (TH) Constraints. Read Sections 7.1-7.3, 7.4.1. Project Part 3 due. Feb. 5 (T) Triggers, PL/SQL. Read Sections 7.4, 8.2. Assignment 4 due. Reminder: Midterm is Feb. 14 (TH)

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
Winter 2002 Arthur Keller – CS 180 7–2 Union, Intersection, Difference “(subquery) UNION (subquery)” produces the union of the two relations. Similarly for INTERSECT , EXCEPT = intersection and set difference. But: in Oracle set difference is MINUS , not EXCEPT . Example Find the drinkers and beers such that the drinker likes the beer and frequents a bar that serves it. Likes(drinker , beer ) Sells(bar , beer , price) Frequents(drinker , bar ) (SELECT * FROM Likes) INTERSECT (SELECT drinker, beer FROM Sells, Frequents WHERE Frequents.bar = Sells.bar );
Winter 2002 Arthur Keller – CS 180 7–3 Forcing Set/Bag Semantics Default for select-from-where is bag; default for union, intersection, and difference is set. Why? Saves time of not comparing tuples as we generate them. But we need to sort anyway when we take intersection or difference. (Union seems to be thrown in for good measure!) Force set semantics with DISTINCT after SELECT . But make sure the extra time is worth it.

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
Winter 2002 Arthur Keller – CS 180 7–4 Example Find the different prices charged for beers. Sells(bar , beer , price) SELECT DISTINCT price FROM Sells; Force bag semantics with ALL after UNION , etc.
Winter 2002 Arthur Keller – CS 180 7–5 Join-Based Expressions A number of forms are provided. Can be used either stand-alone (in place of a select-from-

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
This is the end of the preview. Sign up to access the rest of the document.

## This note was uploaded on 02/21/2011 for the course CS CS 180 taught by Professor Dr.arthur during the Fall '01 term at The University of Akron.

### Page1 / 18

slides7 - Schedule Today Jan 24(TH x x Subqueries Grouping...

This preview shows document pages 1 - 6. Sign up to view the full document.

View Full Document
Ask a homework question - tutors are online