Oracle SQL Fundamentals/Subqueries

This lesson introduces subqueries.

Objectives and Skills
Objectives and skills for the subqueries portion of Oracle SQL Fundamentals I certification include:
 * Using Subqueries to Solve Queries
 * Define subqueries
 * Describe the types of problems that the subqueries can solve
 * List the types of subqueries
 * Write single-row and multiple-row subqueries

Readings

 * 1) Read  Subquery.
 * 2) Read  Correlated subquery.
 * 3) Read Oracle: Using Subqueries.

Multimedia

 * 1) YouTube: Oracle SQL Tutorials Subqueries in Oracle (Theory)
 * 2) YouTube: Oracle SQL Tutorial Subqueries in Oracle [Hands On)

Schema Diagrams

 * 1) Review HR and OE schema diagrams.
 * 2) Review the Oracle: HR and OE schema diagrams.

Single-Row Subqueries

 * 1) Test single-row subqueries using HR data.
 * 2) List department name, employee last name, salary, and the average employee salary using the following query:
 * 3) List department name, employee last name, and salary difference from the average salary using the following query:

Multiple-Row Subqueries

 * 1) Test multiple-row subqueries using HR data.
 * 2) List department name, employee last name, salary, and the average employee salary. Include only employees and departments that include a commission using the following query:

Correlated Subqueries

 * 1) Test correlated subqueries using HR data.
 * 2) List department name, employee last name, salary, and the average employee salary within each department using the following query:

OE Subqueries

 * 1) Test single-row subqueries using OE data. Calculations may be made based on inventory quantity on hand, product pricing, and order detail information.
 * 2) Test multiple-row subqueries using OE data. Calculations may be made based on inventory quantity on hand, product pricing, and order detail information.
 * 3) Test correlated subqueries using OE data. Calculations may be made based on inventory quantity on hand, product pricing, and order detail information.

Lesson Summary

 * Queries can be nested so that the results of one query can be used in another query via a relational operator or aggregation function.
 * A subquery in the FROM clause of a SELECT statement is also called an inline view.
 * A subquery in the WHERE clause of a SELECT statement is also called a nested subquery.
 * A subquery that uses values from the outer query is known as a correlated subquery.
 * A correlated subquery is evaluated once for each row processed by the parent query.
 * Because a correlated subquery is evaluated once for each row processed by the outer query, it can be inefficient.
 * WHERE subquery example: SELECT  FROM WHERE < (SELECT AVG FROM );
 * FROM subquery example: SELECT  FROM AS JOIN (SELECT field1, SUM(field2) FROM table2) AS ON  = ;
 * Correlated subquery example: SELECT  FROM AS WHERE <= (SELECT AVG FROM WHERE field = );

Assessments

 * Flashcards: Quizlet: Oracle 1Z0-051 Exam - Subquery