Sub queries Used mainly in clauses and with FROM quan - - PDF document

sub queries used mainly in clauses and with from quan ti
SMART_READER_LITE
LIVE PREVIEW

Sub queries Used mainly in clauses and with FROM quan - - PDF document

Sub queries Used mainly in clauses and with FROM quan tiers and FORALL . EXISTS Example: Sub query in FROM Find the man ufacturers of the b eers serv ed at Jo e's. SELECT DISTINCT b.manf FROM ( SELECT


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