Filter
Top Products
WHERE PRODUCT = 'TELEVISION' AND STOCKQTY >= 90
Example 8:
In this example, rows for which STOCKQTY is 10 or greater and PRODUCT is VIDEO CASSETTE
PLAYER, and rows for which STOCKQTY is 200 or greater and PRODUCT is TELEVISION are
fetched from the STOCK table.
SELECT ... FROM STOCKS. STOCK
WHERE STOCKQTY >= 10 AND PRODUCT = 'VIDEO CASSETTE PLAYER'
OR STOCKQTY >= 200 AND PRODUCT = 'TELEVISION'
Example 9:
In this example, rows for which PRODUCT is other than TELEVISION or VIDEO CASSETTE PLAYER
are fetched from the STOCK table.
SELECT ... FROM STOCKS. STOCK
WHERE NOT PRODUCT = 'TELEVISION'
AND NOT PRODUCT = 'VIDEO CASSETTE PLAYER'
Using a subquery in a comparison predicate
If a value specified in a comparison predicate in a search condition is a value derived from a function, a subquery can
be coded in the search condition.
The part of the subquery enclosed by parentheses has the same format as the query specification. However, for a
subquery, the derived table must have one column. This means that one value expression (column name, function,
and so on) must be specified in the left-hand side of the search condition. The following is an example of a
comparison predicate in which a subquery is specified:
Example 10:
In this example, the average for PRICE is calculated from the ORDER table, and CUSTOMER is
fetched from the ORDER table where PRICE is higher than average.
91