Chapter 13 Intermediate 51 Questions

Practice Questions — Subqueries (Nested Queries) and EXISTS

← Back to Notes
14 Easy
10 Medium
4 Hard

Topic-Specific Questions

Question 1
Easy
What is a scalar subquery?
Think about the shape of the result.
A subquery that returns a single value (1 row, 1 column). Can be used wherever SQL expects a single value.
Question 2
Easy
What error does MySQL throw if a = subquery returns more than one row?
Error 1242.
ERROR 1242 (21000): Subquery returns more than 1 row
Question 3
Easy
What does EXISTS return?
Based on whether the subquery has rows.
TRUE if the subquery produces at least one row, FALSE otherwise. Row contents do not matter — it only checks existence.
Question 4
Easy
What does this return given sample data (4 customers, 4 orders, Meera has no orders)?
SELECT name FROM customers
WHERE EXISTS (
    SELECT 1 FROM orders WHERE orders.customer_id = customers.id
);
Customers with at least one order.
Aarav Kumar, Priya Sharma, Rohan Mehta
Question 5
Easy
What does this return given sample data?
SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders);
Same as EXISTS for this case.
Aarav Kumar, Priya Sharma, Rohan Mehta
Question 6
Easy
Given a table with salaries 100, 200, 300, 300, 400, what is the 3rd highest salary using DISTINCT?
Distinct values in descending order: 400, 300, 200.
200
Question 7
Easy
Is this a correlated or non-correlated subquery?
SELECT name FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
Does the inner query reference the outer query?
Non-correlated
Question 8
Easy
Is this correlated or non-correlated?
SELECT e.name FROM employees e
WHERE e.salary > (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department = e.department);
Look for outer reference.
Correlated
Question 9
Easy
What error does this produce?
SELECT * FROM (SELECT department, AVG(salary) FROM employees GROUP BY department);
Missing something.
ERROR 1248 (42000): Every derived table must have its own alias
Question 10
Medium
Given sample employees, how many rows does this return?
SELECT name FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
Company avg is around 155,625.
4 rows: Ananya (250k), Divya (190k), Rahul (180k), Sneha (170k)
Question 11
Medium
What does this return on the sample employees?
SELECT e.name, e.salary, e.department
FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary) FROM employees e2
    WHERE e2.department = e.department
);
Per-department average. Engineering avg 185k, Marketing 125k, Sales 130k.
Ananya (Eng 250k vs 185k), Divya (Eng 190k vs 185k), Sneha (Mkt 170k vs 125k). Note: Rahul 180k does NOT qualify (< 185k Eng avg).
Question 12
Medium
Explain why this returns zero rows if any order has NULL customer_id:
SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);
NULL comparisons are UNKNOWN.
x NOT IN (list with NULL) expands to AND of x != each value. x != NULL is UNKNOWN, which combined with AND becomes UNKNOWN (treated as FALSE). So every row is filtered out.
Question 13
Medium
What does this derive?
SELECT dept, avg_sal FROM (
    SELECT department AS dept, AVG(salary) AS avg_sal
    FROM employees GROUP BY department
) t
WHERE avg_sal > 150000;
Departments above 150k avg salary.
Engineering (185k avg). Marketing 125k and Sales 130k do not qualify.
Question 14
Medium
What does this return?
SELECT name FROM employees
WHERE salary > ALL (
    SELECT salary FROM employees WHERE department = 'Marketing'
);
Greater than EVERY marketing salary. Marketing max is 170k.
Ananya Reddy (250k), Divya Nair (190k), Rahul Verma (180k). Sneha at 170k is NOT strictly greater than 170k.
Question 15
Hard
What is the 3rd highest distinct salary in the sample table?
Sort distinct salaries descending: 250k, 190k, 180k, 170k, ...
180000 (Rahul Verma)
Question 16
Hard
What does this return? 
SELECT name FROM employees e1
WHERE 2 = (
    SELECT COUNT(DISTINCT salary) FROM employees e2
    WHERE e2.salary >= e1.salary
);
Count of distinct salaries >= current employee's = 2 means current is 2nd highest.
Divya Nair (salary 190000, the 2nd highest distinct salary)

Mixed & Application Questions

Question 1
Easy
Where can subqueries appear in an SQL statement?
Four clauses.
SELECT, FROM, WHERE, HAVING. They can also appear in INSERT, UPDATE, and DELETE statements.
Question 2
Easy
What is the difference between EXISTS and IN?
Content vs existence.
IN checks if a value equals any item in a list/subquery. EXISTS checks whether the subquery produces any row (row contents ignored). EXISTS handles NULLs better and often short-circuits on first match.
Question 3
Easy
Convert this IN subquery to a JOIN: 
SELECT name FROM customers WHERE id IN (SELECT customer_id FROM orders);
INNER JOIN + DISTINCT.
SELECT DISTINCT c.name
FROM customers c
JOIN orders o ON c.id = o.customer_id;
Question 4
Easy
Why is NOT EXISTS preferred over NOT IN?
NULL handling.
NOT IN returns wrong (empty) results if the subquery contains NULL. NOT EXISTS is NULL-safe.
Question 5
Easy
What does ANY mean in SQL?
Compare to at least one.
ANY returns TRUE if the comparison holds for AT LEAST ONE row in the subquery. = ANY is equivalent to IN.
Question 6
Medium
Write a query to find employees in the Engineering department whose salary is greater than the maximum salary in Marketing.
Scalar subquery with MAX.
SELECT name, salary FROM employees
WHERE department = 'Engineering'
  AND salary > (SELECT MAX(salary) FROM employees WHERE department = 'Marketing');
