Design and Build a Bookstore Database

Design a normalized PostgreSQL database schema from requirements to complex queries.

Introduction

30 Second Summary

Every time you search for a book online, filter by author, or check out with multiple items in your cart, a database is making that happen in milliseconds. The way that database is designed determines whether the store runs smoothly or falls apart under pressure.

In this project, you will design and build a complete PostgreSQL database for an online bookstore called "PageTurn Books." You'll go from business requirements through ER modeling and normalization all the way to complex analytical queries.

What You'll Build

A normalized PostgreSQL database with seven interconnected tables, realistic sample data, and a library of progressively complex queries spanning JOINs, aggregations, and subqueries.

By the end of this project, you'll have:

  • A fully designed and normalized schema built from business requirements through ER modeling to DDL.
  • A populated database with realistic sample data and 10+ queries of increasing complexity that demonstrate what Django ORM and Spring Data JPA generate under the hood.
  • Understanding of the real-world schema design workflow: requirements, entities, relationships, normalization, then DDL.
  • Secret Mission: A wishlists feature that requires modifying your live database with ALTER TABLE and new junction tables.

Are there any prerequisites?

You should have basic SQL knowledge (SELECT, INSERT) and familiarity with relational database concepts. You should also have PostgreSQL and VS Code already installed on your machine.

Set Up Your Project

You have VS Code and the PostgreSQL extension ready. Now you need to connect VS Code to your local PostgreSQL server and create a dedicated database for this project.

You'll also create a project folder with separate SQL files for your schema, sample data, and queries. This keeps your work organized and version-controllable, just like a professional workflow.

In this step, get ready to:
  • Connect VS Code to your local PostgreSQL server.
  • Create the bookstore database.
  • Set up your project folder with SQL files.
Connect to your local PostgreSQL server

The PostgreSQL extension lets you manage databases directly from your editor. You need to tell it where your server is and how to authenticate.

  • Press Ctrl+Alt+D (Windows/Linux) or open the Command Palette (Cmd+Shift+P on macOS) and search for PGSQL: Focus on Connections View to open the PostgreSQL view.
  • Hover over the Connections section header and click the + (Add New Connection) button.
  • The connection dialog opens. Fill in the following fields:
  • Server name: localhost.
  • Authentication Type: Password.
  • User name: postgres.
  • Password: the password you set when you installed PostgreSQL.
  • Click Save & Connect.

Your server should now appear in the Connections tree with a green icon.

What did Save & Connect do?

It saved your connection profile so you don't have to re-enter it next time, and it immediately connected to the server. The green icon in the Connections tree confirms a live connection.

Can't connect to localhost?

Make sure your PostgreSQL server is running. On Windows, check that the postgresql-x64-18 service is started in Windows Services. On macOS, check that PostgreSQL was started via Homebrew (brew services list).

Double-check that your password matches what you set during installation. If you forgot it, you may need to reset it via your system's PostgreSQL configuration.

Help me troubleshoot my PostgreSQL connection

Create the bookstore database

PostgreSQL starts with a default postgres database. You need a separate database for this project so your bookstore tables stay isolated and organized.

  • Right-click your server node in the Connections tree and select New Query.
  • Create the bookstore database by typing the following SQL in the query editor and pressing Ctrl+Shift+E (Windows/Linux) or Cmd+Shift+E (macOS) to run it:
CREATE DATABASE bookstore;

What does this command do?

CREATE DATABASE tells PostgreSQL to create a new, empty database called bookstore. All the tables, data, and queries you build in this project will live inside this database.

  • Right-click the server node in the Connections tree and select Refresh.
  • Expand the server node. You should see bookstore listed alongside the default postgres database.

Now switch your query editor to target the new database.

  • In the query editor toolbar, click the database dropdown and select bookstore.

Keep this editor tab open

You'll use this bookstore-connected query editor throughout the project to run your SQL files. Any query you execute from here will target the bookstore database.

Don't see bookstore after refreshing?

Make sure you ran the CREATE DATABASE statement (check for a success message in the results panel). If you see an error about the database already existing, that means it was created successfully on a previous attempt.

Help me figure out what went wrong

Create your project files

Professional SQL projects keep their code in version-controllable files rather than ephemeral editor sessions. You'll create three files that will hold your schema definitions, sample data, and queries.

  • In VS Code, click Terminal in the top menu bar, then click New Terminal.
  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder and move into it by running these commands:
mkdir bookstore-project
cd bookstore-project
  • Create the three empty SQL files by running the command for your operating system:

macOS

touch schema.sql seed.sql queries.sql

Windows

New-Item -ItemType File -Path "schema.sql", "seed.sql", "queries.sql"
  • Confirm the files exist by running:
ls

You should see schema.sql, seed.sql, and queries.sql in the output.

  • In VS Code, click File in the top menu bar.
  • Click Open Folder.
  • Navigate to your Desktop and select the bookstore-project folder, then click Open.

Your VS Code Explorer sidebar should now show the three empty SQL files.

Why three separate files?

Keeping schema definitions in schema.sql, sample data in seed.sql, and queries in queries.sql mirrors how production teams manage database code.

It makes changes easy to track in version control and lets you re-run each file independently.

Don't see your files in VS Code?

Make sure you opened the bookstore-project folder itself, not its parent. Check the Explorer sidebar header. It should say BOOKSTORE-PROJECT at the top.

Help me find my project files

Your workspace is ready. Next up, you'll analyze business requirements and sketch an ER diagram to design the bookstore's database schema from scratch.

Design Your Schema

Your database is created and your tools are connected. Now comes the most important part of any database project: designing the schema before writing a single line of SQL.

Professional database designers follow a specific methodology. You start with business requirements, identify entities and relationships, sketch an ER diagram, and verify normalization. This is the same pipeline taught in the Elmasri/Navathe textbook because it still works in 2026.

In this step, get ready to:
  • Analyze the business requirements for PageTurn Books and identify entities.
  • Sketch an ER diagram with cardinalities and junction tables.
  • Verify your design passes 3NF normalization.
Analyze the business requirements for PageTurn Books

Every schema starts with a clear picture of what the business needs to store and track. PageTurn Books is an online bookstore with these requirements:

  • The store sells books by various authors.
  • Each book has exactly one publisher but can have multiple authors.
  • Customers can place orders containing multiple books.
  • Customers can leave reviews (1-5 stars) on books they have purchased.
  • Read through the requirements above and identify the core entities: publishers, authors, books, customers, orders, and reviews.
  • For each entity, note its relationship to the others.

What is an entity?

An entity is a distinct "thing" your database needs to store information about. Each entity becomes a table. The relationships between entities determine how those tables connect through foreign keys.

Here are the relationships you should identify from the requirements:

  • Publishers to books: one-to-many (one publisher publishes many books).
  • Authors to books: many-to-many (one author writes many books, one book can have many authors).
  • Customers to orders: one-to-many (one customer places many orders).
  • Orders to books: many-to-many (one order contains many books, one book appears in many orders).
  • Customers to reviews: one-to-many (one customer writes many reviews).
  • Books to reviews: one-to-many (one book receives many reviews).

Why do many-to-many relationships need special handling?

Relational databases cannot directly represent a many-to-many relationship between two tables. You need a third table (called a junction table) that sits between them.

