Nested subqueries in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Find employees with salary above department average

Given a table employees with columns id, name, department_id, and salary, which query returns the names of employees whose salary is greater than the average salary in their department?

SQL

SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = employees.department_id);

A SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = employees.department_id);

B SELECT name FROM employees WHERE salary > AVG(salary) FROM employees GROUP BY department_id;

C SELECT name FROM employees WHERE salary > (SELECT MAX(salary) FROM employees WHERE department_id = employees.department_id);

D SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

💡 Hint

Use a subquery to calculate the average salary for each employee's department.

Option A correctly uses a correlated subquery to compare each employee's salary to the average salary of their own department. Option A is invalid SQL syntax. Option A compares salary to the maximum salary, not average. Option A compares salary to the average salary of all employees, ignoring departments.

❓ query_result

intermediate

List products priced higher than any in category 5

Given a table products with columns product_id, category_id, and price, which query lists all products with a price higher than every product in category 5?

SQL

SELECT product_id FROM products WHERE price > ALL (SELECT price FROM products WHERE category_id = 5);

A SELECT product_id FROM products WHERE price > ALL (SELECT price FROM products WHERE category_id = 5);

B SELECT product_id FROM products WHERE price > ANY (SELECT price FROM products WHERE category_id = 5);

C SELECT product_id FROM products WHERE price > (SELECT MAX(price) FROM products WHERE category_id = 5);

D SELECT product_id FROM products WHERE price >= ALL (SELECT price FROM products WHERE category_id = 5);

💡 Hint

Use ALL to ensure the price is greater than every price in category 5.

Option A correctly uses ALL to compare the price against all prices in category 5. Option A uses ANY, which means price greater than at least one, not all. Option A compares price to the maximum price, which is equivalent but less flexible if multiple max prices exist. Option A uses >= which includes equal prices, not strictly greater.

📝 Syntax

advanced

Identify the syntax error in nested subquery

Which option contains a syntax error in the nested subquery?

SQL

SELECT name FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'NY');

A SELECT name FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'NY') ORDER BY name;

B SELECT name FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE location = 'NY');

C SELECT name FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'NY');

D SELECT name FROM employees WHERE department_id IN SELECT department_id FROM departments WHERE location = 'NY';

💡 Hint

Check if the subquery is properly enclosed in parentheses.

Option D is missing parentheses around the subquery, causing a syntax error. Options C and D are correct syntax. Option D is valid only if the subquery returns a single value; otherwise, it may cause runtime error but no syntax error.

❓ optimization

advanced

Optimize query with nested subquery for better performance

Given the query below, which option optimizes it by reducing repeated subquery execution?

SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = employees.department_id);

A SELECT name FROM employees WHERE salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department_id);

B SELECT name FROM employees WHERE salary > ALL (SELECT AVG(salary) FROM employees GROUP BY department_id);

C WITH dept_avg AS (SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id) SELECT e.name FROM employees e JOIN dept_avg d ON e.department_id = d.department_id WHERE e.salary > d.avg_salary;

D SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

💡 Hint

Use a common table expression (CTE) or join to avoid recalculating averages for each row.

Option C uses a CTE to calculate average salaries per department once, then joins to employees, improving performance. Option C compares salary to overall average, not per department. Options A and C misuse ALL/ANY with aggregates and produce incorrect results.

🧠 Conceptual

expert

Understanding correlated vs non-correlated subqueries

Which statement correctly describes the difference between correlated and non-correlated subqueries?

A Both correlated and non-correlated subqueries run once per query and do not depend on the outer query.

B A correlated subquery depends on the outer query for each row, while a non-correlated subquery runs once independently of the outer query.

C Correlated subqueries can only be used in WHERE clauses, non-correlated only in FROM clauses.

D A non-correlated subquery depends on the outer query for each row, while a correlated subquery runs once independently of the outer query.

💡 Hint

Think about whether the subquery uses columns from the outer query.

Correlated subqueries reference columns from the outer query and are evaluated for each row, while non-correlated subqueries are independent and run once. Option B reverses this. Option B is incorrect because correlated subqueries run multiple times. Option B is false; both types can appear in various clauses.

Practice

1. What does a nested subquery in SQL do?

easy

A. Runs a query inside another query to filter or compare data

B. Creates a new table from existing data

C. Deletes data from multiple tables at once

D. Updates all rows in a table without conditions

Solution

1. Step 1: Understand the concept of nested subqueries

   A nested subquery is a query inside another query that runs first to provide data for the outer query.

2. Step 2: Identify the correct description

   Runs a query inside another query to filter or compare data correctly describes this behavior as running a query inside another to filter or compare data.

