Define SQL subqueries

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

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

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

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Chapter excerpts from Oracle books Writing single-row and multiple-row subqueries List the types of SQL subqueries Using subqueries in SQL Oracle 11g: PL/SQL Basics Oracle 11g: Backup and recovery concepts Migrating to Oracle: Expert Secrets to Migrate from SQL Server and MySQL Oracle Database 11g SQL Tuning Upgrading to Oracle Database 11g Tuning the Oracle database with initialization parameters Protecting your online Oracle data Oracle and SQL Can I specify Oracle column order in my database table? Review: Oracle's 11g R2 database has some good and bad SELECT statement syntax and examples Oracle PL/SQL tutorial PL/SQL datatypes in Oracle PL/SQL functions and triggers in Oracle Stored procedures in PL/SQL Do I need a license for SQL Developer Data Modeler in Oracle? Using the SQL GROUP BY clause for counting combinations How to use an SQL CASE expression Oracle and SQL Research

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary autonomous transaction  (SearchOracle.com) CFML  (SearchOracle.com) dynamic SQL  (SearchOracle.com) foreign key  (SearchOracle.com) Java Database Connectivity  (SearchOracle.com) Open Database Connectivity  (SearchOracle.com) Oracle  (SearchOracle.com) stored procedure  (SearchOracle.com) The Open Group  (SearchOracle.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary