Oracle 1Z0-071: Use Set Operators

Topic snapshot

Sample questions

These questions are original IT Mastery practice items aligned to this topic area. They are designed for self-assessment and are not official exam questions.

Question 1

Topic: Use Set Operators

Which statement is invalid because the two SELECT lists are incompatible for the set operation?

Options:

• A. SELECT 1, 'A' FROM dual UNION SELECT '1', 'A' FROM dual

• B. SELECT 'HR' FROM dual UNION ALL SELECT CAST('SALES' AS VARCHAR2(10)) FROM dual

• C. SELECT 10, 20 FROM dual UNION ALL SELECT 30, 40 FROM dual

• D. SELECT 1 FROM dual UNION SELECT 2.5 FROM dual

Best answer: A

Explanation: Oracle set operators compare SELECT lists by position. Each query must return the same number of expressions, and each corresponding position must be from the same data type group; mixing NUMBER with character data makes the statement invalid.

For UNION and UNION ALL, Oracle checks the two SELECT lists position by position. The lists must have the same number of columns, and each matching column must belong to the same data type group, such as character-to-character or numeric-to-numeric. In the incompatible statement, the first query returns the numeric literal 1 in column 1, while the second query returns the character literal '1' in column 1. Oracle does not treat those as compatible for a set operator.

• Same column count is required.
• Compatibility is checked by column position.
• Different lengths are fine within the same character group.
• Different numeric literals are fine within the numeric group.

The key trap is assuming Oracle will implicitly convert across data type groups in set operations.

• The option using 1 and 2.5 is valid because both expressions are numeric.
• The option using 'HR' and CAST('SALES' AS VARCHAR2(10)) is valid because both expressions are character data.
• The option using two numeric columns on both sides is valid because both positions match as numeric.

Question 2

Topic: Use Set Operators

Which second query can be combined with the following query by using UNION ALL without adding conversion functions?

SELECT last_name, salary, hire_date
FROM employees

Options:

• A. SELECT city, state_province FROM locations

• B. SELECT department_name, location_id, SYSDATE FROM departments

• C. SELECT job_title, min_salary, max_salary FROM jobs

• D. SELECT department_id, department_name, manager_id FROM departments

Best answer: B

Explanation: For UNION ALL, Oracle requires the same number of selected columns and compatible data type groups in the same positions. The query using department_name, location_id, and SYSDATE matches character, numeric, and date, so it is compatible.

Oracle set operators such as UNION and UNION ALL require both SELECT statements to return the same number of expressions. In addition, each corresponding expression must belong to the same data type group in the same position. Oracle can resolve differences within a group, such as one numeric type to another or CHAR to VARCHAR2, but it does not allow set-operator matching across groups such as character to number or number to date.

Here, the first query returns values in this order: character (last_name), numeric (salary), and date (hire_date). A compatible second query must also return exactly three expressions in that same pattern. The choice with department_name, location_id, and SYSDATE meets both rules. The closest distractor matches the first two positions but fails because its third expression is numeric instead of date.

• The option with department_id, department_name, and manager_id fails because the first and third positions are numeric instead of character and date.
• The option with city and state_province fails because it returns only two columns.
• The option with job_title, min_salary, and max_salary fails because the third position is numeric, while the first query’s third position is a date.

Question 3

Topic: Use Set Operators

A report should return every order_id from orders_all, except those order_id values that appear in both orders_returned and orders_priority.

The developer writes:

SELECT order_id FROM orders_all
MINUS
SELECT order_id FROM orders_returned
INTERSECT
SELECT order_id FROM orders_priority;

The query returns far fewer rows than expected. What is the best fix?

Options:

• A. Replace INTERSECT with UNION before applying MINUS.

• B. Parenthesize the INTERSECT query so it is evaluated before MINUS.

• C. Add DISTINCT to each SELECT to remove duplicate order_id values.

• D. Add ORDER BY order_id inside each component SELECT.

Best answer: B

Explanation: In Oracle, multiple set operators in one compound query are evaluated left to right unless parentheses specify a different order. The requirement is to subtract only the rows found in both secondary queries, so that intersection must be formed first and then removed from orders_all.

The core concept is set-operator evaluation order. As written, Oracle treats the statement as:

(SELECT order_id FROM orders_all
 MINUS
 SELECT order_id FROM orders_returned)