Each row in the junction table holds one pair of foreign keys, linking one record from each side. This is exactly what Django's ManyToManyField and JPA's @ManyToMany generate behind the scenes.

Sketch an ER diagram

An ER (Entity-Relationship) diagram is a visual map of your tables and how they connect. Sketching one before writing SQL catches design mistakes early.

  • Grab a piece of paper, open a whiteboard app, or use any free diagramming tool you prefer.
  • Draw a box for each of the 6 core entities: publishers, authors, books, customers, orders, reviews.
  • Draw lines between entities to show relationships. Label each line with the cardinality (1:M or M:M).
  • For each many-to-many relationship, add a junction table box on the connecting line.

Your diagram needs two junction tables:

  • book_authors sits between authors and books. It holds pairs of (book_id, author_id).
  • order_items sits between orders and books. It holds (order_id, book_id) plus extra data: quantity and price_at_purchase.

Why does order_items store price_at_purchase?

Book prices change over time. If you only stored a reference to the book's current price, historical orders would show the wrong totals. The price_at_purchase column captures a snapshot of what the customer actually paid.

This is a classic normalization insight. An order is a historical record, and its line-item prices must be independent of the book's current price.

  • Add quantity and price_at_purchase as attributes inside the order_items box on your diagram.
  • Your final diagram should show 8 boxes total (6 entities + 2 junction tables) with relationship lines connecting them.
Apply normalization to verify your design

Normalization is the process of structuring tables so that each stores exactly one fact per row. You check your design against three normal forms (1NF through 3NF) to catch redundancy before it causes problems.

  • Walk through each of your 8 tables and verify these three rules:
  • 1NF: Every column holds a single atomic value (no lists, no repeating groups). For example, a book does not store multiple author names in one column.
  • 2NF: Every non-key column depends on the entire primary key (not just part of it). This matters for junction tables with composite keys.
  • 3NF: No non-key column depends on another non-key column (no transitive dependencies). For example, publisher country lives in the publishers table, not repeated on every book row.

What does 3NF actually mean in practice?

Third Normal Form (3NF) means: every non-key column tells you something about the primary key, the whole key, and nothing but the key. If a column really describes a different entity, it belongs in that entity's table instead.

The professional rule of thumb: normalize to 3NF first, denormalize only when EXPLAIN ANALYZE proves a join is the measured bottleneck.

  • Confirm that book_authors passes 2NF. Its composite key is (book_id, author_id), and it has no other columns, so there is nothing that could depend on only part of the key.
  • Confirm that order_items passes 2NF. Its composite key is (order_id, book_id). Both quantity and price_at_purchase depend on the specific combination of that order and that book, not on either alone.
  • Confirm that reviews passes 3NF. The rating and review text describe the specific review (identified by its primary key), not the customer or the book independently.

Write notes on your diagram

As you verify each table, jot a quick checkmark or "3NF" on your sketch. When you move to writing SQL in the next step, these annotations help you remember your design decisions.

Your design is complete. You have 6 core entities plus 2 junction tables, all verified to 3NF. Next up, you will translate this diagram into actual CREATE TABLE statements with proper constraints.

Build Your Tables

Your ER diagram maps out the full PageTurn Books database. You know what entities exist, how they relate, and what constraints protect data integrity.

Now it's time to translate that design into actual SQL. You'll write CREATE TABLE statements in schema.sql, following a specific order. Independent tables (no foreign keys) come first, then dependent tables (with foreign keys), then junction tables for many-to-many relationships.

In this step, get ready to:
  • Create tables for independent entities (publishers, authors, customers).
  • Create tables for dependent entities (books, orders, reviews) with foreign key constraints.
  • Create junction tables (book_authors, order_items) and execute the schema.
Write the independent entity tables

Independent entities are tables with no foreign keys. They don't reference any other table, so PostgreSQL must create them first. If you tried to create a books table before publishers exists, the foreign key reference would fail.

  • Open schema.sql in VS Code and add the following code:
-- PageTurn Books Database Schema
-- Designed following 3NF normalization

-- Independent entities first (no foreign keys)

CREATE TABLE publishers (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    country VARCHAR(100),
    founded_year INTEGER,
    CHECK (founded_year > 1400 AND founded_year <= 2026)
);

CREATE TABLE authors (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    birth_year INTEGER,
    bio TEXT
);

CREATE TABLE customers (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    joined_date DATE NOT NULL DEFAULT CURRENT_DATE
);

What does this code do?

Each table uses GENERATED ALWAYS AS IDENTITY for its primary key. This is the SQL-standard way to auto-generate unique IDs in PostgreSQL. It replaces the older SERIAL approach and is preferred for new schemas.

  • NOT NULL prevents empty values in required columns like name and email.
  • UNIQUE on email ensures no two customers share the same email address.
  • CHECK on founded_year enforces a valid range. The database itself rejects bad data before your application code even sees it.
  • DEFAULT CURRENT_DATE on joined_date means PostgreSQL fills in today's date automatically if no value is provided.
  • Save schema.sql.

You won't see a change in your database yet. The next chunk adds the tables that reference these ones.

Write the dependent entity tables

Dependent entities contain FOREIGN KEY columns that point to the independent tables you just defined. The REFERENCES keyword tells PostgreSQL to enforce that every value in that column must exist in the referenced table.

  • In schema.sql, add the following code below your customers table:
-- Dependent entities (have foreign keys)

CREATE TABLE books (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title VARCHAR(300) NOT NULL,
    isbn VARCHAR(13) UNIQUE,
    price NUMERIC(10, 2) NOT NULL,
    published_date DATE,
    pages INTEGER,
    publisher_id INTEGER NOT NULL REFERENCES publishers(id),
    CHECK (price > 0),
    CHECK (pages > 0)
);

CREATE TABLE orders (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id),
    order_date TIMESTAMP NOT NULL DEFAULT NOW(),
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    CHECK (status IN ('pending', 'shipped', 'delivered', 'cancelled'))
);

CREATE TABLE reviews (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id),
    book_id INTEGER NOT NULL REFERENCES books(id),
    rating INTEGER NOT NULL,
    review_text TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    CHECK (rating >= 1 AND rating <= 5),
    UNIQUE (customer_id, book_id)
);

What does this code do?

Each table here references at least one independent entity using REFERENCES. This creates referential integrity. PostgreSQL will reject any insert that points to a non-existent publisher, customer, or book.

  • publisher_id INTEGER NOT NULL REFERENCES publishers(id) means every book must belong to a valid publisher.
  • CHECK (rating >= 1 AND rating <= 5) restricts reviews to a 1-5 star scale.
  • UNIQUE (customer_id, book_id) on reviews prevents a customer from reviewing the same book twice.
  • CHECK (status IN ('pending', 'shipped', 'delivered', 'cancelled')) limits order status to only these four valid values.
  • Save schema.sql.

Still no visible database change. The final chunk adds junction tables and runs everything together.

Write the junction tables and run the schema

Junction tables solve many-to-many relationships. A book can have multiple authors, and an author can write multiple books. The book_authors table sits between them, holding pairs of IDs with a composite PRIMARY KEY.

  • In schema.sql, add the following code below your reviews table:
-- Junction tables (many-to-many relationships)

CREATE TABLE book_authors (
    book_id INTEGER NOT NULL REFERENCES books(id),
    author_id INTEGER NOT NULL REFERENCES authors(id),
    PRIMARY KEY (book_id, author_id)
);

