Using Set Operators practice questions

Worked examples for Using Set Operators

1. Question 1

   A developer needs a query that returns the `emp_id` of every employee who is also recorded as the `manager_id` of at least one other employee. Which query correctly returns this set?

   1. A. SELECT manager_id FROM employees WHERE manager_id IS NOT NULL MINUS SELECT emp_id FROM employees

      The MINUS operands are reversed. This query returns manager_id values not present as any emp_id. Because every manager in this dataset is also an employee, the subtracted set contains all manager IDs and the result is empty—the exact opposite of what is required.

   2. B. SELECT emp_id FROM employees UNION SELECT manager_id FROM employees WHERE manager_id IS NOT NULL

      UNION returns every distinct value that appears in either result set—the union, not the intersection. Because every manager in this dataset is also an employee, combining all emp_id values with all non-NULL manager_id values simply reproduces all eight employee IDs, not only the three who manage others.

   3. C. SELECT emp_id FROM employees INTERSECT SELECT manager_id FROM employeesCorrect answer

      INTERSECT returns only distinct rows common to both result sets. NULL values in manager_id find no match on the left side because emp_id is a PRIMARY KEY and is never NULL. The remaining manager_id values—100, 101, and 106—each appear in the emp_id list, so exactly those three values are returned.

   4. D. SELECT emp_id FROM employees MINUS SELECT manager_id FROM employees WHERE manager_id IS NOT NULL

      MINUS returns rows present in the first result set that are absent from the second—the logical complement of what is needed. Subtracting the manager ID set {100, 101, 106} from all emp_id values yields the employees who are NOT managers: {102, 103, 104, 105, 107}.

   Explanation

   INTERSECT is the correct operator when the goal is values that appear in both result sets. MINUS returns values present in the first set but absent from the second, which inverts the intended logic; reversing its operands yields an empty set instead. UNION combines both sets (eliminating duplicates), so it returns all employee IDs when every manager is also an employee—far more than the intended subset. NULL values in manager_id are handled without issue because emp_id is a primary key and is never NULL, so no unintended NULL matches can occur.

2. Question 2

   What is the outcome of executing the following statement? ```sql SELECT first_name FROM employees UNION SELECT salary FROM employees ```

   1. A. The statement fails with ORA-01790.Correct answer

      Corresponding columns across a UNION must belong to the same datatype group. FIRST_NAME is character and SALARY is numeric, so Oracle rejects the pairing with ORA-01790 (expression must have same datatype as corresponding expression) instead of converting either side.

   2. B. The statement fails with ORA-01789.

      ORA-01789 (query block has incorrect number of result columns) is raised only when the component queries select a different NUMBER of columns. Both branches select exactly one column here, so the arity matches — the defect is datatype, not column count.

   3. C. The statement fails with ORA-00904: invalid identifier.

      ORA-00904 signals a reference to a column or alias that does not exist. Both FIRST_NAME and SALARY are real columns of EMPLOYEES, so this is not the error the statement raises.

   4. D. The statement succeeds and returns a single column holding the names followed by the salaries.

      Oracle does not implicitly convert numbers to characters (or the reverse) to reconcile mismatched UNION branches; incompatible datatype groups are rejected outright, so no combined result is produced.

   Explanation

   The UNION, UNION ALL, INTERSECT, and MINUS operators require each pair of corresponding columns in the component queries to be in the same datatype group. Pairing a character column with a numeric column is incompatible, and Oracle raises an error rather than coercing one side. A mismatch in the number of selected columns is a separate, distinct error with its own code.

3. Question 3

   The COMMISSION values (NUMBER, nullable) for the two departments involved are: ``` dept_id = 20 : 0.10, 0.15, (null) dept_id = 30 : 0.05, 0.20, (null) ``` What value does the following query return? ```sql SELECT commission FROM employees WHERE dept_id = 20 INTERSECT SELECT commission FROM employees WHERE dept_id = 30 ```

   1. A. No rows are returned

      This assumes null never matches null (ordinary NULL = NULL comparison logic). Set operators are the exception - they treat nulls as equal - so the shared null does match and a row is returned.

   2. B. 0.05, 0.1, 0.15, 0.2

      That is the combined distinct list of the non-null commissions, which is what UNION of the two branches would tend toward - not INTERSECT, which keeps only values common to both.

   3. C. NULLCorrect answer

      The only non-empty commission shared by both branches is the null: the two non-null sets {0.10, 0.15} and {0.05, 0.20} are disjoint, but in set operations Oracle treats two nulls as equal, so the null in each branch matches and INTERSECT returns a single NULL row.

   4. D. An error is raised because COMMISSION contains NULLs

      Set operators do not reject null operands; nulls participate normally (and match each other), so the query runs successfully and raises no error.

   Explanation

   INTERSECT returns the distinct rows common to both branches, and for the purpose of set operators Oracle considers two nulls to be equal rather than unknown. The non-null commission values of the two departments do not overlap, so the only shared value is the null that appears in each branch, and it is returned as a single row.