INTERSECT
SELECT order_id FROM orders_priority

That result keeps only order_id values that survive MINUS and also appear in orders_priority, which is not the same as subtracting the overlap of the last two queries.

To match the requirement, compute the overlap first, then subtract it:

SELECT order_id FROM orders_all
MINUS
(
  SELECT order_id FROM orders_returned
  INTERSECT
  SELECT order_id FROM orders_priority
)

A final ORDER BY, if needed, affects only display order, not which rows belong to the set.

• Extra ORDER BY does not fix row membership, and a compound query uses one ORDER BY only at the end.
• Using UNION would subtract rows found in either source, not only rows found in both sources.
• Adding DISTINCT is unnecessary because MINUS and INTERSECT already return distinct rows.

Question 4

Topic: Use Set Operators

You need one result set from orders(order_id NUMBER, order_date DATE) and returns(return_id NUMBER, refund_amount NUMBER(8,2)).

Requirements:

• Return two columns in this order: transaction_id, detail_text
• For orders, detail_text must show order_date as YYYY-MM-DD
• For returns, detail_text must show refund_amount
• Keep duplicate rows if they exist

Which SQL statement best meets these requirements?

Options:

• A. SELECT order_id, TO_CHAR(order_date, 'YYYY-MM-DD') FROM orders UNION ALL SELECT return_id, TO_CHAR(refund_amount) FROM returns

• B. SELECT order_id, order_date FROM orders UNION ALL SELECT return_id, refund_amount FROM returns

• C. SELECT order_id, TO_CHAR(order_date, 'YYYY-MM-DD'), 'ORDER' FROM orders UNION ALL SELECT return_id, TO_CHAR(refund_amount) FROM returns

• D. SELECT order_id, TO_CHAR(order_date, 'YYYY-MM-DD') FROM orders UNION SELECT return_id, TO_CHAR(refund_amount) FROM returns

Best answer: A

Explanation: Set operators require the same number of selected expressions, and corresponding expressions must be in compatible datatype groups. Here, the second column would otherwise be DATE in one query and NUMBER in the other, so both must be converted to character data; UNION ALL is also needed to keep duplicates.

For UNION and UNION ALL, Oracle checks each select-list position across both queries. The lists must have the same number of columns, and each matching position must be in a compatible datatype group. In this case, order_id and return_id are both numeric, so the first position is fine.

The issue is the second position:

• order_date is a DATE
• refund_amount is a NUMBER

Those are not compatible for a set operation, so both expressions must be converted to a common datatype, such as character data with TO_CHAR. Because the requirement says to keep duplicates, UNION ALL is the right operator instead of UNION.

The key takeaway is that set-operator compatibility is checked by position, not by column meaning or alias.

• Using UNION fails the duplicate-preservation requirement because it removes duplicate rows.
• Leaving order_date as DATE and refund_amount as NUMBER makes the second select-list position incompatible.
• Adding a third expression only in the first query makes the two SELECT lists different in column count.

Question 5

Topic: Use Set Operators

A developer must combine product IDs from web_orders and store_orders into one result. The final result must show each product_id only once, even if the same ID appears multiple times in one table or in both tables.

SELECT product_id FROM web_orders
[ set operator ]
SELECT product_id FROM store_orders;

Which set operator correctly meets this requirement?

Options:

• A. INTERSECT

• B. UNION

• C. MINUS

• D. UNION ALL

Best answer: B

Explanation: Use UNION when you need rows from both queries but want duplicate combined rows removed. In Oracle SQL, UNION returns distinct rows across the full result set.

The core rule is that UNION performs duplicate elimination on the combined output of two compatible SELECT statements. That makes it the correct choice when the requirement is to include rows from both sources but return each matching row only once.

In this case:

• web_orders contributes product IDs
• store_orders contributes product IDs
• repeated IDs must appear once in the final result

UNION ALL keeps duplicates, so it does not satisfy the requirement. INTERSECT returns only rows found in both queries, and MINUS returns rows from the first query that are not in the second. The key takeaway is: use UNION for combined distinct results; use UNION ALL only when duplicates should be preserved.

• UNION ALL keeps duplicates and would return repeated product_id values from either source.
• INTERSECT is too restrictive because it returns only IDs present in both tables, not all IDs from either table.
• MINUS is one-sided because it subtracts second-query rows from the first instead of combining both result sets.

Question 6

