Java Reference
In-Depth Information
WHERE Cost * 1.6 < 100;
You can also create more complex calculations as required. The following query will return the profit on
each item as well as the retail price:
SELECT Name,Description,Cost,Cost*1.6 as Retail,Cost*1.6 - Cost AS Profit
FROM Inventory
WHERE Cost * 1.6 < 100;
The preceding code will generate the results in
Table 7-5
.
Table 7-5: More Complex Calculated Columns
Name
Description
Cost
Retail
Profit
Cola
Soda
0.61
0.976
0.366
Lemon
Soda
0.57
0.912
0.342
Miscellaneous Operators: IN and BETWEEN
The
IN
operator provides a simple way to compare fields against a list. For example, to find contacts in
New York State or New Jersey, you can use this query:
SELECT *
FROM Contact_Info
WHERE State IN ('NY', 'NJ');
IN
also works with numbers. For example, if you wanted to select items from the inventory table by
ID
,
you could use this query:
SELECT *
FROM Inventory
WHERE ID IN (1001, 1003, 1004);
The
BETWEEN
operator, as its name suggests, helps you select fields with values that fall between
specified limits. Referring again to the Inventory table (
Table 7-3
), you can query for items with costs in
the $1.03-$1.95 range using the query. Here's an example:
SELECT *
FROM Inventory
WHERE Cost BETYOUEN 1.03 AND 1.95;
Note
BETWEEN
returns values
within
its defined range inclusive of the limits, so if you try the
query against the Inventory table, it will return rows with costs of 1.03 and 1.95.
Set Operators
