SQL Subqueries Practice Questions with Solutions

A SQL Subquery (also called an Inner Query or Nested Query) is a query written inside another SQL query.

The inner query executes first, and its result is then used by the outer query.

Subqueries make SQL more powerful by allowing you to retrieve data based on the result of another query. SQL Subqueries practice questions with solutions help to understand the concepts.

For example, suppose you want to find students who scored more than the average marks.

Instead of calculating the average manually, SQL can do it automatically using a subquery.

SELECT student_name,
       marks

FROM students

WHERE marks >
(
    SELECT AVG(marks)
    FROM students
);

This query first calculates the average marks and then displays only students whose marks are higher than that average.

Why Use SQL Subqueries?

Subqueries help you:

• Retrieve dynamic values
• Avoid writing multiple queries
• Perform advanced filtering
• Build analytical reports
• Solve interview-level SQL problems
• Improve query flexibility

They are commonly used in:

• Banking Systems
• HR Management
• Sales Reports
• E-commerce Applications
• Data Analytics
• Business Intelligence

Types of SQL Subqueries

Sample Table Used Throughout This Chapter

students

1. SQL Query to Display Students Scoring Above Average Marks

Problem Statement

The examination department wants to identify students whose marks are higher than the average marks of all students.

Write an SQL query using a subquery.

SQL Solution

SELECT student_name,
       marks

FROM students

WHERE marks >
(
    SELECT AVG(marks)
    FROM students
);

Sample Output

Explanation

The subquery:

SELECT AVG(marks)
FROM students;

calculates the average marks.

The outer query then compares every student’s marks with this average and returns only those students who scored higher.

Concepts Covered

• Single-row Subquery
• AVG()
• WHERE Clause

2. SQL Query to Display Students Having the Highest Marks

Problem Statement

The principal wants to display the student(s) who scored the highest marks.

Write an SQL query using a subquery.

SQL Solution

SELECT student_name,
       marks

FROM students

WHERE marks =
(
    SELECT MAX(marks)
    FROM students
);

Sample Output

Explanation

The subquery finds the highest marks.

SELECT MAX(marks)
FROM students;

The outer query returns the student whose marks match this value.

If multiple students have the same highest marks, SQL returns all of them.

Concepts Covered

• MAX()
• Single-row Subquery
• WHERE Clause

3. SQL Query to Display Students Enrolled in the Same Course as Rahul

Problem Statement

The institute wants to display all students who are enrolled in the same course as Rahul.

Write an SQL query using a subquery.

SQL Solution

SELECT student_name,
       course

FROM students

WHERE course =
(
    SELECT course

    FROM students

    WHERE student_name = 'Rahul'
);

Sample Output

Explanation

The subquery first determines Rahul’s course.

SELECT course
FROM students
WHERE student_name = 'Rahul';

The outer query then displays every student enrolled in that course.

This type of query is commonly used in:

• Student Management Systems
• HR Databases
• CRM Applications

Concepts Covered

• Single-row Subquery
• Dynamic Filtering
• WHERE Clause

4. SQL Query to Display Employees Earning More Than the Average Salary

Problem Statement

An HR manager wants to identify employees whose salary is higher than the company’s average salary.

Write an SQL query using a subquery.

Sample Table

employees

SQL Solution

SELECT
    employee_name,
    department,
    salary

FROM employees

WHERE salary >
(
    SELECT AVG(salary)

    FROM employees
);

Sample Output

Explanation

The subquery:

SELECT AVG(salary)

FROM employees;

calculates the average salary.

The outer query returns employees earning more than the average salary.

This type of query is commonly used in:

• HR Dashboards
• Payroll Reports
• Salary Analysis
• Performance Reviews

Concepts Covered

• AVG()
• Single-row Subquery
• Employee Database

5. SQL Query to Display Products Costing More Than the Average Product Price

Problem Statement

An e-commerce company wants to identify products that are more expensive than the average product price.

Write an SQL query using a subquery.

Sample Table

products

SQL Solution

SELECT
    product_name,
    category,
    price

FROM products

WHERE price >
(
    SELECT AVG(price)

    FROM products
);

Sample Output

Explanation

The subquery first computes the average price of all products.

SELECT AVG(price)

FROM products;

The outer query compares each product’s price with the average and returns only those products priced above it.

This query is useful for:

• Pricing Analysis
• Inventory Reports
• Product Segmentation
• Sales Dashboards

Concepts Covered

• AVG()
• Single-row Subquery
• Product Database

6. SQL Query to Display Employees Working in Departments Located in Delhi

Problem Statement

A company stores employee details and department details in separate tables.

The HR manager wants to display employees who work in departments located in Delhi.

