🎓 Velnov

🗄️ SQL · Topic 15 of 20

Subqueries & CTEs — SQL Questions with Answers

30 practice questions on Subqueries & CTEs, each with the correct answer and a clear explanation of why it is right. Read through them as revision notes, or take this topic as an interactive quiz to test yourself.

1 easy · 14 medium · 15 hard · Part of SQL: Databases from basics to window functions - 20 topics.

Key concepts in this topic

SubqueriesNestingScalar subqueryAVGCorrelated subqueryPerformanceExecutionEXISTSSemi-joinINNULLDerived tableInline viewCTEWITHReadabilityChainingRecursive CTEHierarchiesStructureSELECTSubqueryTiesPlacement

Jump to: Easy · Medium · Hard

Easy questions (1)

Q1. easy

What is a subquery?

SELECT name FROM students
WHERE marks > (SELECT AVG(marks) FROM students);
  1. A A query nested inside another query, whose result feeds the outer query
  2. B A second database
  3. C A backup query
  4. D A type of join

Answer: A — A query nested inside another query, whose result feeds the outer query

Subqueries appear in WHERE, FROM, SELECT or HAVING. They let you use the result of one query as an input to another - for example, comparing against an average.

SubqueriesNesting

Medium questions (14)

Q2. medium

What is a scalar subquery?

SELECT name, (SELECT AVG(marks) FROM students) AS class_avg FROM students;
  1. A A subquery that returns exactly ONE value (one row, one column)
  2. B A subquery returning many rows
  3. C A subquery with no result
  4. D A subquery in FROM

Answer: A — A subquery that returns exactly ONE value (one row, one column)

Scalar subqueries can be used anywhere a single value is expected, such as = comparisons or the SELECT list. Returning more than one row causes an error.

Scalar subquery

Q3. medium

What does this find? SELECT name FROM products WHERE price > (SELECT AVG(price) FROM products);

  1. A Products priced above the overall average
  2. B The most expensive product
  3. C All products
  4. D An error

Answer: A — Products priced above the overall average

The subquery computes one average value, and the outer query compares each product to it. A classic 'above average' pattern that a single query cannot express with an aggregate in WHERE.

Scalar subqueryAVG

Q6. medium

What does EXISTS with a correlated subquery test?

SELECT c.name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
  1. A Whether at least one matching row exists for the current outer row - it stops at the first match
  2. B The count of rows
  3. C Whether a table exists
  4. D Nothing

Answer: A — Whether at least one matching row exists for the current outer row - it stops at the first match

EXISTS is a semi-join: it returns the outer row if the subquery finds anything. It is efficient because it short-circuits and does not care what the subquery selects.

EXISTSSemi-join

Q8. medium

What does a subquery in the FROM clause create?

SELECT dept, avg_sal FROM (
  SELECT dept, AVG(salary) AS avg_sal FROM employees GROUP BY dept
) t WHERE avg_sal > 50000;
  1. A A derived table (inline view) that the outer query treats as a regular table
  2. B A permanent table
  3. C An index
  4. D A join

Answer: A — A derived table (inline view) that the outer query treats as a regular table

A derived table must be given an alias. It is handy for pre-aggregating or filtering before the outer query works on the result.

Derived tableInline view

Q9. medium

What is a CTE (Common Table Expression)?

WITH dept_avg AS (
  SELECT dept, AVG(salary) AS avg_sal FROM employees GROUP BY dept
)
SELECT * FROM dept_avg WHERE avg_sal > 50000;
  1. A A named temporary result defined with WITH, usable in the query that follows
  2. B A permanent table
  3. C A type of index
  4. D A backup

Answer: A — A named temporary result defined with WITH, usable in the query that follows

CTEs improve readability by naming and separating steps. They exist only for the duration of the query, acting like a labelled subquery you can reference by name.

CTEWITH

Q10. medium

What is the main advantage of a CTE over a nested subquery?

  1. A Readability - complex logic is broken into named, top-to-bottom steps
  2. B It is always faster
  3. C It uses less storage
  4. D It prevents errors

Answer: A — Readability - complex logic is broken into named, top-to-bottom steps