CREATE TABLE order_items (
    order_id INTEGER NOT NULL REFERENCES orders(id),
    book_id INTEGER NOT NULL REFERENCES books(id),
    quantity INTEGER NOT NULL,
    price_at_purchase NUMERIC(10, 2) NOT NULL,
    PRIMARY KEY (order_id, book_id),
    CHECK (quantity > 0),
    CHECK (price_at_purchase > 0)
);

What does this code do?

Junction tables use composite primary keys instead of a single id column. PRIMARY KEY (book_id, author_id) means the combination of both columns must be unique. One author can appear many times, but not twice for the same book.

The order_items table stores price_at_purchase separately from the book's current price. This is a classic normalization insight. Prices change over time, but a completed order is a historical snapshot that must never change.

  • Save schema.sql.
  • Make sure your query editor is connected to the bookstore database using the database dropdown in the editor toolbar.
  • Select all the code in schema.sql with Ctrl+A (Windows/Linux) or Cmd+A (macOS).
  • Execute the SQL by pressing Ctrl+Shift+E (Windows/Linux) or Cmd+Shift+E (macOS).
  • In the Connections tree on the left, right-click your bookstore database node and select Refresh.
  • Expand Tables. You should see all 8 tables listed: publishers, authors, customers, books, orders, reviews, book_authors, and order_items.

Tables not appearing?

  • Check the database dropdown in the editor toolbar. If it says postgres instead of bookstore, switch it before executing.
  • Look at the output panel for error messages. A common one is a foreign key referencing a table that doesn't exist yet. Make sure you have all the code in the correct order (independent tables first).
  • If you see a "relation already exists" error, the tables were partially created from a previous attempt. Run DROP SCHEMA public CASCADE; CREATE SCHEMA public; to reset, then execute schema.sql again.

Still stuck? Help me troubleshoot why my tables aren't appearing in the PostgreSQL Connections tree after running schema.sql.

✔️ Awesome, I've got everything!

Great. Double check you've saved schema.sql and that all 8 tables appear in your Connections tree.

ⓧ I'd like to double check the full code

Here is the complete schema.sql file. Compare it against yours to spot any differences.

-- PageTurn Books Database Schema
-- Designed following 3NF normalization

-- Independent entities first (no foreign keys)

CREATE TABLE publishers (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    country VARCHAR(100),
    founded_year INTEGER,
    CHECK (founded_year > 1400 AND founded_year <= 2026)
);

CREATE TABLE authors (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    birth_year INTEGER,
    bio TEXT
);

CREATE TABLE customers (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    joined_date DATE NOT NULL DEFAULT CURRENT_DATE
);

-- Dependent entities (have foreign keys)

CREATE TABLE books (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title VARCHAR(300) NOT NULL,
    isbn VARCHAR(13) UNIQUE,
    price NUMERIC(10, 2) NOT NULL,
    published_date DATE,
    pages INTEGER,
    publisher_id INTEGER NOT NULL REFERENCES publishers(id),
    CHECK (price > 0),
    CHECK (pages > 0)
);

CREATE TABLE orders (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id),
    order_date TIMESTAMP NOT NULL DEFAULT NOW(),
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    CHECK (status IN ('pending', 'shipped', 'delivered', 'cancelled'))
);

CREATE TABLE reviews (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id),
    book_id INTEGER NOT NULL REFERENCES books(id),
    rating INTEGER NOT NULL,
    review_text TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    CHECK (rating >= 1 AND rating <= 5),
    UNIQUE (customer_id, book_id)
);

-- Junction tables (many-to-many relationships)

CREATE TABLE book_authors (
    book_id INTEGER NOT NULL REFERENCES books(id),
    author_id INTEGER NOT NULL REFERENCES authors(id),
    PRIMARY KEY (book_id, author_id)
);

CREATE TABLE order_items (
    order_id INTEGER NOT NULL REFERENCES orders(id),
    book_id INTEGER NOT NULL REFERENCES books(id),
    quantity INTEGER NOT NULL,
    price_at_purchase NUMERIC(10, 2) NOT NULL,
    PRIMARY KEY (order_id, book_id),
    CHECK (quantity > 0),
    CHECK (price_at_purchase > 0)
);

Your schema is live. Eight empty tables are standing by, connected through foreign keys and protected by constraints. Next up, you'll fill them with realistic sample data so you have something meaningful to query.

Populate and Query Your Database

Your schema is in place. Eight tables sit in the bookstore database, ready to hold data. But empty tables are not useful for learning.

In this step, you will fill those tables with realistic sample data and then write progressively complex queries against it. By the time you finish, you will have seen exactly what Django ORM and Spring Data JPA generate behind the scenes when you call methods like objects.filter() or findByPublishedDateAfter().

In this step, get ready to:
  • Populate all 8 tables with realistic sample data using INSERT statements.
  • Write basic SELECT queries with WHERE, ORDER BY, and multi-table JOINs.
  • Write aggregate queries using GROUP BY, HAVING, and SUM with ORM comparisons.
Seed your database with sample data

A database schema only proves itself when real data flows through it. Your INSERT statements must respect the foreign key relationships you defined. Independent entities (publishers, authors, customers) go first because dependent entities (books, orders) reference them.

  • Open seed.sql in VS Code and paste the following catalog data (publishers, authors, books, and book-author relationships):
-- Sample data for PageTurn Books

-- Publishers
INSERT INTO publishers (name, country, founded_year) VALUES
    ('Penguin Random House', 'United States', 2013),
    ('HarperCollins', 'United States', 1989),
    ('Macmillan Publishers', 'United Kingdom', 1843);

-- Authors
INSERT INTO authors (first_name, last_name, birth_year, bio) VALUES
    ('George', 'Orwell', 1903, 'English novelist and essayist'),
    ('Jane', 'Austen', 1775, 'English novelist known for social commentary'),
    ('Toni', 'Morrison', 1931, 'American novelist and Nobel Prize winner'),
    ('Haruki', 'Murakami', 1949, 'Japanese writer of fiction and non-fiction'),
    ('Chimamanda', 'Adichie', 1977, 'Nigerian writer of novels and short stories'),
    ('Neil', 'Gaiman', 1960, 'English author of short fiction and novels');

-- Books
INSERT INTO books (title, isbn, price, published_date, pages, publisher_id) VALUES
    ('1984', '9780451524935', 9.99, '1949-06-08', 328, 1),
    ('Pride and Prejudice', '9780141439518', 7.99, '1813-01-28', 432, 1),
    ('Beloved', '9781400033416', 14.99, '1987-09-02', 324, 2),
    ('Norwegian Wood', '9780375704024', 15.99, '1987-09-04', 296, 1),
    ('Americanah', '9780307455925', 16.99, '2013-05-14', 477, 2),
    ('American Gods', '9780063081918', 17.99, '2001-06-19', 541, 3),
    ('Kafka on the Shore', '9781400079278', 16.99, '2002-09-12', 467, 1),
    ('The Ocean at the End of the Lane', '9780062459367', 13.99, '2013-06-18', 181, 3);

-- Book-Author relationships (many-to-many)
INSERT INTO book_authors (book_id, author_id) VALUES
    (1, 1),
    (2, 2),
    (3, 3),
    (4, 4),
    (5, 5),
    (6, 6),
    (7, 4),
    (8, 6);