Write an SQL query using a multi-row subquery.

Sample Tables

employees

departments

SQL Solution

SELECT
    employee_name

FROM employees

WHERE department_id IN
(
    SELECT department_id

    FROM departments

    WHERE city = 'Delhi'
);

Sample Output

Explanation

The inner query returns all department IDs located in Delhi.

SELECT department_id
FROM departments
WHERE city='Delhi';

Result:

1
3

The outer query displays employees whose department_id is 1 or 3.

Concepts Covered

• Multi-row Subquery
• IN Operator
• WHERE Clause

7. SQL Query to Display Products Belonging to Premium Categories

Problem Statement

An online store wants to display products that belong to Premium categories.

Sample Tables

products

categories

SQL Solution

SELECT
    product_name

FROM products

WHERE category_id IN
(
    SELECT category_id

    FROM categories

    WHERE category_type = 'Premium'
);

Sample Output

Explanation

The inner query finds all Premium category IDs.

The outer query returns products whose category belongs to those IDs.

Concepts Covered

• IN Operator
• Multi-row Subquery
• Product Database

8. SQL Query to Display Students Enrolled in Popular Courses

Problem Statement

A training institute marks some courses as Popular.

Display students enrolled in those courses.

Sample Tables

students

courses

SQL Solution

SELECT
    student_name

FROM students

WHERE course_id IN
(
    SELECT course_id

    FROM courses

    WHERE category = 'Popular'
);

Sample Output

Explanation

The subquery returns the IDs of Popular courses.

The outer query displays students enrolled in those courses.

Concepts Covered

• IN
• Multi-row Subquery
• Student Database

9. SQL Query to Display Customers Who Purchased Electronic Products

Problem Statement

An online shopping company wants to identify customers who purchased products from the Electronics category.

Sample Tables

orders

products

SQL Solution

SELECT
    customer_name

FROM orders

WHERE product_id IN
(
    SELECT product_id

    FROM products

    WHERE category = 'Electronics'
);

Sample Output

Explanation

The inner query identifies products that belong to the Electronics category.

The outer query displays customers who purchased those products.

Concepts Covered

• Multi-row Subquery
• IN
• E-commerce Database

10. SQL Query to Display Books Written by Award-Winning Authors

Problem Statement

A library wants to display books written by authors who have won literary awards.

Sample Tables

books

authors

SQL Solution

SELECT
    book_title

FROM books

WHERE author_id IN
(
    SELECT author_id

    FROM authors

    WHERE award_winner = 'Yes'
);

Sample Output

Explanation

The subquery returns all award-winning author IDs.

The outer query displays books written by those authors.

Concepts Covered

• Multi-row Subquery
• IN Operator
• Library Database

11. SQL Query Using EXISTS to Display Customers Who Have Placed Orders

Problem Statement

An online shopping company wants to display only those customers who have placed at least one order.

Write an SQL query using the EXISTS operator.

Sample Tables

customers

orders

SQL Solution

SELECT
    customer_name

FROM customers c

WHERE EXISTS
(
    SELECT 1

    FROM orders o

    WHERE o.customer_id = c.customer_id
);

Sample Output

Explanation

The EXISTS operator checks whether the subquery returns at least one row.

For each customer:

• If an order exists → the customer is displayed.
• If no order exists → the customer is ignored.

Unlike IN, EXISTS is often preferred for large datasets because it stops searching after finding the first matching record.

Concepts Covered

• EXISTS
• Correlated Subquery
• Customer Database

12. SQL Query Using NOT EXISTS to Display Customers Without Orders

Problem Statement

The marketing team wants to identify customers who have never placed an order.

Write an SQL query using NOT EXISTS.

SQL Solution

SELECT
    customer_name

FROM customers c

WHERE NOT EXISTS
(
    SELECT 1

    FROM orders o

    WHERE o.customer_id = c.customer_id
);

Sample Output

Explanation

The NOT EXISTS operator returns rows where the subquery finds no matching records.

This query is commonly used for:

• Customer Retention Campaigns
• Inactive User Reports
• Email Marketing
• Sales Analysis

Concepts Covered

• NOT EXISTS
• Correlated Subquery
• Customer Analytics

13. SQL Correlated Subquery to Display Employees Earning More Than Their Department Average

Problem Statement

A company wants to identify employees whose salary is higher than the average salary of their own department.

Write an SQL query using a correlated subquery.

Sample Table

employees

SQL Solution

SELECT
    e1.employee_name,
    e1.department,
    e1.salary

FROM employees e1

WHERE salary >
(
    SELECT AVG(e2.salary)

    FROM employees e2

    WHERE e1.department = e2.department
);

Sample Output

