Using Subqueries to Solve Queries practice questions

Worked examples for Using Subqueries to Solve Queries

1. Question 1

   Department average salaries are: dept 10 = 7850, dept 20 = 6100, dept 30 ≈ 5733; the overall average across all 8 employees is 6400. How many rows does the following query return?

   SELECT dept_id
   FROM   employees
   GROUP BY dept_id
   HAVING AVG(salary) > (SELECT AVG(salary) FROM employees)

   1. A. 3

      3 is the number of departments (groups) before the HAVING filter. HAVING removes the two departments whose average is below the overall average, leaving 1.

   2. B. 2

      Only department 10 (average 7850) exceeds the overall average of 6400. Departments 20 (6100) and 30 (≈5733) are both below it, so 2 is an overcount — only 1 group qualifies.

   3. C. The statement raises an error because a subquery cannot appear in a HAVING clause.

      A subquery is permitted in the HAVING clause; comparing a group's aggregate against a scalar subquery is valid. No error occurs, and exactly one department's average exceeds the overall average.

   4. D. 1Correct answer

      The scalar subquery returns the overall average, 6400. HAVING then keeps only groups whose department average exceeds 6400: dept 10 (7850) qualifies, while dept 20 (6100) and dept 30 (≈5733) do not. Exactly 1 group row is returned.

   Explanation

   The HAVING clause filters groups after aggregation, and it may compare a group's aggregate against a scalar subquery. Here each department's AVG(salary) is compared to the overall average (the subquery result, 6400). Only department 10 (7850) exceeds it; departments 20 (6100) and 30 (≈5733) do not, so a single group row is returned. Using a subquery in HAVING is fully supported — it is the idiomatic way to compare group aggregates against an overall or reference value.

