QuizzesCorrelated Subquery — Concept Check
intermediate
sql

Correlated Subquery — Concept Check

Questions on Correlated Subquery. A correlated subquery references columns from the outer query and re-executes once per outer row, enabling row-by-row comparisons at the cost of potential N+1 performance problems.

7 minutes 7 questions Pass at 70%
Time Limit
7 minutes - timer starts when you begin
Change Answers Freely
Revisit and change any answer before submitting
Full Review After Submit
Correct answers and explanations shown for every question
Auto-submit on timeout
Quiz submits automatically when time runs out

Questions in this quiz

7 questions on Correlated Subquery — Concept Check. Work through them here, then start the quiz to answer them and see your score.

  1. 1.A query is running slowly because it contains a subquery that references a column from the outer query. What is likely causing the performance issue?

    • AThe subquery uses EXISTS, which is inefficient for large datasets.
    • BThe subquery is independent and runs once, causing a large temporary table.
    • CThe subquery is correlated and runs for each outer row, causing N+1 performance problems.
    • DThe subquery uses a window function, which is inherently slow.
  2. 2.You need to check if users have placed at least one order. Which query is the most efficient?

    • AUse COUNT(*) in a subquery to count orders.
    • BUse EXISTS in a subquery to short-circuit on the first match.
    • CUse IN with a subquery to list user IDs.
    • DUse a JOIN to combine users and orders tables.
  3. 3.A developer rewrites a correlated subquery using a JOIN. What is the expected outcome?

    • AIncreased performance overhead due to additional joins.
    • BDecreased performance due to complex query planning.
    • CImproved performance due to reduced subquery executions.
    • DNo change in performance as both methods are equivalent.
  4. 4.Why might a database optimizer fail to unnest a correlated subquery?

    • AThe subquery is too complex for the optimizer to transform.
    • BThe subquery is independent and does not need unnesting.
    • CThe subquery uses a window function, which cannot be unnested.
    • DThe subquery uses EXISTS, which is incompatible with unnesting.
  5. 5.A query uses a correlated subquery to find employees earning more than their department's average salary. What is a potential issue?

    • AThe query will return incorrect results if salaries are NULL.
    • BThe query will not work if departments have no employees.
    • CThe query will fail if there are no departments.
    • DThe query might be slow due to N+1 execution pattern.
  6. 6.What is a key advantage of using EXISTS in a subquery?

    • AIt counts all matches before returning a result.
    • BIt short-circuits on the first match, improving efficiency.
    • CIt guarantees a full scan of the outer table.
    • DIt materializes the subquery into a list for faster access.
  7. 7.In a query with a correlated subquery, what should you check to understand its performance impact?

    • AThe use of window functions in the subquery.
    • BThe number of columns in the subquery.
    • CThe presence of aggregate functions in the subquery.
    • DThe EXPLAIN plan to see if the subquery is unnested.