Nesting SELECT Statements › Using EXISTS › Exercise 7-3

Exercise 7-3

Now You Try It

The budget group needs to know the department IDs of all departments where an employee earns more than $50,000. Enter a SELECT statement that will show this information using the POSITION and EMPLOYEE tables.

Note: Use DISTINCT to eliminate duplicates.

The result looks like this:

 DEPT_ID
 -------
    1100
    1120
    2200
    2210
    3510
    3530
    4500
    4600
    5000
    5200
    6200

 11 rows processed

If your results do not match what you see above, check Review Answers for Chapter 7 for the correct SQL syntax. Remember that result tables may be shortened in this guide.

Using NOT EXISTS

You may also want to retrieve information from a table provided that no rows in another table meet the selection criteria.

For example, you might want to look for possible job openings by finding jobs that have no associated employee. To retrieve this information from the JOB table, first determine which jobs do not exist in the POSITION table. Enter:

select job_id, job_title

       from job
       where not exists
             (select * from position
                     where "position".job_id = job.job_id);

Notes

POSITION is the table name and an SQL keyword; therefore, when the POSITION table name is used as an identifier, it must be enclosed in double quotation marks.

As an alternative, you can use an alias for the table name. For example:

select job_id, job_title
       from job
       where not exists
             (select * from position p
                     where p.job_id = job.job_id);

The result looks like this:

JOB_ID  JOB_TITLE

------  ---------
  3051  Data Entry Clerk
  3029  Computer Operator
  5111  CUST SER REP
  2055  PAYROLL CLERK

4 rows processed