Previous | Table of Contents | Next

Page 44

Ordering Records

When the order by clause is placed in the statement, the records will be ordered. The order by clause accepts column names or the column position number. This number is the position of the column in the select statement. Listing 3.4 shows equivalent examples using each naming approach.

Listing 3.4L_03_04.txt —Ordering Records by Using the Column Name and Column Number

SQL> select first_name, last_name from employee
2  order by last_name;
FIRST_NAME      LAST_NAME
--------------- ---------------
SUSAN           ANTHONY
GEORGE          BUSH
JIMMY           CARTER
WILLIAM         CLINTON
CALVIN          COOLIDGE
DWIGHT          EISENHOWER
.
19 rows selected.
SQL> select first_name, last_name from employee
2  order by  2;
FIRST_NAME      LAST_NAME
--------------- ---------------
SUSAN           ANTHONY
GEORGE          BUSH
JIMMY           CARTER
WILLIAM         CLINTON
CALVIN          COOLIDGE
DWIGHT          EISENHOWER
.
19 rows selected.

Only columns in the select clause can be placed in the order by clause. The default column order is ascending (A_Z). You can change the type of ordering to descending by placing the Desc command following an order column. You can include more than one column in the
order by clause. Each of these columns may have its own order type. Listing 3.5 demonstrates the use of a multi-column ordered select clause.

Listing 3.5L_03_05.TXT—Example of Multiple Order Columns

SQL> select last_name, first_name
2  from employee
3  order by last_name desc, first_name;
LAST_NAME       FIRST_NAME
--------------- ---------------
WILSON          WOODROW
TRUMAN          HAROLD

Page 45

TAFT            WILLIAM
ROOSEVELT       ELEANOR
ROOSEVELT       FRANKLIN
ROOSEVELT       THEODORE
REAGAN          RONALD
.

19 rows selected.

Conditional Select Statements

A simple select statement, as demonstrated in Listing 3.5, displays all the records in a table because it does not contain any criteria to limit the selected records. Generally, a select statement or query needs to obtain a subset of a table's records. To accomplish this, the developer uses the where clause to list a set of conditions each record must match to be retrieved. A condition is a pair of columns or a column and a value separated by an operator such as an equal sign. To be selected, the conditions must evaluate to true. Listing 3.6 contains an example of a conditional statement.

Listing 3.6L_03_06.TXT—Example of a Conditional Select Statement

SQL> select last_name, first_name, payroll_number
2  from employee
3  where fk_department = `POL';
LAST_NAME       FIRST_NAME      PAYROLL_NUMBER
--------------- --------------- --------------
JOHNSON         LYNDON                      31
JOHNSON         ANDREW                      21
CLINTON         WILLIAM                     37
NIXON           RICHARD                     32
KENNEDY         JOHN                        30
ROOSEVELT       FRANKLIN                    27
WILSON          WOODROW                     24

7 rows selected.

This example selects only those records from the employee table that have a value in the department column of `POL'. Because department is defined as a character field in the table, you must enclose the value in single quotes. Numeric values are not enclosed by quotes.

NOTE
Oracle is case sensitive. This means that the value `A' does not equal `a.' The developer should be careful when using character columns in a condition and ensure they evaluate properly.

Unlike the order by clause, values in conditional statements do not have to be in the select clause. The reason is that the database manager or Oracle7 uses the values as it is reading records from the disk. The database manager reads a table record, evaluates the record's values, and places the record into memory if the values agree with the conditions in the select

Page 46

statement. The database manager knows the value of the columns because it has just read the values from the disk. When records are ordered, the database manager has retrieved the records from the disk and placed the identified columns in memory. If the order by columns are not in memory, the database cannot order the selected records.

Oracle has a number of logical operators that you can use in conditional statements. Table 3.2 displays a list of these.

Table 3.2 Logical Operators

Operator Description
= Equal to
!= OR <> Not equal to
Greater than
>= Greater than or equal to
< Less than
<= Less than or equal to
in Equal to any members of a list
between Between two values, inclusive
like Similar to a character pattern
is null Missing value
not Reverses any of the preceding four Operators

Equal Sign (=)

The first logical operator in Table 3.2 is the equality operator. This operator tests one value against another value to determine whether the values are the same. When testing for equality, character values must be the same case to be equal, and a numeric values is never equal to a character value. Character values must be enclosed by single quotes. Numeric values are not enclosed by quotes. Starting with Listing 3.7, the following two examples show a conditional select using an equality operator:

Listing 3.7L_03_07.TXT—Example of Character and Numeric Conditional Expressions

SQL> select last_name, first_name
2  from employee
3  where last_name = `HOOVER';
LAST_NAME       FIRST_NAME
--------------- ---------------

Page 47

HOOVER          HERBERT
SQL> select last_name, first_name
2  from employee
3  where wages = 13000;
LAST_NAME       FIRST_NAME
--------------- ---------------
CARTER          JIMMY
FORD            GERALD

The first query selects records where the value in the last_name column is equal to `HOOVER'. Last_name is a character field and the values are all uppercase. This means the value must be enclosed by single quotes, and the value must be capitalized.

The second query selects records where the value in the wages column = 13000. Wages is defined numeric in the database. This means the value must be numeric and must not be enclosed by single quotes.

Inequality Sign (<> or !=)

The inequality operator is the opposite of the equality operator. This operator means the first value in the clause does not equal the second value. Two symbols are used as the inequality operator. The first symbol is the left-pointing arrow followed by the right-pointing arrow (<>). The second is the exclamation mark followed by the equal sign (!=). You can use either symbol with the same effect. Listing 3.8's query performs the exact opposite of the equality query in the preceding example. This query selects the records that do not have a value in the last_name column equal to `HOOVER'.

Listing 3.8L_03_08.TXT—Executing a Not Equal Conditional Select Statement

SQL> select last_name, first_name
2  from employee
3  where last_name != `HOOVER';
LAST_NAME       FIRST_NAME
--------------- ---------------
COOLIDGE        CALVIN
JOHNSON         LYNDON
REAGAN          RONALD
BUSH            GEORGE
JOHNSON         ANDREW
CLINTON         WILLIAM
CARTER          JIMMY
FORD            GERALD
NIXON           RICHARD
KENNEDY         JOHN
EISENHOWER      DWIGHT
TRUMAN          HAROLD
ROOSEVELT       FRANKLIN
WILSON          WOODROW
TAFT            WILLIAM
                                                                        continues

Previous | Table of Contents | Next