Listing 1, you already know the name, hire
date, and salary of the employee named
Matthew Michaels. To get the same infor-
mation for all other employees, you could
execute the query in Listing 2. As you can
see, the query uses the inequality operator,
!=, and retrieves every row except the one
with the LAST_NAME value of Michaels.
Code Listing 1: Query for finding employees whose salary equals $70,000
SQL> select first_name, last_name, hire_date, salary
2 from employee
3 where salary = 70000;
FIRST_NAME LAST_NAME HIRE_DATE SALARY
——————————————————————————————— ————————————————————————————— ———————————————————————————— ————————
Matthew Michaels 16-MAY-07 70000
1 row selected.
Code Listing 2: Query that excludes the employee Michaels
SQL> select first_name, last_name, hire_date, salary
2 from employee
3 where last_name != 'Michaels';
FIRST_NAME LAST_NAME HIRE_DATE SALARY
——————————————————————————————— ————————————————————————————— ———————————————————————————— ————————
Frances Newton 14-SEP-05 75000
Emily Eckhardt 07-JUL-04 100000
Donald Newton 24-SEP-06 80000
3 rows selected.
Code Listing 3: Query using a literal value (case-sensitive) in a WHERE clause condition
SQL> select first_name, last_name, hire_date, salary
2 from employee
3 where last_name = 'MICHAELS';
no rows selected
Be aware that when you compare a database column value with a character literal, or
string, the case of the data contained in the
database column must, by default, exactly
match the case of the data contained in the
string. The query in Listing 3 returns no rows,
because the case of the string denoting the
employee’s last name is different from that
of the data stored in the EMPLOYEE table’s
LAST_NAME column.
You’ll learn about converting string literal
data to match the case of data contained in
a database column (and vice versa) in subsequent articles in this series.
Note in the example in Listing 3 that
when you compare a string literal with a
database column value, you must enclose
the string literal value in single quotation marks. The same requirement is true
for comparing date literals with database
column values.
Any two values you compare with each
other must be of the same datatype.
Compare only numbers with numbers,
strings with strings, and dates with dates.
Whenever possible, Oracle Database will
perform an implicit datatype conversion,
but in general, you should avoid allowing
Oracle Database to do so. The query in
Listing 4 will return a result, but as a best
practice, you should never compare a
number with a string.
Code Listing 4: Query that performs an implicit datatype conversion
SQL> select first_name, last_name, hire_date, salary
2 from employee
3 where salary = '70000';
FIRST_NAME LAST_NAME HIRE_DATE SALARY
——————————————————————————————— ————————————————————————————— ———————————————————————————— ————————
Matthew Michaels 16-MAY-07 70000
1 row selected.
Code Listing 5: Query for selecting records within a range of values
SQL> select first_name, last_name, salary
2 from employee
3 where salary BETWEEN 75000 and 85000;
FIRST_NAME LAST_NAME SALARY
——————————————————————————————————————————— ——————————————————————————————————————————— ————————
Frances Newton 75000
Donald Newton 80000
2 rows selected.
THE RANGE OF INCLUSION
Sometimes you need to obtain a set of
records (rows) that falls within a certain
range of values. You can do so with the
BET WEEN operator, as in Listing 5.
The results of a BET WEEN operation
can include the listed values that define
the range. Therefore, in the example
in Listing 5, the result list includes an
employee with a salary of $75,000, the
lower end of the range, along with one
whose salary of $80,000 is between
the upper and lower listed values. The
BET WEEN operator is used most often for
number and date comparisons.
THE GREATER AND THE LESSER
The comparison operators >, >=, <, and
<= are useful if you need to obtain a set
of records that fall either above or below