Question 7
Medium
Write a query to find departments whose average salary is above the company average.
GROUP BY + HAVING + scalar subquery.
SELECT department, AVG(salary) AS avg_sal
FROM employees
GROUP BY department
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);
Question 8
Medium
Write a correlated subquery to find employees who are the highest paid in their own department.
Compare to max in same department.
SELECT name, department, salary
FROM employees e
WHERE salary = (
    SELECT MAX(salary) FROM employees
    WHERE department = e.department
);
Question 9
Medium
What is the NULL trap with NOT IN, and how do you avoid it?
x != NULL is UNKNOWN.
If the subquery returns NULL, NOT IN is equivalent to an AND of x != each_value, including x != NULL which is UNKNOWN. AND with UNKNOWN is UNKNOWN (treated as FALSE), so no rows pass. Avoid by using NOT EXISTS, or filter NULLs out: ... WHERE col IS NOT NULL.
Question 10
Medium
Why might a correlated subquery be slower than a JOIN?
Per-row execution.
A correlated subquery logically runs once per outer row. For N outer rows, the inner query may execute N times. A JOIN is a set operation processed once. Modern optimizers often rewrite correlated subqueries as semi-joins internally.
Question 11
Hard
Write a query using a subquery in SELECT to show each employee's salary and the difference from their department's average.
Correlated scalar subquery per row.
SELECT e.name, e.department, e.salary,
       e.salary - (
           SELECT AVG(e2.salary) FROM employees e2
           WHERE e2.department = e.department
       ) AS diff_from_dept_avg
FROM employees e;
Question 12
Hard
Find the 2nd highest distinct salary per department.
Correlated subquery with COUNT DISTINCT.
SELECT name, department, salary
FROM employees e1
WHERE 2 = (
    SELECT COUNT(DISTINCT e2.salary) FROM employees e2
    WHERE e2.department = e1.department AND e2.salary >= e1.salary
);

Multiple Choice Questions

MCQ 1
Which of the following is a scalar subquery?
  • A. A subquery returning multiple rows
  • B. A subquery returning exactly one value (one row, one column)
  • C. A subquery in the FROM clause
  • D. A subquery that uses EXISTS
Answer: B
B is correct. A scalar subquery returns a single value and can be used anywhere a value is expected (in SELECT, WHERE, HAVING).
MCQ 2
What does EXISTS return?
  • A. The matching rows from the subquery
  • B. TRUE if the subquery has at least one row; FALSE otherwise
  • C. The count of rows
  • D. NULL if no rows
Answer: B
B is correct. EXISTS is a boolean test — it only checks whether the subquery produced any rows; the contents are ignored.
MCQ 3
Which operator is equivalent to = ANY?
  • A. ALL
  • B. EXISTS
  • C. IN
  • D. BETWEEN
Answer: C
C is correct. = ANY matches if equal to any value in the set, which is exactly what IN does.
MCQ 4
What MySQL error occurs if a scalar = subquery returns multiple rows?
  • A. ERROR 1064 (syntax)
  • B. ERROR 1242 (Subquery returns more than 1 row)
  • C. ERROR 1054 (Unknown column)
  • D. No error; first row is used
Answer: B
B is correct. MySQL strictly enforces single-row results for = comparisons against subqueries. Use IN, aggregates, or LIMIT.
MCQ 5
Which is the safe way to find customers with no orders?
  • A. WHERE id NOT IN (SELECT customer_id FROM orders)
  • B. WHERE NOT EXISTS (SELECT 1 FROM orders WHERE orders.customer_id = customers.id)
  • C. WHERE customer_id != ALL orders
  • D. WHERE id <> (SELECT customer_id FROM orders)
Answer: B
B is correct. NOT EXISTS handles NULLs gracefully. A is the well-known NULL trap; C is syntactically invalid; D fails if subquery returns multiple rows.
MCQ 6
What is a correlated subquery?
  • A. A subquery that uses JOIN
  • B. A subquery that references columns from the outer query
  • C. A subquery with a GROUP BY
  • D. A subquery that uses EXISTS
Answer: B
B is correct. Correlation means the inner query depends on the outer query's current row, typically through a WHERE condition.
MCQ 7
What requirement does MySQL impose on derived tables (subqueries in FROM)?
  • A. They must be materialized
  • B. They must have an alias
  • C. They cannot contain GROUP BY
  • D. They cannot be nested
