Datasheet

Writing Simple Queries 43
CUSTOMER# NOT NULL VARCHAR2 (12)
BACK_ORDER CHAR (1)
ORD_STATUS CHAR (1)
TOTAL_AMT NOT NULL NUMBER (18,4)
SALES_TAX NUMBER (12,2)
The objective of the query is to find the completed orders that do not have any sales tax.
You want to see the order number and total amount of the order. The corresponding col-
umns that appear in the
SELECT clause are ORDER# and TOTAL_AMT. Since you’re interested
in only the rows with no sales tax in the completed orders, the columns to appear in the
WHERE clause are SALES_TAX (checking for zero sales tax) and ORD_STATUS (checking for
the completeness of the order, which is status code
C). Since the query returns multiple
rows, you want to order them by the order number. Notice that the
SALES_TAX column can
be
NULL, so you want to make sure you get all rows that have a sales tax amount of zero
or
NULL.
SELECT order#, total_amt
FROM purchase_orders
WHERE ord_status = ‘C’
AND (sales_tax IS NULL
OR sales_tax = 0)
ORDER BY order#;
An alternative is to use the NVL function to deal with the NULL values. This function is dis-
cussed in Chapter 2.
Using Expressions
An expression is a combination of one or more values, operators, and SQL functions that
result in a value. The result of an expression generally assumes the datatype of its compo-
nents. The simple expression
5+6 evaluates to 11 and assumes a datatype of NUMBER.
Expressions can appear in the following clauses:
The

SELECT clause of queries
The

WHERE clause, ORDER BY clause, and HAVING clause
The

VALUES clause of the INSERT statement
The

SET clause of the UPDATE statement
I will review the syntax of using these statements in later chapters.
You can include parentheses to group and evaluate expressions and then apply the result
to the rest of the expression. When parentheses are used, the expression in the innermost
95127c01.indd 43 2/18/09 6:37:10 AM