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.