What does this code do?

The INSERT statements populate the "product catalog" side of your bookstore. Notice that publisher_id in the books INSERT uses integers (1, 2, 3) that match the order publishers were inserted. PostgreSQL assigns these IDs automatically via GENERATED ALWAYS AS IDENTITY.

The book_authors junction table links books to authors. Murakami (author 4) wrote both book 4 and book 7. Gaiman (author 6) wrote book 6 and book 8. This creates the many-to-many relationship your schema was designed for.

  • Below the book_authors INSERT, add the transactional data (customers, orders, order items, and reviews):
-- Customers
INSERT INTO customers (first_name, last_name, email, joined_date) VALUES
    ('Alice', 'Johnson', 'alice@example.com', '2024-01-15'),
    ('Bob', 'Smith', 'bob@example.com', '2024-03-22'),
    ('Carol', 'Williams', 'carol@example.com', '2024-06-10'),
    ('David', 'Brown', 'david@example.com', '2025-01-05');

-- Orders
INSERT INTO orders (customer_id, order_date, status) VALUES
    (1, '2025-01-20 10:30:00', 'delivered'),
    (1, '2025-03-15 14:00:00', 'delivered'),
    (2, '2025-02-10 09:15:00', 'delivered'),
    (2, '2025-04-01 16:45:00', 'shipped'),
    (3, '2025-03-20 11:00:00', 'delivered'),
    (3, '2025-05-10 13:30:00', 'pending');

-- Order items
INSERT INTO order_items (order_id, book_id, quantity, price_at_purchase) VALUES
    (1, 1, 1, 9.99),
    (1, 3, 1, 14.99),
    (2, 5, 2, 16.99),
    (3, 4, 1, 15.99),
    (3, 7, 1, 16.99),
    (4, 6, 1, 17.99),
    (5, 2, 1, 7.99),
    (5, 8, 1, 13.99),
    (6, 1, 1, 9.99),
    (6, 5, 1, 16.99);

-- Reviews
INSERT INTO reviews (customer_id, book_id, rating, review_text, created_at) VALUES
    (1, 1, 5, 'A masterpiece that remains relevant today.', '2025-02-01 09:00:00'),
    (1, 3, 4, 'Powerful and haunting prose.', '2025-02-05 10:00:00'),
    (2, 4, 5, 'Beautiful melancholic storytelling.', '2025-03-01 14:00:00'),
    (2, 7, 4, 'Surreal and captivating.', '2025-03-10 16:00:00'),
    (3, 2, 5, 'Witty and timeless.', '2025-04-01 11:00:00'),
    (3, 8, 4, 'Short but enchanting.', '2025-04-05 12:00:00'),
    (1, 5, 4, 'Insightful look at identity and belonging.', '2025-04-10 08:00:00'),
    (3, 1, 5, 'Terrifyingly prescient.', '2025-06-01 09:30:00');

Why is price_at_purchase stored separately?

The order_items table stores price_at_purchase rather than looking up the current price from the books table. Prices change over time. An order is a historical snapshot. If you only stored a foreign key to books.price, a price change today would retroactively alter the total of orders placed last month.

Also notice that David Brown (customer 4) has no orders. This is intentional. You will use him later to test a "customers who never ordered" subquery.

  • Save seed.sql and run it against your database by pressing Ctrl+Shift+E (Windows/Linux) or Cmd+Shift+E (macOS).
  • Verify the data loaded by opening a new query editor and running SELECT COUNT(*) FROM books; in the results panel. You should see 8 returned.

Seeing a foreign key violation error?

This means an INSERT references an ID that does not exist yet. The most common cause is running the file with statements out of order. Publishers, authors, and customers must be inserted before books, orders, and reviews.

If you re-run the file after a partial success, you may also hit a duplicate key error. Run TRUNCATE publishers, authors, customers, books, orders, reviews, book_authors, order_items CASCADE; to clear all tables, then re-run seed.sql from scratch.

Help me debug my seed.sql foreign key error

✔️ Awesome, I've got everything!

Great. Double check you have saved seed.sql and confirmed 8 rows in the books table.

ⓧ I'd like to double check the full code

Here is the complete seed.sql file:

-- Sample data for PageTurn Books

-- Publishers
INSERT INTO publishers (name, country, founded_year) VALUES
    ('Penguin Random House', 'United States', 2013),
    ('HarperCollins', 'United States', 1989),
    ('Macmillan Publishers', 'United Kingdom', 1843);

-- Authors
INSERT INTO authors (first_name, last_name, birth_year, bio) VALUES
    ('George', 'Orwell', 1903, 'English novelist and essayist'),
    ('Jane', 'Austen', 1775, 'English novelist known for social commentary'),
    ('Toni', 'Morrison', 1931, 'American novelist and Nobel Prize winner'),
    ('Haruki', 'Murakami', 1949, 'Japanese writer of fiction and non-fiction'),
    ('Chimamanda', 'Adichie', 1977, 'Nigerian writer of novels and short stories'),
    ('Neil', 'Gaiman', 1960, 'English author of short fiction and novels');

-- Books
INSERT INTO books (title, isbn, price, published_date, pages, publisher_id) VALUES
    ('1984', '9780451524935', 9.99, '1949-06-08', 328, 1),
    ('Pride and Prejudice', '9780141439518', 7.99, '1813-01-28', 432, 1),
    ('Beloved', '9781400033416', 14.99, '1987-09-02', 324, 2),
    ('Norwegian Wood', '9780375704024', 15.99, '1987-09-04', 296, 1),
    ('Americanah', '9780307455925', 16.99, '2013-05-14', 477, 2),
    ('American Gods', '9780063081918', 17.99, '2001-06-19', 541, 3),
    ('Kafka on the Shore', '9781400079278', 16.99, '2002-09-12', 467, 1),
    ('The Ocean at the End of the Lane', '9780062459367', 13.99, '2013-06-18', 181, 3);

-- Book-Author relationships (many-to-many)
INSERT INTO book_authors (book_id, author_id) VALUES
    (1, 1),
    (2, 2),
    (3, 3),
    (4, 4),
    (5, 5),
    (6, 6),
    (7, 4),
    (8, 6);

-- Customers
INSERT INTO customers (first_name, last_name, email, joined_date) VALUES
    ('Alice', 'Johnson', 'alice@example.com', '2024-01-15'),
    ('Bob', 'Smith', 'bob@example.com', '2024-03-22'),
    ('Carol', 'Williams', 'carol@example.com', '2024-06-10'),
    ('David', 'Brown', 'david@example.com', '2025-01-05');

-- Orders
INSERT INTO orders (customer_id, order_date, status) VALUES
    (1, '2025-01-20 10:30:00', 'delivered'),
    (1, '2025-03-15 14:00:00', 'delivered'),
    (2, '2025-02-10 09:15:00', 'delivered'),
    (2, '2025-04-01 16:45:00', 'shipped'),
    (3, '2025-03-20 11:00:00', 'delivered'),
    (3, '2025-05-10 13:30:00', 'pending');