Deeply nested subqueries are hard to follow. CTEs read like a sequence of named steps, and one CTE can be referenced multiple times in the same query.

CTEReadability

Q14. medium

What does this scalar subquery in SELECT do? SELECT name, (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count FROM customers c;

  1. A Shows each customer with their order count via a correlated subquery
  2. B Counts all orders once
  3. C Returns an error
  4. D Lists all orders

Answer: A — Shows each customer with their order count via a correlated subquery

The correlated subquery runs per customer, counting their orders. A LEFT JOIN with GROUP BY often does this more efficiently, but the subquery form is readable.

Correlated subquerySELECT

Q16. medium

Where can subqueries appear in a statement?

  1. A In SELECT, FROM, WHERE, HAVING - and even in INSERT/UPDATE/DELETE
  2. B Only in WHERE
  3. C Only in FROM
  4. D Only in SELECT

Answer: A — In SELECT, FROM, WHERE, HAVING - and even in INSERT/UPDATE/DELETE

Subqueries are versatile. A scalar one in SELECT computes a value per row; one in FROM is a derived table; one in WHERE filters; and they drive many INSERT ... SELECT operations.

SubqueriesPlacement

Q18. medium

Are CTEs materialised (computed once) or inlined?

  1. A It depends on the database - some materialise them, others inline them into the main query
  2. B Always materialised
  3. C Always inlined
  4. D They are never computed

Answer: A — It depends on the database - some materialise them, others inline them into the main query

PostgreSQL historically materialised CTEs (an optimisation fence) but now can inline them; MySQL and SQL Server generally inline. Behaviour affects performance, so test on your platform.

CTEOptimisation

Q20. medium

What must a derived table (FROM subquery) always have?

  1. A An ALIAS - a name by which the outer query refers to it
  2. B An index
  3. C A primary key
  4. D A WHERE clause

Answer: A — An ALIAS - a name by which the outer query refers to it

Most databases require an alias for a subquery in FROM, e.g. ) AS t. Without it you get a syntax error.

Derived tableAlias

Q23. medium

Can a CTE be referenced more than once in the main query?

  1. A Yes - define it once with WITH and use its name multiple times
  2. B No, only once
  3. C Only in subqueries
  4. D Only with UNION

Answer: A — Yes - define it once with WITH and use its name multiple times

This is a readability win over subqueries: a self join on a computed set can reference the same CTE twice by name instead of repeating the subquery.

CTEReuse

Q25. medium

How do you filter a GROUP BY result using a subquery threshold?

  1. A Compute the threshold in a subquery and compare in HAVING (or wrap in a CTE and filter outside)
  2. B Use WHERE with the aggregate
  3. C It cannot be done
  4. D Use ORDER BY

Answer: A — Compute the threshold in a subquery and compare in HAVING (or wrap in a CTE and filter outside)

For example, HAVING SUM(total) > (SELECT AVG(order_total) FROM ...). A CTE that pre-aggregates then a plain WHERE is often clearer.

HAVINGSubquery

Q27. medium

What does WHERE col = (SELECT MAX(col) FROM t) return?

  1. A The row(s) holding the maximum value - including ties
  2. B Only one row always
  3. C All rows
  4. D An error

Answer: A — The row(s) holding the maximum value - including ties

Unlike ORDER BY ... LIMIT 1, comparing to the MAX subquery returns every tied maximum. Useful when ties matter.

SubqueryMAXTies

Q29. medium

Does a subquery in SELECT run for every output row?

  1. A A correlated one does; a non-correlated scalar subquery is typically evaluated once and reused
  2. B Always once
  3. C Always per row
  4. D It never runs

Answer: A — A correlated one does; a non-correlated scalar subquery is typically evaluated once and reused

Correlation is the key: if the subquery references the outer row it recomputes per row; if it is constant, the optimiser can compute it a single time.

Correlated subqueryPerformance

Hard questions (15)

Q4. hard

What is a correlated subquery?

SELECT e.name FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE dept = e.dept);
  1. A A subquery that REFERENCES a column from the outer query, so it re-runs for each outer row
  2. B A subquery that runs once
  3. C A subquery with no WHERE
  4. D Two unrelated queries

