[PDF] - But: in Oracle 7.3.2 set dierence is MINUS , not EXCEPT . PDF Document

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 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 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 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 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 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 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 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 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 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 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 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 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 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 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

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 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