Using Set Operators practice questions

Worked examples for Using Set Operators

1. Question 1

   What is the result of executing the following statement? ```sql SELECT last_name, salary FROM employees WHERE dept_id = 10 ORDER BY salary DESC UNION SELECT last_name, salary FROM employees WHERE dept_id = 30 ```

   1. A. It executes successfully: the department 10 rows come back first, sorted by SALARY descending, and the department 30 rows are appended after them in their own order.

      This is the "ORDER BY sorts just its own branch" misconception. A component query of a compound query is not an independently ordered result set that then gets concatenated; the set operator combines unordered row sources, so a per-branch ORDER BY has no meaning and Oracle rejects the statement at parse time instead of running it.

   2. B. ORA-03048: SQL reserved word ambiguously used in a compound queryCorrect answer

      Correct. In a compound query the ORDER BY clause is legal only after the final component query. Here ORDER BY appears on the first branch and is immediately followed by the UNION keyword, so the parser cannot tell whether the clause belongs to the branch or to the compound query and raises ORA-03048 — the compound-query-specific diagnostic for a reserved word used in an ambiguous position.

   3. C. It executes successfully: Oracle applies the ORDER BY to the whole UNION result, so every row comes back sorted by SALARY descending.

      This assumes Oracle silently relocates a misplaced ORDER BY to the end of the compound query. It does not. Placement is part of the syntax: to sort the combined result the clause must be written after the last component query, and until it is the statement does not parse at all.

   4. D. ORA-00933: SQL command not properly ended

      ORA-00933 is the generic "unexpected text after a complete statement" error, raised for things like trailing tokens after a single SELECT. Oracle diagnoses a set operator following a branch-level ORDER BY with the more specific compound-query error instead, so this is not the code returned here.

   Explanation

   A compound query built with UNION, UNION ALL, INTERSECT, or MINUS accepts a single ORDER BY clause, and it must follow the final component query, where it sorts the entire combined result. Writing ORDER BY on an earlier branch and then continuing with a set operator is a parse error, because the parser cannot attach the clause unambiguously. Oracle neither sorts that branch in isolation nor moves the clause to the end for you, so the statement never executes.

2. Question 2

   Given the data below, how many rows does the following statement return? ```text EMPLOYEES DEPARTMENTS EMP_ID COMMISSION DEPT_ID DEPT_ID 100 (null) 10 10 101 0.10 20 20 102 0.15 20 30 103 (null) 20 40 104 0.05 30 105 (null) 30 106 0.20 30 107 (null) 10 ``` ```sql SELECT dept_id FROM employees WHERE commission IS NULL UNION SELECT dept_id FROM departments ```

   1. A. 8

      Counts UNION as if it were UNION ALL — 4 rows from the first query plus 4 from the second. UNION removes duplicate rows from the combined result; only UNION ALL returns every row including duplicates.

   2. B. 4Correct answer

      The first query yields dept ids 10, 20, 30, 10 and the second yields 10, 20, 30, 40; UNION combines them and removes duplicates from the whole result, leaving the distinct set 10, 20, 30, 40 — four rows.

   3. C. 7

      Applies duplicate elimination inside each query separately (3 distinct ids + 4 distinct ids) and then concatenates. UNION's DISTINCT is applied to the combined result of both queries, not independently to each branch.

   4. D. 3

      Treats UNION as returning only the rows common to both queries, which is INTERSECT's behaviour. UNION returns every distinct row from either query, not just the shared ones.

   Explanation

   UNION concatenates the results of both queries and then eliminates duplicate rows across the entire combined result, so the row count is the number of distinct values in the union of the two sets — never the sum of the two inputs. UNION ALL is the operator that keeps every row from both queries, duplicates included, and INTERSECT is the one that narrows the result to rows common to both. Because duplicate removal happens once over the merged result rather than per branch, ids repeated within a single query and ids repeated across the two queries collapse alike.