Topic: Use Set Operators

A developer is troubleshooting a row-count issue in a compound query. Each of these tables currently has 6 rows, and 2 order_id values appear in both tables. The developer expects all 12 rows to appear, including duplicates, but the query returns only 10 rows.

SELECT order_id
FROM current_orders
UNION
SELECT order_id
FROM archived_orders;

What is the best fix?

Options:

• A. Replace UNION with UNION ALL

• B. Add ORDER BY order_id to the first query

• C. Wrap the second query in a subquery

• D. Add DISTINCT to each SELECT

Best answer: A

Explanation: The issue is caused by UNION, which removes duplicate rows across the combined results. If overlapping order_id values must remain visible, UNION ALL is the correct set operator because it preserves duplicates.

In Oracle SQL, UNION combines the results of two compatible queries and then eliminates duplicate rows from the final combined set. That is why the query returns 10 rows instead of the expected 12 when 2 order_id values exist in both tables. If the requirement is to keep every occurrence from both tables, the correct fix is to use UNION ALL.

• UNION = combine results, then remove duplicates
• UNION ALL = combine results, keep duplicates
• Both queries must still have compatible column counts and datatypes

A sort or extra DISTINCT does not preserve duplicates; it either changes presentation or removes even more rows.

• Extra DISTINCT makes the row-count problem worse because it removes duplicates rather than preserving them.
• Sorting first query is not a valid fix because ORDER BY applies to the final compound query result, not to preserving duplicate rows.
• Subquery wrapping does not change the duplicate-removal behavior of UNION.

Question 7

Topic: Use Set Operators

Examine the SQL statement:

SELECT customer_id, order_total
FROM online_sales
WHERE sale_date = DATE '2026-04-01'
??
SELECT customer_id, order_total
FROM store_sales
WHERE sale_date = DATE '2026-04-01';

The requirement is to return all rows from both queries in one result set. If the same customer_id and order_total combination appears in both tables, both rows must remain in the output. Which set operator should replace ???

Options:

• A. UNION

• B. MINUS

• C. UNION ALL

• D. INTERSECT

Best answer: C

Explanation: Use UNION ALL when you need to stack two compatible query results and keep duplicates. The requirement explicitly says identical rows from both tables must still appear twice, so duplicate elimination must not occur.

The core concept is the difference between UNION and UNION ALL. Both queries in the exhibit are compatible because they return the same number of columns in the same order, and the corresponding columns are intended to be comparable. The deciding requirement is duplicate handling.

• UNION ALL returns every row from the first query and every row from the second query.
• UNION also combines both queries, but it removes duplicate rows.
• INTERSECT returns only rows found in both queries.
• MINUS returns rows from the first query that do not appear in the second.

Because the output must include duplicate rows when the same customer_id and order_total occurs in both tables, UNION ALL is the correct choice.

• UNION removes duplicates so it fails the requirement to keep identical rows from both sources.
• INTERSECT is too restrictive because it returns only common rows, not all rows from both queries.
• MINUS is one-sided because it subtracts the second result set from the first instead of combining both.

Question 8

Topic: Use Set Operators

In Oracle SQL, which mismatch makes two SELECT lists incompatible for UNION or UNION ALL?

Options:

• A. Corresponding character expressions have different lengths, such as CHAR(5) and VARCHAR2(20).

• B. Corresponding expressions are from different data type groups, such as NUMBER and VARCHAR2.

• C. The two query blocks use different column aliases in the SELECT list.

• D. One query block selects a literal, and the other selects a column expression.

Best answer: B

Explanation: For Oracle set operators, each query must return the same number of expressions, and each position must be type-compatible. The key incompatibility here is mixing different data type groups, such as numeric and character expressions, in the same column position.

UNION and UNION ALL compare the SELECT lists by position. Oracle requires the same number of expressions, and each matching position must belong to a compatible data type group. A NUMBER in one query and a VARCHAR2 in the other are incompatible for the same position, because Oracle does not use cross-group implicit conversion for set operations.

Character expressions can differ in length and still be compatible. Column aliases also do not determine compatibility, and a literal is allowed if its data type matches the corresponding expression. If needed, you can make queries compatible by explicitly converting values with functions such as TO_CHAR or CAST.

The main takeaway is that set-operator compatibility is based on expression count and positional data type compatibility, not alias names or whether a value comes from a literal.

