But: in Oracle 7.3.2 set dierence is MINUS , not EXCEPT . - - PDF document

but in oracle 7 3 2 set di erence is minus not except
SMART_READER_LITE
LIVE PREVIEW

But: in Oracle 7.3.2 set dierence is MINUS , not EXCEPT . - - PDF document

Union, In tersection, Dierence \Relation relation" pro duces the union of UNION the t w o relations. Similarly for INTERSECT , = EXCEPT in tersection and set dierence. But: in Oracle 7.3.2 set


slide-1
SLIDE 1 Union, In tersection, Dierence \Relation UNION relation" pro duces the union
  • f
the t w
  • relations.
  • Similarly
for INTERSECT, EXCEPT = in tersection and set dierence.

But: in Oracle 7.3.2 set dierence is MINUS, not EXCEPT. Example Find the drink ers and b eers suc h that the drink er lik es the b eer and frequen ts a bar that serv es it. Likes(drinker , beer ) Sells(bar , beer , price) Frequents(drinker , bar) Likes INTERSECT (SELECT drinker, beer FROM Sells, Frequents WHERE Frequents.bar = Sells.bar ); 1
slide-2
SLIDE 2 Bag Seman tics
  • f
SQL An SQL relation is really a b ag
  • r
multiset.
  • It
ma y con tain the same tuple more than
  • nce,
although there is no sp ecied
  • rder
(unlik e a list).
  • Example:
f1; 2; 1; 3g is a bag and not a set. Bag Union Sum the times an elemen t app ears in the t w
  • bags.
  • Example:
