Oracle SQL Fundamentals/Subqueries

This lesson introduces subqueries.

Objectives and Skills

edit

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

Readings

edit
  1. Read Wikipedia: Subquery.
  2. Read Wikipedia: Correlated subquery.
  3. Read Oracle: Using Subqueries.

Multimedia

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

Activities

edit

Schema Diagrams

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

HR Subqueries

edit

Single-Row Subqueries

edit
  1. 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;

Multiple-Row Subqueries

edit
  1. 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;

Correlated Subqueries

edit
  1. 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;

OE Subqueries

edit
  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

edit
  • 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]

Assessments

edit

References

edit