3. Question 3

   The `employees` table contains exactly the rows below: ```text EMP_ID SALARY COMMISSION DEPT_ID 100 9000 (null) 10 101 6000 0.10 20 102 7500 0.15 20 103 4800 (null) 20 104 5200 0.05 30 105 3900 (null) 30 106 8100 0.20 30 107 6700 (null) 10 ``` How many rows does the following statement return? ```sql SELECT dept_id FROM employees WHERE salary >= 6000 UNION ALL SELECT dept_id FROM employees WHERE commission IS NULL ```

   1. A. 3

      Applies UNION's duplicate elimination to UNION ALL, collapsing the combined result to the distinct dept_id values 10, 20 and 30. UNION ALL performs no de-duplication at all.

   2. B. 4

      Reads the ALL keyword as "rows satisfying all of the queries", i.e. treats the statement as a multiset intersection of the two branches. UNION ALL is a concatenation, not an intersection; ALL modifies duplicate handling, not membership.

   3. C. 5

      Assumes UNION ALL only suppresses values of the second query that already appeared in the first (keeping duplicates within a branch). Every dept_id from the second branch already occurs in the first, so this reasoning drops all four of its rows; UNION ALL in fact compares nothing between branches.

   4. D. 9Correct answer

      UNION ALL returns all rows selected by either query, including every duplicate. The first branch (salary >= 6000: employees 100, 101, 102, 106, 107) returns 5 rows and the second (commission IS NULL: employees 100, 103, 105, 107) returns 4 rows, so the statement returns 5 + 4 = 9 rows.

   Explanation

   UNION ALL is the only one of the four set operators that does no duplicate elimination and no comparison between the two result sets: it simply concatenates them, so its row count is always the sum of the two branch row counts, no matter how much the branches overlap. Rows selected by both branches — here the employees who both earn at least 6000 and have a NULL commission — therefore appear twice. Switching to UNION would collapse the result to the distinct dept_id values instead.

4. Question 4

   The `employees` table has columns `first_name VARCHAR2(30)`, `last_name VARCHAR2(30)`, `salary NUMBER(8,2)` and `dept_id NUMBER(4)`. What is the result of executing the following statement? ```sql SELECT first_name AS name, salary FROM employees WHERE dept_id = 20 UNION ALL SELECT last_name AS surname, salary FROM employees WHERE dept_id = 30 ORDER BY surname ```

   1. A. It executes successfully and returns the rows of both branches sorted by the second branch's surname values.

      Represents the belief that any alias defined in any component query is visible to the compound query's ORDER BY. Aliases from the second and later branches are not part of the compound result's column names, so the statement fails before returning a row.

   2. B. It fails with ORA-00904: "SURNAME": invalid identifier.Correct answer

      The compound result takes its column names from the first component query, so its columns are NAME and SALARY. SURNAME, defined only in the second branch, is not a name the outermost ORDER BY can resolve, so the statement fails with ORA-00904.

   3. C. It fails with ORA-01789: query block has incorrect number of result columns.

      Represents the belief that branches must expose the same column names, not merely the same shape. ORA-01789 is raised only when the branches select a different number of expressions; here both select exactly two, so the column-count rule is satisfied.

   4. D. It fails with ORA-01785: ORDER BY item must be the number of a SELECT-list expression.

      Represents the belief that a compound query's ORDER BY accepts only positional numbers. Oracle accepts a column name or alias there as well — provided it comes from the first component query — so the failure is name resolution, not a positional-only restriction.

   Explanation

   The single ORDER BY of a compound query is correctly placed after the last component query, but placement alone is not enough: it is resolved against the compound result, whose column names and aliases come from the first component query only. Aliases introduced in the second or any later branch never surface as result column names, so referencing one is an invalid identifier rather than an ordering instruction. Sorting by that branch's expression requires either aliasing it in the first branch as well, or ordering by the positional number of the select-list item.