f1; 2; 1g [ f1; 2; 3g = f1; 1; 1; 2; 2; 3g. Bag In tersection T ak e the minim um
  • f
the n um b er
  • f
  • ccurrences
in eac h bag.
  • Example:
f1; 2; 1g \ f1; 2; 3g = f1; 2g. Bag Dierence Prop er-subtract the n um b er
  • f
  • ccurrences
in the t w
  • bags.
  • Example:
f1; 2; 1g
  • f1;
2; 3g = f1g. 2
slide-3
SLIDE 3 La ws for Bags Dier F rom La ws for Sets
  • Some
familiar la ws con tin ue to hold for bags.

Examples: union and in tersection are still comm utativ e and asso ciativ e.
  • But
  • ther
la ws that hold for sets do not hold for bags. Example R \ (S [ T )
  • (R
\ S ) [ (R \ T ) holds for sets.
  • Let
R , S , and T eac h b e the bag f1g.
  • Left
side: S [ T = f1; 1g; R \ (S [ T ) = f1g.
  • Righ
t side: R \ S = R \ T = f1g; (R \ S ) [ (R \ T ) = f1; 1g 6= f1g. 3
slide-4
SLIDE 4 F
  • rcing
Set/Bag Seman tics
  • Default
for select-from-where is bag; default for union, in teresection, and dierence is set.

Wh y? Sa v es time
  • f
not comparing tuples as w e generate them.

But w e need to sort an yw a y when w e tak e in tersection
  • r
dierence. (Union seems to b e thro wn in for go
  • d
measure!)
  • F
  • rce
set seman tics with DISTINCT after SELECT.

But mak e sure the extra time is w
  • rth
it. Example Find the dieren t prices c harged for b eers. Sells(bar , beer , price) SELECT DISTINCT price FROM Sells;
  • F
  • rce
bag seman tics with ALL after UNION, etc. 4
slide-5
SLIDE 5 Aggregations Sum, a vg, min, max, and coun t apply to attributes/columns. Also, coun t(*) applies to tuples.
  • Use
these in lists follo wing SELECT. Example Find the a v erage price
  • f
Bud. Sells(bar , beer , price) SELECT AVG(price) FROM Sells WHERE beer = 'Bud';
  • Coun
ts eac h tuple (presumably eac h bar that sells Bud)
  • nce.
Problem What w
  • uld
w e do if Sells w ere a bag? 5
slide-6
SLIDE 6 Eliminating Duplicates Before Aggregation Find the n um b er
  • f
dieren t prices at whic h Bud is sold. Sells(bar , beer , price) SELECT COUNT(DISTINCT price) FROM Sells WHERE beer = 'Bud';
  • DISTINCT
ma y b e used in an y aggregation, but t ypicall y
  • nly
mak es sense with COUNT. 6
slide-7
SLIDE 7 Grouping F
  • llo
w select-from-where b y GROUP BY and a list
  • f
attributes.
  • The
relation that is the result
  • f
the FROM and WHERE clauses is group ed according to the v alues
  • f
these attributes, and aggregations tak e place
  • nly
within a group. Example Find the a v erage sales price for eac h b eer. Sells(bar , beer , price) SELECT beer, AVG(price) FROM Sells GROUP BY beer; 7
slide-8
SLIDE 8 Example Find, for eac h drink er, the a v erage price
  • f
Bud at the bars they frequen t. Sells(bar , beer , price) Frequents(drinker , bar) SELECT drinker, AVG(price) FROM Frequents, Sells WHERE beer = 'Bud' AND Frequents.bar = Sells.bar GROUP BY drinker;
  • Note:
grouping
  • ccurs
after the pro duct and selection. 8
slide-9
SLIDE 9 Restriction
  • n
SELECT Lists With Aggregation If an y aggregation is used, then e ach elemen t
  • f
a SELECT clause m ust either b e aggregated
  • r
app ear in a group-b y clause. Example The follo wing migh t seem a tempting w a y to nd the bar that sells Bud the c heap est: Sells(bar , beer , price) SELECT b ar, MIN(pric e) FR OM Sel ls WHERE b e er = 'Bud';
  • But
it is illegal in SQL2. Problem Ho w w
  • uld
w e nd that bar? 9
slide-10
SLIDE 10 HAVING clauses
  • \HAVING
condition" eliminates groups when condition is false.
  • Condition
can use the tuple v ariables
  • r
relations in the FROM and their attributes, just lik e the WHERE can.

But the t.v.'s range
  • nly
  • v
er the group. 10
slide-11
SLIDE 11 Example Find the a v erage price
  • f
those b eers that are either serv ed in at least 3 bars
  • r
man ufactured b y Anheuser-Busc h. Beers(name , manf) Sells(bar , beer , price) SELECT beer, AVG(price) FROM Sells GROUP BY beer HAVING COUNT(bar) >= 3 OR beer IN ( SELECT name FROM Beers WHERE manf = 'Anheuser-Busch' ); 11
slide-12
SLIDE 12 DB Mo dications Mo dic ation = insert + delete + up date. Insertion
  • f
a T uple INSERT INTO relation VALUES (list
  • f
v alues).
  • Inserts
the tuple = list
  • f
v alues, asso ciating v alues with attributes in the
  • rder
the attributes w ere declared.

F
  • rget
the
  • rder?
List the attributes as argumen ts
  • f
the relation. Example Likes(drinker , beer ) Insert the fact that Sally lik es Bud. INSERT INTO Likes(drinker, beer) VALUES('Sally', 'Bud'); 12
slide-13
SLIDE 13 Insertion
  • f
the Result
  • f
a Query INSERT INTO relation (sub query). Example Create a (unary) table
  • f
all Sally's p
  • ten
tial buddies, i.e., the p eople who frequen t bars that Sally also frequen ts. Frequents(drinker , bar) CREATE TABLE PotBuddies( name char(30) ); INSERT INTO PotBuddies (SELECT DISTINCT d2.drinker FROM Frequents d1, Frequents d2 WHERE d1.name = 'Sally' AND d2.name <> 'Sally' AND d1.bar = d2.bar ); 13
slide-14
SLIDE 14 Deletion DELETE FROM relation WHERE condition.
  • Deletes
all tuples satisfying the condition from the named relation. Example Sally no longer lik es Bud. Likes(drinker , beer ) DELETE FROM Likes WHERE drinker = 'Sally' AND beer = 'Bud'; Example Mak e the Likes relation empt y . DELETE FROM Likes; 14
slide-15
SLIDE 15 Example Delete all b eers for whic h there is another b eer b y the same man ufacturer. Beers(name , manf) DELETE FROM Beers b WHERE EXISTS (SELECT name FROM Beers WHERE manf = b.manf AND name <> b.name );
  • Note
alias for relation from whic h deletion
  • ccurs.
15
slide-16
SLIDE 16
  • Seman
tics is tric ky . If A.B. mak es Bud and BudLite, do es deletion
  • f
Bud mak e BudLite not satisfy the condition?

SQL2 formal seman tics sa ys \no." Oracle implemen ts this deletion prop erly (i.e., b
  • th
get deleted).
  • General
principle: all conditions in mo dications should b e ev aluated b y the system b efore an y mo ds due to that mo d command
  • ccur.

Esp ecially imp
  • rtan
t when sub queries are used, b ecause they can refer to the relation b eing mo died. 16
slide-17
SLIDE 17 Up dates UPDATE relation SET list
  • f
assignmen ts WHERE condition. Example Drink er F red's phone n um b er is 555-1212. Drinkers(name , addr, phone) UPDATE Drinkers SET phone = '555-1212' WHERE name = 'Fred'; Example Mak e $4 the maxim um price for b eer.
  • Up
dates man y tuples at
  • nce.
Sells(bar , beer , price) UPDATE Sells SET price = 4.00 WHERE price > 4.00; 17