Describe the Types of Problems That the Subqueries Can Solve
There are many situations where you will need the result of one query as the input for
another.
Use of a Subquery Result Set for Comparison Purposes
Which employees have a salary that is less than the average salary? This could be
answered by two statements, or by a single statement with a subquery. The following
example uses two statements:
select avg(salary) from employees;
select last_name from employees where salary < result_of_previous_query ;
Alternatively, this example uses one statement with a subquery:
select last_name from employees where salary < (select avg(salary)from employees);
In this example, the subquery is used to substitute a value into the WHERE clause of the
parent query: it is returning a single value, used for comparison with the rows retrieved by
the parent query.
The subquery could return a set of rows. For example, you could use the following to find
all departments that do actually have one or more employees assigned to them:
select department_name from departments where department_id in
(select distinct(department_id) from employees);