# 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

* Read Wikipedia: Subquery.
* Read Wikipedia: Correlated subquery.
* Read Oracle: Using Subqueries.

## Multimedia

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

## Activities
### Schema Diagrams

* Review HR and OE schema diagrams.
** Review the Oracle: HR and OE schema diagrams.

### HR Subqueries
#### Single-Row Subqueries

* Test single-row subqueries using HR data.
** List department name, employee last name, salary, and the average employee salary using the following query: SELECT D.DEPARTMENT_NAME, E.LAST_NAME, E.SALARY, ROUND((SELECT AVG(SALARY) AS AVERAGE FROM EMPLOYEES)) AS AVERAGE_SALARY FROM DEPARTMENTS D JOIN EMPLOYEES E ON D.DEPARTMENT_ID = E.DEPARTMENT_ID ORDER BY DEPARTMENT_NAME;
** List department name, employee last name, and salary difference from the average salary using the following query: SELECT D.DEPARTMENT_NAME, E.LAST_NAME, TO_CHAR(E.SALARY - (SELECT AVG(SALARY) AS AVERAGE FROM EMPLOYEES), '99,990') AS SALARY_DIFFERENCE FROM DEPARTMENTS D JOIN EMPLOYEES E ON D.DEPARTMENT_ID = E.DEPARTMENT_ID ORDER BY DEPARTMENT_NAME;

#### Multiple-Row Subqueries

* Test multiple-row subqueries using HR data.
** 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: SELECT D.DEPARTMENT_NAME, E.LAST_NAME, E.SALARY, ROUND((SELECT AVG(SALARY) AS AVERAGE FROM EMPLOYEES WHERE COMMISSION_PCT IS NOT NULL)) AS AVERAGE_SALARY FROM DEPARTMENTS D JOIN EMPLOYEES E ON D.DEPARTMENT_ID = E.DEPARTMENT_ID WHERE D.DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE COMMISSION_PCT IS NOT NULL) ORDER BY DEPARTMENT_NAME;

#### Correlated Subqueries

* Test correlated subqueries using HR data.
** List department name, employee last name, salary, and the average employee salary within each department using the following query: SELECT D.DEPARTMENT_NAME, E.LAST_NAME, E.SALARY, S.AVERAGE AS AVERAGE FROM DEPARTMENTS D JOIN EMPLOYEES E ON D.DEPARTMENT_ID = E.DEPARTMENT_ID JOIN (SELECT DEPARTMENT_ID, ROUND(AVG(SALARY)) AS AVERAGE FROM EMPLOYEES GROUP BY DEPARTMENT_ID) S ON E.DEPARTMENT_ID = S.DEPARTMENT_ID ORDER BY DEPARTMENT_NAME;

### OE Subqueries

* Test single-row subqueries using OE data. Calculations may be made based on inventory quantity on hand, product pricing, and order detail information.
* Test multiple-row subqueries using OE data. Calculations may be made based on inventory quantity on hand, product pricing, and order detail information.
* 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 <field(s)> FROM <table1> WHERE <field> < (SELECT AVG(<field>) FROM <table2>);
* FROM subquery example: SELECT <field(s)> FROM <table1> AS <alias1> JOIN (SELECT field1, SUM(field2) FROM table2) AS <alias2> ON <alias1.field> = <alias2.field>;
* Correlated subquery example: SELECT <field(s)> FROM <table1> AS <alias1> WHERE <field> <= (SELECT AVG(<field>) FROM <table2> WHERE field = <alias1.field>);

## Assessments

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