Why subqueries are needed in PostgreSQL - See It in Action

Why Subqueries Are Needed in PostgreSQL

📖 Scenario: You work at a bookstore that keeps track of sales and books in a PostgreSQL database. You want to find out which books have sold more copies than the average number of copies sold across all books.

🎯 Goal: Build a query using a subquery to find books with sales above the average sales.

📋 What You'll Learn

Create a table called books with columns book_id (integer) and title (text).

Create a table called sales with columns sale_id (integer), book_id (integer), and copies_sold (integer).

Insert given sample data into both tables.

Write a subquery to calculate the average copies sold across all books.

Write a main query that uses the subquery to find books with copies sold greater than the average.

💡 Why This Matters

🌍 Real World

Subqueries help answer complex questions like comparing individual sales to overall averages in business databases.

💼 Career

Knowing subqueries is essential for database analysts and developers to write efficient and powerful SQL queries.

1

Create tables and insert sample data

Create a table called books with columns book_id (integer) and title (text). Then create a table called sales with columns sale_id (integer), book_id (integer), and copies_sold (integer). Insert these exact rows into books: (1, 'Learn SQL'), (2, 'Mastering PostgreSQL'), (3, 'Database Basics'). Insert these exact rows into sales: (1, 1, 150), (2, 2, 200), (3, 3, 100).

PostgreSQL

-- Create tables books and sales and insert data
-- Your code here

Hint

Use CREATE TABLE statements for both tables. Use INSERT INTO to add the exact rows.

2

Calculate average copies sold with a subquery

Write a subquery that calculates the average of copies_sold from the sales table and assign it to a variable called avg_sales using a WITH clause.

PostgreSQL

CREATE TABLE books (book_id INTEGER, title TEXT);
CREATE TABLE sales (sale_id INTEGER, book_id INTEGER, copies_sold INTEGER);

INSERT INTO books (book_id, title) VALUES
(1, 'Learn SQL'),
(2, 'Mastering PostgreSQL'),
(3, 'Database Basics');

INSERT INTO sales (sale_id, book_id, copies_sold) VALUES
(1, 1, 150),
(2, 2, 200),
(3, 3, 100);

-- Create a WITH clause named avg_sales to calculate average copies_sold
-- Your code here

Hint

Use a WITH clause to create a temporary result named avg_sales that calculates the average.

3

Write the main query using the subquery

Write a main query that selects title and copies_sold from books joined with sales. Use the subquery avg_sales to filter books where copies_sold is greater than the average. Use avg_sales.average in the WHERE clause.

PostgreSQL

CREATE TABLE books (book_id INTEGER, title TEXT);
CREATE TABLE sales (sale_id INTEGER, book_id INTEGER, copies_sold INTEGER);

INSERT INTO books (book_id, title) VALUES
(1, 'Learn SQL'),
(2, 'Mastering PostgreSQL'),
(3, 'Database Basics');

INSERT INTO sales (sale_id, book_id, copies_sold) VALUES
(1, 1, 150),
(2, 2, 200),
(3, 3, 100);

WITH avg_sales AS (
  SELECT AVG(copies_sold) AS average FROM sales
)
-- Write a SELECT query joining books and sales
-- Filter where copies_sold > avg_sales.average
-- Your code here

Hint

Join books and sales on book_id. Use the subquery avg_sales in the WHERE clause to filter.

4

Explain why subqueries are needed

Add a comment explaining why subqueries like avg_sales are needed in SQL queries.

PostgreSQL

CREATE TABLE books (book_id INTEGER, title TEXT);
CREATE TABLE sales (sale_id INTEGER, book_id INTEGER, copies_sold INTEGER);

INSERT INTO books (book_id, title) VALUES
(1, 'Learn SQL'),
(2, 'Mastering PostgreSQL'),
(3, 'Database Basics');

INSERT INTO sales (sale_id, book_id, copies_sold) VALUES
(1, 1, 150),
(2, 2, 200),
(3, 3, 100);

WITH avg_sales AS (
  SELECT AVG(copies_sold) AS average FROM sales
)
SELECT b.title, s.copies_sold
FROM books b
JOIN sales s ON b.book_id = s.book_id
WHERE s.copies_sold > (SELECT average FROM avg_sales);

-- Add a comment explaining why subqueries are needed
-- Your code here

Hint

Explain that subqueries help calculate values from the whole data and use them in main queries.

Practice

1. Why do we use subqueries in PostgreSQL?
SELECT * FROM employees WHERE department_id IN (SELECT id FROM departments WHERE location = 'NY');

easy

A. To change data types of columns

B. To speed up the database server automatically

C. To ask a question inside another query and filter results based on it

D. To create new tables from existing ones

Solution