Answer: A — A subquery that REFERENCES a column from the outer query, so it re-runs for each outer row

Correlated subqueries depend on the current outer row, executing repeatedly. Powerful but potentially slow - often rewritable as a join or window function.

Correlated subqueryPerformance

Q5. hard

How does a correlated subquery differ from a regular (non-correlated) one in execution?

  1. A A non-correlated subquery runs ONCE; a correlated one runs for EACH outer row
  2. B They run identically
  3. C Correlated runs once
  4. D Non-correlated never runs

Answer: A — A non-correlated subquery runs ONCE; a correlated one runs for EACH outer row

Because it references the outer row, the correlated subquery is conceptually re-evaluated per row. Optimisers can sometimes transform it into a join, but be mindful of the performance model.

Correlated subqueryExecution

Q7. hard

Why is EXISTS often preferred over IN for large subqueries?

  1. A EXISTS can stop at the first match and handles NULLs safely; IN may materialise the whole list and mishandle NULLs
  2. B IN is always faster
  3. C They are identical
  4. D EXISTS is invalid

Answer: A — EXISTS can stop at the first match and handles NULLs safely; IN may materialise the whole list and mishandle NULLs

IN with a subquery containing NULL can behave unexpectedly (especially NOT IN). EXISTS/NOT EXISTS avoid the NULL trap and often perform better on big data.

EXISTSINNULL

Q11. hard

Can you define MULTIPLE CTEs in one query?

WITH sales AS (...),
     ranked AS (SELECT *, RANK() OVER (...) FROM sales)
SELECT * FROM ranked WHERE rnk <= 3;
  1. A Yes - separate them with commas after a single WITH, and later CTEs can reference earlier ones
  2. B No, only one
  3. C Only two
  4. D Only with UNION

Answer: A — Yes - separate them with commas after a single WITH, and later CTEs can reference earlier ones

Chaining CTEs builds a readable pipeline: WITH a AS (...), b AS (SELECT ... FROM a) SELECT ... FROM b. Each step names an intermediate result.

CTEChaining

Q12. hard

What is a RECURSIVE CTE used for?

WITH RECURSIVE nums AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM nums WHERE n < 10
)
SELECT * FROM nums;
  1. A Traversing hierarchies or generating sequences - it references ITSELF until a stopping condition
  2. B Nothing special
  3. C Faster joins
  4. D Sorting data

Answer: A — Traversing hierarchies or generating sequences - it references ITSELF until a stopping condition

WITH RECURSIVE has an anchor (starting rows) and a recursive part that repeatedly joins back to the CTE - ideal for org charts, category trees and number series.

Recursive CTEHierarchies

Q13. hard

What are the two parts of a recursive CTE?

  1. A The anchor member (base rows) and the recursive member (which references the CTE), joined by UNION ALL
  2. B SELECT and FROM
  3. C WHERE and HAVING
  4. D Two subqueries

Answer: A — The anchor member (base rows) and the recursive member (which references the CTE), joined by UNION ALL

The anchor runs once to seed the result; the recursive member runs repeatedly, each time using the previous iteration's rows, until it produces no new rows.

Recursive CTEStructure

Q15. hard

How do you find the customer(s) with the maximum total spend?

WITH t AS (SELECT customer_id, SUM(total) s FROM orders GROUP BY 1)
SELECT * FROM t WHERE s = (SELECT MAX(s) FROM t);
  1. A Use a subquery for the max: WHERE total = (SELECT MAX(total) FROM ...) after aggregating
  2. B ORDER BY total LIMIT 1 always suffices
  3. C MAX(customer)
  4. D It is impossible

Answer: A — Use a subquery for the max: WHERE total = (SELECT MAX(total) FROM ...) after aggregating

Comparing to a MAX subquery returns ALL tied top spenders, whereas LIMIT 1 arbitrarily picks one. A CTE that computes totals first makes this clean.

SubqueryTies

Q17. hard

What does WHERE col IN (SELECT ...) do, and its risk?

  1. A Keeps rows whose col matches any value the subquery returns; risky as NOT IN with a NULL returns no rows
  2. B It always works safely
  3. C It joins tables
  4. D It sorts rows

