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

Need a 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

Need a 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

Need a 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

Need a hint?

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