Writing single-row and multiple-row subqueries

OCA Oracle Database 11g: SQL Fundamentals I Exam Guide
Chapter 8: Using Subqueries to Solve Problems

Table of contents:

Define SQL subqueries
Using subqueries in SQL
List the types of SQL subqueries
Writing single-row and multiple-row subqueries

Following are examples of single- and multiple-row subqueries. They are based on the HR demonstration schema. How would you figure out which employees have a manager who works for a department based in the United Kingdom? This is a possible solution, using multiple-row subqueries:

select last_name from employees
where manager_id in
(select employee_id from employees where department_id in
(select department_id from departments where location_id in
(select location_id from locations where country_id='UK')));

In the preceding example, subqueries are nested three levels deep. Note that the subqueries use the IN operator because it is possible that the queries could return several rows.

You have been asked to find the job with the highest average salary. This can be done with a single-row subquery:

select job_title from jobs natural join employees group by job_title
having avg(salary) =
(select max(avg(salary)) from employees group by job_id);

The subquery returns a single value: the average salary of the department with the highest average salary. It is safe to use the equality operator for this subquery because the MAX function guarantees that only one row will be returned.

The ANY and ALL operators are supported syntax, but their function can be duplicated with other more commonly used operators combined with aggregations. For example, these two statements, which retrieve all employees whose salary is above that of anyone in department 80, will return identical result sets:

More on this book This chapter is excerpted from the book, OCA Oracle Database 11g: SQL Fundamentals I Exam Guide, authored by John Watson and Roopesh Ramklass, published by McGraw-Hill Osborne Media, May, 2008. ISBN 0071597867.

select last_name from employees where salary > all
(select salary from employees where department_id=80);
select last_name from employees where salary >
(select max(salary) from employees where department_id=80);

The following table summarizes the equivalents for ANY and ALL:

EXERCISE 8-4

Write a Query That Is Reliable and User Friendly

In this exercise, develop a multi-row subquery that will prompt for user input. Use either SQL*Plus or SQL Developer. All the queries should be run when connected to the HR schema; it is assumed that the tables have the standard sets of rows.

1. Log on to your database as user HR.
   
2. Design a query that will prompt for a department name and list the last name of every employee in that department:

   select last_name from employees where department_id =
   (select department_id from departments
   where department_name = '&Department_name');

3. Run the query in step 2 three times, when prompted supplying these values: first time, Executive
   second time, executive
   third time, Executiv
   The following illustration shows the result, using SQL*Plus:

   

   
   

4. Note the results from step 3. The first run succeeded because the value entered was an exact match, but the other failed. Adjust the query to make it more user friendly, so that it can handle minor variations in case or spelling:
   

   select last_name from employees where department_id =
   (select department_id from departments
   where upper(department_name) like upper('%&Department_name%'));

   
   
5. Run the query in step 4 three times, using the same values as used in step 3. This time, the query will execute successfully.
   
6. Run the query in step 4 again, and this time enter the value Pu. The query will fail, with an "ORA-01427: single-row subquery returns more than one row" error, because the attempt to make it more user-friendly means that the subquery is no longer guaranteed to be a single-row subquery. The string Pu matches two departments.
   
7. Adjust the query to make it resilient against the ORA-01427 error, and adjust the output to prevent any possible confusion:

   select last_name,department_name from employees join departments
   on employees.department_id = departments.department_id
   where departments.department_id in
   (select department_id from departments
   where upper(department_name) like upper('%&Department_name%'));
   

   The following illustration shows this final step: code that is approaching the ideal of being both bullet proof and user friendly:

--------------------------------------------------------------------------------------------------------------------------------------------------------------------

Download the chapter "Using Subqueries to Solve Problems", which includes practice exercises and a self-test, in PDF form.