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

    Requires Free Membership to View

    Login

A subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery. This chapter from OCA Oracle Database 11g: SQL Fundamentals I Exam Guide explains the places in a query where a subquery may be used and which clauses are optional and required. Read the chapter to better understand the subquery definition.

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

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 subquery is often referred to as an inner query, and the statement within which it occurs is then called the outer query. There is nothing wrong with this terminology, except that it may imply that you can only have two levels, inner and outer. In fact, the Oracle implementation of subqueries does not impose any practical limits on the level of nesting: the depth of nesting permitted in the FROM clause of a statement is unlimited, and that in the WHERE clause is up to 255.

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.
A subquery can have any of the usual clauses for selection and projection. The following are required clauses:
  • A SELECT list
  • A FROM clause

The following are optional clauses:

  • WHERE
  • GROUP BY
  • HAVING

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.

  1. Log on to your database as user HR.
  2. 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;
  3. 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
  4. 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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top