5. Question 5

   The DEPARTMENTS table is defined as (dept_id NUMBER(4), dept_name VARCHAR2(30), location VARCHAR2(30)) and the EMPLOYEES table as (emp_id NUMBER(6), first_name VARCHAR2(30), last_name VARCHAR2(30), salary NUMBER(8,2), commission NUMBER(4,2), manager_id NUMBER(6), dept_id NUMBER(4), hire_date DATE). Which outcome results from executing the following statement? ```sql SELECT dept_id, dept_name FROM departments UNION ALL SELECT emp_id, hire_date FROM employees ORDER BY 1 ```

   1. A. ORA-00933

      Represents the belief that a compound query cannot carry an ORDER BY after UNION ALL, or that the clause must sit inside the final query block. A single ORDER BY after the last branch is exactly where the rule requires it, so ORA-00933 (SQL command not properly ended) is not raised here.

   2. B. ORA-01789

      Represents the belief that mismatched column names or datatypes are reported as a result-column-count problem. ORA-01789 (query block has incorrect number of result columns) fires only when the branches project different numbers of columns; both branches here project exactly two.

   3. C. The statement executes successfully and returns 12 rows

      Represents the belief that UNION ALL, because it does no duplicate elimination, tolerates mismatched datatypes and implicitly converts the DATE to a character value. Datatype matching is required by every set operator, ALL included, so the statement never runs.

   4. D. ORA-01790Correct answer

      Corresponding expressions in the branches of a set operation must have the same datatype. Position 2 pairs dept_name (VARCHAR2) with hire_date (DATE), which Oracle will not implicitly convert across a set operator, so the statement fails with ORA-01790: expression must have same datatype as corresponding expression.

   Explanation

   Every set operator matches the branches column by ordinal position, and each pair of corresponding expressions must be of the same datatype; skipping duplicate elimination with ALL does not relax that requirement, and Oracle does not implicitly convert a DATE to a character value to make the branches compatible. Pairing a VARCHAR2 column with a DATE column in the second position therefore fails at parse time, before ordering is ever considered — the trailing ORDER BY on the last branch is itself legal placement.

6. Question 6

   Consider only these EMPLOYEES rows (the two departments referenced by the query): | EMP_ID | DEPT_ID | MANAGER_ID | |-------:|--------:|-----------:| | 100 | 10 | (null) | | 107 | 10 | 100 | | 104 | 30 | 106 | | 105 | 30 | 106 | | 106 | 30 | (null) | How many rows does the following statement return? ```sql SELECT manager_id FROM employees WHERE dept_id = 10 UNION SELECT manager_id FROM employees WHERE dept_id = 30 ```

   1. A. 2

      Assumes set operators discard rows whose value is NULL, the way an equality predicate filters them out, leaving only 100 and 106. UNION is not a WHERE clause: a NULL is a legitimate value in the result set and is returned as one row.

   2. B. 3Correct answer

      The combined values are NULL, 100, 106, 106, NULL. UNION eliminates duplicates, and in duplicate-elimination and sorting operations Oracle treats two NULLs as equal, so the two NULL rows collapse into one, as do the two 106 rows: NULL, 100, 106.

   3. C. 4

      Applies the comparison rule NULL = NULL is UNKNOWN to duplicate elimination and therefore keeps both NULL rows as distinct. Duplicate elimination is not a comparison predicate — for it, and for sorting and grouping, Oracle treats nulls as equal to each other.

   4. D. 5

      Counts every row produced by both component queries, which is what UNION ALL would return. UNION performs duplicate elimination over the combined result, so the repeated 106 and the repeated NULL are each reduced to a single row.

   Explanation

   UNION returns the distinct rows selected by either component query, so duplicate elimination is applied across the combined result rather than within each branch. Duplicate elimination follows the same null semantics as sorting and grouping, where Oracle treats two nulls as equal to each other — the opposite of the three-valued comparison rule that makes NULL = NULL unknown. Repeated non-null values and repeated nulls therefore each collapse to one row, while UNION ALL would have kept every row.

