Oracle SQL Fundamentals/Set Operators

This lesson introduces set operators.

Objectives and Skills

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

• 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

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

Multimedia

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

Activities

Schema Diagrams

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

HR Set Operators

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

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

• 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.
• The INTERSECT operator takes the results of two queries and returns only rows that appear in both result sets.
• The MINUS operator takes the distinct rows of one query and returns the rows that do not appear in a second result set.
• Set operators do not guarantee the order of rows. In situations where a specific order is desired, ORDER BY must be used.
• UNION syntax: SELECT <field(s)> FROM <table1> UNION SELECT <field(s)> FROM <table2>;
• INTERSECT syntax: SELECT <field(s)> FROM <table1> INTERSECT SELECT <field(s)> FROM <table2>;
• MINUS syntax: SELECT <field(s)> FROM <table1> MINUS SELECT <field(s)> FROM <table2>;

Assessments

• Flashcards: Quizlet: Oracle 1Z0-051 Exam - Set Operators

References

← Subqueries

Oracle SQL Fundamentals

Manipulating Data →