Code Listing 6: Query using less than or equal to operator
SQL> select first_name, last_name, salary
2 from employee
3 where salary <= 80000;
FIRST_NAME LAST_NAME SALARY
——————————————————————————————————————————— ——————————————————————————————————————————— ————————
Frances Newton 75000
Donald Newton 80000
Matthew Michaels 70000
3 rows selected.
Code Listing 7: Query using LIKE operator with literal string and wildcard values
SQL> select first_name, last_name, salary
2 from employee
3 where last_name like 'N%w%';
FIRST_NAME LAST_NAME SALARY
——————————————————————————————————————————— ——————————————————————————————————————————— ————————
Frances Newton 75000
Donald Newton 80000
2 rows selected.
Code Listing 8: Query using LIKE operator with wildcard and literal values
SQL> select first_name, last_name
2 from employee
3 where last_name like '__ w%';
FIRST_NAME LAST_NAME
——————————————————————————————————————————— ———————————————————————————————————————————
Frances Newton
Donald Newton
2 rows selected.
Code Listing 9: Query using IN operator with a list of values
SQL> select first_name, last_name, salary
2 from employee
3 where salary in (75000, 85000, 100000);
FIRST_NAME LAST_NAME SALARY
——————————————————————————————————————————— ——————————————————————————————————————————— ————————
Frances Newton 75000
Emily Eckhardt 100000
2 rows selected.
Code Listing 10: Query using NOT and LIKE operators
SQL> select first_name, last_name
2 from employee
3 where last_name NOT LIKE 'N%';
FIRST_NAME LAST_NAME
——————————————————————————————————————————— ———————————————————————————————————————————
Emily Eckhardt
Matthew Michaels
2 rows selected.
certain criteria. In Listing 6, the less than
or equal to operator, <=, is used to fetch a
list of employees whose yearly salary is less
than or equal to $80,000.
MATCH WHAT YOU LIKE
Whenever you don’t know or remember
the exact spelling of a data value such as
a name or you suspect data corruption
(incorrect values in your database), you
may want to perform an inexact search.
The LIKE operator can help you carry out
such a task. This operator performs pattern
matching by using wildcard characters.
The underscore (_) wildcard denotes a
single character, and the percentage (%)
wildcard denotes any number of characters
(including zero characters). The query in
Listing 7 obtains records in which the last
name begins with the uppercase letter N
and contains the lowercase letter w. In the
query in Listing 7, an unknown number of
characters can exist between the N and the
w, and an unknown number of characters
can exist after the w—hence the use of two
% wildcards in the expression.
Consider the query in Listing 8. In
this example, the WHERE clause limits
the result set to rows in which the last
name begins with two characters, has a
lowercase letter w as the third character,
and ends with any character(s) or at the
third character. You can place the or _
wildcard character anywhere within a literal
character string (which, as always, must be
enclosed in single quotation marks).
THE IN CROWD
The IN operator evaluates a comma-delimited list of values enclosed within a
set of parentheses. The query in Listing 9
uses the IN operator to retrieve employees
who have an annual salary of $75,000,
$85,000, or $100,000.
NEGATING WI TH NOT
The BET WEEN, IN, and LIKE comparison
operators can all be negated with the NOT
logical operator. (I’ll describe logical operators shortly.) Consider the query in Listing 10.
This query returns all the employees whose
last name does not begin with an uppercase
letter N.
