Build an Online Store Database

Design a multi-table PostgreSQL database with JOINs, aggregations, and indexes.

Introduction

30 Second Summary

Every time you add something to your cart on an online store, a database is tracking what you picked, how many you want, and who you are. Behind every "Order Confirmed" screen is a carefully structured set of tables working together.

In this project, you will build a fully functional online store database using PostgreSQL. You will create related tables, enforce data integrity with constraints, and write queries that answer real business questions.

What You'll Build

A multi-table relational database with products, customers, and orders linked by foreign keys, populated with sample data and queried using JOINs and aggregations.

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

  • A PostgreSQL database with four related tables enforcing data integrity through constraints like PRIMARY KEY, FOREIGN KEY, NOT NULL, and CHECK.
  • A collection of JOIN and aggregation queries that answer business questions like "What are the top-selling products?" and "How much has each customer spent?".
  • Indexes applied to frequently-queried columns with before-and-after performance proof using EXPLAIN ANALYZE.
  • Secret Mission: Add product search with LIKE patterns, subqueries, and a database VIEW for reporting.

Are there any prerequisites?

This project assumes you have PostgreSQL installed on Windows with psql accessible from the command line. If you installed PostgreSQL using the standard EDB installer, you already have everything you need.

Connect to PostgreSQL and Create Your Database

Every project starts with a workspace. For a SQL project, that workspace is a database. PostgreSQL runs as a server on your machine, and you interact with it through a command-line client called psql.

In this step, you'll connect to PostgreSQL, create a fresh database for your online store, and learn the navigation commands that let you explore what's inside.

In this step, get ready to:
  • Connect to PostgreSQL via psql.
  • Create the online_store database.
  • Explore psql navigation commands.
Open psql and connect to the default postgres database

PostgreSQL comes with psql, a command-line tool that lets you run SQL commands directly against your database server. You'll use it for the entire project.

  • Press the Windows key to open your search bar.
  • Type Command Prompt and press Enter to open it.
  • Connect to PostgreSQL as the postgres superuser by running this command:
psql -U postgres
  • When prompted, enter the password you set during PostgreSQL installation.

What does this command do?

  • psql launches the PostgreSQL command-line client.
  • -U postgres tells it to connect as the postgres superuser. This is the default admin account created during installation.
  • On Windows, psql connects via TCP/IP to localhost on port 5432 by default.

You should see the prompt change to postgres=#. The # symbol confirms you're connected as a superuser.

  • Verify your PostgreSQL version by running this command:
SELECT version();

You should see output showing your PostgreSQL version number.

Not seeing the postgres=# prompt?

  • If you see "command not found" or "not recognized", make sure PostgreSQL's bin folder is in your system PATH. The default location on Windows is C:\Program Files\PostgreSQL\18\bin.
  • If you see "password authentication failed", double-check the password you set during installation. It is case-sensitive.
  • Help me connect to PostgreSQL with psql on Windows.
Create the online_store database and connect to it

Right now you're connected to the default postgres database. This is PostgreSQL's built-in admin database. You'll create a separate database specifically for your online store project.

  • Create your project database by running this command:
CREATE DATABASE online_store;

What does this command do?

  • CREATE DATABASE is a SQL command that creates a new, empty database on your PostgreSQL server.
  • The name online_store uses an underscore because SQL identifiers cannot contain spaces.
  • The semicolon at the end tells psql you've finished writing the command.
  • Connect to your new database by running this command:
\c online_store

Your prompt should change to online_store=#. This confirms you're now working inside the online_store database.

  • Confirm your database appears in the server's database list by running:
\l

You should see online_store listed alongside the default databases like postgres and template1.

Seeing "database already exists"?

If you previously ran CREATE DATABASE online_store;, the database already exists. You can safely skip to \c online_store to connect to it.

Help me fix a database creation error in psql.

Explore psql navigation commands

psql has built-in meta-commands that start with a backslash. These let you inspect your database structure without writing full SQL queries.

  • List all tables in your current database by running:
\dt

You should see a message saying "Did not find any relations" since you haven't created any tables yet.

  • See the syntax help for the CREATE TABLE command by running:
\h CREATE TABLE

This displays the full syntax for CREATE TABLE. You'll use this command in the next step to build your first table.

  • View all available psql meta-commands by running:
\?
  • Press q to exit the help view and return to the psql prompt.