-- Order items
INSERT INTO order_items (order_id, book_id, quantity, price_at_purchase) VALUES
    (1, 1, 1, 9.99),
    (1, 3, 1, 14.99),
    (2, 5, 2, 16.99),
    (3, 4, 1, 15.99),
    (3, 7, 1, 16.99),
    (4, 6, 1, 17.99),
    (5, 2, 1, 7.99),
    (5, 8, 1, 13.99),
    (6, 1, 1, 9.99),
    (6, 5, 1, 16.99);

-- Reviews
INSERT INTO reviews (customer_id, book_id, rating, review_text, created_at) VALUES
    (1, 1, 5, 'A masterpiece that remains relevant today.', '2025-02-01 09:00:00'),
    (1, 3, 4, 'Powerful and haunting prose.', '2025-02-05 10:00:00'),
    (2, 4, 5, 'Beautiful melancholic storytelling.', '2025-03-01 14:00:00'),
    (2, 7, 4, 'Surreal and captivating.', '2025-03-10 16:00:00'),
    (3, 2, 5, 'Witty and timeless.', '2025-04-01 11:00:00'),
    (3, 8, 4, 'Short but enchanting.', '2025-04-05 12:00:00'),
    (1, 5, 4, 'Insightful look at identity and belonging.', '2025-04-10 08:00:00'),
    (3, 1, 5, 'Terrifyingly prescient.', '2025-06-01 09:30:00');
Write basic SELECT and JOIN queries

With data in your tables, you can now write the same queries that ORMs generate when you call methods like select_related() or JOIN FETCH. You will start with basic filtering and work up to multi-table JOINs.

  • Open queries.sql and paste the following basic SELECT queries with their ORM equivalents as comments:
-- PageTurn Books: Query Collection
-- From basic to advanced, with ORM comparisons

-- =============================================
-- BASIC QUERIES
-- =============================================

-- 1. All books sorted by price (descending)
SELECT title, price, published_date
FROM books
ORDER BY price DESC;
-- Django: Book.objects.order_by('-price').values('title', 'price', 'published_date')
-- JPA: findAllByOrderByPriceDesc()

-- 2. Books published after 2000 that cost less than $17
SELECT title, price, published_date
FROM books
WHERE published_date > '2000-01-01'
  AND price < 17.00
ORDER BY published_date;
-- Django: Book.objects.filter(published_date__gt='2000-01-01', price__lt=17)
-- JPA: findByPublishedDateAfterAndPriceLessThan(date, price)

What do the ORM comments show?

Each query includes a comment showing how Django ORM or Spring Data JPA would express the same logic. In Django, order_by('-price') generates ORDER BY price DESC. In JPA, the method name findAllByOrderByPriceDesc() generates the same SQL through naming conventions.

The point is not to memorize the ORM syntax. It is to see that every ORM call becomes a SQL statement. When something goes wrong, you will know exactly what query to debug.

  • Below the basic queries, add the JOIN queries section:
-- =============================================
-- JOIN QUERIES
-- =============================================

-- 3. Books with their publisher name (INNER JOIN)
SELECT b.title, b.price, p.name AS publisher
FROM books b
INNER JOIN publishers p ON b.publisher_id = p.id
ORDER BY p.name, b.title;
-- Django: Book.objects.select_related('publisher').values('title', 'price', 'publisher__name')
-- JPA: @Query("SELECT b FROM Book b JOIN FETCH b.publisher")

-- 4. All authors, including those with no books (LEFT JOIN)
SELECT a.first_name, a.last_name, b.title
FROM authors a
LEFT JOIN book_authors ba ON a.id = ba.author_id
LEFT JOIN books b ON ba.book_id = b.id
ORDER BY a.last_name;
-- Django: Author.objects.prefetch_related('books').all()

-- 5. Complete order details: customer, book titles, quantities
SELECT
    c.first_name || ' ' || c.last_name AS customer,
    o.order_date,
    b.title,
    oi.quantity,
    oi.price_at_purchase,
    (oi.quantity * oi.price_at_purchase) AS line_total
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN books b ON oi.book_id = b.id
ORDER BY o.order_date DESC, c.last_name;

What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN only returns rows where a match exists in both tables. Query 3 shows books with their publishers. Every book has a publisher (the foreign key is NOT NULL), so INNER JOIN is safe here.

LEFT JOIN returns all rows from the left table even when no match exists on the right. Query 4 uses LEFT JOIN so authors with zero books still appear in the results (their title column shows NULL). In your current data every author has at least one book, but the query is written to handle future authors who do not yet have publications.

  • Save queries.sql.
  • Highlight query 3 (the INNER JOIN) and press Ctrl+Shift+E (Windows/Linux) or Cmd+Shift+E (macOS) to run only the selected text. You should see 8 rows, each showing a book title, price, and publisher name.
  • Highlight and run query 5. You should see 10 rows showing each order line item with the customer name, book title, quantity, price, and calculated line total.

Run one query at a time

When your file has multiple statements, select the specific query text before pressing the execute shortcut. If nothing is selected, the extension runs the entire file contents and you will see multiple result sets.

Seeing zero rows returned?

If your JOINs return empty results, your tables are likely still empty. Switch back to seed.sql and confirm it ran successfully by checking for INSERT messages in the output panel. If you see errors, re-run the seed file after fixing any issues.

Help me debug why my JOIN query returns zero rows

Write aggregate queries with GROUP BY and HAVING

Aggregate functions collapse multiple rows into summary values. These are the queries that power dashboards, reports, and analytics. They are also where ORMs start to struggle.

  • Below the JOIN queries section in queries.sql, add the aggregate queries:
-- =============================================
-- AGGREGATE QUERIES
-- =============================================

-- 6. Number of books per publisher
SELECT p.name AS publisher, COUNT(b.id) AS book_count
FROM publishers p
LEFT JOIN books b ON p.id = b.publisher_id
GROUP BY p.name
ORDER BY book_count DESC;
-- Django: Publisher.objects.annotate(book_count=Count('books')).order_by('-book_count')

-- 7. Average rating per book (only books with 2+ reviews)
SELECT b.title, AVG(r.rating) AS avg_rating, COUNT(r.id) AS review_count
FROM books b
INNER JOIN reviews r ON b.id = r.book_id
GROUP BY b.title
HAVING COUNT(r.id) >= 2
ORDER BY avg_rating DESC;
-- Django: Book.objects.annotate(avg_rating=Avg('reviews__rating'), rc=Count('reviews')).filter(rc__gte=2)

-- 8. Total revenue per customer
SELECT
    c.first_name || ' ' || c.last_name AS customer,
    SUM(oi.quantity * oi.price_at_purchase) AS total_spent
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
INNER JOIN order_items oi ON o.id = oi.order_id
GROUP BY c.id, c.first_name, c.last_name
ORDER BY total_spent DESC;
-- Django: Customer.objects.annotate(total=Sum(F('orders__items__quantity') * F('orders__items__price_at_purchase')))

What does HAVING do that WHERE cannot?

WHERE filters individual rows before aggregation happens. HAVING filters groups after aggregation. In query 7, you cannot write WHERE COUNT(r.id) >= 2 because the count does not exist yet at the WHERE stage. You need HAVING to filter on the aggregated result.

