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

INTERSECT

Answer

DIFFERENCE

Answer

NOT IN

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

UNION and UNION ALL (equal precedence)

Answer

MINUS

Answer

All set operators have equal precedence evaluated left to right

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

All rows from all three tables with full deduplication applied

Answer

All rows from all three tables with no deduplication at all

Answer

An error because UNION and UNION ALL cannot be mixed in one query

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

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

Answer

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

Answer

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

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

NULL

Answer

All rows from both queries combined

Answer

An ORA-00942 table or view does not exist error

Question 6

Which set operator best describes a 'logical difference' or 'relative complement' operation between two sets in Oracle SQL?

Accepted Answer

MINUS

Answer

UNION

Answer

UNION ALL

Answer

INTERSECT

Question 7

How many SELECT statements can be combined using set operators in a single Oracle SQL compound query?

Accepted Answer

There is no practical limit imposed by Oracle

Answer

Maximum of 2

Answer

Maximum of 4

Answer

Maximum of 255