Answer: A — Keeps rows whose col matches any value the subquery returns; risky as NOT IN with a NULL returns no rows

IN is fine, but its negation NOT IN silently returns nothing if the subquery yields a NULL. Prefer NOT EXISTS for anti-membership tests.

INNOT INNULL

Q19. hard

How can a correlated subquery often be rewritten for speed?

  1. A As a JOIN or a window function that computes the value once per group instead of per row
  2. B It cannot be rewritten
  3. C As a CROSS JOIN
  4. D As a UNION

Answer: A — As a JOIN or a window function that computes the value once per group instead of per row

For example, 'salary above department average' via a correlated subquery can become AVG(salary) OVER (PARTITION BY dept) - one pass instead of many.

Window functionsPerformance

Q21. hard

What does this recursive CTE compute? WITH RECURSIVE t AS (SELECT 1 n UNION ALL SELECT n*2 FROM t WHERE n < 100) SELECT * FROM t;

  1. A Powers of 2 up to 128 (1, 2, 4, ... doubling until n >= 100)
  2. B Numbers 1 to 100
  3. C An infinite loop
  4. D An error

Answer: A — Powers of 2 up to 128 (1, 2, 4, ... doubling until n >= 100)

The anchor seeds 1; each step doubles the previous value while it is under 100, yielding 1, 2, 4, 8, 16, 32, 64, 128 (128 is produced from 64, which was < 100).

Recursive CTESequences

Q22. hard

What prevents a recursive CTE from looping forever?

  1. A A termination condition in the recursive member's WHERE - and databases also enforce a max recursion depth
  2. B Nothing - they always stop
  3. C The LIMIT clause only
  4. D They cannot loop

Answer: A — A termination condition in the recursive member's WHERE - and databases also enforce a max recursion depth

You must ensure the recursive part eventually produces no rows. Cyclic data (A manages B manages A) can loop, so many databases cap recursion depth as a safety net.

Recursive CTETermination

Q24. hard

What does a subquery returning multiple rows require in a comparison?

  1. A An operator like IN, ANY or ALL - a bare = expects a single value and errors
  2. B Nothing special
  3. C Only =
  4. D A join

Answer: A — An operator like IN, ANY or ALL - a bare = expects a single value and errors

= (SELECT ...) fails if the subquery returns many rows. Use IN for membership, or > ANY / > ALL for range comparisons against the set.

INANYALL

Q26. hard

When would you choose a CTE over a temporary table?

  1. A For a single query's readability without persistence; temp tables suit reuse across multiple statements or very large intermediate data
  2. B CTEs are always better
  3. C Temp tables cannot be indexed
  4. D They are identical

Answer: A — For a single query's readability without persistence; temp tables suit reuse across multiple statements or very large intermediate data

A CTE lives only within one statement. If you need the intermediate result across several queries, or want to index it, a temporary table is the better tool.

CTETemp tables

Q28. hard

What is a common readability pattern for a multi-step analysis?

  1. A Chained CTEs: filter in one, aggregate in the next, rank in the next, then SELECT the final result
  2. B One giant nested subquery
  3. C Many separate queries copied by hand
  4. D CROSS JOINs

Answer: A — Chained CTEs: filter in one, aggregate in the next, rank in the next, then SELECT the final result

Building a pipeline of named CTEs mirrors how you think about the problem and makes each step testable. It is the modern idiom for complex analytical SQL.

CTEPipelines

Q30. hard

How do you find the second-highest salary using a subquery?

  1. A SELECT MAX(salary) FROM emp WHERE salary < (SELECT MAX(salary) FROM emp)
  2. B SELECT MAX(salary) FROM emp
  3. C ORDER BY salary LIMIT 2
  4. D It is impossible

Answer: A — SELECT MAX(salary) FROM emp WHERE salary < (SELECT MAX(salary) FROM emp)

The subquery finds the top salary; the outer MAX finds the highest below it - the second highest. A famous interview question; window functions (DENSE_RANK) generalise it to Nth.

SubqueryNth highestInterview

Practice “Subqueries & CTEs” as a quiz