1Z0-071 Set Operators 3

Question 1

When using set operators, which columns must have compatible data types between the two SELECT statements?

Accepted Answer

All corresponding columns in both SELECT statements

Answer

Every corresponding column pair (column 1 with column 1, column 2 with column 2, and so on) must have compatible data types across both SELECT statements.

Question 2

Which of the following is NOT a valid set operator in Oracle SQL?

Accepted Answer

INTERSECT ALL

Answer

Oracle SQL does not support INTERSECT ALL or MINUS ALL; the only valid set operators are UNION, UNION ALL, INTERSECT, and MINUS.

Question 3

What is the result of the following query: SELECT 1 FROM DUAL UNION SELECT 1 FROM DUAL?

Accepted Answer

Returns one row containing the value 1

Answer

UNION eliminates duplicate rows, so even though both SELECT statements return the value 1, the final result contains only one distinct row.

Question 4

In a compound query using set operators, an ORDER BY clause can reference output columns using which methods?

Accepted Answer

Column aliases from the first SELECT or position numbers

Answer

In set operator queries, ORDER BY can reference columns by aliases from the first SELECT statement or by their positional number; table-qualified names are not permitted.

Question 5

Why is UNION generally slower in performance compared to UNION ALL?

Accepted Answer

UNION requires an additional sort and deduplication step to eliminate duplicate rows

Answer

UNION must perform an extra sort or hash operation to identify and remove duplicate rows from the combined result, adding overhead that UNION ALL avoids.

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

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

1Z0-071 Set Operators 3