Back to SQL Practice: 331 Real Interview Queries
Q4Question 4 of 9

Subqueries: Scalar, Correlated, EXISTS, IN, UNION

Answer: Subqueries nest a SELECT inside another query. Scalar subqueries return one value, correlated subqueries run per outer row, EXISTS checks for any matching rows, and UNION combines result sets.

Key Concepts
  • A scalar subquery returns exactly one value. Used in SELECT or WHERE: WHERE salary > (SELECT AVG(salary) FROM employees).
  • A correlated subquery references the outer query. It runs once per outer row, making it slower than non-correlated subqueries on large tables.
  • EXISTS returns TRUE if the subquery returns at least one row. Often faster than IN for large datasets because it stops at the first match.
  • IN checks if a value matches any value in the subquery result. NOT IN fails silently if the subquery returns NULL. Use NOT EXISTS instead.
  • Subqueries in FROM create a derived table (inline view). Must have an alias: FROM (SELECT ... ) AS subquery_alias.
  • UNION combines two SELECT results and removes duplicates. UNION ALL keeps all rows (faster). Both require matching column count and types.
  • Rewrite correlated subqueries as JOINs or CTEs when performance is poor. The optimizer may do this automatically, but not always.

Definition

Subqueries nest a SELECT inside another query. Scalar subqueries return one value, correlated subqueries run per outer row, EXISTS checks for any matching rows, and UNION combines result sets.
  • Scalar subquery: Returns one value. Used in WHERE or SELECT.
  • Correlated subquery: References the outer query. Runs once per outer row (can be slow).
  • EXISTS: Returns TRUE if subquery has any rows. Stops at first match (efficient).
  • IN vs EXISTS: IN materializes the full list. EXISTS short-circuits. NOT IN fails with NULLs.
  • Derived table: Subquery in FROM clause. Must have an alias.
  • UNION: Combines results, removes duplicates. UNION ALL keeps all rows.
  • Tip: Rewrite slow correlated subqueries as JOINs or CTEs.

This section is a focused tutorial on Subqueries. Each question teaches you to think through the problem before writing SQL, build the query step by step, understand how the database executes it, and optimize for performance.

Understanding the Concepts

## Subqueries: Scalar, Correlated, EXISTS, IN, UNION

A subquery is a SELECT nested inside another query. A scalar subquery returns one value. A correlated subquery references the outer query and runs once per outer row. EXISTS returns true if the subquery has rows. IN checks membership in a list. UNION combines results from multiple SELECTs.

Sample Tables

employees, departments, and orders tables (shared across all queries in this section):

Employees and departments tables as defined in the JOINs section above.

orders:

order_idcustomer_idamountorder_dateproductstatus
1001C01500002026-01-15HomeLoanCompleted
1002C02300002026-01-20CarLoanCompleted
1003C01700002026-02-10HomeLoanPending
1004C03200002026-02-15PersonalCompleted
1005C02400002026-03-01CarLoanCancelled

customers:

customer_idnamecitysignup_date
C01PriyaMumbai2024-01-10
C02RajeshDelhi2024-03-15
C03SnehaChennai2025-06-20

---

Q1. Find the second highest salary from the Employee table

Problem: Find the second highest salary from the Employee table.

How to think before writing:

  1. Can I solve the inner part first as a subquery, then use the result in the outer query?
  2. What condition filters the rows I need? Write the WHERE clause for that condition.

Pseudo query (plain English first):

TEXT
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result

SQL Solution:

SQL
SELECT MAX(salary) AS SecondHighestSalary 
FROM employees 
WHERE salary < ( 
    SELECT MAX(salary) 
    FROM employees 
);

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.

Optimization: If the subquery runs once per outer row (correlated), consider rewriting as a JOIN or CTE for better performance.

---

Q2. Write a query to find the median salary

Problem: Write a query to find the median salary.

How to think before writing:

  1. Can I solve the inner part first as a subquery, then use the result in the outer query?
  2. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result

SQL Solution:

SQL
SELECT AVG(salary) AS median_salary 
FROM ( 
  SELECT salary 
  FROM employees 
  ORDER BY salary 
  LIMIT 2 - (SELECT COUNT(*) FROM employees) 
% 2 
  OFFSET (SELECT (COUNT(*) - 1) / 2 FROM 
employees) 
) AS median_subquery;

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. SELECT: Projects the final columns and evaluates expressions/aliases.
  3. ORDER BY: Sorts the final result set.
  4. LIMIT/TOP: Returns only the first N rows from the sorted result.

Optimization: Run EXPLAIN ANALYZE to check the execution plan and add indexes where needed.

---

Q3. Write a query to find gaps in a sequence of numbers (missing IDs)

Problem: Write a query to find gaps in a sequence of numbers (missing IDs).

How to think before writing:

  1. Can I solve the inner part first as a subquery, then use the result in the outer query?
  2. What condition filters the rows I need? Write the WHERE clause for that condition.
  3. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result

SQL Solution:

SQL
SELECT (id + 1) AS missing_id 
FROM employees e1 
WHERE NOT EXISTS ( 
  SELECT 1 FROM employees e2 WHERE e2.id = 
e1.id + 1 
) 
ORDER BY missing_id;

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. ORDER BY: Sorts the final result set.

Optimization: If the subquery runs once per outer row (correlated), consider rewriting as a JOIN or CTE for better performance.

---

Q4. Write a query to find employees with salary greater than average salary in the entire company, ordered by salary descending

Problem: Write a query to find employees with salary greater than average salary in the entire company, ordered by salary descending.

How to think before writing:

  1. Can I solve the inner part first as a subquery, then use the result in the outer query?
  2. What condition filters the rows I need? Write the WHERE clause for that condition.
  3. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result

SQL Solution:

SQL
SELECT name, salary 
FROM employees 
WHERE salary > (SELECT AVG(salary) FROM 
employees) 
ORDER BY salary DESC;

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. ORDER BY: Sorts the final result set.

Optimization: If the subquery runs once per outer row (correlated), consider rewriting as a JOIN or CTE for better performance.

---

Q5. Find customers who made purchases in every category available

Problem: Find customers who made purchases in every category available.

How to think before writing:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Do I need to filter groups after aggregation? That means HAVING, not WHERE.
  4. Can I solve the inner part first as a subquery, then use the result in the outer query?
  5. Are there duplicates I need to remove? Use DISTINCT.

Pseudo query (plain English first):

TEXT
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result

SQL Solution:

SQL
SELECT customer_id 
FROM sales s 
GROUP BY customer_id 
HAVING COUNT(DISTINCT category_id) = 
(SELECT COUNT(DISTINCT category_id) FROM 
sales);

Step-by-step how the database executes this:

  1. FROM: Reads table sales.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. HAVING: Filters groups. Only groups passing the condition remain.
  4. SELECT: Projects the final columns and evaluates expressions/aliases.
  5. DISTINCT: Removes duplicate rows by comparing all selected columns.

Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.

---

Q6. Find employees who earn more than the average salary across the company but less than the highest salary in their department

Problem: Find employees who earn more than the average salary across the company but less than the highest salary in their department.

How to think before writing:

  1. Can I solve the inner part first as a subquery, then use the result in the outer query?
  2. What condition filters the rows I need? Write the WHERE clause for that condition.

Pseudo query (plain English first):

TEXT
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result

SQL Solution:

SQL
SELECT * 
FROM employees e 
WHERE salary > (SELECT AVG(salary) FROM 
employees) 
  AND salary < ( 
    SELECT MAX(salary) 
    FROM employees 
    WHERE department_id = e.department_id 
  );

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.

Optimization: If the subquery runs once per outer row (correlated), consider rewriting as a JOIN or CTE for better performance.

---

Q7. Write a query to find all employees who are at the lowest level in the hierarchy (no subordinates)

