1Z0-071 Set Operators 2 — Questions and Answers
Question 1: In Oracle SQL, the MINUS operator is the Oracle-specific equivalent of which ANSI SQL standard set operator?
- INTERSECT
- EXCEPT (Correct answer)
- DIFFERENCE
- NOT IN
Correct answer: EXCEPT
Oracle's MINUS operator is functionally equivalent to the ANSI SQL standard EXCEPT operator, both returning rows from the first query not found in the second.
Question 2: When multiple set operators are used in a single query without parentheses, which operator has the highest precedence in Oracle SQL?
- UNION and UNION ALL (equal precedence)
- MINUS
- INTERSECT (Correct answer)
- All set operators have equal precedence evaluated left to right
Correct answer: INTERSECT
INTERSECT has higher precedence than UNION, UNION ALL, and MINUS in Oracle SQL, so it is evaluated first when no parentheses are used.
Question 3: What is the result of: SELECT id FROM t1 UNION SELECT id FROM t2 UNION ALL SELECT id FROM t3?
- All rows from all three tables with full deduplication applied
- Distinct rows from (t1 UNION t2) combined with ALL rows from t3 including duplicates (Correct answer)
- All rows from all three tables with no deduplication at all
- An error because UNION and UNION ALL cannot be mixed in one query
Correct answer: Distinct rows from (t1 UNION t2) combined with ALL rows from t3 including duplicates
UNION and UNION ALL have equal precedence and are evaluated left to right, so t1 UNION t2 is computed first with deduplication, then UNION ALL appends all rows from t3.
Question 4: Which of the following queries correctly places the ORDER BY clause when using set operators?
- SELECT id FROM emp ORDER BY id UNION SELECT id FROM dept;
- SELECT id FROM emp UNION SELECT id FROM dept ORDER BY id; (Correct answer)
- SELECT id FROM emp UNION ORDER BY id SELECT id FROM dept;
- ORDER BY id SELECT id FROM emp UNION SELECT id FROM dept;
Correct answer: SELECT id FROM emp UNION SELECT id FROM dept ORDER BY id;
The ORDER BY clause must appear at the very end of the compound query after the final SELECT statement in a set operator expression.
Question 5: What does INTERSECT return when there are NO rows common to both result sets?
- NULL
- An empty result set containing zero rows (Correct answer)
- All rows from both queries combined
- An ORA-00942 table or view does not exist error
Correct answer: An empty result set containing zero rows
INTERSECT returns an empty result set with no rows when the two queries share no common rows; this is a valid outcome and not an error condition.
Question 6: Which set operator best describes a 'logical difference' or 'relative complement' operation between two sets in Oracle SQL?
- UNION
- UNION ALL
- INTERSECT
- MINUS (Correct answer)
Correct answer: MINUS
MINUS returns the logical difference between two sets by returning rows present in the first result set that are absent from the second result set.
Question 7: How many SELECT statements can be combined using set operators in a single Oracle SQL compound query?
- Maximum of 2
- Maximum of 4
- Maximum of 255
- There is no practical limit imposed by Oracle (Correct answer)
Correct answer: There is no practical limit imposed by Oracle
Oracle SQL does not impose a strict practical limit on the number of SELECT statements that can be chained together using set operators in a single compound query.
1Z0-071 Set Operators 2
In Oracle SQL, the MINUS operator is the Oracle-specific equivalent of which ANSI SQL standard set operator?