1. Step 1: Understand the role of the subquery

   The subquery (SELECT id FROM departments WHERE location = 'NY') finds department IDs located in NY.

2. Step 2: See how the main query uses the subquery

   The main query selects employees whose department_id matches any ID from the subquery result.

3. Final Answer:

   To ask a question inside another query and filter results based on it -> Option C

4. Quick Check:

   Subqueries filter data using inner query results [OK]

Hint: Subqueries answer inner questions to filter outer query [OK]

Common Mistakes:

• Thinking subqueries speed up queries automatically
• Confusing subqueries with table creation
• Using subqueries to change data types

2. Which of the following is the correct syntax for a subquery in PostgreSQL?

easy

A. SELECT * FROM table WHERE column == (SELECT MAX(column) FROM table);

B. SELECT * FROM table WHERE column = (SELECT MAX(column) FROM table);

C. SELECT * FROM table WHERE column = SELECT MAX(column) FROM table;

D. SELECT * FROM table WHERE column IN SELECT MAX(column) FROM table;

Solution

1. Step 1: Identify correct subquery syntax

   Subqueries must be enclosed in parentheses and used with comparison operators like = or IN.

2. Step 2: Check each option

   SELECT * FROM table WHERE column = (SELECT MAX(column) FROM table); correctly uses parentheses and = with a subquery. The other options have syntax errors like using == instead of =, omitting parentheses entirely, or missing them after IN.

3. Final Answer:

   SELECT * FROM table WHERE column = (SELECT MAX(column) FROM table); -> Option B

4. Quick Check:

   Subqueries need parentheses and proper operators [OK]

Hint: Subqueries always go inside parentheses [OK]

Common Mistakes:

• Using double equals (==) instead of single equals (=)
• Omitting parentheses around subqueries
• Using IN without parentheses

3. What will be the output of this query?

SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

medium

A. Syntax error due to subquery

B. Names of employees earning less than the average salary

C. All employee names

D. Names of employees earning more than the average salary

Solution

1. Step 1: Understand the subquery

   The subquery (SELECT AVG(salary) FROM employees) calculates the average salary of all employees.

2. Step 2: Apply the condition in the main query

   The main query selects employee names where their salary is greater than this average.

3. Final Answer:

   Names of employees earning more than the average salary -> Option D

4. Quick Check:

   Subquery returns average; main query filters salaries above it [OK]

Hint: Subqueries can return single values to compare in main query [OK]

Common Mistakes:

• Thinking it returns employees below average
• Assuming syntax error due to subquery
• Confusing AVG with SUM

4. Identify the error in this query:

SELECT name FROM employees WHERE department_id = SELECT id FROM departments WHERE name = 'Sales';

medium

A. Missing parentheses around the subquery

B. Wrong comparison operator used

C. Subquery returns multiple rows causing error

D. Incorrect table name used

Solution

1. Step 1: Check subquery syntax

   The subquery must be enclosed in parentheses to be valid in the WHERE clause.

2. Step 2: Identify the missing parentheses

   The query misses parentheses around SELECT id FROM departments WHERE name = 'Sales', causing syntax error.

3. Final Answer:

   Missing parentheses around the subquery -> Option A

4. Quick Check:

   Subqueries need parentheses to be valid [OK]

Hint: Always put subqueries inside parentheses [OK]

Common Mistakes:

• Forgetting parentheses around subqueries
• Assuming wrong operator causes error
• Thinking table name is incorrect

5. You want to find employees who earn more than the average salary in their own department. Which query correctly uses a subquery to achieve this?

hard

A. SELECT name, salary, department_id FROM employees WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = employees.department_id);

B. SELECT name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

C. SELECT name FROM employees WHERE salary > ALL (SELECT salary FROM employees);

D. SELECT name FROM employees WHERE salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department_id);

Solution

1. Step 1: Understand the requirement

   We need to compare each employee's salary to the average salary of their own department.

2. Step 2: Analyze each option

   SELECT name, salary, department_id FROM employees WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = employees.department_id); uses a correlated subquery that calculates average salary per employee's department. SELECT name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); compares to overall average, ignoring departments. SELECT name FROM employees WHERE salary > ALL (SELECT salary FROM employees); compares salary to all salaries, not averages. SELECT name FROM employees WHERE salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department_id); compares to a list of averages but uses ANY incorrectly.

3. Final Answer:

   SELECT name, salary, department_id FROM employees WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = employees.department_id); -> Option A

4. Quick Check:

   Correlated subquery compares salary to department average [OK]

Hint: Use correlated subqueries to compare within groups [OK]

Common Mistakes:

• Comparing to overall average instead of department average
• Using ALL or ANY incorrectly with averages
• Not correlating subquery with outer query