Using Conditional Retrieval › Using Keywords in Predicates › Exercise 4-8

Exercise 4-8

Now You Try It

The Payroll department needs to identify employees whose salaries are $41,600, $45,240, or $50,440 for tax rate purposes. Using the BENEFITS table, write a SELECT statement to display employee ID and salary.

The result looks like this:

 EMP_ID   SALARY_AMOUNT
 ------   -------------
   4773        45240.00
   3769        41600.00

 2 rows processed

If your results do not match what you see above, check Review Answers for Chapter 4 for the correct SQL syntax.

You can insert NOT before IN to identify values you do not want returned.

The LIKE Predicate and Masks

You can use the keyword LIKE and mask characters to find a character string when you know or are concerned about only some of the characters. Mask characters are symbols that serve as place holders for other characters. For example, LIKE 'Th%' means everything beginning with Th. The % is the mask specifying that any number of characters can follow.

This table shows the symbols you can use as mask characters:

How It's Done

The company nurse wants to identify all employees whose last names begin with S in order to notify them that their yearly physical examination is due. Enter:

select emp_lname
       from employee
       where emp_lname like 'S%';

Enter the character string to be matched exactly as you expect to find it in the database. Use uppercase and lowercase letters as necessary.

The result looks like this:

EMP_LNAME
---------
Sampson
Smith
Smith
Spade

4 rows processed

Where to Place Mask Characters

Mask characters can come anywhere in the search string.

If you want to find all employees whose names contain mp, enter:

select emp_lname
       from employee
       where emp_lname like '%mp%';

The result looks like this:

EMP_LNAME
---------
Sampson
Thompson
Thompson
Thompson

4 rows processed