Query 8 demonstrates why knowing SQL matters even with an ORM. The Django equivalent uses F() expressions, Sum(), and chained lookups across three models. The raw SQL is shorter and easier to reason about for reporting queries.

  • Save queries.sql.
  • Highlight and run query 6. You should see 3 rows showing Penguin Random House with 4 books, HarperCollins with 2, and Macmillan Publishers with 2.
  • Highlight and run query 7. You should see only books that have 2 or more reviews. The book "1984" should appear with an average rating of 5.0 and a review count of 2.
  • Highlight and run query 8. Alice Johnson should appear as the top spender.

Query 7 returning no results?

Make sure your reviews table has data. Run SELECT COUNT(*) FROM reviews; to confirm you have 8 rows. If the count is 0, your seed.sql reviews INSERT may have failed due to a duplicate (customer_id, book_id) pair or a CHECK constraint violation on the rating.

Help me troubleshoot my aggregate query returning empty results

✔️ Awesome, I've got everything!

Great. Double check you have saved queries.sql with all 8 queries.

ⓧ I'd like to double check the full code

Here is the complete queries.sql file:

-- PageTurn Books: Query Collection
-- From basic to advanced, with ORM comparisons

-- =============================================
-- BASIC QUERIES
-- =============================================

-- 1. All books sorted by price (descending)
SELECT title, price, published_date
FROM books
ORDER BY price DESC;
-- Django: Book.objects.order_by('-price').values('title', 'price', 'published_date')
-- JPA: findAllByOrderByPriceDesc()

-- 2. Books published after 2000 that cost less than $17
SELECT title, price, published_date
FROM books
WHERE published_date > '2000-01-01'
  AND price < 17.00
ORDER BY published_date;
-- Django: Book.objects.filter(published_date__gt='2000-01-01', price__lt=17)
-- JPA: findByPublishedDateAfterAndPriceLessThan(date, price)

-- =============================================
-- JOIN QUERIES
-- =============================================

-- 3. Books with their publisher name (INNER JOIN)
SELECT b.title, b.price, p.name AS publisher
FROM books b
INNER JOIN publishers p ON b.publisher_id = p.id
ORDER BY p.name, b.title;
-- Django: Book.objects.select_related('publisher').values('title', 'price', 'publisher__name')
-- JPA: @Query("SELECT b FROM Book b JOIN FETCH b.publisher")

-- 4. All authors, including those with no books (LEFT JOIN)
SELECT a.first_name, a.last_name, b.title
FROM authors a
LEFT JOIN book_authors ba ON a.id = ba.author_id
LEFT JOIN books b ON ba.book_id = b.id
ORDER BY a.last_name;
-- Django: Author.objects.prefetch_related('books').all()

-- 5. Complete order details: customer, book titles, quantities
SELECT
    c.first_name || ' ' || c.last_name AS customer,
    o.order_date,
    b.title,
    oi.quantity,
    oi.price_at_purchase,
    (oi.quantity * oi.price_at_purchase) AS line_total
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN books b ON oi.book_id = b.id
ORDER BY o.order_date DESC, c.last_name;

-- =============================================
-- AGGREGATE QUERIES
-- =============================================

-- 6. Number of books per publisher
SELECT p.name AS publisher, COUNT(b.id) AS book_count
FROM publishers p
LEFT JOIN books b ON p.id = b.publisher_id
GROUP BY p.name
ORDER BY book_count DESC;
-- Django: Publisher.objects.annotate(book_count=Count('books')).order_by('-book_count')

-- 7. Average rating per book (only books with 2+ reviews)
SELECT b.title, AVG(r.rating) AS avg_rating, COUNT(r.id) AS review_count
FROM books b
INNER JOIN reviews r ON b.id = r.book_id
GROUP BY b.title
HAVING COUNT(r.id) >= 2
ORDER BY avg_rating DESC;
-- Django: Book.objects.annotate(avg_rating=Avg('reviews__rating'), rc=Count('reviews')).filter(rc__gte=2)

-- 8. Total revenue per customer
SELECT
    c.first_name || ' ' || c.last_name AS customer,
    SUM(oi.quantity * oi.price_at_purchase) AS total_spent
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
INNER JOIN order_items oi ON o.id = oi.order_id
GROUP BY c.id, c.first_name, c.last_name
ORDER BY total_spent DESC;
-- Django: Customer.objects.annotate(total=Sum(F('orders__items__quantity') * F('orders__items__price_at_purchase')))

You now have a populated database and a library of queries that span filtering, joining, and aggregating. Next up, you will push into subqueries and write SQL that ORMs cannot express cleanly.

Write Advanced Queries

Your database is populated with realistic data, and you have a solid foundation of basic SELECT, JOIN, and aggregate queries. Now it is time to tackle the queries that separate someone who uses SQL from someone who understands it.

Subqueries let you nest one question inside another. Advanced multi-table queries combine JOINs, GROUP BY, HAVING, and nested SELECTs in ways that ORMs cannot express cleanly. These are the queries you will drop to raw SQL for in Django and Spring Boot.

In this step, get ready to:
  • Write subqueries using NOT IN, NOT EXISTS, and HAVING comparisons.
  • Write advanced queries that push beyond what ORMs can express.
  • Compare your SQL with Django ORM and Spring Data JPA equivalents.
Write subqueries to answer nested questions

A subquery is a SELECT statement embedded inside another query. It lets you answer questions like "find all customers who have NOT done something" or "find books that beat the average."

  • In queries.sql, scroll to the bottom of the file (after query 8).
  • Add the following subquery section header and query 9 to find customers who have never placed an order:
-- =============================================
-- SUBQUERIES
-- =============================================

-- 9. Customers who have never placed an order
SELECT first_name, last_name, email
FROM customers
WHERE id NOT IN (SELECT DISTINCT customer_id FROM orders);
-- Django: Customer.objects.filter(orders__isnull=True)
-- Alternative using NOT EXISTS (often more efficient):
SELECT c.first_name, c.last_name, c.email
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

What does this code do?

This query answers a common business question: which customers signed up but never bought anything?

  • NOT IN collects all customer IDs from the orders table into a list, then excludes anyone whose ID appears in that list.
  • NOT EXISTS checks row-by-row whether a matching order exists for each customer. PostgreSQL can often optimize this better for large tables.
  • Both approaches return the same result. The Django ORM equivalent filter(orders__isnull=True) generates a LEFT JOIN under the hood.
  • Highlight query 9 (the first NOT IN variant) and press Ctrl+Shift+E to run it.
  • You should see one row: David Brown. He is the only customer who has never placed an order in the sample data.

Getting zero rows back?

Make sure you highlight only the first SELECT statement (up to the semicolon) before pressing Ctrl+Shift+E. If you highlight both SELECT statements at once, the results pane shows only the last one.

Help me debug my subquery results.

  • Now add query 10 below query 9 to find books whose average rating is above the overall average:
-- 10. Books with above-average rating
SELECT b.title, AVG(r.rating) AS avg_rating
FROM books b
INNER JOIN reviews r ON b.id = r.book_id
GROUP BY b.id, b.title
HAVING AVG(r.rating) > (SELECT AVG(rating) FROM reviews)
ORDER BY avg_rating DESC;

What makes this a correlated subquery?

The inner SELECT AVG(rating) FROM reviews calculates the overall average rating across all reviews. The outer query then compares each book's individual average against that global average using HAVING.

This pattern is powerful for finding records that "beat the average." The subquery runs once, and its result is used as a threshold for the HAVING filter.

  • Save queries.sql.
  • Highlight query 10 and press Ctrl+Shift+E to run it.
  • You should see books with an average rating above 4.5 (the overall average). 1984 and Norwegian Wood should appear near the top with a 5.0 average.

