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

Sub queries � Used mainly in clauses and with FROM quan ti�ers 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 s.beer FROM Sells s WHERE s.bar.name = "Joe's Bar" ) b 1

Quan ti�ers � Bo olean-v alued expressions for use in WHERE - clauses. < collecti on > FOR ALL x IN : < condition > < collecti on > EXISTS x IN : < condition > � The expression has v alue if the condition TRUE is true for all (resp. at least one) elemen ts of the collect i on. 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 ould y ou �nd the bars that sold b eers only for more than $5? 2

Example Find the bars suc h that the only 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

Extraction of Collecti on Elemen ts a) A collect i on 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

b) Extracting all elemen ts of a collecti on, one at a time: 1. T urn the collecti on in to a list. 2. Extract elemen ts of a list with < list name > [i] . Example Prin t Jo e's men u, in order of price, with b eers of 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

Aggregation The �v e op erators a vg, min, max, sum, coun t apply to an y collecti on, as long as the op erators mak e sense for the elemen t t yp e. Example Find the a v erage price of 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 of is tec hnically SELECT a bag of 1-�eld structs, whic h is iden ti�ed with the bag of the v alues of that �eld. 6

Grouping Recall SQL grouping, for example: SELECT bar, AVG(price) FROM Sells GROUP BY bar; � Is the v alue the \name" of the group, or bar the common v alue for the comp onen t of bar all tuples in the group? � In SQL it do esn't matter, but in OQL, y ou can create groups from the v alues of an y function(s), not just attributes. ✦ Th us, groups are iden ti�ed b y common v alues, not \name." ✦ Example: group b y �rst letter of bar names (metho d needed). 7

Outline of OQL Group-By Collectio n De�ned b y FROM, WHERE Group b y v alues of function(s) Collecti on with function v alues and partition T erms from clause SELECT Output collecti on 8

Example Find the a v erage price of 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 on = Sells . ✦ But tec hnicall y , it is a bag of structs of the form s 1 ) Struct(s: Where s 1 is a ob ject. Note, the lone Sell �eld is named s ; in general, there are �elds for all of the \t ypical ob jects" in the clause. FROM 9

2. In termediate collect io n: ✦ One function: maps s.bar.name Sell ob jects to the v alue of the name of the s bar referred to b y s . ✦ Collecti on is a set of structs of t yp e: Struct{barName: string, partition: Set< Struct{s: Sell} > } F or example: Struct(barName = "Joe's Bar", f s g ) partition = ; : : : ; s n 1 where are all the structs with s ; : : : ; s n 1 one �eld, named s , whose v alue is one of the ob jects that represen t Jo e's Bar Sell selling some b eer. 10

3. Output collecti on: consists of b eer-a v erage price pairs, one for eac h struct in the in termediate collecti on. ✦ T yp e of structures in the output: Struct{barName: string, avgPrice: real} ✦ Note that in the sub query of the SELECT clause: SELECT barName, avgPrice: AVG( SELECT p.s.price FROM partition p ) W e let range o v er all structs in p partition . Eac h of these structs con tains a single �eld named and has a s Sell ob ject as its v alue. Th us, p.s.price extracts the price from one of the Sell ob jects. ✦ T ypical output struct: Struct(barName = "Joe's Bar", avgPrice = 2.83) 11

Another, Less T ypical Example Find, for eac h b eer, the n um b er of 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 o olean function that is true i� the price is lo w. 3. A b o olean function that is true i� the price is high. 12

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 on: P airs ( b; s ), where b is a ob ject, and is a ob ject Beer s Sell represen ting the sale of that b eer at some bar. ✦ T yp e of collecti on mem b ers: Struct{b: Beer, s: Sell} 13

2. In termediate collect io n: Quadruples consisting of a b eer name, b o oleans telli ng whether this group is for high, lo w, or neither prices for that b eer, and the partition for that group. ✦ The partition is a set of structs of the t yp e: Struct{b: Beer, s: Sell} A t ypical v alue: ob ject , Struct(b: "Bud" a ob ject in v olving Bud ) s: Sell 14

✦ T yp e of 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 on: 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 , and are the sets of b eer- S S S low hig h mid sells pairs ( b; s ) where the b eer is Bud and has, s 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

3. Output collecti on: The �rst three comp onen ts of eac h group's struct are copied to the output, 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