Ex #2: Find names of people who bought American products Ex #3: Find names of people who bought American products and did not buy French products Ex #4: Find names of people who bought American products and they live in Charlotte. Ex #5: Find people who bought stuff from Joe or bought products from a company whose stock prices is more than $50.
57 Subqueries • A parenthesized SELECT-FROM-WHERE statement ( subquery ) can be used as a value in a number of places, including FROM and WHERE clauses. • Example: in place of a relation in the FROM clause, we can place another query, and then query its result. – Better use a tuple-variable to name tuples of the result. – E.g., select * from (select * from T) as T1
58 Subqueries That Return One Tuple • If a subquery is guaranteed to produce one tuple, then the subquery can be used as a value. – Usually, the tuple has one component. – Also typically, a single tuple is guaranteed by keyness of attributes. – A run-time error occurs if there is no tuple or more than one tuple.
59 Example • From Sells(bar, beer, price), find the bars that serve Summerbrew for the same price Joe charges for Bud. • Can we answer it without using subqueries? • Two queries would surely work: 1. Find the price Joe charges for Bud. 2.Find the bars that serve Sumerbrew at that price.
- Fall '14
- From Sells