Definition
- 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_id | customer_id | amount | order_date | product | status |
|---|---|---|---|---|---|
| 1001 | C01 | 50000 | 2026-01-15 | HomeLoan | Completed |
| 1002 | C02 | 30000 | 2026-01-20 | CarLoan | Completed |
| 1003 | C01 | 70000 | 2026-02-10 | HomeLoan | Pending |
| 1004 | C03 | 20000 | 2026-02-15 | Personal | Completed |
| 1005 | C02 | 40000 | 2026-03-01 | CarLoan | Cancelled |
customers:
| customer_id | name | city | signup_date |
|---|---|---|---|
| C01 | Priya | Mumbai | 2024-01-10 |
| C02 | Rajesh | Delhi | 2024-03-15 |
| C03 | Sneha | Chennai | 2025-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:
- Can I solve the inner part first as a subquery, then use the result in the outer query?
- What condition filters the rows I need? Write the WHERE clause for that condition.
Pseudo query (plain English first):
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result
SQL Solution:
SELECT MAX(salary) AS SecondHighestSalary
FROM employees
WHERE salary < (
SELECT MAX(salary)
FROM employees
);
Step-by-step how the database executes this:
- FROM: Reads table
employees. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- 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:
- Can I solve the inner part first as a subquery, then use the result in the outer query?
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result
SQL Solution:
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:
- FROM: Reads table
employees. - SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
- 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:
- Can I solve the inner part first as a subquery, then use the result in the outer query?
- What condition filters the rows I need? Write the WHERE clause for that condition.
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result
SQL Solution:
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:
- FROM: Reads table
employees. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- 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:
- Can I solve the inner part first as a subquery, then use the result in the outer query?
- What condition filters the rows I need? Write the WHERE clause for that condition.
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result
SQL Solution:
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM
employees)
ORDER BY salary DESC;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
- Can I solve the inner part first as a subquery, then use the result in the outer query?
- Are there duplicates I need to remove? Use DISTINCT.
Pseudo query (plain English first):
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result
SQL Solution:
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:
- FROM: Reads table
sales. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- 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:
- Can I solve the inner part first as a subquery, then use the result in the outer query?
- What condition filters the rows I need? Write the WHERE clause for that condition.
Pseudo query (plain English first):
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result
SQL Solution:
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:
- FROM: Reads table
employees. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- 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:
- Can I solve the inner part first as a subquery, then use the result in the outer query?
- What condition filters the rows I need? Write the WHERE clause for that condition.
Pseudo query (plain English first):
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result
SQL Solution:
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:
- FROM: Reads table
employees. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Can I solve the inner part first as a subquery, then use the result in the outer query?
Pseudo query (plain English first):
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result
SQL Solution:
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:
- FROM: Reads table
orders. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- 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:
- Can I solve the inner part first as a subquery, then use the result in the outer query?
- What condition filters the rows I need? Write the WHERE clause for that condition.
Pseudo query (plain English first):
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result
SQL Solution:
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:
- FROM: Reads table
employees. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
- Can I solve the inner part first as a subquery, then use the result in the outer query?
- Are there duplicates I need to remove? Use DISTINCT.
Pseudo query (plain English first):
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result
SQL Solution:
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:
- FROM: Reads table
sales. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- 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:
- Can I solve the inner part first as a subquery, then use the result in the outer query?
- What condition filters the rows I need? Write the WHERE clause for that condition.
- Are there duplicates I need to remove? Use DISTINCT.
Pseudo query (plain English first):
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result
SQL Solution:
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:
- FROM: Reads table
orders. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- 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:
- Can I solve the inner part first as a subquery, then use the result in the outer query?
- What condition filters the rows I need? Write the WHERE clause for that condition.
Pseudo query (plain English first):
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result
SQL Solution:
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:
- FROM: Reads table
employees. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
- Which tables do I need? How are they related? What column connects them?
- Do I need to compare rows within the same table? That means a self-join with aliases.
Pseudo query (plain English first):
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result
SQL Solution:
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:
- FROM: Reads table
projects. - INNER JOIN: Combines rows from joined tables using the ON condition.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- 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:
- Can I solve the inner part first as a subquery, then use the result in the outer query?
- What condition filters the rows I need? Write the WHERE clause for that condition.
Pseudo query (plain English first):
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result
SQL Solution:
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:
- FROM: Reads table
employees. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
Pseudo query (plain English first):
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result
SQL Solution:
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:
- FROM: Reads table
employees. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- 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:
- Can I solve the inner part first as a subquery, then use the result in the outer query?
- What condition filters the rows I need? Write the WHERE clause for that condition.
Pseudo query (plain English first):
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result
SQL Solution:
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:
- FROM: Reads table
employees. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Which tables do I need? How are they related? What column connects them?
- Can I solve the inner part first as a subquery, then use the result in the outer query?
Pseudo query (plain English first):
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result
SQL Solution:
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:
- FROM: Reads table
employees. - INNER JOIN: Combines rows from joined tables using the ON condition.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- 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:
- Can I solve the inner part first as a subquery, then use the result in the outer query?
- What condition filters the rows I need? Write the WHERE clause for that condition.
Pseudo query (plain English first):
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result
SQL Solution:
SELECT *
FROM employees
WHERE salary > (SELECT AVG(salary) FROM
employees);
Step-by-step how the database executes this:
- FROM: Reads table
employees. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
- Can I solve the inner part first as a subquery, then use the result in the outer query?
- Are there duplicates I need to remove? Use DISTINCT.
Pseudo query (plain English first):
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result
SQL Solution:
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:
- FROM: Reads table
project_assignments. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
- Can I solve the inner part first as a subquery, then use the result in the outer query?
Pseudo query (plain English first):
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result
SQL Solution:
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:
- FROM: Reads table
employees. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
- Can I solve the inner part first as a subquery, then use the result in the outer query?
- Are there duplicates I need to remove? Use DISTINCT.
Pseudo query (plain English first):
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result
SQL Solution:
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:
- FROM: Reads table
sales. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- 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:
- Can I solve the inner part first as a subquery, then use the result in the outer query?
- What condition filters the rows I need? Write the WHERE clause for that condition.
Pseudo query (plain English first):
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result
SQL Solution:
SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM
employees);
Step-by-step how the database executes this:
- FROM: Reads table
employees. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Can I solve the inner part first as a subquery, then use the result in the outer query?
Pseudo query (plain English first):
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result
SQL Solution:
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:
- FROM: Reads table
orders. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- 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:
- Can I solve the inner part first as a subquery, then use the result in the outer query?
- What condition filters the rows I need? Write the WHERE clause for that condition.
- Are there duplicates I need to remove? Use DISTINCT.
Pseudo query (plain English first):
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result
SQL Solution:
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:
- FROM: Reads table
customers. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Which tables do I need? How are they related? What column connects them?
- Can I solve the inner part first as a subquery, then use the result in the outer query?
Pseudo query (plain English first):
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result
SQL Solution:
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:
- FROM: Reads table
employees. - INNER JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- 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:
- Can I solve the inner part first as a subquery, then use the result in the outer query?
- What condition filters the rows I need? Write the WHERE clause for that condition.
- Are there duplicates I need to remove? Use DISTINCT.
Pseudo query (plain English first):
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result
SQL Solution:
SELECT product_id
FROM products
WHERE product_id NOT IN (SELECT DISTINCT
product_id FROM sales);
Step-by-step how the database executes this:
- FROM: Reads table
products. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- 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:
- Which tables do I need? How are they related? What column connects them?
- Can I solve the inner part first as a subquery, then use the result in the outer query?
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result
SQL Solution:
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:
- FROM: Reads table
employees. - INNER JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
- 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:
- Can I solve the inner part first as a subquery, then use the result in the outer query?
- What condition filters the rows I need? Write the WHERE clause for that condition.
- Are there duplicates I need to remove? Use DISTINCT.
Pseudo query (plain English first):
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result
SQL Solution:
SELECT *
FROM employees
WHERE id NOT IN (SELECT DISTINCT
employee_id FROM promotions);
Step-by-step how the database executes this:
- FROM: Reads table
employees. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
- Can I solve the inner part first as a subquery, then use the result in the outer query?
- Are there duplicates I need to remove? Use DISTINCT.
Pseudo query (plain English first):
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result
SQL Solution:
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:
- FROM: Reads table
sales. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- 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:
- Can I solve the inner part first as a subquery, then use the result in the outer query?
- What condition filters the rows I need? Write the WHERE clause for that condition.
- Are there duplicates I need to remove? Use DISTINCT.
Pseudo query (plain English first):
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result
SQL Solution:
SELECT DISTINCT manager_id
FROM employees
WHERE manager_id NOT IN (SELECT DISTINCT id
FROM employees);
Step-by-step how the database executes this:
- FROM: Reads table
employees. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- 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.
---