OCA Oracle Database 11g: SQL Fundamentals I Exam Guide
Chapter 8: Using Subqueries to Solve Problems
OCA Oracle Database 11g: SQL Fundamentals I Exam Guide, Chapter 8
Table of contents:
This chapter is the first of two that show how two or more SELECT commands can be combined into one statement. The first technique (covered in this chapter) is the use of subqueries. A subquery is a SELECT statement whose output is used as input to another SELECT statement (or indeed to a DML statement, as done in Chapter 10). The second technique is the use of set operators, where the results of several SELECT commands are combined into a single result set.
Define SQL subqueries
A subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery. A subquery can return a set of rows or just one row to its parent query. A scalar subquery is a query that returns exactly one value: a single row, with a single column. Scalar subqueries can be used in most places in a SQL statement where you could use an expression or a literal value.
The places in a query where a subquery may be used are as follows:
- In the SELECT list used for column projection
- In the FROM clause
- In the WHERE clause
- In the HAVING clause
- A SELECT list
- A FROM clause
The following are optional clauses:
- GROUP BY
The subquery (or subqueries) within a statement must be executed before the parent query that calls it, in order that the results of the subquery can be passed to the parent.
EXERCISE 8-1 Types of Subqueries
In this exercise, you will write code that demonstrates the places where subqueries can be used. Use either SQL*Plus or SQL Developer. All the queries should be run when connected to the HR schema.
- Log on to your database as user HR.
- Write a query that uses subqueries in the column projection list. The query will report on the current numbers of departments and staff:
select sysdate Today, (select count(*) from departments) Dept_count, (select count(*) from employees) Emp_count from dual;
- Write a query to identify all the employees who are managers. This will require using a subquery in the WHERE clause to select all the employees whose EMPLOYEE_ID appears as a MANAGER_ID:
select last_name from employees where (employee_id in (select manager_id from employees)); ch08.indd 359 4/28/08 10:48:49 AM
- Write a query to identify the highest salary paid in each country. This will require using a subquery in the FROM clause:
select max(salary),country_id from (select salary,department_id,location_id,country_id from employees natural join departments natural join locations) group by country_id;
Download the chapter "Using Subqueries to Solve Problems", which includes practice exercises and a self-test, in PDF form.
Continue to the next section: Using subqueries in SQL
This was first published in October 2008