Oracle SQL Fundamentals/Set Operators

From Wikiversity
Jump to navigation Jump to search

This lesson introduces set operators.

Objectives and Skills[edit | edit source]

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

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

Multimedia[edit | edit source]

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

Activities[edit | edit source]

Schema Diagrams[edit | edit source]

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

HR Set Operators[edit | edit source]

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

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

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

References[edit | edit source]