Oracle SQL Fundamentals/Set Operators
This lesson introduces set operators.
Objectives and Skills
editObjectives 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
editMultimedia
editActivities
editSchema Diagrams
edit- Review HR and OE schema diagrams.
- Review the Oracle: HR and OE schema diagrams.
HR Set Operators
edit- Test set operators using HR data.
- Combine two queries using UNION with the following:
SELECT REGION_NAME FROM REGIONS
UNION
SELECT REGION_NAME FROM REGIONS; - Combine two queries using UNION ALL with the following:
SELECT REGION_NAME FROM REGIONS
UNION ALL
SELECT REGION_NAME FROM REGIONS; - Combine two queries using INTERSECT with the following:
SELECT REGION_NAME FROM REGIONS
INTERSECT
SELECT REGION_NAME FROM REGIONS; - Combine two queries using MINUS with the following:
SELECT REGION_NAME FROM REGIONS
MINUS
SELECT REGION_NAME FROM REGIONS; - 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; - 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;
- Combine two queries using UNION with the following:
OE Set Operators
edit- Test set operators using OE data.
- Combine queries using UNION.
- Combine queries using UNION ALL.
- Combine queries using INTERSECT.
- Combine queries using MINUS.
- 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- Flashcards: Quizlet: Oracle 1Z0-051 Exam - Set Operators
References
edit