OCA Oracle Database 11g: SQL Fundamentals I Exam Guide
Chapter 8: Using Subqueries to Solve Problems
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:
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.
- Log on to your database as user HR.
- 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');
- 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:
- 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%'));
- Run the query in step 4 three times, using the same values as used in step 3. This time, the query will execute successfully.
- 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.
- 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.
|
|
|
|
|
| TechTarget provides technology professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective purchase decisions and managing their organizations' technology projects - with its network of . |
|
| |
All Rights Reserved, , TechTarget |
|
|
|
|