Answer: B
B is correct. MySQL requires every derived table to have an alias (e.g., ... ) AS t). Without it: ERROR 1248.
MCQ 8
What does this query compute?
SELECT name FROM employees WHERE salary > ALL (SELECT salary FROM employees WHERE department = 'Marketing');
  • A. Employees earning more than at least one marketing employee
  • B. Employees earning more than every marketing employee
  • C. Employees in the Marketing department
  • D. The maximum salary in Marketing
Answer: B
B is correct. > ALL means greater than every value in the set. Equivalent to > MAX(...).
MCQ 9
Why is NOT IN unsafe when the subquery may return NULL?
  • A. Because NULL is treated as 0
  • B. Because x != NULL is UNKNOWN, which makes NOT IN return no TRUE results
  • C. Because NOT IN is slower
  • D. Because NULL cannot be compared
Answer: B
B is correct. Any comparison with NULL yields UNKNOWN. AND-ed with UNKNOWN keeps it UNKNOWN, which SQL treats as not-true. Every row is filtered out.
MCQ 10
Which query finds the 2nd highest distinct salary?
  • A. SELECT MAX(salary) FROM employees WHERE salary < MAX(salary)
  • B. SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1
  • C. SELECT MAX(salary) FROM employees
  • D. SELECT MIN(salary) FROM employees
Answer: B
B is correct. DISTINCT removes duplicate salaries, ORDER BY DESC puts highest first, OFFSET 1 skips the first, LIMIT 1 takes one — the 2nd highest distinct salary. A is invalid (aggregate with aggregate).
MCQ 11
What does a subquery in the SELECT list typically do?
  • A. Join tables
  • B. Add a computed column that depends on a subquery value
  • C. Group rows
  • D. Filter rows
Answer: B
B is correct. A SELECT-list subquery returns one value per row and becomes an additional column. Often a correlated per-row computation or a global scalar.
MCQ 12
A correlated subquery logically runs:
  • A. Once total
  • B. Once per outer row
  • C. Once per table
  • D. Never
Answer: B
B is correct. Correlated subqueries depend on the current outer row, so they conceptually re-execute for each. Optimizers may transform them into more efficient forms.
MCQ 13
Which query correctly finds employees who earn more than their department average?
  • A. SELECT * FROM employees WHERE salary > AVG(salary)
  • B. SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees)
  • C. SELECT e.* FROM employees e WHERE e.salary > (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department = e.department)
  • D. SELECT * FROM employees HAVING salary > AVG(salary)
Answer: C
C is correct. Correlated subquery per department. A is invalid (aggregate in WHERE). B compares to the global average. D misuses HAVING.
MCQ 14
Why might EXISTS be faster than IN on very large tables?
  • A. EXISTS materializes the full list first
  • B. EXISTS can short-circuit on the first matching row; IN may evaluate the whole subquery
  • C. IN is deprecated
  • D. EXISTS uses an index automatically
Answer: B
B is correct. EXISTS only needs one matching row to return TRUE, allowing early termination. Modern optimizers often produce similar plans for both.
MCQ 15
What is the canonical query for the Nth highest salary?
  • A. SELECT salary FROM employees LIMIT N
  • B. SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET N-1
  • C. SELECT MAX(salary) FROM employees
  • D. SELECT COUNT(salary) FROM employees
Answer: B
B is correct. DISTINCT + sorted descending + OFFSET N-1 + LIMIT 1 returns the Nth distinct salary. Alternative: DENSE_RANK window function.

Coding Challenges

Challenge 1: Employees Above Company Average

Easy
Write a query to find all employees whose salary is greater than the overall average salary.
SELECT name, department, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;

Challenge 2: Customers With at Least One Order

Easy
Use EXISTS to find customers who have placed at least one order.
SELECT c.id, c.name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

Challenge 3: 3rd Highest Salary

Medium
Find the 3rd highest distinct salary in the employees table.
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 2;

Challenge 4: Employees Above Department Average

Medium
Using a correlated subquery, find employees whose salary is greater than the average salary of their own department.
SELECT e.name, e.department, e.salary
FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department = e.department
);

Challenge 5: Departments Above Company Average

Medium
Find departments whose average salary exceeds the company average.
SELECT department, AVG(salary) AS avg_sal
FROM employees
GROUP BY department
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);

Challenge 6: Customers With No Orders (NOT EXISTS)

Hard
Find customers who have never placed an order. Use NOT EXISTS to avoid the NULL trap.
SELECT c.id, c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

Challenge 7: Nth Highest via Correlated Subquery

Hard
Write a parametric query that returns the Nth highest distinct salary using a correlated subquery (no LIMIT).
SELECT DISTINCT salary FROM employees e1
WHERE 3 = (
    SELECT COUNT(DISTINCT salary) FROM employees e2
    WHERE e2.salary >= e1.salary
);

Challenge 8: Highest Paid in Each Department

Hard
List the highest-paid employee(s) in every department. Handle ties (multiple employees at the same max salary).
SELECT name, department, salary
FROM employees e
WHERE salary = (
    SELECT MAX(salary) FROM employees e2
    WHERE e2.department = e.department
)
ORDER BY department;

Need to Review the Concepts?

Go back to the detailed notes for this chapter.

Read Chapter Notes

Want to master SQL and databases with a mentor?

Explore our MySQL Masterclass