Ex 2 Find names of people who bought American products Ex 3 Find names of

Ex 2 find names of people who bought american

This preview shows page 55 - 68 out of 139 pages.

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.
Image of page 55
56 Subqueries
Image of page 56
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
Image of page 57
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.
Image of page 58
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.
Image of page 59
Image of page 60
Image of page 61
Image of page 62
Image of page 63
Image of page 64
Image of page 65
Image of page 66
67Example
Image of page 67
Image of page 68

You've reached the end of your free preview.

Want to read all 139 pages?

  • Fall '14
  • From Sells

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

Stuck? We have tutors online 24/7 who can help you get unstuck.
A+ icon
Ask Expert Tutors You can ask You can ask You can ask (will expire )
Answers in as fast as 15 minutes