Problem: Write a query to find all employees who are at the lowest level in the hierarchy (no subordinates).

How to think before writing:

  1. Can I solve the inner part first as a subquery, then use the result in the outer query?
  2. What condition filters the rows I need? Write the WHERE clause for that condition.

Pseudo query (plain English first):

TEXT
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result

SQL Solution:

SQL
SELECT * 
FROM employees e 
WHERE NOT EXISTS ( 
  SELECT 1 FROM employees sub WHERE 
sub.manager_id = e.id 
);

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.

Optimization: If the subquery runs once per outer row (correlated), consider rewriting as a JOIN or CTE for better performance.

---

Q8. Find the average number of orders per customer and standard deviation

Problem: Find the average number of orders per customer and standard deviation.

How to think before writing:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Can I solve the inner part first as a subquery, then use the result in the outer query?

Pseudo query (plain English first):

TEXT
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result

SQL Solution:

SQL
SELECT AVG(order_count) AS avg_orders, 
STDDEV(order_count) AS stddev_orders 
FROM ( 
  SELECT customer_id, COUNT(*) AS order_count 
  FROM orders 
  GROUP BY customer_id 
) sub;

Step-by-step how the database executes this:

  1. FROM: Reads table orders.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.

Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.

---

Q9. Find employees whose salary is above the average salary of their department but below the company-wide average

Problem: Find employees whose salary is above the average salary of their department but below the company-wide average.

How to think before writing:

  1. Can I solve the inner part first as a subquery, then use the result in the outer query?
  2. What condition filters the rows I need? Write the WHERE clause for that condition.

Pseudo query (plain English first):

TEXT
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result

SQL Solution:

SQL
SELECT * 
FROM employees e 
WHERE salary > ( 
    SELECT AVG(salary)  
    FROM employees  
    WHERE department_id = e.department_id 
)  
AND salary < (SELECT AVG(salary) FROM 
employees);

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.

Optimization: If the subquery runs once per outer row (correlated), consider rewriting as a JOIN or CTE for better performance.

---

Q10. List the customers who purchased all products in a specific category

Problem: List the customers who purchased all products in a specific category.

How to think before writing:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Do I need to filter groups after aggregation? That means HAVING, not WHERE.
  4. Can I solve the inner part first as a subquery, then use the result in the outer query?
  5. Are there duplicates I need to remove? Use DISTINCT.

Pseudo query (plain English first):

TEXT
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result

SQL Solution:

SQL
SELECT customer_id 
FROM sales 
WHERE category_id = 10 -- example category 
GROUP BY customer_id 
HAVING COUNT(DISTINCT product_id) = ( 
    SELECT COUNT(DISTINCT product_id) 
    FROM products 
    WHERE category_id = 10 
);

Step-by-step how the database executes this:

  1. FROM: Reads table sales.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. GROUP BY: Collapses rows into groups. One output row per unique group value.
  4. HAVING: Filters groups. Only groups passing the condition remain.
  5. SELECT: Projects the final columns and evaluates expressions/aliases.
  6. DISTINCT: Removes duplicate rows by comparing all selected columns.

Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.

---

Q11. Find the customers who placed orders only in the last 30 days

Problem: Find the customers who placed orders only in the last 30 days.

How to think before writing:

  1. Can I solve the inner part first as a subquery, then use the result in the outer query?
  2. What condition filters the rows I need? Write the WHERE clause for that condition.
  3. Are there duplicates I need to remove? Use DISTINCT.

Pseudo query (plain English first):

TEXT
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result

SQL Solution:

SQL
SELECT DISTINCT customer_id 
FROM orders 
WHERE order_date >= CURRENT_DATE - 
INTERVAL '30 days' 
AND customer_id NOT IN ( 
  SELECT DISTINCT customer_id  
  FROM orders 
  WHERE order_date < CURRENT_DATE - 
INTERVAL '30 days' 
);

