Previous | Table of Contents | Next

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.TXT—Example 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.TXT—Example 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.TXT—Example 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.

Using Variables

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.TXT—Using 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.

Performing Column Arithmetic and Combination

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.TXT—Example 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.TXT—Example 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.TXT—Example 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.TXT—Example of Using an Expression in the Where Clause

SQL> select last_name, first_name, wages
2  from employee
                                                                          continues

Previous | Table of Contents | Next