Writing single-row and multiple-row subqueries

This chapter excerpt explains how to use and write single-row and multiple-row subqueries in SQL, with sample code and exercises.

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

sql subqueries book cover image
Single and multiple-row subqueries are two of the three broad divisions of subqueries. This chapter from OCA Oracle Database 11g: SQL Fundamentals I Exam Guide explains and gives examples of how to use and write these types of subqueries. OCA Oracle Database 11g: SQL Fundamentals I Exam Guide, Chapter 8

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:

Operator Meaning
< ANY less than the highest
> ANY not equal to any member in a list
ANY more than the lowest
= ANY equivalent to IN
> ALL more than the highest
< ALL less than the lowest

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.

This was first published in October 2008

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close