Step-by-step how the database executes this:

  1. FROM: Reads table orders.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. DISTINCT: Removes duplicate rows by comparing all selected columns.

Optimization: If the subquery runs once per outer row (correlated), consider rewriting as a JOIN or CTE for better performance.

---

Q12. Find employees whose salary is above their department’s average but below the overall average salary

Problem: Find employees whose salary is above their department’s average but below the overall average salary.

How to think before writing:

  1. Can I solve the inner part first as a subquery, then use the result in the outer query?
  2. What condition filters the rows I need? Write the WHERE clause for that condition.

Pseudo query (plain English first):

TEXT
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result

SQL Solution:

SQL
SELECT * 
FROM employees e 
WHERE salary > (SELECT AVG(salary) FROM 
employees WHERE department_id = e.department_id) 
AND salary < (SELECT AVG(salary) FROM 
employees);

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.

Optimization: If the subquery runs once per outer row (correlated), consider rewriting as a JOIN or CTE for better performance.

---

Q13. Find employees who have managed more than 3 projects

Problem: Find employees who have managed more than 3 projects.

How to think before writing:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Do I need to filter groups after aggregation? That means HAVING, not WHERE.
  4. Which tables do I need? How are they related? What column connects them?
  5. Do I need to compare rows within the same table? That means a self-join with aliases.

Pseudo query (plain English first):

TEXT
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result

SQL Solution:

SQL
SELECT manager_id, COUNT(DISTINCT project_id) 
AS project_count 
FROM projects 
GROUP BY manager_id 
HAVING COUNT(DISTINCT project_id) > 3; 
 
--68. Write a query to calculate the difference in days 
between each employee's hire date and their manager’s 
hire date. 
SELECT e.name AS employee, m.name AS manager,  
       DATEDIFF(day, m.hire_date, e.hire_date) AS 
days_difference 
FROM employees e 
JOIN employees m ON e.manager_id = m.id; 
 
 
(Syntax of DATEDIFF varies by SQL dialect)

Step-by-step how the database executes this:

  1. FROM: Reads table projects.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  3. GROUP BY: Collapses rows into groups. One output row per unique group value.
  4. HAVING: Filters groups. Only groups passing the condition remain.
  5. SELECT: Projects the final columns and evaluates expressions/aliases.
  6. DISTINCT: Removes duplicate rows by comparing all selected columns.

Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.

---

Q14. List employees who earn more than all their subordinates

Problem: List employees who earn more than all their subordinates.

How to think before writing:

  1. Can I solve the inner part first as a subquery, then use the result in the outer query?
  2. What condition filters the rows I need? Write the WHERE clause for that condition.

Pseudo query (plain English first):

TEXT
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result

SQL Solution:

SQL
SELECT e.id, e.name, e.salary 
FROM employees e 
WHERE e.salary > ALL ( 
  SELECT salary  
  FROM employees sub  
  WHERE sub.manager_id = e.id 
);

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.

Optimization: If the subquery runs once per outer row (correlated), consider rewriting as a JOIN or CTE for better performance.

---

Q15. Find the second highest salary per department

Problem: Find the second highest salary per department.

How to think before writing:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.

Pseudo query (plain English first):

TEXT
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result

SQL Solution:

SQL
without using window functions. 
SELECT department_id, MAX(salary) AS 
second_highest_salary 
FROM employees e1 
WHERE salary < ( 
  SELECT MAX(salary) 
  FROM employees e2 
  WHERE e2.department_id = e1.department_id 
) 
GROUP BY department_id;

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. GROUP BY: Collapses rows into groups. One output row per unique group value.
  4. SELECT: Projects the final columns and evaluates expressions/aliases.

Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.

---

Q16. Find employees whose salary is above the average salary of their department but below the company-wide average

Problem: Find employees whose salary is above the average salary of their department but below the company-wide average.

How to think before writing:

  1. Can I solve the inner part first as a subquery, then use the result in the outer query?
  2. What condition filters the rows I need? Write the WHERE clause for that condition.

