Page 52
Listing 3.15Continued
TAFT WILLIAM 1 record selected.
In this next example, only one of the three conditions must be met for the set of conditions to be evaluated as true and the record selected (see Listing 3.16).
Listing 3.16L_03_16.TXTExample of the Use of `Or' in the Where Clause
SQL> select last_name, first_name, wages 2 from employee 3 where last_name = `TRUMAN' 4 or first_name = `WILLIAM' 5 or wages = 13000; LAST_NAME FIRST_NAME WAGES --------------- --------------- --------- CLINTON WILLIAM 15000 CARTER JIMMY 13000 FORD GERALD 13000 TRUMAN HAROLD 11000 TAFT WILLIAM 8500 5 records selected.
Problems sometimes occur when the two symbols `and' and `or' are both used in the same set of conditional statements. When conditions are linked using these words, sometimes it is necessary to group sets of statements together using parentheses (()). These enable the developer to change the logic of the condition. Listing 3.17 demonstrates the use of and, or, and () to document the selection criteria.
Listing 3.17L_03_17.TXTExample of Using the `and' and `or' in the Where Clause Using Parentheses
SQL> select last_name, first_name, fk_department 2 from employee 3 where last_name = `ROOSEVELT' 4 and (fk_department = `WEL' 5 or fk_department = `POL'); LAST_NAME FIRST_NAME FK_D --------------- --------------- ---- ROOSEVELT FRANKLIN POL ROOSEVELT ELEAOR WEL 2 records selected.
This query returns records of employees that have a last_name value of ROOSEVELT and are in department `WEL' or `POL.' If the parentheses are removed, as in Listing 3.18, the condition logic is changed.
Page 53
Listing 3.18L_03_18.TXTExample of How Removing Parentheses in the Where Clause Changes the Selection Logic
SQL> select last_name, first_name, fk_department 2 from employee 3 where last_name = `ROOSEVELT' 4 and fk_department = `WEL' 5 or fk_department = `POL'; LAST_NAME FIRST_NAME FK_D --------------- --------------- ---- JOHNSON LYNDON POL JOHNSON ANDREW POL CLINTON WILLIAM POL NIXON RICHARD POL KENNEDY JOHN POL ROOSEVELT FRANKLIN POL WILSON WOODROW POL ROOSEVELT ELEAOR WEL 8 rows selected.
This query returns the employees that have a last_name value of ROOSEVELT and are in department `WEL.' The change in the query causes all employees in the `POL' department to be selected even though their last_name does not equal ROOSEVELT.
Sometimes it is convenient to have variables in the
select statement. Variables can cause SQL*PLUS to prompt the user for a value when the script is executed or can contain
values that will be used in titles or footnotes. Prompting the user for a value is a useful device
because you can design one query that can produce multiple different reports based on
the user input. You may, for example, develop a query that produces a list of employees for a
department. If you make the department number expression a variable, you can use one
select script for multiple departments.
Listing 3.19 illustrates the use of an ampersand (&) character to prompt the user to enter a value when the script is executed. Whenever SQL*PLUS sees the &, it stops execution of the script and prompts for a value. To make the prompt more user-friendly, the example includes an accept statement that prints some boilerplate text describing for the user the information needed. Without the accept statement, SQL*PLUS shows only the name of the variable.
Listing 3.19L_03_19.TXTUsing a Variable and an Ampersand to Input Values into a Script
SQL> select last_name, first_name, wages 2 from employee 3 where fk_department = `&dpt'; Enter value for dpt: POL old 3: where fk_department = `&dpt' new 3: where fk_department = `POL'
continues
Page 54
Listing 3.19Continued
LAST_NAME FIRST_NAME WAGES --------------- --------------- --------- JOHNSON LYNDON 12000 JOHNSON ANDREW 7500 CLINTON WILLIAM 15000 NIXON RICHARD 12500 KENNEDY JOHN 11500 ROOSEVELT FRANKLIN 10400 WILSON WOODROW 9000 7 rows selected.
Sometimes it's advantageous to perform mathematical functions on columns in a select clause. SQL*PLUS enables you to place common arithmetic operators between numeric columns or arguments. The calculation is known as an expression.
Expressions contain several column names and are separated from other columns by a comma (,). SQL*PLUS treats expressions or the columns and operators between commas in the select clause as one virtual column. You can use the addition (+), subtraction (-), multiplication (*), and division (/) operators. Listing 3.20 shows an example of a numeric expression that computes the weekly wages for each employee.
Listing 3.20L_03_20.TXTExample of Column Arithmetic
SQL> select last_name, first_name, wages, wages/52 2 from employee; LAST_NAME FIRST_NAME WAGES WAGES/52 --------------- --------------- --------- -------- COOLIDGE CALVIN 9500 182.69231 JOHNSON LYNDON 12000 230.76923 REAGAN RONALD 13500 259.61538 BUSH GEORGE 14000 269.23077 JOHNSON ANDREW 7500 144.23077 CLINTON WILLIAM 15000 288.46154 CARTER JIMMY 13000 250 . 19 records selected.
Character columns can be combined by using the concatenation (||) operator. This operator is used to combine table columns and literals. Literals are string characters listed in the select clause that are repeated on every row. The example shown in Listing 3.21 concatenates the literal ", "between the columns last_name and first_name.
Page 55
Listing 3.21L_03_21.TXTExample of Column Concatenation
SQL> select last_name||','||first_name 2 from employee; LAST_NAME||','||FIRST_NAME COOLIDGE,CALVIN JOHNSON,LYNDON REAGAN,RONALD BUSH,GEORGE JOHNSON,ANDREW CLINTON,WILLIAM CARTER,JIMMY . 19 records selected
When SQL*PLUS creates a virtual column using an expression, it places the entire expression in the default column heading. The reason for this is the expression becomes the de facto column name. It is the name used in order by and group by clauses as well as in column headings. Using a column alias can change this. The alias is a character string placed immediately behind any column or expression. Aliases that are one word may be entered without any single quotes. Aliases that are two or more words or that contain upper case characters are enclosed by double quotes (see Listing 3.22).
Listing 3.22L_03_22.TXTExample of Column Aliases
SQL> select last_name||','||first_name name, 2 wages "ANNUAL WAGES" 3 from employee; NAME ANNUAL WAGES ------------------------------- ------------ COOLIDGE,CALVIN 9500 JOHNSON,LYNDON 12000 REAGAN,RONALD 13500 BUSH,GEORGE 14000 JOHNSON,ANDREW 7500 . 19 records selected.
Finally, as Listing 3.23 shows, expressions may also be used in where clauses:
Listing 3.23L_03_23.TXTExample of Using an Expression in the Where Clause
SQL> select last_name, first_name, wages 2 from employee
continues