slides7 - Schedule Today: Jan. 24 (TH) x x Subqueries,...

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

View Full Document Right Arrow Icon
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)
Background image of page 1

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

View Full DocumentRight Arrow Icon
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 );
Background image of page 2
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.
Background image of page 3

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

View Full DocumentRight Arrow Icon
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.
Background image of page 4
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-
Background image of page 5

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

View Full DocumentRight Arrow Icon
Image of page 6
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 18

slides7 - Schedule Today: Jan. 24 (TH) x x Subqueries,...

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

View Full Document Right Arrow Icon
Ask a homework question - tutors are online