Stepwise-Lab

Set operators: 1Z0-071 practice questions

Questions in the style of exam 1Z0-071 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
  • 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;
  1. The numbers are converted to strings and 18 rows are returned
  2. Raises ORA-01790: expression must have same datatype as corresponding expression
  3. Raises ORA-01722: invalid number
  4. 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;
  1. It fails: the branches return different numbers of columns (ORA-01789)
  2. It fails: department_id has no column of the same type to match in the second branch (ORA-01790)
  3. It runs: the missing third column is filled with NULL, and 14 rows are returned
  4. 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;
  1. It returns 14 names sorted alphabetically
  2. ORA-00904: "E"."LAST_NAME": invalid identifier
  3. Only the employee names are sorted
  4. 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;
  1. 0
  2. 1
  3. 2
  4. 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.