Pseudo query (plain English first):

TEXT
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result

SQL Solution:

SQL
SELECT * 
FROM employees e 
WHERE salary > (SELECT AVG(salary) FROM 
employees WHERE department_id = e.department_id) 
AND salary < (SELECT AVG(salary) FROM 
employees);

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.

Optimization: If the subquery runs once per outer row (correlated), consider rewriting as a JOIN or CTE for better performance.

---

Q17. Write a query to list all employees who have a salary equal to the average salary of their department

Problem: Write a query to list all employees who have a salary equal to the average salary of their department.

How to think before writing:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Which tables do I need? How are they related? What column connects them?
  4. Can I solve the inner part first as a subquery, then use the result in the outer query?

Pseudo query (plain English first):

TEXT
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result

SQL Solution:

SQL
SELECT e.* 
FROM employees e 
JOIN ( 
  SELECT department_id, AVG(salary) AS avg_salary 
  FROM employees 
  GROUP BY department_id 
) d ON e.department_id = d.department_id AND 
e.salary = d.avg_salary;

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  3. GROUP BY: Collapses rows into groups. One output row per unique group value.
  4. SELECT: Projects the final columns and evaluates expressions/aliases.

Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.

---

Q18. Find employees who earn more than the average salary of the entire company

Problem: Find employees who earn more than the average salary of the entire company.

How to think before writing:

  1. Can I solve the inner part first as a subquery, then use the result in the outer query?
  2. What condition filters the rows I need? Write the WHERE clause for that condition.

Pseudo query (plain English first):

TEXT
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result

SQL Solution:

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

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.

Optimization: If the subquery runs once per outer row (correlated), consider rewriting as a JOIN or CTE for better performance.

---

Q19. Find employees who have worked on all projects

Problem: Find employees who have worked on all projects.

How to think before writing:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Do I need to filter groups after aggregation? That means HAVING, not WHERE.
  4. Can I solve the inner part first as a subquery, then use the result in the outer query?
  5. Are there duplicates I need to remove? Use DISTINCT.

Pseudo query (plain English first):

TEXT
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result

SQL Solution:

SQL
SELECT employee_id 
FROM project_assignments 
GROUP BY employee_id 
HAVING COUNT(DISTINCT project_id) = (SELECT 
COUNT(*) FROM projects);

Step-by-step how the database executes this:

  1. FROM: Reads table project_assignments.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. HAVING: Filters groups. Only groups passing the condition remain.
  4. SELECT: Projects the final columns and evaluates expressions/aliases.
  5. DISTINCT: Removes duplicate rows by comparing all selected columns.

Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.

---

Q20. List departments with average salary greater than the overall average

Problem: List departments with average salary greater than the overall average.

How to think before writing:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Do I need to filter groups after aggregation? That means HAVING, not WHERE.
  4. Can I solve the inner part first as a subquery, then use the result in the outer query?

Pseudo query (plain English first):

TEXT
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result

SQL Solution:

SQL
SELECT department_id, AVG(salary) AS avg_salary 
FROM employees 
GROUP BY department_id 
HAVING AVG(salary) > (SELECT AVG(salary) 
FROM employees);

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. HAVING: Filters groups. Only groups passing the condition remain.
  4. SELECT: Projects the final columns and evaluates expressions/aliases.

Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.

---

Q21. Find customers who ordered all products in a category

Problem: Find customers who ordered all products in a category.

How to think before writing:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Do I need to filter groups after aggregation? That means HAVING, not WHERE.
  4. Can I solve the inner part first as a subquery, then use the result in the outer query?
  5. Are there duplicates I need to remove? Use DISTINCT.

Pseudo query (plain English first):

TEXT
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result

SQL Solution:

SQL
SELECT customer_id 
FROM sales 
WHERE product_id IN (SELECT product_id FROM 
products WHERE category_id = 1) 
GROUP BY customer_id 
HAVING COUNT(DISTINCT product_id) = (SELECT 
COUNT(*) FROM products WHERE category_id = 1);

