Using Subqueries to Solve Queries practice questions

Worked examples for Using Subqueries to Solve Queries

1. Question 1

   The `employees` table has a self-referencing `manager_id` column that is NULL for employees who report to nobody, and every non-NULL `manager_id` matches an existing `emp_id`. Which query lists the last name of every employee who is **not** the manager of any other employee?

   1. A. SELECT e.last_name FROM employees e WHERE NOT EXISTS (SELECT 1 FROM employees m WHERE m.manager_id = e.emp_id);Correct answer

      The correlated subquery is re-evaluated per outer row and asks only whether any row exists whose manager_id equals this employee's emp_id. NOT EXISTS is TRUE exactly when that probe returns zero rows, and rows with a NULL manager_id simply fail the correlation predicate rather than poisoning the result — so NULLs cannot suppress the whole answer the way NOT IN does.

   2. B. SELECT e.last_name FROM employees e WHERE e.emp_id NOT IN (SELECT m.manager_id FROM employees m);

      Treats NOT IN as safe when the subquery can return NULL. Because at least one employee has a NULL manager_id, the subquery result contains NULL, so `emp_id NOT IN (..., NULL)` evaluates to UNKNOWN for every candidate row that is not an outright match and to FALSE for those that are — the query returns no rows at all.

   3. C. SELECT e.last_name FROM employees e WHERE e.manager_id IS NULL;

      Confuses the two directions of the self-reference: it tests whether the employee HAS a manager, not whether the employee IS a manager. It returns the top-of-hierarchy employees, which is a different set from the employees nobody reports to.

   4. D. SELECT DISTINCT m.last_name FROM employees m JOIN employees e ON e.manager_id = m.emp_id;

      Inverts the requirement: the join keeps only the parent rows that are matched by some child, so it lists the employees who DO manage someone — the exact complement of the requested set.

   Explanation

   NOT EXISTS and NOT IN are not interchangeable when the inner query can produce NULL. `x NOT IN (subquery)` is rewritten as `x <> v1 AND x <> v2 AND ...`; a NULL member makes one conjunct UNKNOWN, so the predicate can never be TRUE and the outer query returns nothing. A correlated NOT EXISTS tests only whether the probe returned any row, so a NULL correlation column merely fails to match and the anti-join behaves as intended. Reversing the correlation (matching the employee's own manager_id, or joining parent to child) answers a different question entirely.

2. Question 2

   Department 20 contains three employees, and every one of them has a non-NULL SALARY. Which query returns the last names of exactly those employees who earn more than **at least one** employee in department 20?

   1. A. SELECT last_name FROM employees WHERE salary > ALL (SELECT salary FROM employees WHERE dept_id = 20);

      Reads ALL as if it meant "at least one". `> ALL` is TRUE only when the salary exceeds every value returned, i.e. salary > MAX(dept 20 salary), so it returns only the top earners and drops everyone who beats just some of the department.

   2. B. SELECT last_name FROM employees WHERE salary IN (SELECT salary FROM employees WHERE dept_id = 20);

      Confuses membership with comparison: IN is equivalent to `= ANY`, so it tests equality against the department's salaries rather than the greater-than relationship the question asks for, returning only employees whose salary exactly matches one of them.

   3. C. SELECT last_name FROM employees WHERE salary > ANY (SELECT salary FROM employees WHERE dept_id = 20);Correct answer

      `> ANY` is TRUE when the comparison holds for at least one value returned by the subquery, which is exactly "earns more than at least one employee in department 20" — equivalently salary > MIN(dept 20 salary).

   4. D. SELECT last_name FROM employees WHERE salary > (SELECT salary FROM employees WHERE dept_id = 20);

      Uses the single-row operator `>` against a subquery that returns three rows, so the statement fails at run time with ORA-01427: single-row subquery returns more than one row. A multiple-row subquery requires ANY/SOME, ALL, or IN.

   Explanation

   A comparison operator followed by ANY (or its synonym SOME) is TRUE when the comparison succeeds against at least one value the subquery returns, so `> ANY` behaves like a comparison against the minimum of that list. The same operator followed by ALL demands the comparison hold against every returned value, behaving like a comparison against the maximum, and IN tests equality membership rather than ordering. A bare comparison operator is a single-row operator: pairing it with a subquery that returns more than one row raises ORA-01427 instead of returning a result set.

3. Question 3

   The `employees` table holds exactly eight rows. `emp_id` is the primary key, and the `emp_id`, `last_name`, and `commission` columns contain these values: ``` EMP_ID LAST_NAME COMMISSION ------ --------- ---------- 100 King (null) 101 Chen 0.10 102 Diaz 0.15 103 Novak (null) 104 Osei 0.05 105 Petrov (null) 106 Quinn 0.20 107 Rossi (null) ``` How many rows does the following query return? ```sql SELECT last_name FROM employees WHERE commission = (SELECT commission FROM employees WHERE emp_id = 103) ```

   1. A. 1

      Assumes `=` is reflexive so a row always matches its own value, returning employee 103. Because that value is NULL, even comparing the row with itself yields UNKNOWN, not TRUE.

   2. B. 4

      Treats NULL = NULL as TRUE and so matches the four employees whose commission is NULL. NULL is not equal to anything, including another NULL; only IS NULL tests for it.

   3. C. 8

      Treats an UNKNOWN predicate as satisfied and therefore returns every row. WHERE keeps only rows for which the condition evaluates to TRUE; UNKNOWN rows are discarded just like FALSE ones.

   4. D. 0Correct answer

      The single-row subquery legally returns exactly one value, NULL. Any comparison of a value with NULL using `=` evaluates to UNKNOWN, so no row satisfies the WHERE clause and the query returns no rows.

   Explanation

   A subquery on the primary key returns exactly one row, so the single-row operator `=` is valid and no ORA-01427 arises — but the single value it returns is NULL. Every comparison against NULL with `=`, `!=`, `<`, or `>` evaluates to UNKNOWN rather than TRUE or FALSE, and a WHERE clause returns only the rows for which its condition is TRUE. That holds even for the row that supplied the NULL, so the result set is empty; matching NULL commissions requires IS NULL instead.

4. Question 4

   The EMPLOYEES table contains exactly these rows in the relevant columns: ``` EMP_ID LAST_NAME COMMISSION ------ --------- ---------- 100 King (null) 101 Chen 0.10 102 Diaz 0.15 103 Novak (null) 104 Osei 0.05 105 Petrov (null) 106 Quinn 0.20 107 Rossi (null) ``` LAST_NAME values are unique. How many rows does the following statement return? ```sql SELECT last_name FROM employees WHERE commission = (SELECT commission FROM employees WHERE last_name = 'Novak') ```

   1. A. 4

      Assumes NULL = NULL evaluates to TRUE, so the four rows with a NULL commission (King, Novak, Petrov, Rossi) would match. A comparison in which either operand is NULL evaluates to UNKNOWN, and only rows whose condition is TRUE are returned.

   2. B. 1

      Assumes equality is reflexive for the subquery's own source row — that Novak must at least match himself. Equality is UNKNOWN, not TRUE, when the compared value is NULL, even when both sides come from the same row; only IS NULL tests for NULL.

   3. C. 3

      Combines the belief that NULL = NULL is TRUE with the belief that a row is excluded from matching the subquery it feeds, leaving King, Petrov and Rossi. Neither rule exists: the row that supplies the subquery value is an ordinary candidate row, and NULL equality is never TRUE.

   4. D. 0Correct answer

      The single-row subquery returns exactly one row whose COMMISSION is NULL, so the predicate becomes `commission = NULL` for every row. Any comparison involving NULL evaluates to UNKNOWN rather than TRUE, so no row satisfies the WHERE clause.

   Explanation

   A single-row subquery used with `=` supplies one value to the comparison, and when that value is NULL the predicate reduces to a comparison against NULL. Oracle evaluates any comparison with a NULL operand to UNKNOWN, and a WHERE clause returns only rows for which the condition is TRUE, so such a query returns no rows no matter how many NULLs the table holds. Testing for the absence of a value requires IS NULL or IS NOT NULL, which are the only conditions that treat NULL as a testable state.

5. Question 5

   Department 20 contains three employees, and no two of them earn the same salary. Every one of those three salaries is non-NULL. What is the result of executing the following statement? ```sql SELECT last_name FROM employees WHERE salary = (SELECT salary FROM employees WHERE dept_id = 20) ```

   1. A. The statement fails with ORA-01427: single-row subquery returns more than one row.Correct answer

      The subquery returns three rows, but `=` requires a single-row subquery. Oracle detects the second row at run time and raises ORA-01427, so no rows are returned at all.

   2. B. The statement executes successfully and returns the last names of the three employees in department 20.

      Assumes `=` with a multi-row subquery degrades to `IN`. Oracle does not do that: `=` is a single-row comparison operator, and the operand on its right must yield at most one row. Only `IN` or `= ANY` accept a multi-row subquery.

   3. C. The statement fails with ORA-00913: too many values.

      Confuses cardinality with degree. ORA-00913 is raised when the number of *expressions* on one side of a comparison does not match the other side (e.g. `WHERE (a, b) = (SELECT a FROM t)`). Here the subquery selects one column, so the degree matches; the problem is the number of rows.

   4. D. The statement fails with ORA-01422: exact fetch returns more than requested number of rows.

      Mistakes the PL/SQL error for the SQL one. ORA-01422 comes from a PL/SQL `SELECT ... INTO` that fetches more than one row; a multi-row subquery under a single-row comparison operator in plain SQL raises ORA-01427 instead.

   Explanation

   A subquery used as the operand of a single-row comparison operator (=, !=, >, <, >=, <=) is a single-row subquery: it may return zero or one row. Returning zero rows makes the condition UNKNOWN and yields no rows, but returning two or more rows is a run-time error, ORA-01427, that aborts the whole statement. To compare against a set of values the query must use a multiple-row operator such as IN, = ANY, or = ALL, or the subquery must be reduced to one row (for example with an aggregate).

6. Question 6

   The `employees` table stores each worker's own key in `emp_id` and the key of the person they report to in `manager_id`. Employees at the top of the hierarchy have `manager_id` set to NULL, so the `manager_id` column contains NULLs. Which query returns exactly the employees who are **not** the manager of any other employee, and returns no other rows?

   1. A. SELECT e.emp_id, e.last_name FROM employees e WHERE NOT EXISTS (SELECT 1 FROM employees m WHERE m.manager_id = e.emp_id)Correct answer

      Correctly correlated: for each outer row the subquery looks for an employee whose `manager_id` equals that outer `emp_id`, i.e. a direct report. NOT EXISTS is TRUE only when no such row is found, so exactly the employees with no subordinates are returned. NULLs in `manager_id` are harmless here — a NULL simply fails the equality and produces no row, which is precisely the intended meaning.

   2. B. SELECT e.emp_id, e.last_name FROM employees e WHERE NOT EXISTS (SELECT 1 FROM employees m WHERE m.manager_id = m.emp_id)

      The subquery mentions only its own alias `m`, so nothing correlates it to the outer row: it is evaluated once, finds no employee who is their own manager, and returns no rows. NOT EXISTS over an empty result is therefore TRUE for every outer row, so this lists the entire table — the classic 'forgot to reference the outer alias' error that turns a correlated subquery into an uncorrelated one.

   3. C. SELECT e.emp_id, e.last_name FROM employees e WHERE e.emp_id NOT IN (SELECT m.manager_id FROM employees m)

      Treats NOT IN as interchangeable with NOT EXISTS. Because `manager_id` contains at least one NULL, every `emp_id <> NULL` comparison in the expanded NOT IN chain is UNKNOWN, so the whole condition can never be TRUE and the query returns zero rows (SQL Language Reference: NOT IN yields FALSE or UNKNOWN if any value in the list is NULL).

   4. D. SELECT e.emp_id, e.last_name FROM employees e WHERE NOT EXISTS (SELECT 1 FROM employees m WHERE m.emp_id = e.manager_id)

      Reverses the direction of the correlation: it asks whether the outer row's *manager* exists, not whether the outer row *is* a manager. Only the top-level employees, whose `manager_id` is NULL and therefore matches nothing, satisfy NOT EXISTS — so this returns the employees with no manager rather than the employees with no subordinates.

   Explanation

   A correlated subquery is re-evaluated for each row of the parent query, and NOT EXISTS is TRUE for that row only when the re-evaluated subquery returns nothing. Expressing 'is not a manager' therefore requires the inner predicate to compare the inner row's `manager_id` against the outer row's `emp_id`; dropping the outer reference makes the subquery uncorrelated, and swapping the two columns asks the opposite question. NOT IN cannot substitute for NOT EXISTS here because a single NULL among the subquery's values makes every comparison UNKNOWN, silently returning an empty result.

7. Question 7

   The `employees` table holds exactly these eight rows (only the columns the statement reads are shown): ``` EMP_ID FIRST_NAME COMMISSION DEPT_ID 100 Alice (null) 10 101 Bob 0.10 20 102 Carol 0.15 20 103 Dave (null) 20 104 Eve 0.05 30 105 Frank (null) 30 106 Grace 0.20 30 107 Heidi (null) 10 ``` No other table references `employees`. How many rows does the following statement delete? ```sql DELETE FROM employees e WHERE NOT EXISTS ( SELECT 1 FROM employees peer WHERE peer.dept_id = e.dept_id AND peer.commission > e.commission) ```

   1. A. 0

      Treats `SELECT 1` as a constant that always produces a row, so NOT EXISTS is assumed never TRUE. The select list of an EXISTS subquery is irrelevant, but the subquery's WHERE clause still filters: a row is produced only if some correlated peer satisfies both predicates.

   2. B. 2

      Assumes a NULL commission makes the whole condition UNKNOWN so the row survives, leaving only the two departmental commission leaders. NULL does not propagate to the outer WHERE here: it merely stops the inner comparison from ever being TRUE, which empties the subquery and makes NOT EXISTS definitively TRUE.

   3. C. 4

      Counts only the NULL-commission rows, assuming a NULL is the only way the correlated subquery can come up empty. A row with the largest commission in its own department also has no qualifying peer, so it is deleted too.

   4. D. 6Correct answer

      NOT EXISTS is TRUE whenever the correlated subquery returns no rows. For the four NULL-commission rows (Alice, Dave, Frank, Heidi) `peer.commission > NULL` is UNKNOWN for every peer, so the subquery is empty and each row is deleted; additionally the highest commission in a department has no peer above it, deleting Carol (0.15 in dept 20) and Grace (0.20 in dept 30). Only Bob and Eve survive, so 6 rows are removed.

   Explanation

   NOT EXISTS is evaluated per outer row and is TRUE exactly when the correlated subquery returns zero rows; unlike NOT IN, it never yields UNKNOWN, so a row either qualifies or it does not. A comparison against a NULL operand evaluates to UNKNOWN, and UNKNOWN rows are not returned by the subquery — so an outer row with a NULL in the compared column makes the subquery empty and therefore satisfies NOT EXISTS rather than being skipped. The same emptiness arises legitimately for any outer row that no correlated peer can beat, which is why the group's top value is deleted alongside the NULL rows. The subquery's select list plays no part in the decision; only whether at least one row survives its WHERE clause matters.

8. Question 8

   In the `employees` table each row is one employee, `dept_id` identifies the department the employee belongs to, and `hire_date` is never NULL. Which query returns the first name of exactly those employees for whom **no one else in the same department was hired on an earlier date** (the earliest hire of each department, and every employee tied for that earliest date), and returns no other rows?

   1. A. SELECT e.first_name FROM employees e WHERE NOT EXISTS (SELECT 1 FROM employees e2 WHERE e2.dept_id = e.dept_id AND e2.hire_date < e.hire_date);Correct answer

      The subquery is correlated on `e.dept_id` and `e.hire_date` and is re-evaluated for each candidate outer row; it returns a row only when some employee of the same department has a strictly earlier hire date. The strict `<` means the outer row cannot match itself, so NOT EXISTS is TRUE exactly for the department's earliest hires, and for all rows tied on that earliest date.

   2. B. SELECT e.first_name FROM employees e WHERE NOT EXISTS (SELECT 1 FROM employees e2 WHERE e2.dept_id = e.dept_id AND e2.hire_date <= e.hire_date);

      Assumes the correlated subquery automatically excludes the outer row itself. It does not: `e2` scans the same table, so the outer row always satisfies `e2.dept_id = e.dept_id AND e2.hire_date <= e.hire_date` by matching itself. The subquery therefore returns at least one row for every candidate, NOT EXISTS is FALSE for all of them, and the query returns zero rows.

   3. C. SELECT e.first_name FROM employees e WHERE e.hire_date NOT IN (SELECT e2.hire_date FROM employees e2 WHERE e2.dept_id = e.dept_id AND e2.hire_date < e.hire_date);

      Treats NOT IN as a test that the correlated subquery is empty, the way NOT EXISTS is. NOT IN tests membership instead: the subquery yields only dates strictly earlier than `e.hire_date`, so `e.hire_date` can never be a member of it. The predicate is TRUE for every employee — including later hires — so the query returns the whole table.

   4. D. SELECT e.first_name FROM employees e WHERE EXISTS (SELECT 1 FROM employees e2 WHERE e2.dept_id = e.dept_id AND e2.hire_date > e.hire_date);

      Negates the predicate rather than the quantifier: it reads "nobody in the department was hired earlier" as "somebody in the department was hired later". That condition selects everyone who is not the department's latest hire — the complement of the required set at the other end of the range — so it returns the later hires and omits the departments' single-employee edge cases.

   Explanation

   A correlated subquery references a column of the outer query and is therefore re-evaluated once per candidate outer row; EXISTS is TRUE if that evaluation returns at least one row and NOT EXISTS is TRUE if it returns none. Expressing "no one in my department was hired before me" needs NOT EXISTS with a strict comparison, because the inner table scan includes the outer row itself and a non-strict comparison would let every row disqualify itself. NOT IN is not interchangeable with NOT EXISTS here: it tests whether a value appears among the returned values, not whether the result set is empty. Reversing the comparison to look for a later hire tests the opposite end of the ordering and answers a different question.