Nested subqueries in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a nested subquery in SQL?

A nested subquery is a query written inside another query. It helps to use the result of one query inside another query.

beginner

Can a nested subquery return multiple rows?

Yes, a nested subquery can return multiple rows if used with operators like IN or EXISTS. But if used with =, it must return only one row.

intermediate

How does a nested subquery help in filtering data?

A nested subquery can find a set of values or a single value that the outer query uses to filter its results, like finding employees who work in departments with more than 10 people.

intermediate

What is the difference between a correlated and a non-correlated nested subquery?

A correlated subquery depends on the outer query for its values and runs once per row. A non-correlated subquery runs once and returns a result used by the outer query.

beginner

Write a simple example of a nested subquery to find employees with salary above the average salary.

SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

What does a nested subquery do in SQL?

A Creates a new table

B Runs a query inside another query

C Deletes data from a table

D Changes the database schema

A nested subquery runs a query inside another query to use its result.

Which operator allows a nested subquery to return multiple rows?

A IN

B =

C <>

D BETWEEN

The IN operator allows a nested subquery to return multiple rows.

What is a correlated subquery?

A A subquery that deletes rows

B A subquery that runs once

C A subquery that creates a new table

D A subquery that depends on the outer query

A correlated subquery depends on the outer query and runs once per row.

Which of these is a valid use of a nested subquery?

A UPDATE employees SET salary = salary + 1000;

B DELETE FROM employees WHERE salary = 50000;

C SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

D CREATE TABLE new_employees;

Option C uses a nested subquery to compare salary with average salary.

If a nested subquery returns more than one row but is used with '=', what happens?

A Error occurs

B Only first row is used

C Query runs successfully

D Rows are concatenated

Using '=' with multiple rows causes an error because '=' expects a single value.

Explain what a nested subquery is and give a simple example.

Think about a query inside another query.

Describe the difference between correlated and non-correlated nested subqueries.

Focus on how the subquery uses values from the outer query.