3. Final Answer:

   Runs a query inside another query to filter or compare data -> Option A

4. Quick Check:

   Nested subquery = query inside query [OK]

Hint: Nested means one query inside another [OK]

Common Mistakes:

• Confusing nested subquery with table creation
• Thinking nested subqueries delete data
• Assuming nested subqueries update all rows blindly

2. Which of the following is the correct syntax for a nested subquery in SQL?

easy

A. SELECT * FROM table WHERE id IN SELECT id FROM table2 WHERE value = 10;

B. SELECT * FROM table WHERE id == (SELECT id FROM table2 WHERE value = 10);

C. SELECT * FROM table WHERE id = SELECT id FROM table2 WHERE value = 10;

D. SELECT * FROM table WHERE id = (SELECT id FROM table2 WHERE value = 10);

Solution

1. Step 1: Review correct nested subquery syntax

   The inner query must be enclosed in parentheses and used with operators like = or IN.

2. Step 2: Check each option

   SELECT * FROM table WHERE id = (SELECT id FROM table2 WHERE value = 10); uses parentheses correctly and equals operator, making it valid SQL syntax.

3. Final Answer:

   SELECT * FROM table WHERE id = (SELECT id FROM table2 WHERE value = 10); -> Option D

4. Quick Check:

   Nested subquery syntax uses parentheses [OK]

Hint: Always use parentheses around subqueries [OK]

Common Mistakes:

• Missing parentheses around subquery
• Using double equals (==) instead of single =
• Omitting parentheses causing syntax errors

3. Given the tables:
Employees(emp_id, name, dept_id)
Departments(dept_id, dept_name)
What does this query return?

SELECT name FROM Employees WHERE dept_id = (SELECT dept_id FROM Departments WHERE dept_name = 'Sales');

medium

A. Names of employees who work in the Sales department

B. All employee names regardless of department

C. Department names where employees work

D. Employee names who do not work in Sales

Solution

1. Step 1: Understand the inner query

   The inner query finds the dept_id for the 'Sales' department from Departments table.

2. Step 2: Apply the outer query condition

   The outer query selects employee names whose dept_id matches the Sales dept_id found by the inner query.

3. Final Answer:

   Names of employees who work in the Sales department -> Option A

4. Quick Check:

   Inner query finds Sales dept_id, outer filters employees [OK]

Hint: Inner query finds filter value, outer applies it [OK]

Common Mistakes:

• Thinking it returns all employees
• Confusing employee names with department names
• Assuming it returns employees not in Sales

4. Identify the error in this SQL query:

SELECT name FROM Employees WHERE dept_id = SELECT dept_id FROM Departments WHERE dept_name = 'HR';

medium

A. Using = instead of IN for subquery

B. Missing parentheses around the subquery

C. Wrong table name used in subquery

D. Subquery returns multiple columns

Solution

1. Step 1: Check subquery syntax

   The subquery must be enclosed in parentheses to be valid inside WHERE clause.

2. Step 2: Identify the missing parentheses

   The query lacks parentheses around the subquery, causing syntax error.

3. Final Answer:

   Missing parentheses around the subquery -> Option B

4. Quick Check:

   Subqueries need parentheses [OK]

Hint: Always wrap subqueries in parentheses [OK]

Common Mistakes:

• Forgetting parentheses around subquery
• Using wrong operator without parentheses
• Assuming subquery syntax is optional

5. You want to find all customers who placed orders with amounts greater than the average order amount. Which query correctly uses a nested subquery to achieve this?

hard

A. SELECT customer_id FROM Orders WHERE amount < (SELECT AVG(amount) FROM Orders);

B. SELECT customer_id FROM Orders WHERE amount = (SELECT AVG(amount) FROM Orders);

C. SELECT customer_id FROM Orders WHERE amount > (SELECT AVG(amount) FROM Orders);

D. SELECT customer_id FROM Orders WHERE amount IN (SELECT AVG(amount) FROM Orders);

Solution

1. Step 1: Understand the goal

   We want customers with orders greater than the average order amount.

2. Step 2: Analyze each option's condition

   SELECT customer_id FROM Orders WHERE amount > (SELECT AVG(amount) FROM Orders); uses > with a subquery calculating average amount, correctly filtering orders above average.

3. Final Answer:

   SELECT customer_id FROM Orders WHERE amount > (SELECT AVG(amount) FROM Orders); -> Option C

4. Quick Check:

   Use > with AVG subquery to find above-average orders [OK]

Hint: Compare with (SELECT AVG(...)) using > for above average [OK]

Common Mistakes:

• Using = instead of > to find above average
• Using < which finds below average
• Using IN with a single value subquery incorrectly