Database Reference
In-Depth Information
Computed Fields
You can use a computation in place of a field by typing the computation. For readability, you can type the
computation in parentheses, although it is not necessary to do so.
The following SELECT clause selects the CustomerNum and CustomerName columns as well as the
results of subtracting the Balance column from the CreditLimit column:
348
SELECT CustomerNum, CustomerName, CreditLimit-Balance
Functions
You can use aggregate functions in a SELECT clause. The most commonly used functions are AVG (to calcu-
late an average), COUNT (to count the number of rows), MAX (to determine the maximum value), MIN (to
determine the minimum value), and SUM (to calculate a total).
The following SELECT clause calculates the average balance:
SELECT AVG(Balance)
CONDITIONS
A condition is an expression that can be evaluated as either true or false. When you use a condition in a
WHERE clause, the results of the query contain those rows for which the condition is true. You can create
simple conditions and compound conditions using the BETWEEN, LIKE, and IN operators, as described in the
following sections.
Simple Conditions
A simple condition includes the field name, a comparison operator, and another field name or a value. The
available comparison operators are
(equal to),
(less than),
(greater than),
(less than or equal to),
¼
<
>
<¼
(not equal to).
The following WHERE clause uses a condition to select rows in which the balance is greater than the
credit limit:
(greater than or equal to), and
>¼
<>
WHERE Balance
>
CreditLimit
Compound Conditions
Compound conditions are formed by connecting two or more simple conditions using one or both of the fol-
lowing operators: AND and OR. You can also precede a single condition with the NOT operator to negate a
condition. When you connect simple conditions using the AND operator, all the simple conditions must be
true for the compound condition to be true. When you connect simple conditions using the OR operator, the
compound condition will be true whenever any of the simple conditions are true. Preceding a condition with
the NOT operator reverses the truth or falsity of the original condition. That is, if the original condition is
true, the new condition will be false; if the original condition is false, the new one will be true.
The following WHERE clause is true if those parts for which the warehouse number is equal to 3 or the
number of units on hand is greater than 20:
WHERE Warehouse
¼'
3
'
OR OnHand
>
20
The following WHERE clause is true if those parts for which both the warehouse number is equal to 3
and the number of units on hand is greater than 20:
WHERE Warehouse
¼'
3
'
AND OnHand
>
20
The following WHERE clause is true if the warehouse number is not equal to 3:
WHERE NOT (Warehouse
¼'
3
'
)