Step-by-step how the database executes this:

  1. FROM: Reads table sales.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. GROUP BY: Collapses rows into groups. One output row per unique group value.
  4. HAVING: Filters groups. Only groups passing the condition remain.
  5. SELECT: Projects the final columns and evaluates expressions/aliases.
  6. DISTINCT: Removes duplicate rows by comparing all selected columns.

Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.

---

Q22. Find the second highest salary in the company

Problem: Find the second highest salary in the company.

How to think before writing:

  1. Can I solve the inner part first as a subquery, then use the result in the outer query?
  2. What condition filters the rows I need? Write the WHERE clause for that condition.

Pseudo query (plain English first):

TEXT
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result

SQL Solution:

SQL
SELECT MAX(salary) AS second_highest_salary 
FROM employees 
WHERE salary < (SELECT MAX(salary) FROM 
employees);

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.

Optimization: If the subquery runs once per outer row (correlated), consider rewriting as a JOIN or CTE for better performance.

---

Q23. Find the average number of orders per customer

Problem: Find the average number of orders per customer.

How to think before writing:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Can I solve the inner part first as a subquery, then use the result in the outer query?

Pseudo query (plain English first):

TEXT
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result

SQL Solution:

SQL
SELECT AVG(order_count) AS 
avg_orders_per_customer 
FROM ( 
  SELECT customer_id, COUNT(*) AS order_count 
  FROM orders 
  GROUP BY customer_id 
) sub;

Step-by-step how the database executes this:

  1. FROM: Reads table orders.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.

Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.

---

Q24. List all customers who have never ordered product X

Problem: List all customers who have never ordered product X.

How to think before writing:

  1. Can I solve the inner part first as a subquery, then use the result in the outer query?
  2. What condition filters the rows I need? Write the WHERE clause for that condition.
  3. Are there duplicates I need to remove? Use DISTINCT.

Pseudo query (plain English first):

TEXT
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result

SQL Solution:

SQL
SELECT customer_id 
FROM customers 
WHERE customer_id NOT IN ( 
  SELECT DISTINCT customer_id 
  FROM sales 
  WHERE product_id = 'X' 
);

Step-by-step how the database executes this:

  1. FROM: Reads table customers.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. DISTINCT: Removes duplicate rows by comparing all selected columns.

Optimization: If the subquery runs once per outer row (correlated), consider rewriting as a JOIN or CTE for better performance.

---

Q25. Find employees who have the same salary as the average salary in their job title

Problem: Find employees who have the same salary as the average salary in their job title.

How to think before writing:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Which tables do I need? How are they related? What column connects them?
  4. Can I solve the inner part first as a subquery, then use the result in the outer query?

Pseudo query (plain English first):

TEXT
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result

SQL Solution:

SQL
SELECT e.* 
FROM employees e 
JOIN ( 
  SELECT job_title, AVG(salary) AS avg_salary 
  FROM employees 
  GROUP BY job_title 
) j ON e.job_title = j.job_title 
WHERE e.salary = j.avg_salary;

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. GROUP BY: Collapses rows into groups. One output row per unique group value.
  5. SELECT: Projects the final columns and evaluates expressions/aliases.

Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.

---

Q26. Find products never ordered

Problem: Find products never ordered.

How to think before writing:

  1. Can I solve the inner part first as a subquery, then use the result in the outer query?
  2. What condition filters the rows I need? Write the WHERE clause for that condition.
  3. Are there duplicates I need to remove? Use DISTINCT.

Pseudo query (plain English first):

TEXT
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result

SQL Solution:

SQL
SELECT product_id 
FROM products 
WHERE product_id NOT IN (SELECT DISTINCT 
product_id FROM sales);

Step-by-step how the database executes this:

  1. FROM: Reads table products.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. DISTINCT: Removes duplicate rows by comparing all selected columns.