7. Question 7

   The EMPLOYEES table has columns EMP_ID, FIRST_NAME, LAST_NAME, SALARY, COMMISSION, MANAGER_ID, DEPT_ID and HIRE_DATE. Both query blocks below select the same two columns, in the same order, with the same datatypes, and neither block is parenthesized. What is the result of executing this statement? ```sql SELECT first_name, salary FROM employees WHERE dept_id = 20 ORDER BY salary DESC UNION SELECT first_name, salary FROM employees WHERE commission IS NOT NULL ```

   1. A. ORA-01789

      ORA-01789 ('query block has incorrect number of result columns') is raised only when the branches of a set operation disagree on column count. Both branches here select exactly two columns, FIRST_NAME and SALARY, so the SELECT lists match and this error cannot occur.

   2. B. ORA-03048Correct answer

      An unparenthesized query block that carries ORDER BY cannot be an operand of a set operator, so the parser raises ORA-03048 ('SET operator not allowed on the query block containing ORDER BY ...'). Moving the single ORDER BY to the end of the whole statement — or parenthesizing the branch — makes it legal.

   3. C. The statement executes: the DEPT_ID = 20 rows come back sorted by SALARY descending, followed by the COMMISSION IS NOT NULL rows in unspecified order

      This is the per-branch-ordering misconception. A compound query is one statement producing one result set; there is no notion of ordering one branch independently and concatenating it ahead of another. Oracle rejects the statement at parse time rather than producing this layout.

   4. D. The statement executes and the ORDER BY on the first query block is silently ignored, so all rows are returned in unspecified order

      Oracle never silently discards a branch-level ORDER BY in a set operation. The restriction is enforced by the parser, so the statement fails before any branch is evaluated and no rows are returned at all.

   Explanation

   A compound query built with UNION, UNION ALL, INTERSECT or MINUS is a single statement, so it accepts at most one ORDER BY clause and that clause must follow the last query block, where it orders the combined result. Attaching ORDER BY to a bare (unparenthesized) branch makes that branch illegal as an operand of the set operator, and Oracle rejects the statement at parse time — no branch is evaluated and no rows come back. To sort the whole result, put one ORDER BY at the very end and reference the first query block's column names or positional column numbers; to sort inside a branch you must parenthesize it, and that inner ordering still does not survive into the combined result.

8. Question 8

   In the schema, `departments.dept_id` is `NUMBER(4)` and `departments.dept_name` is `VARCHAR2(30)`; `employees.emp_id` is `NUMBER(6)` and `employees.salary` is `NUMBER(8,2)`. What is the result of executing the following statement? ```sql SELECT dept_id, dept_name FROM departments UNION SELECT emp_id, salary FROM employees ```

   1. A. ORA-01789: query block has incorrect number of result columns

      Assumes the only compatibility rule for a compound query is the column count. Both query blocks select two expressions, so the count rule is satisfied and this error is not raised; the failure comes from the datatype rule instead.

   2. B. ORA-01790: expression must have same datatype as corresponding expressionCorrect answer

      Corresponding expressions in the component queries of a compound query must be of the same datatype group. The second expressions pair a character column (dept_name) with a numeric column (salary), which Oracle rejects at parse time with ORA-01790.

   3. C. ORA-01722: invalid number

      Assumes Oracle implicitly converts the character values to numbers and then fails on data that does not look numeric. Set operators do not apply implicit character-to-number conversion across branches; the mismatch is rejected before any row is fetched.

   4. D. ORA-00904: invalid identifier

      Assumes the branches of a compound query must select identically named columns. Column names need not match — the result set simply takes its names from the first query — so no identifier error occurs.

   Explanation

   A compound query built with UNION, UNION ALL, INTERSECT, or MINUS requires each component query to select the same number of expressions, and each pair of corresponding expressions must be in the same datatype group. Column names are irrelevant: the result set inherits the names of the first query. Pairing a VARCHAR2 column with a NUMBER column violates the datatype rule, and Oracle raises the error while parsing rather than attempting any implicit conversion.