Page 44
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.TXTExample 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.
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.TXTExample 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 |
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.TXTExample 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.
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.TXTExecuting 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