2. Question 2

   In the employees table, the manager_id column records who each employee reports to. The employees reporting to Alice (emp_id 100) are Bob, Carol, and Heidi. What value does the following query return?

   SELECT (SELECT COUNT(*)
           FROM   employees s
           WHERE  s.manager_id = e.emp_id) AS reports
   FROM   employees e
   WHERE  e.emp_id = 100

   1. A. 3Correct answer

      The scalar subquery in the SELECT list is correlated to the outer row via s.manager_id = e.emp_id, so for Alice (emp_id 100) it counts the employees whose manager_id is 100 — Bob, Carol, and Heidi — returning 3. A correlated scalar subquery may appear in the SELECT list and is evaluated once per outer row.

   2. B. 1

      1 is the number of outer rows the query returns (Alice's single row), not the value of her correlated report count. The scalar subquery counts Alice's direct reports, which is 3.

   3. C. 8

      8 is the total number of employees. The subquery is correlated and counts only rows whose manager_id equals Alice's emp_id (100), not the whole table.

   4. D. The statement raises an error because a subquery cannot appear in the SELECT list.

      A scalar subquery is permitted in the SELECT list, and it may be correlated to the outer query. As long as it returns a single value per outer row (a COUNT always does), no error occurs.

   Explanation

   A correlated scalar subquery can appear in the SELECT list, where it is evaluated once for each outer row using that row's column values. Here s.manager_id = e.emp_id ties the inner COUNT to the current employee, so for Alice (emp_id 100) it counts her direct reports — Bob, Carol, Heidi — yielding 3. A scalar subquery is legal in the projection as long as it returns exactly one value per row, which COUNT guarantees.

3. Question 3

   Which of the following queries correctly returns the FIRST_NAME of every employee who is not recorded as the MANAGER_ID of any other employee in the EMPLOYEES table?

   1. A. SELECT first_name FROM employees e WHERE NOT EXISTS (SELECT 1 FROM employees sub WHERE sub.emp_id = e.manager_id)

      The correlation condition is reversed: sub.emp_id = e.manager_id tests whether the current employee's own manager exists in the table, not whether anyone reports to the current employee. For Alice and Grace, MANAGER_ID is NULL; the subquery WHERE clause compares sub.emp_id = NULL, which is always UNKNOWN, so no row is found and NOT EXISTS is TRUE — returning employees who themselves have no manager (Alice and Grace). The remaining six employees all have a valid manager who exists in the table, so NOT EXISTS is FALSE for them. This returns two rows rather than the expected five.

   2. B. SELECT first_name FROM employees e WHERE NOT EXISTS (SELECT 1 FROM employees sub WHERE sub.manager_id = e.emp_id)Correct answer

      NOT EXISTS returns TRUE for a given outer row only when the correlated subquery finds zero matching rows — here, no row in EMPLOYEES whose MANAGER_ID equals the outer employee's EMP_ID. Alice (emp_id 100), Bob (101), and Grace (106) each appear as a MANAGER_ID elsewhere, so NOT EXISTS is FALSE for them and they are excluded. The remaining five employees — Carol, Dave, Eve, Frank, and Heidi — never appear as a MANAGER_ID, so NOT EXISTS is TRUE for each and they are returned. NULL values in MANAGER_ID on other rows do not affect this result because the subquery tests equality against the outer EMP_ID, which is a non-NULL primary key.

   3. C. SELECT first_name FROM employees e WHERE e.emp_id NOT IN (SELECT manager_id FROM employees)

      NOT IN silently fails when the subquery result set contains any NULL. Two rows in EMPLOYEES have a NULL MANAGER_ID (Alice and Grace), so the subquery includes NULLs in its result. For every candidate EMP_ID, the comparison EMP_ID = NULL evaluates to UNKNOWN, which propagates through the NOT IN condition, making the entire predicate UNKNOWN for every outer row. Oracle treats UNKNOWN as non-TRUE and excludes every row, so this query returns zero rows — not the five non-managers.

   4. D. SELECT first_name FROM employees e WHERE EXISTS (SELECT 1 FROM employees sub WHERE sub.manager_id = e.emp_id)

      Omitting NOT before EXISTS inverts the semantics: the predicate is TRUE when at least one subordinate row is found, so the query returns employees who ARE managers — Alice, Bob, and Grace, the three employees whose EMP_ID appears in another row's MANAGER_ID. This is the logical complement of the desired result, not the desired result itself.

   Explanation

   NOT EXISTS is the correct tool for this anti-join pattern: for each outer row the correlated subquery re-executes and the outer row passes the filter only when zero matches are found inside. NOT IN appears semantically equivalent but is undermined by NULL: a single NULL in the subquery result causes every NOT IN comparison to evaluate to UNKNOWN, and Oracle excludes every outer row, returning zero results. Reversing the correlation columns in the subquery changes the logical question being asked — finding employees who have no manager rather than employees who manage no one — and produces a completely different, incorrect result set.

4. Question 4

   In the employees table, manager_id is NULL for Alice and Grace; the non-NULL manager_id values present are 100, 101, and 106. Which query correctly returns the 5 employees who are NOT anyone's manager (i.e. whose emp_id never appears as a manager_id)?

   1. A. SELECT first_name FROM employees WHERE emp_id NOT IN (SELECT manager_id FROM employees)

      The subquery result includes NULL (Alice and Grace have NULL manager_id). With a NULL in the list, NOT IN evaluates to UNKNOWN for every outer row — emp_id <> NULL is never TRUE — so the WHERE clause is never satisfied and the query returns 0 rows, not 5.

   2. B. SELECT first_name FROM employees WHERE emp_id NOT IN (SELECT manager_id FROM employees WHERE manager_id IS NOT NULL)Correct answer

      Filtering NULLs out of the subquery removes the NOT IN trap: the list becomes {100, 101, 106}. NOT IN then correctly excludes the three managers and returns the other five employees (Carol, Dave, Eve, Frank, Heidi). Guarding the subquery with IS NOT NULL is the standard fix for NOT IN with nullable columns.

   3. C. SELECT first_name FROM employees WHERE emp_id NOT IN (SELECT emp_id FROM employees WHERE manager_id IS NOT NULL)

      This excludes employees who HAVE a manager. The subquery returns the emp_ids of the six employees with a non-NULL manager_id, so NOT IN returns the two employees without a manager (Alice, Grace) — 2 rows, not the 5 non-managers.

   4. D. SELECT first_name FROM employees WHERE emp_id != ALL (SELECT manager_id FROM employees)

      '!= ALL' is logically identical to NOT IN, so it inherits the same NULL flaw: because the subquery list contains NULL, emp_id != NULL is UNKNOWN for every row, the predicate is never TRUE, and the query returns 0 rows.

   Explanation

   NOT IN (and its equivalent != ALL) silently returns nothing when the subquery result contains a NULL, because the expansion includes emp_id <> NULL, which is UNKNOWN — never TRUE — for every row. The reliable fix is to exclude NULLs inside the subquery with WHERE manager_id IS NOT NULL, leaving the clean list {100, 101, 106}; NOT IN then correctly returns the five non-managers. Projecting emp_id instead of manager_id in the subquery answers a different question entirely (employees without a manager).

5. Question 5

   Which query returns the FIRST_NAME of the most recently hired employee in each department — that is, the employee whose HIRE_DATE is the latest among all employees sharing their own DEPT_ID?

   1. A. SELECT e.first_name FROM employees e WHERE e.hire_date = (SELECT MAX(s.hire_date) FROM employees s);

      Dropping the correlation predicate makes the subquery a single uncorrelated MAX over the whole table, so only the one globally latest-hired employee matches — not the latest within each department.

   2. B. SELECT e.first_name FROM employees e WHERE e.hire_date = (SELECT MIN(s.hire_date) FROM employees s WHERE s.dept_id = e.dept_id);

      The correlation is correct but MIN selects the earliest-hired employee in each department, the opposite extreme of the latest hire the question asks for.

   3. C. SELECT e.first_name FROM employees e WHERE e.hire_date = (SELECT MAX(s.hire_date) FROM employees s WHERE s.dept_id = e.dept_id);Correct answer

      Correlating the inner query on s.dept_id = e.dept_id makes Oracle recompute MAX(hire_date) within each candidate row's own department, so a row qualifies exactly when its hire_date equals the latest hire_date in that department. A correlated subquery is evaluated once per outer row, giving one winner per department.

   4. D. SELECT e.first_name FROM employees e WHERE e.hire_date = MAX(e.hire_date);

      An aggregate (group) function such as MAX is not permitted in a WHERE clause — the WHERE filter is applied before aggregation — so this raises ORA-00934. MAX must live in a subquery or a HAVING clause, not be compared directly in WHERE.

   Explanation

   Selecting the extreme row per group requires correlating the aggregate subquery on the grouping column so the aggregate is recomputed for each outer row's group; a correlated subquery runs once per candidate row. Removing the correlation predicate collapses the aggregate to one whole-table value, swapping MAX for MIN picks the opposite extreme, and placing an aggregate directly in a WHERE clause is a syntax error because aggregates are evaluated only after rows are filtered.

6. Question 6

   Which of the following queries correctly returns the FIRST_NAME of every employee who is the sole direct report of their manager — that is, no other employee in the EMPLOYEES table shares the same MANAGER_ID value?

   1. A. SELECT e.first_name FROM employees e WHERE e.manager_id IS NOT NULL AND NOT EXISTS ( SELECT 1 FROM employees sub WHERE sub.dept_id = e.manager_id AND sub.emp_id <> e.emp_id );

      Uses sub.dept_id = e.manager_id as the correlation predicate, confusing department id with manager id. Dept_id values in the table are 10, 20, and 30; manager_id values are 100, 101, and 106. These domains never overlap, so the subquery always returns no rows, NOT EXISTS is always TRUE, and all six employees who have a non-NULL manager_id (Bob, Carol, Dave, Eve, Frank, Heidi) are returned.

   2. B. SELECT e.first_name FROM employees e WHERE e.manager_id IS NOT NULL AND NOT EXISTS ( SELECT 1 FROM employees sub WHERE sub.manager_id = e.manager_id AND sub.emp_id <> e.emp_id );Correct answer

      Filters to employees who have a recorded manager (IS NOT NULL guard), then checks that no sibling row shares the same manager_id. For Dave (manager_id 101) no other employee has manager_id 101, so the subquery returns no rows and NOT EXISTS is TRUE. All other employees with a non-NULL manager_id have at least one sibling sharing their manager (manager 100 has Bob, Carol, Heidi; manager 106 has Eve and Frank), so NOT EXISTS is FALSE for them. Only Dave is returned.

   3. C. SELECT e.first_name FROM employees e WHERE e.manager_id IS NOT NULL AND e.manager_id NOT IN ( SELECT sub.manager_id FROM employees sub WHERE sub.emp_id <> e.emp_id );

      Uses NOT IN with a correlated subquery that projects manager_id from all other employees. Because Alice and Grace have NULL in their manager_id column, the subquery's result set always contains NULL regardless of which employee is being evaluated. When any list member is NULL, NOT IN evaluates to UNKNOWN — never TRUE — for every value tested, so no employee is ever included in the result. This is the NULL-in-NOT-IN trap.

   4. D. SELECT e.first_name FROM employees e WHERE NOT EXISTS ( SELECT 1 FROM employees sub WHERE sub.manager_id = e.manager_id AND sub.emp_id <> e.emp_id );

      Omits the IS NOT NULL guard on e.manager_id. For Alice and Grace, whose manager_id is NULL, the subquery condition sub.manager_id = e.manager_id becomes NULL = NULL, which Oracle evaluates as UNKNOWN; the WHERE clause is never satisfied, the subquery returns no rows, NOT EXISTS is TRUE, and both employees are incorrectly included alongside Dave. The result contains three rows instead of one.

   Explanation

   NOT EXISTS is the reliable idiom for 'no peer shares this value': the correlation predicate uses ordinary equality, so any NULL manager_id values in sibling rows simply make the WHERE clause UNKNOWN and those rows are excluded from the subquery — the outer IS NOT NULL guard then prevents employees without a manager from being evaluated at all. NOT IN fails silently when the subquery's projection contains even one NULL: the expression expands to a conjunction that includes a comparison with NULL, which is always UNKNOWN under three-valued logic, so the entire NOT IN expression is UNKNOWN for every row and the query returns nothing. The self-exclusion predicate (emp_id <> emp_id of the outer row) is also essential; without it the subquery always finds the outer employee among the inner rows, NOT EXISTS is always FALSE, and no employee qualifies regardless of how many direct reports their manager has.

7. Question 7

   The employees who appear as a manager_id have these direct-report counts: Alice (emp_id 100) has 3 reports, Bob (101) has 1, Grace (106) has 2; every other employee has 0. How many rows does the following query return?

   SELECT first_name
   FROM   employees e
   WHERE  (SELECT COUNT(*)
           FROM   employees s
           WHERE  s.manager_id = e.emp_id) > 1

   1. A. 3

      3 counts everyone who manages anyone at all (Alice, Bob, Grace). The predicate requires MORE THAN one report, which excludes Bob (exactly 1 report), leaving 2.

   2. B. 1

      1 would count only the single employee with the most reports (Alice). The condition > 1 is satisfied by any employee with 2 or more reports, which includes Grace (2) as well as Alice (3).

   3. C. 6

      6 counts the employees who are NOT managers of more than one person (the complement). The query keeps only those whose report count exceeds 1, which is 2 employees, not 6.

   4. D. 2Correct answer

      The correlated scalar subquery counts each employee's direct reports, and the outer WHERE keeps those with more than one. Only Alice (3 reports) and Grace (2 reports) exceed 1; Bob has exactly 1 (not > 1) and everyone else has 0. So 2 rows are returned.

   Explanation

   A correlated scalar subquery can be used directly in a WHERE comparison. Here it counts each employee's direct reports (rows whose manager_id equals the outer emp_id), and the outer predicate keeps employees with more than one report. Alice (3) and Grace (2) satisfy > 1; Bob has exactly 1 and is excluded, as is everyone with 0. The result is 2 rows.

8. Question 8

   The EMPLOYEES table contains the following rows (only the relevant columns are shown):

   EMP_ID	FIRST_NAME	DEPT_ID	COMMISSION
   100	Alice	10	NULL
   101	Bob	20	0.10
   102	Carol	20	0.15
   103	Dave	20	NULL
   104	Eve	30	0.05
   105	Frank	30	NULL
   106	Grace	30	0.20
   107	Heidi	10	NULL

   What value does the following query return?

   SELECT COUNT(*)
   FROM employees e
   WHERE e.commission < (
     SELECT MAX(commission)
     FROM employees sub
     WHERE sub.dept_id = e.dept_id
   )

   1. A. 4

      This follows from treating NULL commission as zero: if NULL is read as 0, then Dave (0 < 0.15 = TRUE) and Frank (0 < 0.20 = TRUE) would appear to qualify alongside Bob and Eve, yielding 4. Oracle does not coerce NULL to any numeric value; NULL in a comparison with a non-NULL number produces UNKNOWN, not TRUE, and UNKNOWN in a WHERE clause is rejected just as FALSE is.

   2. B. 6

      This results from collapsing Oracle's three-valued logic to two values — treating UNKNOWN as equivalent to TRUE rather than as a non-qualifying result. Six rows produce a condition that is either TRUE or UNKNOWN (Bob, Dave, Eve, Frank, Alice, Heidi), but Oracle's WHERE clause admits only TRUE. Rows where the condition is UNKNOWN — because e.commission is NULL or because the correlated MAX returns NULL — are silently filtered out, exactly as FALSE rows are.

   3. C. 0

      This results from assuming that when the correlated subquery returns NULL for any outer row (as it does for department 10, whose only commission values are NULL), the WHERE clause becomes UNKNOWN for every row in the outer query. That is a scope error: the NULL result from the inner query affects only the individual outer rows whose correlated dept_id matches department 10. Outer rows in departments 20 and 30 receive non-NULL MAX values (0.15 and 0.20, respectively) and can produce TRUE comparisons independently.

   4. D. 2Correct answer

      Oracle's MAX aggregate ignores NULLs, so MAX(commission) is 0.15 for dept 20, 0.20 for dept 30, and NULL for dept 10 (all values are NULL, leaving nothing to aggregate). For dept 10, Alice and Heidi each compare their NULL commission to a NULL MAX: NULL < NULL = UNKNOWN — both are excluded. For dept 20, Bob (0.10 < 0.15 = TRUE) qualifies; Carol (0.15 < 0.15 = FALSE) and Dave (NULL < 0.15 = UNKNOWN) do not. For dept 30, Eve (0.05 < 0.20 = TRUE) qualifies; Frank (NULL < 0.20 = UNKNOWN) and Grace (0.20 < 0.20 = FALSE) do not. COUNT(*) = 2.

   Explanation

   Oracle's MAX aggregate ignores NULL values entirely; a department group whose commission column contains only NULLs returns NULL from MAX, leaving nothing for the outer comparison. Any standard comparison operator applied to NULL — including the less-than predicate here — evaluates to UNKNOWN in Oracle's three-valued logic regardless of whether the NULL appears on the left side (a NULL column value) or the right side (a NULL result from the correlated subquery). Oracle's WHERE clause retains a row only when the condition evaluates to TRUE; UNKNOWN is treated identically to FALSE and the row is discarded. Evaluating each department's correlated MAX independently and applying these rules yields exactly two rows that satisfy the condition with a TRUE result.