Why learn meta-commands?

Commands like \dt, \l, and \d let you explore database structure at a glance. In job interviews and production environments, these are the first commands you'll reach for when navigating an unfamiliar database.

✔️ Awesome, I've got everything!

Great. Make sure your prompt still shows online_store=# before moving on.

ⓧ I'd like to double check the full code

Here are the key commands you ran in this step. Everything after connecting with psql -U postgres:

-- Connect to PostgreSQL first: psql -U postgres
-- Then run these commands:

CREATE DATABASE online_store;

-- Connect to the new database
\c online_store

Your database is ready and waiting for tables. Next up, you'll design the products table with data types and constraints that keep your data clean.

Design the Products Table

Your database is created and you are connected. Now it is time to design the first table that will hold your store's product catalog.

Every online store needs a structured way to track products, prices, and inventory. You will create a products table that uses constraints to enforce data integrity, meaning the database itself rejects bad data before it ever gets stored.

In this step, get ready to:
  • Create the products table with data types and constraints.
  • Insert sample product data and test constraint enforcement.
  • Practice basic SELECT queries to retrieve and filter data.
Create the products table with constraints

A table definition tells PostgreSQL what columns to create, what type of data each column holds, and what rules to enforce. These rules are called constraints.

  • Create the products table by running this SQL in your psql session:
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    price NUMERIC(10,2) NOT NULL CHECK (price > 0),
    stock_quantity INTEGER NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

What does this code do?

Each line defines a column with a specific data type and optional constraints:

  • SERIAL creates an auto-incrementing integer. Combined with PRIMARY KEY, it guarantees every product gets a unique ID.
  • VARCHAR(100) limits the product name to 100 characters. NOT NULL means a name is required for every product.
  • NUMERIC(10,2) stores precise decimal values (up to 10 digits, 2 after the decimal). CHECK (price > 0) rejects zero or negative prices.
  • DEFAULT 0 on stock_quantity means new products start with zero stock unless you specify otherwise.
  • TIMESTAMPTZ with DEFAULT NOW() automatically records when each product was added.
  • Inspect the table structure by running:
\d products

You should see a table listing all six columns with their data types and any constraints attached (not-null markers, default values, the check expression).

Seeing "relation already exists"?

This means you already ran the CREATE TABLE command once before. If you need to start over, drop the table first with DROP TABLE products; and then re-run the CREATE TABLE statement.

If you see a syntax error, check for missing commas between column definitions and matching parentheses at the end of the statement.

Help me fix my CREATE TABLE error in psql

Insert sample product data

An empty table is not very useful for learning queries. You will load six products spanning electronics, books, and accessories.

  • Insert six sample products by running this statement:
INSERT INTO products (name, description, price, stock_quantity) VALUES
    ('Wireless Headphones', 'Noise-cancelling over-ear headphones', 79.99, 25),
    ('USB-C Hub', '7-in-1 adapter with HDMI and ethernet', 34.99, 50),
    ('Mechanical Keyboard', 'RGB backlit with Cherry MX switches', 129.99, 15),
    ('Python Crash Course', 'Beginner programming book', 24.99, 100),
    ('Laptop Stand', 'Adjustable aluminum stand', 45.99, 30),
    ('Webcam HD', '1080p webcam with microphone', 59.99, 40);

What does this code do?

This inserts six rows in a single statement. Notice you do not provide product_id or created_at values. PostgreSQL fills those in automatically using the SERIAL sequence and DEFAULT NOW() respectively.

  • Verify the data was inserted by running:
SELECT * FROM products;

You should see all six products listed with their auto-generated IDs and timestamps.

  • Now test the CHECK constraint by trying to insert a product with a negative price:
INSERT INTO products (name, description, price, stock_quantity)
VALUES ('Bad Product', 'Testing constraints', -5.00, 10);

PostgreSQL rejects this with an error stating the new row violates the check constraint. The database refuses to store a product with an invalid price.

  • Test the NOT NULL constraint by trying to insert a product without a name:
INSERT INTO products (description, price, stock_quantity)
VALUES ('Missing name test', 25.00, 5);

PostgreSQL rejects this too, reporting that a null value in the name column violates the not-null constraint. Your data integrity rules are working.

Why do constraints matter?

Constraints act as a safety net built directly into your database. Without them, application bugs or manual errors could insert nonsensical data (free products, nameless items) that corrupts reports and breaks downstream code.

