Java Reference
In-Depth Information
Figure 7-5:
Subquery to find above average purchases
The
next section
discusses a number of additional ways in which subqueries can be used.
Additional Uses of Subqueries
Earlier examples discussed how you can use calculated values, or even literals, in place of simple data
field values in the
SELECT
clause of a query, as in the following example:
SELECT 'Average Cost' AS Name, AVG(oi.Qty * i.Cost)
AS 'AVG'
FROM Ordered_Items oi, Inventory i
WHERE oi.Item_Number = i.Item_Number;
This query returns the following result:
Name
AVG
Average Cost
3.7045
Using a Subquery in the SELECT List
You can also use results returned by subqueries in the
SELECT
list of a query. This can be useful if you
want to create a summary table of items by category, as might be the case with the Inventory table. If,
for example, you wanted to tabulate the average cost of various types of products in the inventory, you
could use a query with subqueries in the command line, like the example shown in
Figure 7-6
.
