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  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

Schema Diagrams

 * 1) Review HR and OE schema diagrams.
 * 2) Review the Oracle: HR and OE schema diagrams.

HR Set Operators

 * 1) Test set operators using HR data.
 * 2) Combine two queries using UNION with the following:
 * 3) Combine two queries using UNION ALL with the following:
 * 4) Combine two queries using INTERSECT with the following:
 * 5) Combine two queries using MINUS with the following:
 * 6) Combine multiple queries to create a report using the following:
 * 7) Combine and sort the results from multiple queries using the following:

OE Set Operators

 * 1) Test set operators using OE data.
 * 2) Combine queries using UNION.
 * 3) Combine queries using UNION ALL.
 * 4) Combine queries using INTERSECT.
 * 5) Combine queries using MINUS.
 * 6) 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  FROM UNION SELECT  FROM ;
 * INTERSECT syntax: SELECT  FROM INTERSECT SELECT  FROM ;
 * MINUS syntax: SELECT  FROM MINUS SELECT  FROM ;

Assessments

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