Study with the several resources on Docsity
Earn points by helping other students or get them with a premium plan
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
The concept of subqueries and collections in object query language (oql). It covers the use of subqueries in from clauses with quantifiers like exists and forall, and the use of boolean-valued expressions in where clauses with for all and exists. The document also provides examples and explanations of how to extract collection elements and perform aggregation using oql.
Typology: Slides
1 / 16
This page cannot be seen from the preview
Don't miss anything!
Sub queries
Used mainly in FROM clauses and with quanti ers EXISTS and FORALL.
Example: Sub query in FROM
Find the manufacturers of the b eers served at Jo e's. SELECT DISTINCT b.manf FROM ( SELECT s.beer FROM Sells s WHERE s.bar.name = "Joe's Bar" ) b
Quanti ers
Bo olean-valued expressions for use in WHERE- clauses. FOR ALL x IN
The expression has value TRUE if the condition is true for all (resp. at least one) elements 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.
Problem
How would you nd the bars that only sold b eers for more than $5?
Extraction of Collecti on Elements
a) A collect i on with a single memb er: Extract the memb er with ELEMENT.
Example
Find the price Jo e charges for Bud and put the result in a variable p.
p = ELEMENT( SELECT s.price FROM Sells s WHERE s.bar.name = "Joe's Bar" AND s.beer.name = "Bud" )
b) Extracting all elements of a collecti on, one at a time:
Example
Print Jo e's menu, 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 );
Grouping
Recall SQL grouping, for example:
SELECT bar, AVG(price) FROM Sells GROUP BY bar;
Is the bar value the \name" of the group, or the common value for the bar comp onent of all tuples in the group?
In SQL it do esn't matter, but in OQL, you can create groups from the values of any function(s), not just attributes. F Thus, groups are identi ed by common values, not \name." F Example: group by rst letter of bar names (metho d needed).
Outline of OQL Group-By
Collectio n De ned by FROM, WHERE
Collecti on with function values and partition
Group by values of function(s)
Terms from SELECT clause
Output collecti on
F One function: s.bar.name maps Sell ob jects s to the value of the name of the bar referred to by s. F Collecti on is a set of structs of typ e: Struct{barName: string, partition: Set< Struct{s: Sell}
} For example: Struct(barName = "Joe's Bar", partition = fs 1 ; : : : ; sn g) where s 1 ; : : : ; sn are all the structs with one eld, named s, whose value is one of the Sell ob jects that represent Jo e's Bar selling some b eer.
F Typ e of structures in the output: Struct{barName: string, avgPrice: real}
F Note that in the sub query of the SELECT clause: SELECT barName, avgPrice: AVG( SELECT p.s.price FROM partition p ) We let p range over all structs in partition. Each of these structs contains a single eld named s and has a Sell ob ject as its value. Thus, p.s.price extracts the price from one of the Sell ob jects. F Typical output struct: Struct(barName = "Joe's Bar", avgPrice = 2.83)
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.
