Java Reference
In-Depth Information
Table 7-4: Calculated Result Fields
ID
Name
Description
Cost
Retail
1006
Fig Bar
Cookies
1.57
2.512
1007
Sugar Cookies
Cookies
1.03
1.648
1008
Cola
Soda
0.61
0.976
1009
Lemon
Soda
0.57
0.912
1010
Orange
Soda
0.71
1.136
Aliases
In the preceding example, the expression command uses the key word
AS
. Using the optional
AS
clause enables you to assign a meaningful name, or
alias
, to an expression, which makes it easier to
refer back to the expression later on. An alias can be used as a normal column name when you need
to refer to the column elsewhere in a statement, as you will see in examples later in the topic. In this
example
, AS
assigned the name (alias) "Retail" to the calculated value column.
When assigning and using an alias, you must bear in mind the order in which SQL processes the
various clauses constituting the command, since the output of one clause is the input to the next one.
The order in which the subclauses of a SQL command are processed is shown in the following list:
FROM
clause
WHERE
clause
GROUP BY
clause
HAVING
clause
SELECT
clause
ORDER BY
clause
Since you used
AS
to assign an alias in the
SELECT
clause, you can't use the alias as part of the
predicate in a
WHERE
clause, since the
WHERE
clause has already been executed by the time you get to
the
SELECT
. The alias can, however, be used in an
ORDER BY
, if, for example, you wanted to order the
inventory table by Retail, as shown here:
SELECT ID,Name,Description,Cost,Cost*1.6 AS Retail
FROM Inventory ORDER BY Retail;
Tip
When you create a calculated field in a result, you should always use
AS
to assign a name
to the field. This is because there is no defined naming convention for calculated fields in
SQL. Different variants of SQL assign different arbitrary names.
Arithmetic operators can also be used in the
WHERE
clause. For example, to list only items whose retail
price is below 100, you would use the following code:
SELECT Name,Description,Cost,Cost*1.6 AS Retail
FROM Inventory