Optimization: If the subquery runs once per outer row (correlated), consider rewriting as a JOIN or CTE for better performance.

---

Q27. Find employees who have been promoted but their salary didn’t increase

Problem: Find employees who have been promoted but their salary didn’t increase.

How to think before writing:

  1. Which tables do I need? How are they related? What column connects them?
  2. Can I solve the inner part first as a subquery, then use the result in the outer query?
  3. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result

SQL Solution:

SQL
SELECT e.id, e.name 
FROM employees e 
JOIN promotions p ON e.id = p.employee_id 
WHERE e.salary <= (SELECT salary_before FROM 
promotion_history WHERE employee_id = e.id 
ORDER BY promotion_date DESC LIMIT 1);

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. SELECT: Projects the final columns and evaluates expressions/aliases.
  5. ORDER BY: Sorts the final result set.
  6. LIMIT/TOP: Returns only the first N rows from the sorted result.

Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.

---

Q28. Find employees who have never been promoted

Problem: Find employees who have never been promoted.

How to think before writing:

  1. Can I solve the inner part first as a subquery, then use the result in the outer query?
  2. What condition filters the rows I need? Write the WHERE clause for that condition.
  3. Are there duplicates I need to remove? Use DISTINCT.

Pseudo query (plain English first):

TEXT
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result

SQL Solution:

SQL
SELECT * 
FROM employees 
WHERE id NOT IN (SELECT DISTINCT 
employee_id FROM promotions);

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. DISTINCT: Removes duplicate rows by comparing all selected columns.

Optimization: If the subquery runs once per outer row (correlated), consider rewriting as a JOIN or CTE for better performance.

---

Q29. Find products ordered by all customers

Problem: Find products ordered by all customers.

How to think before writing:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Do I need to filter groups after aggregation? That means HAVING, not WHERE.
  4. Can I solve the inner part first as a subquery, then use the result in the outer query?
  5. Are there duplicates I need to remove? Use DISTINCT.

Pseudo query (plain English first):

TEXT
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result

SQL Solution:

SQL
SELECT product_id 
FROM sales 
GROUP BY product_id 
HAVING COUNT(DISTINCT customer_id) = 
(SELECT COUNT(*) FROM customers);

Step-by-step how the database executes this:

  1. FROM: Reads table sales.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. HAVING: Filters groups. Only groups passing the condition remain.
  4. SELECT: Projects the final columns and evaluates expressions/aliases.
  5. DISTINCT: Removes duplicate rows by comparing all selected columns.

Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.

---

Q30. Find all managers who do not manage any employee

Problem: Find all managers who do not manage any employee.

How to think before writing:

  1. Can I solve the inner part first as a subquery, then use the result in the outer query?
  2. What condition filters the rows I need? Write the WHERE clause for that condition.
  3. Are there duplicates I need to remove? Use DISTINCT.

Pseudo query (plain English first):

TEXT
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result

SQL Solution:

SQL
SELECT DISTINCT manager_id 
FROM employees 
WHERE manager_id NOT IN (SELECT DISTINCT id 
FROM employees);

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. DISTINCT: Removes duplicate rows by comparing all selected columns.

Optimization: If the subquery runs once per outer row (correlated), consider rewriting as a JOIN or CTE for better performance.

---

Key Takeaway

This section covered 30 interview questions on Subqueries: Scalar, Correlated, EXISTS, IN, UNION. Each question followed the same learning pattern: understand the problem, think about the approach, write a pseudo query in plain English, translate to SQL, understand the execution order, and consider optimization.

A subquery is a SELECT nested inside another query. A scalar subquery returns one value. A correlated subquery references the outer query and runs once per outer row. EXISTS returns true if the subquery has rows. IN checks membership in a list. UNION combines results from multiple SELECTs.

Practice each query on the shared sample table. If you can solve all 30 without looking at the answer, you have mastered this concept.

---

Share
Use ← → arrow keys to navigate