SQL Exercises

SQL Subqueries Exercises (With Answers)

12 Exercises
~60 min
8 Intermediate4 Advanced

Exercise 1

intermediate

Question

Find employees who earn more than the average salary.

Table Schema

SQL
CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  salary DECIMAL(10,2)
);
Show Solution

Solution

SQL
1SELECT name, salary
2FROM employees
3WHERE salary > (SELECT AVG(salary) FROM employees);

Expected Output

| name | salary |
|---|---|
| Alice | 75000 |
| Bob | 80000 |

Explanation

A scalar subquery returns a single value and can be used with comparison operators in WHERE clause.

Exercise 1

intermediate

Question

Find employees who earn more than the average salary.

Table Schema

SQL
CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  salary DECIMAL(10,2)
);
Show Solution

Solution

SQL
1SELECT name, salary
2FROM employees
3WHERE salary > (SELECT AVG(salary) FROM employees);

Expected Output

| name | salary |
|---|---|
| Alice | 75000 |
| Bob | 80000 |

Explanation

A scalar subquery returns a single value and can be used with comparison operators in WHERE clause.

Exercise 1

intermediate

Question

Find employees who earn more than the average salary.

Table Schema

SQL
CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  salary DECIMAL(10,2)
);
Show Solution

Solution

SQL
1SELECT name, salary
2FROM employees
3WHERE salary > (SELECT AVG(salary) FROM employees);

Expected Output

| name | salary |
|---|---|
| Alice | 75000 |
| Bob | 80000 |

Explanation

A scalar subquery returns a single value and can be used with comparison operators in WHERE clause.

Exercise 1

intermediate

Question

Find employees who earn more than the average salary.

Table Schema

SQL
CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  salary DECIMAL(10,2)
);
Show Solution

Solution

SQL
1SELECT name, salary
2FROM employees
3WHERE salary > (SELECT AVG(salary) FROM employees);

Expected Output

| name | salary |
|---|---|
| Alice | 75000 |
| Bob | 80000 |

Explanation

A scalar subquery returns a single value and can be used with comparison operators in WHERE clause.

Exercise 2

intermediate

Question

Find products that have never been ordered.

Table Schema

SQL
CREATE TABLE products (id INT, name VARCHAR(100));
CREATE TABLE order_items (id INT, product_id INT);
Show Solution

Solution

SQL
1SELECT name
2FROM products
3WHERE id NOT IN (SELECT DISTINCT product_id FROM order_items);

Expected Output

| name |
|---|
| Discontinued Item |

Explanation

Subquery with NOT IN finds values that don't exist in another table's results.

Exercise 2

intermediate

Question

Find products that have never been ordered.

Table Schema

SQL
CREATE TABLE products (id INT, name VARCHAR(100));
CREATE TABLE order_items (id INT, product_id INT);
Show Solution

Solution

SQL
1SELECT name
2FROM products
3WHERE id NOT IN (SELECT DISTINCT product_id FROM order_items);

Expected Output

| name |
|---|
| Discontinued Item |

Explanation

Subquery with NOT IN finds values that don't exist in another table's results.

Exercise 2

intermediate

Question

Find products that have never been ordered.

Table Schema

SQL
CREATE TABLE products (id INT, name VARCHAR(100));
CREATE TABLE order_items (id INT, product_id INT);
Show Solution

Solution

SQL
1SELECT name
2FROM products
3WHERE id NOT IN (SELECT DISTINCT product_id FROM order_items);

Expected Output

| name |
|---|
| Discontinued Item |

Explanation

Subquery with NOT IN finds values that don't exist in another table's results.

Exercise 2

intermediate

Question

Find products that have never been ordered.

Table Schema

SQL
CREATE TABLE products (id INT, name VARCHAR(100));
CREATE TABLE order_items (id INT, product_id INT);
Show Solution

Solution

SQL
1SELECT name
2FROM products
3WHERE id NOT IN (SELECT DISTINCT product_id FROM order_items);

Expected Output

| name |
|---|
| Discontinued Item |

Explanation

Subquery with NOT IN finds values that don't exist in another table's results.

Exercise 3

advanced

Question

Find the second highest salary in the company.

Table Schema

SQL
CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  salary DECIMAL(10,2)
);
Show Solution

Solution

SQL
1SELECT MAX(salary) AS second_highest
2FROM employees
3WHERE salary < (SELECT MAX(salary) FROM employees);

Expected Output

| second_highest |
|---|
| 75000 |

Explanation

Nested subqueries can solve complex problems. Here we find the max salary that is less than the overall max.

Exercise 3

advanced

Question

Find the second highest salary in the company.

Table Schema

SQL
CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  salary DECIMAL(10,2)
);
Show Solution

Solution

SQL
1SELECT MAX(salary) AS second_highest
2FROM employees
3WHERE salary < (SELECT MAX(salary) FROM employees);

Expected Output

| second_highest |
|---|
| 75000 |

Explanation

Nested subqueries can solve complex problems. Here we find the max salary that is less than the overall max.

Exercise 3

advanced

Question

Find the second highest salary in the company.

Table Schema

SQL
CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  salary DECIMAL(10,2)
);
Show Solution

Solution

SQL
1SELECT MAX(salary) AS second_highest
2FROM employees
3WHERE salary < (SELECT MAX(salary) FROM employees);

Expected Output

| second_highest |
|---|
| 75000 |

Explanation

Nested subqueries can solve complex problems. Here we find the max salary that is less than the overall max.

Exercise 3

advanced

Question

Find the second highest salary in the company.

Table Schema

SQL
CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  salary DECIMAL(10,2)
);
Show Solution

Solution

SQL
1SELECT MAX(salary) AS second_highest
2FROM employees
3WHERE salary < (SELECT MAX(salary) FROM employees);

Expected Output

| second_highest |
|---|
| 75000 |

Explanation

Nested subqueries can solve complex problems. Here we find the max salary that is less than the overall max.

Related Content

Continue Learning

exercisePostgreSQL: SQL Subqueries Exercises (With Answers)
exerciseMySQL: SQL Subqueries Exercises (With Answers)
exerciseSQL Server: SQL Subqueries Exercises (With Answers)
exerciseIntermediate SQL Subqueries Exercises
exerciseSQL SELECT Statement Exercises (With Answers)

From Our Blog

Introduction to SubqueriesWhy Subqueries Can Be SlowCTEs vs. Subqueries: A Comparison

Ready for more practice?

Join SQL Mastery and get access to interactive exercises, quizzes, and more.