4. Question 4

   Consider the following statement executed against the schema. What is the outcome? ```sql SELECT emp_id, first_name FROM employees WHERE dept_id = 10 UNION SELECT dept_id FROM departments; ```

   1. A. The statement fails with ORA-01790 (expression must have same datatype as corresponding expression).

      ORA-01790 is raised when the branches have the SAME number of columns but a corresponding pair has incompatible datatypes. Here the column counts themselves differ, so the count check fires first; the datatype rule is never reached.

   2. B. The statement fails with ORA-01789 (query block has incorrect number of result columns).Correct answer

      Every query block joined by a set operator must project the same number of columns. The first block selects two columns (emp_id, first_name) and the second selects one (dept_id), so Oracle rejects the statement for the column-count mismatch with ORA-01789 before it ever compares datatypes.

   3. C. The statement fails with ORA-00904 (invalid identifier).

      ORA-00904 signals a mistyped or non-existent column name. Every column referenced here (emp_id, first_name, dept_id) exists in its table, so this error is not raised.

   4. D. The statement runs successfully, padding the shorter query with NULLs.

      Oracle does not pad a shorter set-operation branch with NULLs; unequal column counts make the compound query invalid, so it cannot execute at all.

   Explanation

   Queries combined by UNION, UNION ALL, INTERSECT, or MINUS must each select the same number of columns, with corresponding columns in compatible datatype groups. When the counts differ, Oracle rejects the statement for the count mismatch itself, not for any datatype comparison, and does not silently reconcile the branches.

5. Question 5

   The DEPARTMENTS and EMPLOYEES tables contain the following DEPT_ID values: **DEPARTMENTS.dept_id:** 10, 20, 30, 40 **Distinct EMPLOYEES.dept_id:** 10, 20, 30 How many rows does the following query return? ```sql SELECT dept_id FROM departments MINUS SELECT dept_id FROM employees ```

   1. A. 3

      3 is the count of distinct dept_ids that DO appear in the employees table (10, 20, 30). MINUS returns rows that are absent from the second query — the complement — not the rows that match it.

   2. B. 1Correct answer

      MINUS returns distinct rows from the first query that do not appear in the second. The departments table contains dept_ids 10, 20, 30, and 40; all eight employees belong to dept 10, 20, or 30, so only dept_id 40 (Research, with no employees) is absent from the second query and survives the MINUS — exactly 1 row.

   3. C. 4

      4 is the raw row count of the departments table before MINUS is applied. MINUS eliminates every dept_id that also appears among employees, reducing the result from 4 to 1.

   4. D. 0

      Zero rows would result only if every dept_id in the departments table also appeared in the employees table. Department 40 (Research) has no employees in the fixture, so its dept_id is absent from the second query and is not removed by MINUS.

   Explanation

   MINUS returns each distinct row from the first query that does not appear anywhere in the second query, eliminating duplicates from both sides before comparing. The departments table has four dept_ids (10, 20, 30, 40), while every employee is assigned to dept 10, 20, or 30. Because dept_id 40 exists in departments but not in employees, it is the sole surviving row and the query returns exactly one row.

6. Question 6

   The following rows from the `EMPLOYEES` table are relevant to the query below. **Employees with salary > 7 000:** | EMP_ID | DEPT_ID | SALARY | |--------|---------|--------| | 100 | 10 | 9000 | | 102 | 20 | 7500 | | 106 | 30 | 8100 | **Employees with commission IS NOT NULL:** | EMP_ID | DEPT_ID | COMMISSION | |--------|---------|------------| | 101 | 20 | 0.10 | | 102 | 20 | 0.15 | | 104 | 30 | 0.05 | | 106 | 30 | 0.20 | How many rows does the following query return? ```sql SELECT dept_id FROM employees WHERE salary > 7000 UNION ALL SELECT dept_id FROM employees WHERE commission IS NOT NULL ```

   1. A. 7Correct answer

      UNION ALL concatenates both result sets with no duplicate removal. The first branch contributes 3 rows (dept_ids 10, 20, 30) and the second contributes 4 rows (dept_ids 20, 20, 30, 30), giving 3 + 4 = 7 rows in total.

   2. B. 3

      3 is the row count that UNION—not UNION ALL—would return. UNION eliminates all duplicate values across both result sets, leaving only the distinct dept_id values {10, 20, 30}. UNION ALL performs no deduplication of any kind.

   3. C. 4

      4 is the row count from the second branch alone (the four employees with a non-NULL commission). UNION ALL concatenates both branches in full; it does not suppress or replace one branch with the other.

   4. D. 5

      5 results from mistakenly deduplicating within each branch before combining: the first branch yields 3 distinct dept_id values and the second yields 2 distinct dept_id values, totalling 5. UNION ALL does not deduplicate within individual branches; it passes every row from each branch through unchanged.

   Explanation

   UNION ALL is a straight row-by-row concatenation of the two result sets with no deduplication—neither within a single branch nor between the two branches. The total row count is always the arithmetic sum of the individual branch counts. This differs fundamentally from UNION, which eliminates duplicates across the combined set and would return only 3 distinct dept_id values here. A common trap is treating UNION ALL as though it applies per-branch deduplication before merging, which it does not.

