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:^[1]

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

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

Test single-row subqueries using HR data.
1. 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;
2. 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;

Test multiple-row subqueries using HR data.
1. 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;

Test correlated subqueries using HR data.
1. 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;

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.

Queries can be nested so that the results of one query can be used in another query via a relational operator or aggregation function.^[2]
A subquery in the FROM clause of a SELECT statement is also called an inline view.^[3]
A subquery in the WHERE clause of a SELECT statement is also called a nested subquery.^[4]
A subquery that uses values from the outer query is known as a correlated subquery.^[5]
A correlated subquery is evaluated once for each row processed by the parent query.^[6]
Because a correlated subquery is evaluated once for each row processed by the outer query, it can be inefficient.^[7]
WHERE subquery example: SELECT <field(s)> FROM <table1> WHERE <field> < (SELECT AVG(<field>) FROM <table2>);^[8]
FROM subquery example: SELECT <field(s)> FROM <table1> AS <alias1> JOIN (SELECT field1, SUM(field2) FROM table2) AS <alias2> ON <alias1.field> = <alias2.field>;^[9]
Correlated subquery example: SELECT <field(s)> FROM <table1> AS <alias1> WHERE <field> <= (SELECT AVG(<field>) FROM <table2> WHERE field = <alias1.field>);^[10]