Previous | Table of Contents | Next

Page 48

Listing 3.8Continued

ROOSEVELT       THEODORE
ANTHONY         SUSAN
ROOSEVELT       ELEANOR

18 rows selected.

Greater Than (>) and Less Than (<)

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

Using the IN Operator

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

Using the BETWEEN Operator

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

Using the LIKE Operator

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

Using the NOT Operator

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

Understanding Multiple Conditions

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

Previous | Table of Contents | Next