• Different lengths do not break compatibility when both expressions are character types.
• Different aliases are irrelevant because set operators match columns by position, not alias name.
• Literal versus column is acceptable if the literal resolves to a compatible data type.

Question 9

Topic: Use Set Operators

Which statement correctly describes how Oracle Database evaluates a compound query that uses more than one set operator and does not use parentheses?

Options:

• A. INTERSECT is always evaluated before UNION and MINUS.

• B. Parentheses do not affect evaluation order in compound set queries.

• C. All set operators have equal precedence, so Oracle evaluates them from left to right.

• D. MINUS is evaluated before other set operators unless ORDER BY is present.

Best answer: C

Explanation: In Oracle Database, compound queries without parentheses are evaluated left to right because the set operators have equal precedence. Parentheses are what change the meaning by forcing a different grouping order.

The core rule is that Oracle evaluates set operators in a compound query from left to right when no parentheses are present. That means UNION, UNION ALL, INTERSECT, and MINUS are not reordered automatically based on a higher built-in precedence in current Oracle SQL behavior. If you want a different result, you must add parentheses around the subquery pair you want Oracle to process first.

This matters most when INTERSECT is mixed with UNION or MINUS, because a different grouping can return a different final row set. ORDER BY does not change set-operator precedence; it applies only to the overall compound query result.

The key takeaway is simple: no parentheses means left-to-right evaluation, and parentheses explicitly change the meaning.

• INTERSECT first is a common SQL assumption, but current Oracle behavior for compound queries is left to right without parentheses.
• MINUS first with ORDER BY is incorrect because ORDER BY does not control evaluation order among set operators.
• Parentheses ignored is false because parentheses are exactly how you force a different grouping and result.

Question 10

Topic: Use Set Operators

Two tables, online_orders and store_orders, each contain a single compatible column named customer_id. You need one combined result of all customer_id values from both tables. If the same value appears multiple times in either table or in both tables, every occurrence must remain in the output, including repeated NULL values.

Which query best meets the requirement?

Options:

• A. SELECT customer_id FROM online_orders UNION ALL SELECT customer_id FROM store_orders

• B. SELECT DISTINCT customer_id FROM online_orders UNION ALL SELECT DISTINCT customer_id FROM store_orders

• C. SELECT customer_id FROM online_orders INTERSECT SELECT customer_id FROM store_orders

• D. SELECT customer_id FROM online_orders UNION SELECT customer_id FROM store_orders

Best answer: A

Explanation: Use UNION ALL when the requirement is to keep every returned row from both queries. Unlike UNION, it does not eliminate duplicate values, so repeated IDs and repeated NULL rows remain in the final result.

The core concept is the difference between UNION and UNION ALL. In Oracle SQL, UNION returns distinct rows from the combined result set, so duplicate values are removed after the two query results are merged. UNION ALL simply appends the second result set to the first and keeps all rows exactly as returned.

Because the requirement says every occurrence must remain, including repeated values and repeated NULLs, the compound query must not perform duplicate elimination. DISTINCT in either individual query would already remove duplicates before the sets are combined, and INTERSECT would return only common values. The key takeaway is simple: if duplicates must remain, choose UNION ALL, not UNION.

• Using UNION fails because it removes duplicate rows from the combined result.
• Using DISTINCT in each query fails because duplicates are lost within each table before the compound query runs.
• Using INTERSECT fails because it returns only values found in both tables, not all rows from both tables.

Continue with full practice

Use the Oracle 1Z0-071 Practice Test page for the full IT Mastery route, mixed-topic practice, timed mock exams, explanations, and web/mobile app access.

Try Oracle 1Z0-071 on Web View Oracle 1Z0-071 Practice Test

Related focused pages

• Free Oracle 1Z0-071 Full-Length Practice Exam
• Relational Database Concepts
• SQL SELECT
• Restrict and Sort Data
• Single-Row Functions
• Conversion and CASE
• Group Functions
• Display Data from Multiple Tables
• Use Subqueries to Solve Queries
• Manage Tables by Using DML Statements
• Indexes and Sequences
• DDL Tables and Relationships
• Manage Views
• Control User Access
• Data Dictionary Views
• Manage Data in Different Time Zones

Free review resource

Read the Oracle 1Z0-071 Cheat Sheet on Tech Exam Lexicon, then return to IT Mastery for timed practice.