By catching bad data at the database level, you ensure integrity regardless of which application or user is writing to the table.

INSERT not working?

If you see a syntax error, check that every row of values is separated by a comma and the final row ends with a semicolon, not a comma.

If you see a "relation does not exist" error, make sure you are connected to the online_store database. Your prompt should show online_store=#. If not, run \c online_store to reconnect.

Help me debug my INSERT statement error

Practice basic SELECT queries

With data in your table, you can start asking questions. SELECT queries retrieve and filter rows based on conditions you specify.

  • Run each of these queries one at a time to explore your product data:
-- Products priced above $20
SELECT name, price FROM products WHERE price > 20;

-- All products sorted by price (most expensive first)
SELECT * FROM products ORDER BY price DESC;

-- Total number of products
SELECT COUNT(*) FROM products;

-- Average product price
SELECT AVG(price) FROM products;

What do these queries do?

  • WHERE price > 20 filters rows, returning only products that meet the condition.
  • ORDER BY price DESC sorts results from highest to lowest price.
  • COUNT(*) counts all rows in the table. You should see 6.
  • AVG(price) calculates the average price across all products.

All four queries should return results. The COUNT should return 6, and the AVG should return approximately 62.66.

Query returns zero rows?

If SELECT * FROM products; returns nothing, your INSERT may not have succeeded. Re-run the INSERT statement from the previous substep and check for error messages.

If you accidentally created the table in the wrong database, run \c online_store to switch back and try again.

Help me figure out why my SELECT returns no rows

✔️ Awesome, I've got everything!

Great. Double check you have 6 products in your table by running SELECT COUNT(*) FROM products; one more time.

ⓧ I'd like to double check the full code

Here is everything you should have run in this step. Compare it against what you entered:

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    price NUMERIC(10,2) NOT NULL CHECK (price > 0),
    stock_quantity INTEGER NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO products (name, description, price, stock_quantity) VALUES
    ('Wireless Headphones', 'Noise-cancelling over-ear headphones', 79.99, 25),
    ('USB-C Hub', '7-in-1 adapter with HDMI and ethernet', 34.99, 50),
    ('Mechanical Keyboard', 'RGB backlit with Cherry MX switches', 129.99, 15),
    ('Python Crash Course', 'Beginner programming book', 24.99, 100),
    ('Laptop Stand', 'Adjustable aluminum stand', 45.99, 30),
    ('Webcam HD', '1080p webcam with microphone', 59.99, 40);

Your products table is loaded and protected by constraints. Next up, you will build the customers and orders tables and link them together with foreign keys.

Build the Customers and Orders Tables

Your products table is ready with six items in stock. Now you need people to buy those products and a system to track what they order.

In this step, you'll create three more tables that connect to each other through foreign keys. Foreign keys create links between tables so you can trace which customer placed which order for which product.

In this step, get ready to:
  • Create the customers table with a UNIQUE email constraint.
  • Create the orders and order_items tables with foreign keys.
  • Populate the tables with sample order data.
Create the customers table with a UNIQUE email constraint

Every online store needs to know who its customers are. The customers table stores names and email addresses, with a constraint that prevents two customers from sharing the same email.

  • Create the customers table by running this SQL in your psql session:
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

What does this code do?

  • SERIAL PRIMARY KEY auto-generates a unique ID for each customer.
  • VARCHAR(50) NOT NULL limits names to 50 characters and requires a value.
  • UNIQUE NOT NULL on the email column means every customer must have an email, and no two customers can share the same one.
  • TIMESTAMPTZ DEFAULT NOW() automatically records when each customer was added.
  • Verify the table structure by running:
\d customers

You should see all five columns listed with their types and constraints.

Seeing an error after CREATE TABLE?

  • Make sure your SQL statement ends with a semicolon. Without it, psql waits for more input instead of executing.
  • If you see relation "customers" already exists, you already created the table. Run DROP TABLE customers; first, then try again.

Help me debug this error

  • Insert four sample customers by running:
INSERT INTO customers (first_name, last_name, email) VALUES
    ('Alice', 'Johnson', 'alice.johnson@email.com'),
    ('Bob', 'Smith', 'bob.smith@email.com'),
    ('Carol', 'Williams', 'carol.williams@email.com'),
    ('David', 'Brown', 'david.brown@email.com');

What does this code do?