7. Question 7

   A developer needs a query that returns the FIRST_NAME of every employee who works in department 10 OR earns a salary above 8 000, with each name appearing at most once in the result. Which query satisfies this requirement?

   1. A. SELECT first_name FROM employees WHERE dept_id = 10 UNION ALL SELECT first_name FROM employees WHERE salary > 8000

      UNION ALL returns every row from both branches without removing duplicates; Alice satisfies both conditions and therefore appears in both branches, producing two occurrences of 'Alice' in the four-row result — violating the at-most-once requirement.

   2. B. SELECT first_name FROM employees WHERE dept_id = 10 INTERSECT SELECT first_name FROM employees WHERE salary > 8000

      INTERSECT returns only rows present in both result sets, implementing AND semantics rather than OR; it returns only Alice (who satisfies both conditions simultaneously), omitting Heidi (dept 10 only) and Grace (high salary only).

   3. C. SELECT first_name FROM employees WHERE dept_id = 10 MINUS SELECT first_name FROM employees WHERE salary > 8000

      MINUS removes from the first result set every row that appears in the second; Alice is subtracted out because she also satisfies the salary condition, leaving only Heidi — the opposite of a combined OR list.

   4. D. SELECT first_name FROM employees WHERE dept_id = 10 UNION SELECT first_name FROM employees WHERE salary > 8000Correct answer

      UNION combines the two result sets and eliminates duplicate rows: Alice (dept 10 and salary 9 000 > 8 000), Heidi (dept 10, salary 6 700), and Grace (salary 8 100 > 8 000) each appear exactly once — three names in total.

   Explanation

   UNION combines the rows from both component queries and automatically eliminates duplicate rows from the merged result, making it the correct choice when the goal is every row satisfying either condition with no repeated values. UNION ALL skips deduplication and returns every row from both queries, so any name satisfying both conditions appears more than once. INTERSECT narrows the result to only rows present in both branches simultaneously, and MINUS further restricts it to rows in the first branch that are absent from the second — neither operator models a deduplicated OR across two conditions.

8. Question 8

   A developer must retrieve every department ID present in the DEPARTMENTS table that is not referenced by any row in the EMPLOYEES table. Which query correctly returns this set?

   1. A. SELECT dept_id FROM departments INTERSECT SELECT dept_id FROM employees

      INTERSECT returns only values present in both result sets — the dept_ids shared by DEPARTMENTS and EMPLOYEES — which is the common membership. The developer needs the exclusive difference (IDs in DEPARTMENTS only), not the overlap.

   2. B. SELECT dept_id FROM departments UNION SELECT dept_id FROM employees

      UNION returns every distinct dept_id from either table combined. This includes all dept_ids that already have employee rows, which must be excluded; the result is a full union across both sets rather than the department-exclusive difference.

   3. C. SELECT dept_id FROM departments MINUS SELECT dept_id FROM employeesCorrect answer

      MINUS returns every row from the first result set that does not appear in the second. Placing DEPARTMENTS on the left correctly exposes the IDs that exist in the department catalog but have no matching employee rows — the intended exclusive difference.

   4. D. SELECT dept_id FROM employees MINUS SELECT dept_id FROM departments

      Reverses the operands of MINUS, which is not commutative. With EMPLOYEES on the left, the query returns employee dept_ids absent from DEPARTMENTS; because every employee dept_id is a valid foreign key referencing an existing department, this always produces an empty result — the opposite of the intended set.

   Explanation

   MINUS is not commutative: it returns rows present in the first query's result that do not appear in the second's, so operand order determines which table's exclusive rows are exposed. INTERSECT finds only the rows common to both result sets, and UNION returns all distinct rows from either side — neither operator isolates the rows belonging exclusively to one table.