Page 48
Listing 3.8Continued
ROOSEVELT THEODORE ANTHONY SUSAN ROOSEVELT ELEANOR 18 rows selected.
The greater than and less than operators evaluate to true if the value on the open side of the arrow is greater than the value on the closed side of the arrow. The statement is read left to right. A > B reads A is greater than B, and A < B reads A is less than B. When evaluating character values, upper case characters (A) have a smaller value than lower case characters (a). Alpha characters (a or A) are greater than numeric characters. Listing 3.9 shows an example of greater than conditional select statement.
Listing 3.9L_03_09.TXTUsing the Greater Than Operator in a Conditional Select Statement
SQL> select last_name, first_name 2 from employee 3 where last_name > `HOOVER'; LAST_NAME FIRST_NAME --------------- --------------- JOHNSON LYNDON REAGAN RONALD JOHNSON ANDREW NIXON RICHARD KENNEDY JOHN TRUMAN HAROLD ROOSEVELT FRANKLIN WILSON WOODROW TAFT WILLIAM ROOSEVELT THEODORE ROOSEVELT ELEANOR 11 rows selected.
When the value on both sides of the greater than operator are equal, the condition does not evaluate to true. If you would like to also select records that equal the evaluation condition, place an equal sign after the operator (>= , <=). This changes the operator to greater than or equal and less than or equal operators. Listing 3.10 shows an example of this type of operator:
Listing 3.10L_03_10.TXTExample of a Greater Than or Equal Conditional Statement
SQL> select last_name, first_name 2 from employee 3 where last_name >= `HOOVER';
Page 49
--------------- --------------- JOHNSON LYNDON REAGAN RONALD JOHNSON ANDREW NIXON RICHARD KENNEDY JOHN TRUMAN HAROLD ROOSEVELT FRANKLIN HOOVER HERBERT WILSON WOODROW TAFT WILLIAM ROOSEVELT THEODORE ROOSEVELT ELEANOR 12 rows selected.
The IN operator enables you to evaluate one argument against a set of other arguments.
The syntax of the statement requires the set of values to be enclosed by parentheses
(()), and
you must separate each value by a comma (,). There is no limit to the number of values
that you can include in the set. This operator is useful because it reduces the number of condition
statements needed to obtain the data. To obtain the data without the operator, you must
list a statement for each value in the IN set. Listing 3.11 shows an example of an
IN conditional statement:
Listing 3.11L_03_11.TXTExample of an IN Operator
SQL> select last_name, first_name 2 from employee 3 where last_name in (`HOOVER', `ROOSEVELT'); LAST_NAME FIRST_NAME --------------- --------------- ROOSEVELT FRANKLIN HOOVER HERBERT ROOSEVELT THEODORE ROOSEVELT ELEANOR 4 rows selected.
This operator is also used to simplify the conditional statement in the where clauses. It enables the developer to select records that fall between two values. Records that equal the arguments are also selected. You can use this operator to compare numeric and character values. Listing 3.12 shows an example of a BETWEEN conditional statement:
Page 50
Listing 3.12L_03_12.TXTExample of the BETWEEN Operator
SQL> select last_name, first_name, wages 2 from employee 3 where wages between 13000 and 15000; LAST_NAME FIRST_NAME WAGES --------------- ---------------- -------- REAGAN RONALD 13500 BUSH GEORGE 14000 CLINTON WILLIAM 15000 CARTER JIMMY 13000 FORD GERALD 13000 5 rows selected.
The LIKE operator enables the user to select rows based on a specific set of characters in a character string. You cannot use this operator on numeric values. It compares the character-defined column against the identified pattern. Rows that contain a column value that match are selected.
Two symbols are used to record the sequence: the percent sign (%), and the underscore symbol (_). The % symbol designates the sequence of the character. For instance, `a%' means the column must begin with a small letter `a.' The % symbol tells Oracle to ignore any following characters. The string `a%q%' means that the column must begin with a small letter `a' and the string must also contain a small letter `q.' This example does not care what character falls between the `a' and `q' or what character falls after it. The string `a%q' is similar except that the string must end with a small letter `q.'
The `_' symbol is used to indicate the position of the character. For example, `_a%' means the column value must contain a value of `a' in the second position. It does care about any other characters. The string `%q_a' means that the column must have a letter `q' with a letter `a' following it two positions later. The `a' must also be the last character in the string because it is not followed by the `%' symbol.
Listing 3.13 demonstrates a LIKE conditional statement. The statement selects records that begin with a letter `R' in the last_name column and have a value of `HE' in the second and third positions of the first_name column.
Listing 3.13L_03_13.TXTExample of LIKE Operators Using Both Symbols
SQL> select last_name, first_name 2 from employee 3 where last_name like `R%' 4 and first_name like `_HE%'; LAST_NAME FIRST_NAME --------------- --------------- ROOSEVELT THEODORE 1 row selected.
Page 51
The NOT operator is used to negate any of the operators. The symbol for not is an exclamation mark (!) or the word not. Listing 3.14 shows an example of the NOT operator:
Listing 3.14L_03_14.TXTExample of the NOT Operator
SQL> select last_name, first_name 2 from employee 3 where last_name not like `ROOS%'; 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 HOOVER HERBERT WILSON WOODROW TAFT WILLIAM ANTHONY SUSAN 16 rows selected.
This query selects all rows from the employee table where the last_name column does not begin with the letters `ROOS.'
The examples portrayed thus far have contained at most two conditional statements. Generally, when obtaining records from the database, many conditional statements are required. Fortunately there are no limits to the number of conditions that you can include. Multiple statements are combined through the use of the `and' and `or' words. The `and' word means both the statement preceding it and following it must be true. The `or' word means either the statement preceding it or following it must be true. In the Listing 3.15, for example, both the conditions must be met for the record to be selected.
Listing 3.15L_03_15.TXTExample of Multiple Conditions
SQL> select last_name, first_name 2 from employee 3 where last_name = `TAFT' 4 and first_name = `WILLIAM'; LAST_NAME FIRST_NAME --------------- ---------------
continues