This inserts four customers in a single statement. Notice you only provide first_name, last_name, and email. PostgreSQL fills in customer_id automatically (via SERIAL) and created_at automatically (via DEFAULT NOW()).

  • Confirm the data was inserted by running:
SELECT * FROM customers;

You should see four rows with Alice, Bob, Carol, and David listed.

Now test what happens when you try to insert a duplicate email. This is the UNIQUE constraint in action.

  • Try inserting a customer with an email that already exists by running:
INSERT INTO customers (first_name, last_name, email) VALUES ('Test', 'User', 'alice.johnson@email.com');

PostgreSQL rejects the insert with an error mentioning that the key value already exists. This is your UNIQUE constraint protecting data integrity.

Why enforce unique emails?

In a real online store, an email address identifies a customer's account. If two people could register with the same email, password resets, order confirmations, and login would all break.

The UNIQUE constraint prevents this at the database level, so even buggy application code cannot create duplicates.

Create the orders and order_items tables with foreign keys

Orders need to reference the customer who placed them. Order items need to reference both the order they belong to and the product being purchased. These cross-table links are called foreign keys.

  • Create the orders table by running:
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
    order_date TIMESTAMPTZ DEFAULT NOW(),
    total_amount NUMERIC(10,2) NOT NULL
);

What does this code do?

  • REFERENCES customers(customer_id) creates a foreign key. Every order must belong to a customer that exists in the customers table.
  • NUMERIC(10,2) stores the order total as an exact decimal value with up to 10 digits and 2 decimal places. This avoids floating-point rounding errors on money.
  • NOT NULL on customer_id means every order must have an owner. You cannot have an anonymous order.
  • Create the order_items table by running:
CREATE TABLE order_items (
    order_item_id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders(order_id),
    product_id INTEGER NOT NULL REFERENCES products(product_id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price NUMERIC(10,2) NOT NULL
);

What does this code do?

  • This table has two foreign keys: one pointing to orders and one pointing to products. Each order item belongs to exactly one order and references exactly one product.
  • CHECK (quantity > 0) prevents anyone from inserting an order item with zero or negative quantity.
  • unit_price records the price at the time of purchase. This is important because product prices can change over time.
  • Inspect the table structures to confirm the foreign keys by running:
\d orders
\d order_items

You should see the foreign key constraints listed under each table's description. The output shows which columns reference which tables.

Now test that the foreign key actually blocks invalid data.

  • Try inserting an order with a non-existent customer by running:
INSERT INTO orders (customer_id, total_amount) VALUES (99, 19.99);

PostgreSQL rejects this with a foreign key violation error. Customer 99 does not exist, so the database refuses to create an order pointing to a phantom customer.

Why are foreign keys important?

Without foreign keys, you could accidentally create orders that reference deleted customers or order items pointing to products that no longer exist.

Foreign keys guarantee that every relationship in your database is valid. If you try to break a link, PostgreSQL stops you immediately.

Getting a different error?

  • If you see a "relation does not exist" error when creating order_items, make sure you created the orders table first. Tables must exist before other tables can reference them.
  • If you accidentally dropped the products table, you will need to recreate it before order_items can reference it.

Help me debug this foreign key issue

Populate with sample order data

Your table structure is in place. Now you need realistic data to query against in the next steps.

  • Insert five orders across different customers by running:
INSERT INTO orders (customer_id, total_amount) VALUES
    (1, 114.98),
    (2, 129.99),
    (1, 45.99),
    (3, 84.98),
    (4, 59.99);

What does this code do?

This creates five orders. Notice that customer 1 (Alice) has two orders, while the others have one each. This mirrors real shopping behavior where some customers order more frequently than others.

  • Insert ten order items linking orders to products by running:
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
    (1, 1, 1, 79.99),
    (1, 4, 1, 24.99),
    (1, 2, 1, 34.99),
    (2, 3, 1, 129.99),
    (3, 5, 1, 45.99),
    (4, 1, 1, 79.99),
    (4, 4, 1, 24.99),
    (5, 6, 1, 59.99),
    (4, 2, 1, 34.99),
    (2, 5, 1, 45.99);

What does this code do?

Each row links an order to a product with a quantity and price. For example, row (1, 1, 1, 79.99) means order 1 includes 1 unit of product 1 (Wireless Headphones) at $79.99. Some orders contain multiple items, which is why orders 1, 2, and 4 appear more than once.

  • Verify all four tables now exist by running:
\dt

You should see products, customers, orders, and order_items all listed in the output.

Missing a table in the list?

  • If \dt shows fewer than four tables, one of your CREATE TABLE statements may have failed silently. Scroll up in your terminal to check for errors.
  • Confirm you are connected to online_store (your prompt should show online_store=#). If it shows postgres=#, run \c online_store to switch.

Help me figure out why my tables are missing

✔️ Awesome, I've got everything!

Great. Make sure all four tables appear in your \dt output before moving on.

ⓧ I'd like to double check the full code

Here is the complete SQL you should have run in this step:

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO customers (first_name, last_name, email) VALUES
    ('Alice', 'Johnson', 'alice.johnson@email.com'),
    ('Bob', 'Smith', 'bob.smith@email.com'),
    ('Carol', 'Williams', 'carol.williams@email.com'),
    ('David', 'Brown', 'david.brown@email.com');

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
    order_date TIMESTAMPTZ DEFAULT NOW(),
    total_amount NUMERIC(10,2) NOT NULL
);

CREATE TABLE order_items (
    order_item_id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders(order_id),
    product_id INTEGER NOT NULL REFERENCES products(product_id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price NUMERIC(10,2) NOT NULL
);

INSERT INTO orders (customer_id, total_amount) VALUES
    (1, 114.98),
    (2, 129.99),
    (1, 45.99),
    (3, 84.98),
    (4, 59.99);

INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
    (1, 1, 1, 79.99),
    (1, 4, 1, 24.99),
    (1, 2, 1, 34.99),
    (2, 3, 1, 129.99),
    (3, 5, 1, 45.99),
    (4, 1, 1, 79.99),
    (4, 4, 1, 24.99),
    (5, 6, 1, 59.99),
    (4, 2, 1, 34.99),
    (2, 5, 1, 45.99);

Your database now has a complete relational structure with four interconnected tables. Next up, you'll write queries that combine data across these tables using JOINs to answer real business questions.

Query with JOINs and Aggregations

Your database has four tables full of data, but right now each table is an island.

A real online store needs to answer questions like "How much has Alice spent?" or "What product sells the most?". These questions require combining data from multiple tables. In this step, you will use INNER JOIN to connect your tables and aggregate functions to crunch numbers across rows.

In this step, get ready to:
  • Write JOIN queries that combine data from two and four tables.
  • Answer business questions using GROUP BY with SUM, COUNT, and AVG.
  • Filter grouped results with HAVING to find top customers and products.
Write JOIN queries to combine related data

An INNER JOIN connects rows from two tables where a specified column matches. It only returns rows that have a match on both sides.

  • Run this query in your psql session to see each order alongside the customer who placed it:
-- Each order with customer name
SELECT
    o.order_id,
    c.first_name,
    c.last_name,
    o.total_amount,
    o.order_date
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;

What does this query do?

The FROM orders o part gives the orders table a short alias o. INNER JOIN customers c brings in the customers table (aliased as c) and the ON clause tells PostgreSQL how to match rows: where the customer_id in orders equals the customer_id in customers.

Only orders that have a matching customer appear in the result. If an order referenced a deleted customer, it would be excluded.

You should see 5 rows, one for each order, now showing the customer's first and last name instead of just a numeric ID.

  • Now run a query that joins four tables to see the full picture of what each customer ordered:
-- Order items with product and customer details
SELECT
    c.first_name,
    c.last_name,
    p.name AS product_name,
    oi.quantity,
    oi.unit_price
FROM order_items oi
INNER JOIN orders o ON oi.order_id = o.order_id
INNER JOIN customers c ON o.customer_id = c.customer_id
INNER JOIN products p ON oi.product_id = p.product_id;

How does a multi-table JOIN work?

Each INNER JOIN adds another table to the result. PostgreSQL chains them together: order_items connects to orders (to find which order), orders connects to customers (to find who placed it), and order_items connects to products (to find the product name).

The AS product_name part creates a column alias so the output heading reads product_name instead of the generic name.

You should see 10 rows, one for each order item, now with readable customer names and product names instead of numeric IDs.

Seeing a syntax error?

  • Make sure each INNER JOIN line has the ON keyword followed by the matching condition.
  • Check that your table aliases (o, c, p, oi) match exactly between the FROM/JOIN lines and the SELECT columns.
  • Every SQL statement in psql must end with a semicolon.

Help me debug my INNER JOIN query in psql

Answer business questions with GROUP BY and aggregates

JOINs show individual rows, but business questions often need summaries. GROUP BY collapses multiple rows into groups, and aggregate functions like SUM, COUNT, and AVG compute a single value from each group.

  • Run this query to answer "How much has each customer spent total?":
-- Total spent per customer
SELECT
    c.first_name,
    c.last_name,
    SUM(o.total_amount) AS total_spent
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY total_spent DESC;

What does GROUP BY do here?

GROUP BY c.customer_id, c.first_name, c.last_name tells PostgreSQL to combine all rows for the same customer into one group. SUM(o.total_amount) then adds up that customer's order totals into one number.

Every column in SELECT that is not inside an aggregate function must appear in the GROUP BY clause. That is why first_name and last_name are listed there too.

You should see Alice at the top with a total_spent of 160.97 (she placed two orders), followed by Bob, Carol, and David.

  • Run this query to find the top-selling products by quantity sold:
-- Top-selling products by quantity
SELECT
    p.name,
    SUM(oi.quantity) AS total_sold
FROM products p
INNER JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.name
ORDER BY total_sold DESC;

What does this query do?

This joins products to order_items, groups by product, and sums the quantity column. ORDER BY total_sold DESC puts the best-sellers at the top.

You should see Wireless Headphones and Python Crash Course tied at the top with 2 units sold each.

  • Run this query to count how many orders each customer has placed:
-- Orders per customer
SELECT
    c.first_name,
    c.last_name,
    COUNT(o.order_id) AS order_count
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name;

SUM vs COUNT

SUM adds numeric values together (like prices or quantities). COUNT counts how many rows exist in each group. Use SUM for totals and COUNT for "how many" questions.

You should see Alice with an order_count of 2, and Bob, Carol, and David each with 1.

Getting a column must appear in GROUP BY error?

  • Every column in your SELECT that is not wrapped in an aggregate function (SUM, COUNT, AVG) must be listed in the GROUP BY clause.
  • If you selected first_name but only grouped by customer_id, add first_name to your GROUP BY.

Help me fix my GROUP BY query

Filter aggregated results with HAVING

WHERE filters individual rows before grouping. HAVING filters groups after the aggregation is complete. Use HAVING when you want to keep only groups that meet a condition on the aggregated value.

  • Run this query to find customers who have spent more than $50 total:
-- Customers who spent more than $50
SELECT
    c.first_name,
    c.last_name,
    SUM(o.total_amount) AS total_spent
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
HAVING SUM(o.total_amount) > 50;

Why HAVING instead of WHERE?

The total_spent value does not exist until PostgreSQL finishes grouping and summing. WHERE runs before grouping happens, so it cannot see aggregated values. HAVING runs after grouping, so it can filter on SUM, COUNT, or any other aggregate result.

You should see all four customers in the result because each one has spent more than $50.

  • Run this query to find products that have sold more than 2 units:
-- Products that sold more than 2 units
SELECT
    p.name,
    SUM(oi.quantity) AS total_sold
FROM products p
INNER JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.name
HAVING SUM(oi.quantity) > 2;

What does this query do?

This query groups order items by product, sums the quantity, and then uses HAVING to keep only products where the total sold exceeds 2. Products with exactly 1 or 2 units sold are excluded from the results.

You should see USB-C Hub in the result with a total_sold of 3 (it appears in three separate order items).

HAVING query returning no rows?

  • Double-check your threshold value. If no group meets the condition, an empty result is correct.
  • Make sure you are using HAVING (filters groups) and not WHERE (filters rows before grouping).
  • Confirm your order_items data was inserted correctly by running SELECT * FROM order_items; and checking you have 10 rows.

Help me understand my HAVING query results

✔️ Awesome, I've got everything!

Great. All seven queries should be returning the expected results in your psql session.

ⓧ I'd like to double check the full code

Here is the complete 04_join_queries.sql file with all the queries from this step:

-- Each order with customer name
SELECT
    o.order_id,
    c.first_name,
    c.last_name,
    o.total_amount,
    o.order_date
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;

-- Order items with product and customer details
SELECT
    c.first_name,
    c.last_name,
    p.name AS product_name,
    oi.quantity,
    oi.unit_price
FROM order_items oi
INNER JOIN orders o ON oi.order_id = o.order_id
INNER JOIN customers c ON o.customer_id = c.customer_id
INNER JOIN products p ON oi.product_id = p.product_id;

-- Total spent per customer
SELECT
    c.first_name,
    c.last_name,
    SUM(o.total_amount) AS total_spent
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY total_spent DESC;

-- Top-selling products by quantity
SELECT
    p.name,
    SUM(oi.quantity) AS total_sold
FROM products p
INNER JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.name
ORDER BY total_sold DESC;

-- Orders per customer
SELECT
    c.first_name,
    c.last_name,
    COUNT(o.order_id) AS order_count
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name;

-- Customers who spent more than $50
SELECT
    c.first_name,
    c.last_name,
    SUM(o.total_amount) AS total_spent
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
HAVING SUM(o.total_amount) > 50;

-- Products that sold more than 2 units
SELECT
    p.name,
    SUM(oi.quantity) AS total_sold
FROM products p
INNER JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.name
HAVING SUM(oi.quantity) > 2;

You can now answer real business questions by combining data across tables with JOINs and summarizing it with aggregates. Next up, you will make these queries faster by adding indexes and measuring the performance difference.

Optimize with Indexes

Your JOIN and aggregation queries are answering real business questions. But as your database grows from 6 products to 60,000, those queries will slow down.

Indexes are the solution. They work like a book's index, letting PostgreSQL jump straight to matching rows instead of scanning the entire table. In this step, you will measure performance before and after adding indexes.

In this step, get ready to:
  • Measure query performance using EXPLAIN ANALYZE.
  • Create indexes on frequently-queried columns.
  • Verify how the PostgreSQL optimizer uses your indexes.
Measure query performance without indexes

Before creating any indexes, you need a baseline. PostgreSQL provides a tool called EXPLAIN ANALYZE that shows exactly how the database executes a query and how long it takes.

  • Run this command in your psql session to see how PostgreSQL finds products priced above 20:
-- Check query plan before indexes
EXPLAIN ANALYZE SELECT * FROM products WHERE price > 20;

What does EXPLAIN ANALYZE do?

EXPLAIN ANALYZE does two things. First, it actually runs the query. Second, it shows you the execution plan the optimizer chose, including the strategy and timing.

You should see Seq Scan in the output. This means PostgreSQL is scanning every row in the table sequentially to find matches. For 6 rows this is fast, but imagine doing this across millions of rows.

  • Look at the output. Note the Seq Scan on products line and the execution time reported at the bottom.

Reading the execution time

The timing is shown in milliseconds. On a small table like this, it will be very fast (under 1ms). The important thing is the scan type, not the raw speed.

Create indexes on frequently-queried columns

Now that you have a baseline, it is time to add indexes. You will create them on columns that appear in WHERE clauses and JOIN conditions, since those are the columns PostgreSQL needs to search through most often.

  • Create three indexes by running these commands:
-- Create indexes
CREATE INDEX idx_products_price ON products (price);
CREATE INDEX idx_orders_customer_id ON orders (customer_id);
CREATE INDEX idx_order_items_product_id ON order_items (product_id);

What do these indexes target?

  • idx_products_price speeds up queries that filter by price (like WHERE price > 20).
  • idx_orders_customer_id speeds up JOINs between orders and customers.
  • idx_order_items_product_id speeds up JOINs between order_items and products.
  • Confirm your indexes exist by running:
\di

You should see your three new indexes listed alongside the automatic primary key indexes that PostgreSQL created when you defined your tables.

Don't see your indexes?

Make sure each CREATE INDEX statement ended with a semicolon. If you see an error about the index already existing, that means it was created successfully on a prior attempt.

help me figure out why my CREATE INDEX command is not working in psql

Verify index usage with EXPLAIN ANALYZE

With indexes in place, you can re-run the same query and compare the execution plan. The PostgreSQL optimizer decides whether to use an index or stick with a sequential scan based on what it calculates will be faster.

  • Re-run the same EXPLAIN ANALYZE query from earlier:
-- Check query plan after indexes
EXPLAIN ANALYZE SELECT * FROM products WHERE price > 20;

Why does it still show Seq Scan?

With only 6 rows, PostgreSQL's optimizer knows a full table scan is actually faster than looking up the index first and then fetching rows. This is smart behavior, not a failure.

Indexes shine on large tables (thousands or millions of rows). The optimizer always makes the final call on which strategy is cheapest.

  • Now test how indexes help with a JOIN query by running:
-- Check index usage on a JOIN
EXPLAIN ANALYZE
SELECT c.first_name, c.last_name, o.total_amount
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
WHERE o.total_amount > 50;

What should I see?

The output shows how PostgreSQL joins the two tables. You may see Index Scan or Bitmap Index Scan mentioned with larger datasets. On your small dataset, expect sequential scans to remain the chosen strategy.

The key takeaway is that you can now read a query plan. You know where to look (scan type, timing) and what it means for performance at scale.

  • Run \di one more time to confirm all three indexes are still listed.

Extra for Experts: When do indexes hurt?

Indexes speed up reads but slow down writes. Every INSERT, UPDATE, or DELETE must also update the index. For write-heavy tables with rare reads, too many indexes can actually degrade overall performance.

✔️ Awesome, I've got everything!

Great. Make sure you have saved all the SQL you ran in this step to a file called 05_indexes.sql for your records.

ⓧ I'd like to double check the full code

Here is everything you should have in 05_indexes.sql:

-- Check query plan before indexes
EXPLAIN ANALYZE SELECT * FROM products WHERE price > 20;

-- Create indexes
CREATE INDEX idx_products_price ON products (price);
CREATE INDEX idx_orders_customer_id ON orders (customer_id);
CREATE INDEX idx_order_items_product_id ON order_items (product_id);

-- Check query plan after indexes
EXPLAIN ANALYZE SELECT * FROM products WHERE price > 20;

-- Check index usage on a JOIN
EXPLAIN ANALYZE
SELECT c.first_name, c.last_name, o.total_amount
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
WHERE o.total_amount > 50;

You now have a complete, optimized database with tables, constraints, JOINs, aggregations, and indexes. Next up is the Secret Mission, where you will add pattern search, subqueries, and a reusable VIEW.

Secret mission

Add Product Search, Subqueries, and Views

Your online store database is solid, but real applications need more powerful query techniques. In this secret mission, you'll add pattern-based product search with LIKE, write nested subqueries that answer complex questions in a single statement, and create a reusable VIEW that acts like a virtual table for reporting.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally on your machine, so there are no ongoing costs. The online_store database takes up minimal disk space in your PostgreSQL installation.

Decide whether to keep your database running, leave it idle for later, or delete it entirely.

Resources you created:

  • The online_store PostgreSQL database (contains products, customers, orders, and order_items tables, plus indexes and views).

Keep everything running

No action needed. Choose this if you plan to keep practicing SQL queries or want to experiment with more complex queries on your existing data.

  • The database persists between sessions. You can close psql and reconnect anytime with psql -U postgres followed by \c online_store.
  • Your tables, data, indexes, and views all remain exactly as you left them.
  • The database uses minimal disk space (well under 1 MB for this dataset).

Pause - I'll come back to this later

PostgreSQL databases persist on disk automatically. There is nothing to pause or shut down for the database itself.

  • Close your psql session by running \q.
  • If you want to stop the PostgreSQL server from running in the background, open the Windows Services app, find postgresql-x64-18, and click Stop.
  • To restart later, return to Services and click Start on the same entry.

Delete - I don't want to use this again

Remove the project database entirely. This permanently deletes all tables, data, indexes, and views you created.

  • You must be connected to a different database before dropping online_store. Switch to the default postgres database by running:
\c postgres
  • Drop the online_store database by running:
DROP DATABASE online_store;
  • Verify the database is gone by running \l. You should no longer see online_store in the list.
  • Exit psql by running \q.

Nice Work!

Nice Work!

Nice work! You just built a fully functional online store database from scratch using PostgreSQL.

You've learned how to:

  • Built a relational database with four interconnected tables enforcing data integrity through PRIMARY KEY, FOREIGN KEY, CHECK, and UNIQUE constraints.
  • Queried across multiple tables using INNER JOIN and answered business questions with GROUP BY, SUM, COUNT, AVG, and HAVING.
  • Created indexes on frequently-queried columns and used EXPLAIN ANALYZE to understand how PostgreSQL plans and executes your queries.
  • Secret Mission: Implemented pattern search with LIKE, wrote subqueries, and created a VIEW for reusable reporting.

Ready to quiz yourself?