1Z0-071 Set Operators 2

Question 1

In Oracle SQL, the MINUS operator is the Oracle-specific equivalent of which ANSI SQL standard set operator?

Accepted Answer

EXCEPT

Answer

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?

Accepted Answer

INTERSECT

Answer

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?

Accepted Answer

Distinct rows from (t1 UNION t2) combined with ALL rows from t3 including duplicates

Answer

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?

Accepted Answer

SELECT id FROM emp UNION SELECT id FROM dept ORDER BY id;

Answer

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?

Accepted Answer

An empty result set containing zero rows

Answer

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.

1Z0-071 - Oracle Database SQL Certified Associate Practice Test

1Z0-071 - Oracle Database SQL Certified Associate Practice Test

1Z0-071 Set Operators 2