Seeing a syntax error?

Check that you have both GROUP BY b.id, b.title (not just b.title). PostgreSQL requires all non-aggregated columns in the GROUP BY clause.

Help me fix my above-average rating query.

Write queries that ORMs struggle with

Django's ORM and Spring Data JPA handle simple JOINs and aggregations well. But when you combine nested subqueries, correlated references to outer tables, and multi-level aggregation in a single statement, ORMs either cannot express the query at all or produce inefficient SQL.

  • Add the following section header and query 11 below query 10 in queries.sql. This finds each publisher's best-rated book using a deeply nested subquery:
-- =============================================
-- ADVANCED: Queries ORMs struggle with
-- =============================================

-- 11. For each publisher, show their best-rated book
SELECT p.name AS publisher, b.title, AVG(r.rating) AS avg_rating
FROM publishers p
INNER JOIN books b ON p.id = b.publisher_id
INNER JOIN reviews r ON b.id = r.book_id
GROUP BY p.name, b.title
HAVING AVG(r.rating) = (
    SELECT MAX(sub_avg)
    FROM (
        SELECT AVG(r2.rating) AS sub_avg
        FROM books b2
        INNER JOIN reviews r2 ON b2.id = r2.book_id
        WHERE b2.publisher_id = p.id
        GROUP BY b2.id
    ) AS publisher_ratings
)
ORDER BY avg_rating DESC;
-- This query is nearly impossible to express cleanly in Django ORM or JPA
-- without dropping to raw SQL or multiple queries.

What does this query do?

This is a three-level query. The outer query calculates the average rating for every book grouped by publisher. The HAVING clause then filters to only keep books whose average equals the maximum average for that publisher.

  • The innermost subquery (publisher_ratings) calculates the average rating for each book belonging to a specific publisher.
  • The middle subquery wraps that in MAX(sub_avg) to find the highest average rating among that publisher's books.
  • The WHERE b2.publisher_id = p.id makes this correlated. The inner query references the outer query's p.id, so PostgreSQL re-evaluates it for each publisher.
  • Save queries.sql.
  • Highlight query 11 and press Ctrl+Shift+E to run it.
  • You should see one row per publisher showing their best-rated book and its average rating.

Getting an error about p.id in the subquery?

