[PDF] - Sub queries Used mainly in clauses and with FROM quan PDF Document

SLIDE 1 Sub queries

Used

mainly in FROM clauses and with quan tiers EXISTS and FORALL. Example: Sub query in FROM Find the man ufacturers

f

the b eers serv ed at Jo e's. SELECT DISTINCT b.manf FROM ( SELECT s.beer FROM Sells s WHERE s.bar.name = "Joe's Bar" ) b 1

SLIDE 2 Quan tiers

Bo
lean-v

alued expressions for use in WHERE- clauses. FOR ALL x IN <collecti

n>

: <condition> EXISTS x IN <collecti

n>

: <condition>

The

expression has v alue TRUE if the condition is true for all (resp. at least

ne)

elemen ts

f

the collect i

n.

Example Find all bars that sell some b eer for more than $5. SELECT b.name FROM Bars b WHERE EXISTS s IN b.beersSold : s.price > 5.00 Problem Ho w w

uld

y

u

nd the bars that

nly

sold b eers for more than $5? 2

SLIDE 3 Example Find the bars suc h that the

nly

b eers they sell for more than $5 are man ufactured b y P ete's. SELECT b.name FROM Bars b WHERE FOR ALL be IN ( SELECT s.beer FROM b.beersSold s WHERE s.price > 5.00 ) : be.manf = "Pete's" 3

SLIDE 4 Extraction

f

Collecti

n

Elemen ts a) A collect i

n

with a single mem b er: Extract the mem b er with ELEMENT. Example Find the price Jo e c harges for Bud and put the result in a v ariable p. p = ELEMENT( SELECT s.price FROM Sells s WHERE s.bar.name = "Joe's Bar" AND s.beer.name = "Bud" ) 4

SLIDE 5 b) Extracting all elemen ts

f

a collecti

n,
ne

at a time: 1. T urn the collecti

n

in to a list. 2. Extract elemen ts

f

a list with <list name>[i]. Example Prin t Jo e's men u, in

rder
f

price, with b eers

f

the same price listed alphab etical ly . L = SELECT s.beer.name, s.price FROM Sells s WHERE s.bar.name = "Joe's Bar" ORDER BY s.price, s.beer.name; printf("Beer\tPri ce\n\ n"); for(i=1; i<=COUNT(L); i++) printf("%s\t%f\n ", L[i].name, L[i].price ); 5

SLIDE 6 Aggregation The v e

p

erators a vg, min, max, sum, coun t apply to an y collecti

n,

as long as the

p

erators mak e sense for the elemen t t yp e. Example Find the a v erage price

f

b eer at Jo e's. x = AVG( SELECT s.price FROM Sells s WHERE s.bar.name = "Joe's Bar" );

Note

co ersion: result

f

SELECT is tec hnically a bag

f

1-eld structs, whic h is iden tied with the bag

f

the v alues

f

that eld. 6

SLIDE 7 Grouping Recall SQL grouping, for example: SELECT bar, AVG(price) FROM Sells GROUP BY bar;

Is

the bar v alue the \name"

f

the group,

r

the common v alue for the bar comp

nen

t

f

all tuples in the group?

In

SQL it do esn't matter, but in OQL, y

u

can create groups from the v alues

f

an y function(s), not just attributes.

✦

Th us, groups are iden tied b y common v alues, not \name."

✦

Example: group b y rst letter

f

bar names (metho d needed). 7

SLIDE 8 Outline

f

OQL Group-By Collectio n Dened b y FROM, WHERE Collecti

n

with function v alues and partition Group b y v alues

f

function(s) T erms from SELECT clause Output collecti

n

8

SLIDE 9 Example Find the a v erage price

f

b eer at eac h bar. SELECT barName, avgPrice: AVG( SELECT p.s.price FROM partition p ) FROM Sells s GROUP BY barName: s.bar.name 1. Initial collecti

n

= Sells.

✦

But tec hnicall y , it is a bag

f

structs

f

the form Struct(s: s1) Where s1 is a Sell

b

ject. Note, the lone eld is named s; in general, there are elds for all

f

the \t ypical

b

jects" in the FROM clause. 9

SLIDE 10 2. In termediate collect io n:

✦

One function: s.bar.name maps Sell

b

jects s to the v alue

f

the name

f

the bar referred to b y s.

✦

Collecti

n

is a set

f

structs

f

t yp e: Struct{barName: string, partition: Set< Struct{s: Sell} > } F

r

example: Struct(barName = "Joe's Bar", partition = fs 1 ; : : : ; s n g) where s 1 ; : : : ; s n are all the structs with

ne

eld, named s, whose v alue is

ne
f

the Sell

b

jects that represen t Jo e's Bar selling some b eer. 10

SLIDE 11 3. Output collecti

n:

consists

f

b eer-a v erage price pairs,

ne

for eac h struct in the in termediate collecti

n.

✦

T yp e

f

structures in the

utput:

Struct{barName: string, avgPrice: real}

✦

Note that in the sub query

f

the SELECT clause: SELECT barName, avgPrice: AVG( SELECT p.s.price FROM partition p ) W e let p range

v

er all structs in partition. Eac h

f

these structs con tains a single eld named s and has a Sell

b

ject as its v alue. Th us, p.s.price extracts the price from

ne
f

the Sell

b

jects.

✦

T ypical

utput

struct: Struct(barName = "Joe's Bar", avgPrice = 2.83) 11

SLIDE 12 Another, Less T ypical Example Find, for eac h b eer, the n um b er

f

bars that c harge a \lo w" price ( 2:00) and a \high" price ( 4:00) for that b eer.

Strategy:

group b y three things: 1. The b eer name, 2. A b

lean

function that is true i the price is lo w. 3. A b

lean

function that is true i the price is high. 12

SLIDE 13 The Query SELECT beerName, low, high, count: COUNT(partition) FROM Beers b, b.soldBy s GROUP BY beerName: b.name, low: s.price <= 2.00, high: s.price >= 4.00 1. Initial collecti

n:

P airs (b; s), where b is a Beer

b

ject, and s is a Sell

b

ject represen ting the sale

f

that b eer at some bar.

✦

T yp e

f

collecti

n

mem b ers: Struct{b: Beer, s: Sell} 13

SLIDE 14 2. In termediate collect io n: Quadruples consisting

f

a b eer name, b

leans

telli ng whether this group is for high, lo w,

r

neither prices for that b eer, and the partition for that group.

✦

The partition is a set

f

structs

f

the t yp e: Struct{b: Beer, s: Sell} A t ypical v alue: Struct(b: "Bud"

b

ject, s: a Sell

b

ject in v

lving

Bud) 14

SLIDE 15

✦

T yp e

f

quadruples in the in termediate collect ion: Struct{ beerName: string, low: boolean, high: boolean, partition: Set<Struct{ b: Beer, s: Sell }> } T ypical structs in in termediate collecti

n:

b eerName lo w high partition Bud TR UE F ALSE S low Bud F ALSE TR UE S hig h Bud F ALSE F ALSE S mid

where

S low S hig h , and S mid are the sets

f

b eer- sells pairs (b; s) where the b eer is Bud and s has, resp ectiv ely , a lo w ( 2:00), high ( 4:00) and medium (b et w een 2.00 and 4.00) price.

Note

the partition with low = high = TRUE m ust b e empt y and will not app ear. 15

SLIDE 16 3. Output collecti

n:

The rst three comp

nen

ts

f

eac h group's struct are copied to the

utput,

and the last (partition) is coun ted. The result: b eerName lo w high coun t Bud TR UE F ALSE 27 Bud F ALSE TR UE 14 Bud F ALSE F ALSE 36

16