Oracle SQL Fundamentals/Set Operators

This lesson introduces set operators.

Objectives and Skills

edit

Objectives and skills for the set operators portion of Oracle SQL Fundamentals I certification include:[1]

  • Using the Set Operators
    • Describe set operators
    • Use a set operator to combine multiple queries into a single query
    • Control the order of rows returned

Readings

edit
  1. Read Wikipedia: Set operations (SQL).
  2. Read Oracle: The UNION (ALL), INTERSECT, MINUS Operators.

Multimedia

edit
  1. YouTube: SQL Basics with Oracle - Traditional set operators: UNION, INTERSECT & MINUS

Activities

edit

Schema Diagrams

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

HR Set Operators

edit
  1. Test set operators using HR data.
    1. Combine two queries using UNION with the following:
      SELECT REGION_NAME FROM REGIONS
      UNION
      SELECT REGION_NAME FROM REGIONS;
    2. Combine two queries using UNION ALL with the following:
      SELECT REGION_NAME FROM REGIONS
      UNION ALL
      SELECT REGION_NAME FROM REGIONS;
    3. Combine two queries using INTERSECT with the following:
      SELECT REGION_NAME FROM REGIONS
      INTERSECT
      SELECT REGION_NAME FROM REGIONS;
    4. Combine two queries using MINUS with the following:
      SELECT REGION_NAME FROM REGIONS
      MINUS
      SELECT REGION_NAME FROM REGIONS;
    5. Combine multiple queries to create a report using the following:
      SELECT 'Regions' AS "Report Item", COUNT(*) AS "Value" FROM REGIONS
      UNION ALL
      SELECT 'Countries', COUNT(*) FROM COUNTRIES
      UNION ALL
      SELECT 'Locations', COUNT(*) FROM LOCATIONS
      UNION ALL
      SELECT 'Departments', COUNT(*) FROM DEPARTMENTS
      UNION ALL
      SELECT 'Employees', COUNT(*) FROM EMPLOYEES;
    6. Combine and sort the results from multiple queries using the following:
      SELECT 'Regions' AS "Report Item", COUNT(*) AS "Value" FROM REGIONS
      UNION ALL
      SELECT 'Countries', COUNT(*) FROM COUNTRIES
      UNION ALL
      SELECT 'Locations', COUNT(*) FROM LOCATIONS
      UNION ALL
      SELECT 'Departments', COUNT(*) FROM DEPARTMENTS
      UNION ALL
      SELECT 'Employees', COUNT(*) FROM EMPLOYEES
      ORDER BY 2 DESC;

OE Set Operators

edit
  1. Test set operators using OE data.
    1. Combine queries using UNION.
    2. Combine queries using UNION ALL.
    3. Combine queries using INTERSECT.
    4. Combine queries using MINUS.
    5. Sort the results of combined queries.

Lesson Summary

edit
  • The UNION clause combines the results of two SQL queries into a single table of all matching rows. The two queries must result in the same number of columns and compatible data types in order to unite. Any duplicate records are automatically removed unless UNION ALL is used.[2]
  • The INTERSECT operator takes the results of two queries and returns only rows that appear in both result sets.[3]
  • The MINUS operator takes the distinct rows of one query and returns the rows that do not appear in a second result set.[4]
  • Set operators do not guarantee the order of rows. In situations where a specific order is desired, ORDER BY must be used.[5]
  • UNION syntax: SELECT <field(s)> FROM <table1> UNION SELECT <field(s)> FROM <table2>;[6]
  • INTERSECT syntax: SELECT <field(s)> FROM <table1> INTERSECT SELECT <field(s)> FROM <table2>;[7]
  • MINUS syntax: SELECT <field(s)> FROM <table1> MINUS SELECT <field(s)> FROM <table2>;[8]

Assessments

edit

References

edit