Make sure the correlated reference WHERE b2.publisher_id = p.id uses p.id (the alias from the outer query's publishers p). If you used a different alias in the outer query, the inner query cannot find it.

Help me debug my nested subquery for best-rated book per publisher.

  • Add query 12 below query 11 to create a monthly revenue report:
-- 12. Monthly revenue report with running context
SELECT
    TO_CHAR(o.order_date, 'YYYY-MM') AS month,
    COUNT(DISTINCT o.id) AS order_count,
    SUM(oi.quantity * oi.price_at_purchase) AS monthly_revenue
FROM orders o
INNER JOIN order_items oi ON o.id = oi.order_id
WHERE o.status != 'cancelled'
GROUP BY TO_CHAR(o.order_date, 'YYYY-MM')
ORDER BY month;
-- Django: Orders.objects.exclude(status='cancelled').annotate(month=TruncMonth('order_date')).values('month').annotate(...)
-- Doable in Django but verbose; raw SQL is clearer for reporting queries.

What does this query do?

This reporting query groups orders by month and calculates total revenue for each period.

  • TO_CHAR(o.order_date, 'YYYY-MM') formats the timestamp into a year-month string for grouping.
  • COUNT(DISTINCT o.id) counts unique orders (not individual line items) per month.
  • The WHERE o.status != 'cancelled' excludes cancelled orders from revenue calculations.
  • Save queries.sql.
  • Highlight query 12 and press Ctrl+Shift+E to run it.
  • You should see rows for each month (2025-01 through 2025-05) with order counts and revenue totals.

Seeing unexpected revenue numbers?

Double check that your SUM multiplies oi.quantity * oi.price_at_purchase (not b.price). The price_at_purchase column captures what the customer actually paid at order time.

Help me fix my monthly revenue query.

Compare your SQL with ORM equivalents

Look back at the comments you added after each query. They show what the Django ORM or Spring Data JPA equivalent would look like for the same logic.

Why does this matter for framework developers?

For simple queries (1-2), the ORM version is nearly identical in expressiveness. Book.objects.filter(price__lt=17) maps directly to a WHERE clause. No reason to write raw SQL here.

For aggregate queries (6-8), ORMs handle them but the syntax gets verbose. Django's annotate() and F() expressions work, but reading the ORM code is harder than reading the SQL.

For query 11 (nested correlated subquery), the ORM simply cannot express it. You need Model.objects.raw() in Django or @Query with native SQL in Spring. Knowing how to write this SQL is the skill that makes you effective in those moments.

Extra for Experts: When should you use raw SQL in production?

Use your ORM for CRUD operations and simple queries. Drop to raw SQL for reporting queries, complex analytics, and any query where the ORM generates multiple round-trips to the database instead of one statement. The monthly revenue report (query 12) is a perfect example. Django can do it, but the raw SQL is more readable and more maintainable.

✔️ Awesome, I've got everything!

Great. Double check you have saved queries.sql with all 12 queries.

ⓧ I'd like to double check the full code

Here is the complete queries.sql file with all 12 queries:

-- PageTurn Books: Query Collection
-- From basic to advanced, with ORM comparisons

-- =============================================
-- BASIC QUERIES
-- =============================================

-- 1. All books sorted by price (descending)
SELECT title, price, published_date
FROM books
ORDER BY price DESC;
-- Django: Book.objects.order_by('-price').values('title', 'price', 'published_date')
-- JPA: findAllByOrderByPriceDesc()

-- 2. Books published after 2000 that cost less than $17
SELECT title, price, published_date
FROM books
WHERE published_date > '2000-01-01'
  AND price < 17.00
ORDER BY published_date;
-- Django: Book.objects.filter(published_date__gt='2000-01-01', price__lt=17)
-- JPA: findByPublishedDateAfterAndPriceLessThan(date, price)

-- =============================================
-- JOIN QUERIES
-- =============================================

-- 3. Books with their publisher name (INNER JOIN)
SELECT b.title, b.price, p.name AS publisher
FROM books b
INNER JOIN publishers p ON b.publisher_id = p.id
ORDER BY p.name, b.title;
-- Django: Book.objects.select_related('publisher').values('title', 'price', 'publisher__name')
-- JPA: @Query("SELECT b FROM Book b JOIN FETCH b.publisher")

-- 4. All authors, including those with no books (LEFT JOIN)
SELECT a.first_name, a.last_name, b.title
FROM authors a
LEFT JOIN book_authors ba ON a.id = ba.author_id
LEFT JOIN books b ON ba.book_id = b.id
ORDER BY a.last_name;
-- Django: Author.objects.prefetch_related('books').all()

-- 5. Complete order details: customer, book titles, quantities
SELECT
    c.first_name || ' ' || c.last_name AS customer,
    o.order_date,
    b.title,
    oi.quantity,
    oi.price_at_purchase,
    (oi.quantity * oi.price_at_purchase) AS line_total
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN books b ON oi.book_id = b.id
ORDER BY o.order_date DESC, c.last_name;

-- =============================================
-- AGGREGATE QUERIES
-- =============================================

-- 6. Number of books per publisher
SELECT p.name AS publisher, COUNT(b.id) AS book_count
FROM publishers p
LEFT JOIN books b ON p.id = b.publisher_id
GROUP BY p.name
ORDER BY book_count DESC;
-- Django: Publisher.objects.annotate(book_count=Count('books')).order_by('-book_count')

-- 7. Average rating per book (only books with 2+ reviews)
SELECT b.title, AVG(r.rating) AS avg_rating, COUNT(r.id) AS review_count
FROM books b
INNER JOIN reviews r ON b.id = r.book_id
GROUP BY b.title
HAVING COUNT(r.id) >= 2
ORDER BY avg_rating DESC;
-- Django: Book.objects.annotate(avg_rating=Avg('reviews__rating'), rc=Count('reviews')).filter(rc__gte=2)

-- 8. Total revenue per customer
SELECT
    c.first_name || ' ' || c.last_name AS customer,
    SUM(oi.quantity * oi.price_at_purchase) AS total_spent
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
INNER JOIN order_items oi ON o.id = oi.order_id
GROUP BY c.id, c.first_name, c.last_name
ORDER BY total_spent DESC;
-- Django: Customer.objects.annotate(total=Sum(F('orders__items__quantity') * F('orders__items__price_at_purchase')))

-- =============================================
-- SUBQUERIES
-- =============================================

-- 9. Customers who have never placed an order
SELECT first_name, last_name, email
FROM customers
WHERE id NOT IN (SELECT DISTINCT customer_id FROM orders);
-- Django: Customer.objects.filter(orders__isnull=True)
-- Alternative using NOT EXISTS (often more efficient):
SELECT c.first_name, c.last_name, c.email
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- 10. Books with above-average rating
SELECT b.title, AVG(r.rating) AS avg_rating
FROM books b
INNER JOIN reviews r ON b.id = r.book_id
GROUP BY b.id, b.title
HAVING AVG(r.rating) > (SELECT AVG(rating) FROM reviews)
ORDER BY avg_rating DESC;

-- =============================================
-- ADVANCED: Queries ORMs struggle with
-- =============================================

-- 11. For each publisher, show their best-rated book
SELECT p.name AS publisher, b.title, AVG(r.rating) AS avg_rating
FROM publishers p
INNER JOIN books b ON p.id = b.publisher_id
INNER JOIN reviews r ON b.id = r.book_id
GROUP BY p.name, b.title
HAVING AVG(r.rating) = (
    SELECT MAX(sub_avg)
    FROM (
        SELECT AVG(r2.rating) AS sub_avg
        FROM books b2
        INNER JOIN reviews r2 ON b2.id = r2.book_id
        WHERE b2.publisher_id = p.id
        GROUP BY b2.id
    ) AS publisher_ratings
)
ORDER BY avg_rating DESC;
-- This query is nearly impossible to express cleanly in Django ORM or JPA
-- without dropping to raw SQL or multiple queries.

-- 12. Monthly revenue report with running context
SELECT
    TO_CHAR(o.order_date, 'YYYY-MM') AS month,
    COUNT(DISTINCT o.id) AS order_count,
    SUM(oi.quantity * oi.price_at_purchase) AS monthly_revenue
FROM orders o
INNER JOIN order_items oi ON o.id = oi.order_id
WHERE o.status != 'cancelled'
GROUP BY TO_CHAR(o.order_date, 'YYYY-MM')
ORDER BY month;
-- Django: Orders.objects.exclude(status='cancelled').annotate(month=TruncMonth('order_date')).values('month').annotate(...)
-- Doable in Django but verbose; raw SQL is clearer for reporting queries.

You now have a complete query library spanning basic lookups through complex analytics. Next up, you will tackle the Secret Mission and evolve your schema with a brand new wishlists feature.

Secret mission

Schema Evolution - Add Wishlists

Your bookstore database is live and full of data. Now a product manager wants a wishlists feature. Can you evolve the schema without breaking what already exists? This is the real-world challenge: adding tables and relationships to a populated database.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely on your local PostgreSQL installation, so there are no ongoing cloud costs. Decide whether to keep your database running, pause the service to free up resources, or delete everything entirely.

Resources you used:

  • A local PostgreSQL database called bookstore with 10 tables (including the secret mission wishlists tables).
  • Project SQL files: schema.sql, seed.sql, queries.sql, schema_update.sql, seed_wishlists.sql, queries_wishlists.sql.

Keep everything running

No action needed. Choose this if you are still actively experimenting with your queries or want to extend the database further.

  • Your PostgreSQL service continues running in the background.
  • The bookstore database and all its tables remain intact.
  • Your SQL files stay on disk, ready for further edits.

Pause - I'll come back to this later

Stop the PostgreSQL service to free up memory. Your database files remain on disk so you can start the service again later without losing data.

  • Stop the PostgreSQL service using the method that matches your operating system and installation.

macOS

If you installed PostgreSQL via Homebrew, stop the service by running:

brew services stop postgresql@18

If you installed PostgreSQL via the EDB installer instead of Homebrew, open System Preferences and look for the PostgreSQL panel to stop the server.

Windows

Stop the PostgreSQL service through Windows Services:

  • Press the Windows key and type Services. Press Enter to open the Services app.
  • Scroll down and find postgresql-x64-18 in the list.
  • Right-click the service and select Stop.

To restart later, start the service again using the same tool you used to stop it.

Delete - I don't want to use this again

Remove the project database and all local files to start completely fresh.

  • Drop the bookstore database. You must be connected to a different database (such as the default postgres database) to run this command.

Switch to the default database using the database dropdown in the query editor toolbar, then run:

DROP DATABASE bookstore;
  • Delete your project SQL files. Remove all six files from your project folder: schema.sql, seed.sql, queries.sql, schema_update.sql, seed_wishlists.sql, and queries_wishlists.sql.

macOS

Remove the project folder by running:

rm -rf ~/Desktop/bookstore-project

Replace the path above with wherever you saved your SQL files.

Windows

Delete the project folder using File Explorer, or run this in PowerShell:

Remove-Item -Recurse -Force ~\Desktop\bookstore-project

Replace the path above with wherever you saved your SQL files.

  • Optionally, uninstall the PostgreSQL extension from VS Code by opening the Extensions panel (Ctrl+Shift+X), finding PostgreSQL by Microsoft, and clicking Uninstall.

Nice Work!

Nice Work!

Nice work! You just designed and built a complete PostgreSQL database for PageTurn Books, going from business requirements all the way through to advanced analytical queries.

You've learned how to:

  • Design a normalized database schema from business requirements using ER modeling, then implement it with PRIMARY KEYs, FOREIGN KEYs, CHECK constraints, and junction tables for many-to-many relationships.
  • Write 10+ queries of increasing complexity, spanning basic SELECT/WHERE, multi-table JOINs, GROUP BY/HAVING aggregations, and subqueries.
  • Connect SQL fundamentals to what Django ORM and Spring Data JPA generate under the hood, understanding exactly when raw SQL outperforms framework abstractions.
  • Secret Mission: Extended a live database with a wishlists feature using new junction tables, proving you can evolve a schema without breaking existing data.

Ready to quiz yourself?