Explanation

This is a correlated subquery because the inner query depends on each row of the outer query.

For every employee:

1. SQL calculates the average salary of that employee’s department.
2. It compares the employee’s salary with the department average.
3. Employees earning more than the average are returned.

Concepts Covered

• Correlated Subquery
• AVG()
• Department Analysis

14. SQL Subquery in the FROM Clause

Problem Statement

A company wants to calculate the average salary of employees whose salary is greater than ₹50,000.

Write an SQL query using a subquery in the FROM clause.

SQL Solution

SELECT
    AVG(salary) AS average_salary

FROM
(
    SELECT salary

    FROM employees

    WHERE salary > 50000
) AS high_salary;

Sample Output

Explanation

The inner query creates a temporary table named high_salary.

The outer query calculates the average salary from that temporary result.

This technique is useful when complex filtering needs to be performed before aggregation.

Concepts Covered

• FROM Subquery
• Aggregate Functions
• Temporary Result Set

15. SQL Subquery in the SELECT Clause

Problem Statement

The HR department wants to display every employee along with the overall average company salary.

Write an SQL query using a subquery in the SELECT clause.

SQL Solution

SELECT
    employee_name,
    salary,

    (
        SELECT AVG(salary)

        FROM employees
    ) AS company_average_salary

FROM employees;

Sample Output

Explanation

The subquery calculates the overall average salary once.

That value is displayed alongside every employee record.

This approach is useful in:

• HR Reports
• Business Dashboards
• Salary Comparisons
• Performance Analysis

Concepts Covered

• SELECT Subquery
• Aggregate Functions
• Reporting Queries

Chapter Summary

In this chapter, you learned how to use SQL Subqueries to solve problems where one query depends on the result of another query.

A subquery is simply a query inside another SQL query. It helps you write dynamic, flexible, and powerful SQL statements without manually calculating intermediate values.

Throughout this chapter, you practiced:

• Single-row subqueries using aggregate functions
• Multi-row subqueries with the IN operator
• Correlated subqueries
• Using the EXISTS operator
• Using the NOT EXISTS operator
• Subqueries inside the SELECT clause
• Subqueries inside the FROM clause

These techniques are widely used in real-world database applications for reporting, filtering, and business analytics.

Key Takeaways

• A subquery is a query inside another SQL query.
• The inner query executes before the outer query.
• Single-row subqueries return one value.
• Multi-row subqueries return multiple values.
• Use IN when the subquery returns multiple rows.
• Use EXISTS to check whether matching records exist.
• Use NOT EXISTS to find missing records.
• Correlated subqueries execute once for each row of the outer query.
• Subqueries can be written inside the SELECT, FROM, and WHERE clauses.
• Subqueries are frequently used in SQL interviews and business reporting.

Frequently Asked Questions (FAQs)

1. What is a SQL Subquery?

A SQL subquery is a query written inside another SQL query.

Example:

SELECT student_name,
       marks

FROM students

WHERE marks >
(
    SELECT AVG(marks)
    FROM students
);

2. What is the difference between a single-row subquery and a multi-row subquery?

3. When should I use the IN operator with a subquery?

Use IN when the inner query returns multiple values.

Example:

SELECT employee_name

FROM employees

WHERE department_id IN
(
    SELECT department_id

    FROM departments

    WHERE city = 'Delhi'
);

4. What is a correlated subquery?

A correlated subquery depends on the current row of the outer query and executes once for every row.

Example:

SELECT employee_name

FROM employees e1

WHERE salary >
(
    SELECT AVG(e2.salary)

    FROM employees e2

    WHERE e1.department = e2.department
);

5. What is the difference between EXISTS and IN?

• IN compares values returned by a subquery.
• EXISTS checks whether the subquery returns at least one matching row.

EXISTS is generally more efficient when working with large datasets because it stops searching after finding the first match.

6. Can a subquery be written inside the SELECT clause?

Yes.

Example:

SELECT
    employee_name,
    salary,

    (
        SELECT AVG(salary)

        FROM employees
    ) AS company_average

FROM employees;

7. Can a subquery be written inside the FROM clause?

Yes.

Example:

SELECT AVG(salary)

FROM
(
    SELECT salary

    FROM employees

    WHERE salary > 50000
) AS high_salary;

8. Where are SQL subqueries used in real-world applications?

SQL subqueries are commonly used in:

• Banking Systems
• HR Management
• Inventory Management
• Student Portals
• CRM Software
• E-commerce Platforms
• Business Intelligence Dashboards
• Financial Reporting
• Sales Analytics
• Data Warehousing

Written by Shubhranshu Shekhar, who has trained 20000+ students in coding.

Rishabh Kumar