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

Union, In tersection, Di�erence \Relation relation" pro duces the union of UNION the t w o relations. � Similarly for INTERSECT , = EXCEPT in tersection and set di�erence. ✦ But: in Oracle 7.3.2 set di�erence 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

Bag Seman tics of SQL An SQL relation is really a b ag or multiset . � It ma y con tain the same tuple more than once, although there is no sp eci�ed order (unlik e a list). � Example: f 1 ; 2 ; 1 ; 3 g is a bag and not a set. Bag Union Sum the times an elemen t app ears in the t w o bags. � Example: f 1 ; 2 ; 1 g [ f 1 ; 2 ; 3 g = f 1 ; 1 ; 1 ; 2 ; 2 ; 3 g . Bag In tersection T ak e the minim um of the n um b er of o ccurrences in eac h bag. � Example: f 1 ; 2 ; 1 g \ f 1 ; 2 ; 3 g = f 1 ; 2 g . Bag Di�erence Prop er-subtract the n um b er of o ccurrences in the t w o bags. � Example: f 1 ; 2 ; 1 g � f 1 ; 2 ; 3 g = f 1 g . 2

La ws for Bags Di�er 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 other la ws that hold for sets do not hold for bags. Example \ ( S [ ) � ( R \ ) [ ( R \ ) holds for sets. R T S T � Let , , and eac h b e the bag f 1 g . R S T � Left side: [ = f 1 ; 1 g ; \ ( S [ ) = f 1 g . S T R T � Righ t side: \ = \ = f 1 g ; ( R \ ) [ R S R T S ( R \ ) = f 1 ; 1 g 6 = f 1 g . T 3

F orcing Set/Bag Seman tics � Default for select-from-where is bag; default for union, in teresection, and di�erence is set. ✦ Wh y? Sa v es time of 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 or di�erence. (Union seems to b e thro wn in for go o d measure!) � F orce set seman tics with after DISTINCT SELECT . ✦ But mak e sure the extra time is w orth it. Example Find the di�eren t prices c harged for b eers. Sells(bar , beer , price) SELECT DISTINCT price FROM Sells; � F orce bag seman tics with after UNION , etc. ALL 4

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 of 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) once. Problem What w ould w e do if w ere a bag? Sells 5

Eliminating Duplicates Before Aggregation Find the n um b er of di�eren t prices at whic h Bud is sold. Sells(bar , beer , price) SELECT COUNT(DISTINCT price) FROM Sells WHERE beer = 'Bud'; � ma y b e used in an y aggregation, but DISTINCT t ypicall y only mak es sense with COUNT . 6

Grouping F ollo w select-from-where b y and a list of GROUP BY attributes. � The relation that is the result of the and FROM clauses is group ed according to the WHERE v alues of these attributes, and aggregations tak e place only 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

Example Find, for eac h drink er, the a v erage price of 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 o ccurs after the pro duct and selection. 8

Restriction on Lists With SELECT Aggregation If an y aggregation is used, then e ach elemen t of a clause m ust either b e aggregated or app ear SELECT 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 ould w e �nd that bar? 9

clauses HAVING � \ HAVING condition" eliminates groups when condition is false. � Condition can use the tuple v ariables or relations in the and their attributes, just FROM lik e the can. WHERE ✦ But the t.v.'s range only o v er the group. 10

Example Find the a v erage price of those b eers that are either serv ed in at least 3 bars or 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

DB Mo di�cations Mo di�c ation = insert + delete + up date. Insertion of a T uple relation (list of v alues). INSERT INTO VALUES � Inserts the tuple = list of v alues, asso ciating v alues with attributes in the order the attributes w ere declared. ✦ F orget the order? List the attributes as argumen ts of the relation. Example Likes(drinker , beer ) Insert the fact that Sally lik es Bud. INSERT INTO Likes(drinker, beer) VALUES('Sally', 'Bud'); 12

Insertion of the Result of a Query relation (sub query). INSERT INTO Example Create a (unary) table of all Sally's p oten 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

Deletion relation condition. DELETE FROM WHERE � 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 relation empt y . Likes DELETE FROM Likes; 14

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 o ccurs. 15

� Seman tics is tric ky . If A.B. mak es Bud and BudLite, do es deletion of 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 oth get deleted). � General principle: all conditions in mo di�cations should b e ev aluated b y the system b efore an y mo ds due to that mo d command o ccur. ✦ Esp ecially imp ortan t when sub queries are used, b ecause they can refer to the relation b eing mo di�ed. 16

Up dates relation list of assignmen ts UPDATE SET 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 once. Sells(bar , beer , price) UPDATE Sells SET price = 4.00 WHERE price > 4.00; 17