Set operators: 1Z0-171 practice questions
Questions in the style of exam 1Z0-171 on this topic. Answer each one first, then open its answer and explanation.
What this topic covers
- Matching the SELECT statements
- UNION and UNION ALL
- INTERSECT
- MINUS and EXCEPT
- ORDER BY in set operations
Practice questions
1. What happens?
SELECT e.employee_id FROM employees e
UNION
SELECT e.last_name FROM employees e;- The numbers are converted to strings and 18 rows are returned
- Raises ORA-01790: expression must have same datatype as corresponding expression
- Raises ORA-01722: invalid number
- Raises ORA-01789: query block has incorrect number of result columns
Show the answer
Answer: B
Corresponding columns must be in the same data type group, and set operators never convert between groups, so NUMBER above VARCHAR2 raises ORA-01790. Oracle does not even try to turn the names into numbers, so there is no ORA-01722, and the 18 rows are what you get only after wrapping employee_id in TO_CHAR. Both branches have one column, so it is not ORA-01789.
2. What happens when you run this query?
SELECT e.last_name, e.salary, e.department_id FROM employees e
UNION ALL
SELECT c.customer_name, c.credit_limit FROM customers c;- It fails: the branches return different numbers of columns (ORA-01789)
- It fails: department_id has no column of the same type to match in the second branch (ORA-01790)
- It runs: the missing third column is filled with NULL, and 14 rows are returned
- It runs: UNION ALL compares nothing, so the column counts do not have to agree, and 14 rows are returned
Show the answer
Answer: A
Every SELECT in a compound query must return the same number of columns, with UNION ALL as with UNION: the rows of both branches land in the same columns. Oracle never pads a branch, so it raises ORA-01789. ORA-01790 is the error for a type mismatch in a position that both branches have. To combine queries of different widths, add NULL (or a literal) as a placeholder column to the shorter one: SELECT c.customer_name, c.credit_limit, NULL FROM customers c.
3. What happens when you run this query?
SELECT e.last_name FROM employees e
UNION
SELECT c.customer_name FROM customers c
ORDER BY e.last_name;- It returns 14 names sorted alphabetically
- ORA-00904: "E"."LAST_NAME": invalid identifier
- Only the employee names are sorted
- ORA-00918: column ambiguously defined
Show the answer
Answer: B
The ORDER BY of a compound query applies to the combined result, which has no table aliases, so e.last_name cannot be resolved (ORA-00904). Use the unqualified name LAST_NAME, an alias from the first SELECT, or the position 1.
4. Order 1006 was placed on 31 March 2024 at 23:30; no other order is dated 31 March 2024. How many rows does this query return?
SELECT o.order_date FROM orders o
INTERSECT
SELECT DATE '2024-03-31' FROM dual;- 0
- 1
- 2
- It raises ORA-01790 because a DATE literal is not a column
Show the answer
Answer: A
INTERSECT compares complete values, including the time part of a DATE: 2024-03-31 23:30:00 is not equal to 2024-03-31 00:00:00, so no row matches. With TRUNC(o.order_date) in the first branch the result is 1 row. A DATE literal and a DATE column are the same data type, so there is no ORA-01790.