SQL Basics - Java Database Programming Bible - page 72

Table 3-6: Inventory

ID Name Description Qty Cost

1003 Shredded Wheat Cereal 103 2.05

1004 Oatmeal Cereal 15 0.98

1005 Chocolate Chip Cookies 217 1.26

1006 Fig Bar Cookies 162 1.57

1007 Sugar Cookies Cookies 276 1.03

1008 Cola Soda 144 0.61

1009 Lemon Soda Soda 96 0.57

1010 Orange Soda Soda 84 0.71

This query returns the following result:

ID Name Description Qty Cost

1004 Oatmeal Cereal 15 0.98

You can also perform a calculation in a WHERE clause. For example, if you normally
mark up the cost of an item by 60% to get the sales price, you can perform this
calculation in the WHERE clause. To list only items whose retail price is below 100.00,
use this format:

SELECT Name,Description,Cost,Cost*1.6 AS Retail
FROM Inventory
WHERE Cost * 1.6 < 100;

Creating calculated result columns

Arithmetic operators are also very useful for creating a calculated result field. For
example, you can calculate a retail price by marking up a cost as follows:

SELECT ID,Name,Description,Cost,Cost*1.6 AS Retail
FROM Inventory;

This query returns the additional column "Retail," as shown in Table 3-7 .

Table 3-7: Calculated Result Fields

ID Name Description Cost Retail

1001 Corn Flakes Cereal 1.95 3.12