1Z0-071 Set Operators 1 — Questions and Answers
Question 1: Which set operator returns all rows from both queries, including duplicate rows?
- UNION
- UNION ALL (Correct answer)
- INTERSECT
- MINUS
Correct answer: UNION ALL
UNION ALL combines result sets from both queries and retains all duplicate rows without performing any deduplication.
Question 2: Which set operator returns only the rows that appear in both result sets?
- UNION
- UNION ALL
- INTERSECT (Correct answer)
- MINUS
Correct answer: INTERSECT
INTERSECT returns only the rows that are common to both SELECT statements, eliminating rows unique to either query.
Question 3: Which set operator returns rows from the first query that do NOT appear in the second query?
- UNION
- UNION ALL
- INTERSECT
- MINUS (Correct answer)
Correct answer: MINUS
MINUS (Oracle-specific) returns all distinct rows selected by the first query that are not present in the second query result.
Question 4: When combining two SELECT statements with a set operator, which of the following is a mandatory requirement?
- Both queries must reference the same tables
- Both SELECT statements must return the same number of columns (Correct answer)
- The ORDER BY clause must be present in each SELECT
- Both queries must have identical WHERE clause conditions
Correct answer: Both SELECT statements must return the same number of columns
All set operators require that both SELECT statements return the same number of columns for the operation to execute successfully.
Question 5: In a compound query using set operators, the column headers in the final result are determined by which SELECT statement?
- The last SELECT statement
- Alphabetical order of all column names
- The first SELECT statement (Correct answer)
- The SELECT statement that returns the most rows
Correct answer: The first SELECT statement
Oracle uses the column names or aliases from the first SELECT statement to label the columns in the combined result set.
Question 6: Where must the ORDER BY clause be placed when used with set operators in Oracle SQL?
- Before the first SELECT statement
- After each individual SELECT statement
- Only at the very end of the entire compound query (Correct answer)
- ORDER BY cannot be used with set operators
Correct answer: Only at the very end of the entire compound query
When using set operators, a single ORDER BY clause must appear at the end of the entire compound query and applies to the complete combined result set.
Question 7: What happens when corresponding columns in a UNION query have completely incompatible data types (for example, DATE and NUMBER)?
- Oracle uses the data type from the first SELECT
- Oracle converts all values to VARCHAR2 automatically
- An ORA-01790 error is returned (Correct answer)
- NULL values replace rows with incompatible types
Correct answer: An ORA-01790 error is returned
Oracle returns ORA-01790 ('expression must have same datatype as corresponding expression') when corresponding columns have incompatible data types in a set operator query.
1Z0-071 Set Operators 1
Which set operator returns all rows from both queries, including duplicate rows?