PostgreSQL: SQL Subqueries Exercises (With Answers)

This guide is specifically for PostgreSQL syntax.

Learn SQL Subqueries with hands-on practice exercises. Each exercise includes the table schema, solution query, expected output, and detailed explanation.

What You'll Practice

These exercises cover Subqueries from basic to advanced concepts. Work through each problem to build your SQL skills.

Exercise 1

Question: Find employees who earn more than the average salary.

Table Schema

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

Solution

SELECT name, salary
FROM employees
WHERE 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

Question: Find products that have never been ordered.

Table Schema

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

Solution

SELECT name
FROM products
WHERE 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

Question: Find the second highest salary in the company.

Table Schema

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

Solution

SELECT MAX(salary) AS second_highest
FROM employees
WHERE 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.

Common Mistakes to Avoid

• Forgetting to use proper syntax for Subqueries
• Not considering NULL values in comparisons
• Confusing similar operators or clauses

Related SQL Exercises

Continue practicing with these related topics:

PostgreSQL-Specific Notes

This page covers PostgreSQL syntax. Other databases may have different syntax for similar operations.