Mini Amazon Product Catalog on RDS

Build an e-commerce database on AWS RDS PostgreSQL with SQL queries.

Introduction

30 Second Summary

Every time you search for a product on Amazon, a database is working behind the scenes. It connects products, categories, customers, and orders so the storefront knows what to show you and how to track your purchase.

In this project, you will build your own version of that e-commerce database on Amazon's cloud. You will spin up a live PostgreSQL database on AWS RDS, design tables from scratch, and write SQL queries that simulate real storefront operations.

What You'll Build

Picture connecting to your own cloud database from a terminal window, running a query, and instantly seeing a ranked list of your store's top-selling products with revenue totals.

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

A live PostgreSQL database running in the AWS cloud that you can connect to and query directly from your Mac terminal.
A product catalog with categories and products you can browse, modeled after how Amazon organizes its storefront.
SQL queries that answer real business questions like "What are the top-selling products?" and "How much revenue did each category generate?"
Secret Mission: Add product reviews and ratings to your database, then query for the highest-rated products.

Are there any prerequisites?

You will need an AWS account on a paid plan. Homebrew and the PostgreSQL client (psql) will be installed during Step 1.

Before We Start

Before we dive in, let's lock in what you're about to build and why it matters.

Install psql and Set Up Your AWS RDS Database

You are about to build a cloud-hosted e-commerce database, but first you need two things: a tool to talk to the database, and the database itself. The tool is psql, the official PostgreSQL command-line client. The database will live on Amazon RDS, a managed service that handles the heavy lifting of running a database server in the cloud.

In this step, you will install psql on your Mac, spin up a PostgreSQL instance on RDS, and connect to it from your terminal.

In this step, get ready to:

Install the psql client on your Mac using Homebrew.
Create a PostgreSQL RDS instance in the AWS Console.
Connect to your cloud database from your terminal.

Install the psql client using Homebrew

To connect to a remote PostgreSQL database, you need the psql client on your Mac. You will install it through Homebrew, the most popular package manager for macOS.

Open your terminal by pressing Cmd+Space to open your search bar.
Type Terminal and press Enter to open it.
Check if Homebrew is already installed by running this command:

brew --version

✔️ I see a version number

Homebrew is ready. Move on to the next command below.

ⓧ Command not found

Homebrew is not installed yet. That is expected if this is your first time using it.

Install Homebrew by running this command:

/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

What does this command do?

This downloads and runs the official Homebrew installer from GitHub. The script will explain what it plans to do and ask you to confirm before making any changes.

Follow the post-install instructions

After the install finishes, Homebrew may print instructions to add it to your PATH. Follow those instructions before continuing. They typically ask you to run two commands that start with eval.

Confirm Homebrew is working by running:

brew --version

You should see a version number printed in your terminal.

Still seeing command not found?

Your terminal may not have picked up the new PATH yet. Close the terminal window, open a fresh one, and try brew --version again.

If it still does not work, re-read the post-install instructions Homebrew printed and make sure you ran the eval commands it gave you.

Help me install Homebrew on my Mac

Now that Homebrew is ready, you can install libpq. This is a lightweight PostgreSQL client package that includes psql without installing a full database server on your Mac.

Install libpq by running this command:

brew install libpq

Why libpq instead of the full PostgreSQL package?

The libpq formula gives you just the client tools (including psql) without installing a local PostgreSQL server. Since your database will run on AWS RDS, you only need the client to connect to it remotely.

Homebrew installs libpq as "keg-only," which means it does not automatically add psql to your PATH. You need to link it explicitly.

Link libpq so psql becomes available in your terminal by running:

brew link --force libpq

What does keg-only mean?

Homebrew keeps some packages in their own isolated folder (a "keg") to avoid conflicts with other software. The brew link --force command creates shortcuts (symlinks) so your terminal can find psql without needing the full path.

Before you run the version check, what output do you think psql --version will print?

Confirm psql is available by running:

psql --version

You should see a version number printed, confirming that psql is installed and accessible.

Still seeing command not found for psql?

Make sure you ran brew link --force libpq and that it completed without errors. If you see a warning about existing files, run brew link --force --overwrite libpq instead.

If the link command succeeded but psql is still not found, open a new terminal window and try again.

Help me get psql working on my Mac

Create a PostgreSQL RDS instance in the AWS Console

With psql installed, you now need the database it will connect to. You will create a PostgreSQL database instance on AWS RDS using the AWS Management Console.

Open your browser and go to the RDS console at https://console.aws.amazon.com/rds/.
Sign in to your AWS account if prompted.
Click Create database.

You will now configure the database settings. There are many options on this page. Follow each setting carefully.

Under Choose a database creation method, select Standard create.
Under Engine type, select PostgreSQL.
Under Templates, select Sandbox.

Why Standard create instead of Easy create?

Easy create defaults to private access, meaning your database would only be reachable from inside the AWS network. Since you are connecting from your Mac, you need public access. Standard create lets you configure this.

Under DB instance class, select db.t3.micro.
Under DB instance identifier, enter storefront-db.
Under Master username, leave the default value postgres.
Under Master password, choose a password you will remember and enter it. Confirm it in the second field.

Write down your password

You will need this password to connect from your terminal. Save it somewhere safe for the next few minutes.

Now scroll down to the Connectivity section. This is where you tell AWS to allow connections from your Mac.

Under Connectivity, expand Additional configuration if it is not already visible.
Set Public access to Yes.

What is public access?

Setting Public access to Yes gives your database a public endpoint that your Mac can reach over the internet. Without this, the database would only be accessible from inside the AWS cloud network, and your psql client could not connect to it.

Next, you need to set up a security group rule to allow PostgreSQL traffic from your computer.

Under VPC security group, select Create new to create a new security group.
Give the security group a name you can recognise, such as storefront-sg.

Now scroll down to the Additional configuration section at the very bottom of the page.

Under Initial database name, enter storefront.
Leave all other settings at their defaults.
Click Create database at the bottom of the page.

Why set an initial database name?

If you skip this field, RDS creates the instance without a user database inside it. Setting it to storefront means the storefront database is ready and waiting when the instance finishes launching.

AWS will now start creating your database. This takes up to 10 minutes. You will see the status change from Creating to Available when it is ready.

While you wait, you need to update the security group to allow your Mac to connect on the PostgreSQL port.

Click on your new database instance name (storefront-db) in the databases list.
Scroll down to the Connectivity & security section.
Under Security, click the link to your VPC security group (it will have the name you set, like storefront-sg).
Click the Inbound rules tab.
Click Edit inbound rules.
Click Add rule.
Set Type to PostgreSQL. This automatically sets the port to 5432.
Set Source to My IP. AWS will auto-detect your current IP address.
Click Save rules.

What is a security group?

A security group acts as a firewall for your database. By default, all network access to a new RDS instance is blocked. The inbound rule you just added says: "allow connections on port 5432 (PostgreSQL) only from my specific IP address."

This keeps your database safe while still letting your Mac connect.

Don't see the security group link?

Make sure you clicked on the storefront-db instance name, not the checkbox next to it. The security group link appears in the Connectivity & security section of the instance detail page.

Help me find the security group settings for my RDS instance

Connect to your database from Terminal

Your database is running in the cloud, and your security group allows your Mac to reach it. Time to connect.

Go back to the RDS console and click on your storefront-db instance.
Click the Connectivity & security tab.
Find the Endpoint value. It looks something like storefront-db.xxxxxxxxxxxx.us-east-1.rds.amazonaws.com. Copy it.

Do you think the connection will succeed? You have the client installed, the database running, and the firewall rule in place.

Switch back to your terminal and connect to the database by running this command:

psql --host=[[RDS_ENDPOINT="your RDS endpoint e.g. storefront-db.xxxxxxxxxxxx.us-east-1.rds.amazonaws.com"]] --port=5432 --username=postgres --password --dbname=storefront

When prompted, enter the master password you set during instance creation.

What do these flags mean?

--host is the RDS endpoint you just copied. It tells psql where to find the database server.
--port=5432 is the default PostgreSQL port.
--username=postgres is the master username you left as the default.
--password tells psql to prompt you for your password instead of passing it in the command.
--dbname=storefront connects directly to the storefront database you created with the instance.

You should see the storefront=> prompt. This confirms you are connected to your cloud database from your local terminal.

Seeing a connection timeout or refused error?

Check these common causes:

Make sure the RDS instance status is Available, not Creating. If it is still creating, wait a few more minutes.
Confirm Public access is set to Yes on the instance. Check the Connectivity & security tab in the RDS console.
Verify the security group inbound rule has Type set to PostgreSQL (port 5432) and Source set to My IP.

If your IP address changed since you created the rule (e.g. you switched WiFi networks), update the security group rule with your new IP.

Help me connect psql to my RDS instance

Your database is live and you are connected. Next up, you will design the product catalog schema and fill it with data.

Design Your Product Catalog Schema

Your PostgreSQL database is running on AWS and you're connected from your terminal. Right now, though, it's completely empty.

Amazon organizes millions of products into categories like Electronics, Books, and Clothing. Each product belongs to exactly one category. In this step, you'll create that same structure inside your database using two tables connected by a foreign key.

In this step, get ready to:

Create categories and products tables with proper data types and constraints.
Insert realistic sample data modeled after an Amazon-style storefront.
Browse products by category using a SQL JOIN query.

Create the categories and products tables

Every product on Amazon belongs to a category. To model this, you need two tables: one for categories and one for products, linked together so every product points back to its category.

Create the categories table by pasting this SQL into your storefront=> prompt:

-- Create the categories table
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE
);

What does this code do?

SERIAL creates an auto-incrementing integer. Each new category automatically gets the next number as its id.
PRIMARY KEY makes id the unique identifier for each row. No two categories can share the same id.
VARCHAR(100) stores text up to 100 characters. NOT NULL UNIQUE means every category must have a name, and no two categories can share the same name.

After you press Enter, psql prints CREATE TABLE to confirm the table was created.

Seeing a syntax error?

Make sure you included the semicolon at the very end of the statement. Without it, psql waits for more input instead of running the command.
If you see relation "categories" already exists, you already ran this command. That's fine. Move on to the next table.

Why is my CREATE TABLE statement failing in psql?

Create the products table by pasting this SQL:

-- Create the products table
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price_cents INTEGER NOT NULL CHECK (price_cents > 0),
    stock INTEGER NOT NULL DEFAULT 0,
    category_id INTEGER NOT NULL REFERENCES categories(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

What does this code do?

price_cents INTEGER NOT NULL CHECK (price_cents > 0) stores prices as whole-number cents instead of decimal dollars. This avoids floating-point rounding errors. A product that costs $79.99 is stored as 7999. Stripe, Amazon, and most e-commerce systems use this pattern.
CHECK (price_cents > 0) is a constraint that prevents anyone from inserting a product with a zero or negative price.
category_id INTEGER NOT NULL REFERENCES categories(id) is a foreign key. It forces every product to point to a valid category. You cannot insert a product with a category_id that does not exist in the categories table.
DEFAULT 0 on stock means new products start with zero stock unless you specify otherwise. DEFAULT NOW() on created_at automatically records the current timestamp when a product is added.

You should see CREATE TABLE printed again.
Confirm both tables exist by running this command:

\dt

You should see categories and products listed in a table with columns for Schema, Name, Type, and Owner.

Don't see both tables?

If you see only one table, scroll up and check whether the other CREATE TABLE printed an error. You may need to re-paste that statement.
Make sure you created categories before products. The products table has a foreign key that references categories, so categories must exist first.

Why don't I see my tables when I run \dt in psql?

✔️ Awesome, I've got everything!

Great. Both tables are ready for data.

ⓧ I'd like to double check the full code

Here is the complete schema you should have run. Compare it against what you pasted into psql.

-- Create the categories table
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE
);

-- Create the products table
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price_cents INTEGER NOT NULL CHECK (price_cents > 0),
    stock INTEGER NOT NULL DEFAULT 0,
    category_id INTEGER NOT NULL REFERENCES categories(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

Insert sample data and browse products

Tables without data are like empty shelves. Let's stock your storefront with categories and products so you can query them.

Insert four product categories by running this statement:

-- Insert categories
INSERT INTO categories (name) VALUES
    ('Electronics'),
    ('Books'),
    ('Home and Kitchen'),
    ('Clothing');

What does this code do?

This inserts four rows into the categories table. You only need to provide the name column because id is auto-generated by SERIAL. Electronics gets id 1, Books gets id 2, and so on.

psql prints INSERT 0 4 to confirm all four rows were added.
Verify the categories by running:

SELECT * FROM categories;

You should see a table with four rows: Electronics, Books, Home and Kitchen, and Clothing, each with an auto-assigned id.

Seeing a duplicate key error?

If you see a message about a unique constraint violation, you have already inserted these categories. Run SELECT * FROM categories; to confirm the data is there and move on.

Why am I getting a duplicate key error when inserting categories?

Insert nine products across all four categories by running this statement:

-- Insert products
INSERT INTO products (name, description, price_cents, stock, category_id) VALUES
    ('Wireless Bluetooth Headphones', 'Noise-canceling over-ear headphones with 30-hour battery life', 7999, 45, 1),
    ('USB-C Charging Cable 6ft', 'Braided nylon fast-charging cable', 1299, 200, 1),
    ('Portable Phone Stand', 'Adjustable aluminum desk stand for phones and tablets', 1899, 80, 1),
    ('SQL For Beginners', 'A hands-on guide to learning SQL from scratch', 2499, 120, 2),
    ('The Art of Problem Solving', 'Strategies for creative and critical thinking', 1899, 65, 2),
    ('Stainless Steel Water Bottle', 'Double-wall insulated 32oz bottle', 2199, 150, 3),
    ('Non-Stick Frying Pan 10 inch', 'Ceramic coated pan with cool-touch handle', 3499, 35, 3),
    ('Cotton Crew Neck T-Shirt', '100% organic cotton, unisex fit', 1499, 300, 4),
    ('Running Shorts', 'Lightweight moisture-wicking shorts with zip pocket', 2799, 90, 4);

What does this code do?

Each row provides a product name, description, price in cents, stock quantity, and a category_id that links the product to one of the four categories you just created.
Notice that category_id uses the actual id values from the categories table: 1 for Electronics, 2 for Books, 3 for Home and Kitchen, 4 for Clothing. The foreign key constraint prevents you from using an id that does not exist.
Prices like 7999 represent $79.99. You will convert cents to dollars in your queries later.

psql prints INSERT 0 9 to confirm all nine products were added.

Seeing a foreign key violation?

This means a category_id in your products does not match any id in the categories table. Run SELECT * FROM categories; to check which IDs exist, and make sure you inserted the four categories first.

Why am I getting a foreign key violation when inserting products?

✔️ Awesome, I've got everything!

Great. Your storefront is stocked with categories and products.

ⓧ I'd like to double check the full code

Here is the complete seed data you should have run. Compare it against what you pasted into psql.

-- Insert categories
INSERT INTO categories (name) VALUES
    ('Electronics'),
    ('Books'),
    ('Home and Kitchen'),
    ('Clothing');

-- Insert products
INSERT INTO products (name, description, price_cents, stock, category_id) VALUES
    ('Wireless Bluetooth Headphones', 'Noise-canceling over-ear headphones with 30-hour battery life', 7999, 45, 1),
    ('USB-C Charging Cable 6ft', 'Braided nylon fast-charging cable', 1299, 200, 1),
    ('Portable Phone Stand', 'Adjustable aluminum desk stand for phones and tablets', 1899, 80, 1),
    ('SQL For Beginners', 'A hands-on guide to learning SQL from scratch', 2499, 120, 2),
    ('The Art of Problem Solving', 'Strategies for creative and critical thinking', 1899, 65, 2),
    ('Stainless Steel Water Bottle', 'Double-wall insulated 32oz bottle', 2199, 150, 3),
    ('Non-Stick Frying Pan 10 inch', 'Ceramic coated pan with cool-touch handle', 3499, 35, 3),
    ('Cotton Crew Neck T-Shirt', '100% organic cotton, unisex fit', 1499, 300, 4),
    ('Running Shorts', 'Lightweight moisture-wicking shorts with zip pocket', 2799, 90, 4);

Now for the real payoff. You have products spread across four categories, but a basic SELECT * FROM products only shows the raw category_id number. Before you run the next query, think about this: how would you display the actual category name alongside each product?

Browse all products with their category names by running this JOIN query:

-- Browse all products with their category names
SELECT
    p.name AS product,
    c.name AS category,
    p.price_cents / 100.0 AS price_dollars,
    p.stock
FROM products p
JOIN categories c ON p.category_id = c.id
ORDER BY c.name, p.name;

What does this query do?

JOIN categories c ON p.category_id = c.id connects each product to its category by matching the foreign key to the primary key. This is how you combine data from two related tables.
p and c are aliases. Instead of writing products.name every time, you write p.name.
p.price_cents / 100.0 converts the integer cents to a human-readable dollar amount. Dividing by 100.0 (not 100) forces decimal output so you see 79.99 instead of 79.
ORDER BY c.name, p.name sorts results alphabetically by category first, then by product name within each category.

You should see all nine products listed in a table, grouped by category. Each row shows the product name, category name, price in dollars, and stock count.

Seeing zero rows?

Run SELECT * FROM products; to check whether the products table has data. If it is empty, re-run the INSERT statement for products.
Run SELECT * FROM categories; to check whether categories has data. The JOIN returns rows only when it finds matching records in both tables.

Why does my JOIN query return zero rows even though I inserted data?

Why store prices as cents?

Floating-point numbers like 79.99 can introduce tiny rounding errors in math operations. Over thousands of transactions, those errors add up. Storing money as integer cents (7999) keeps every calculation exact.

You only convert to dollars at display time, like the price_cents / 100.0 in your query. This is the same approach Stripe and most production e-commerce platforms use.

Your product catalog is live. You have categories, products, and a query that browses them together. Next up, you'll build the order system so customers can actually buy these products.

Build the Order System

In the last step, you built a product catalog with categories and products. Right now, though, your database is just a list.

The real value of a database like Amazon's is tracking who buys what. In this step, you will create customers, orders, and order items to model real purchases. This is where foreign keys really shine. They connect customers to their orders, and orders to the products purchased.

In this step, get ready to:

Create the customers, orders, and order_items tables with foreign key relationships.
Insert sample customers and simulate placing orders with multiple items.
Write a multi-table JOIN query that shows complete orders with customer names, products, and totals.

Create the customers, orders, and order_items tables

Every order on Amazon starts with a person. You need a place to store who your customers are before you can track what they buy.

Create the customers table by running this SQL in your storefront=> prompt:

-- Create the customers table
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE
);

What does this table do?

SERIAL PRIMARY KEY auto-generates a unique ID for each customer, just like the categories and products tables.
UNIQUE on email prevents duplicate accounts. No two customers can share the same email address.

You should see CREATE TABLE printed in your terminal confirming the table was created.

Next, you need an orders table that links each order back to the customer who placed it.

Create the orders table by running:

-- Create the orders table
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id),
    order_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    status VARCHAR(20) NOT NULL DEFAULT 'pending'
);

What does this table do?

REFERENCES customers(id) creates a foreign key. Every order MUST belong to an existing customer. PostgreSQL will reject any order that points to a customer ID that does not exist.
order_date defaults to the current time using NOW(), so you do not need to specify it manually.
status defaults to 'pending', which you can update later to 'completed' or 'cancelled'.

You should see CREATE TABLE again.

The final table is the most important one. A single order on Amazon can contain multiple products. The order_items table tracks each individual product line within an order.

Create the order_items table by running:

-- Create the order_items table
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders(id),
    product_id INTEGER NOT NULL REFERENCES products(id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price_cents INTEGER NOT NULL CHECK (unit_price_cents > 0)
);

Why store the price in order_items?

Notice unit_price_cents is stored here, even though the products table already has a price_cents column. Product prices change over time. If you just looked up the current price from products, an order from last year would show today's price instead of what the customer actually paid.

Copying the price at the time of purchase into order_items is the standard e-commerce pattern used by companies like Amazon and Stripe.

Confirm all five tables exist by running:

\dt

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

Missing a table or seeing an error?

If you see an error about a relation already existing, you may have run a CREATE TABLE statement twice. That is fine. The table is already there.
If you see an error about a referenced table not existing, make sure you created the tables in order: customers first, then orders, then order_items. Foreign keys require the referenced table to exist first.
help me fix a CREATE TABLE error in psql

Insert sample customers and simulate orders

Empty tables do not tell you much. You need sample customers and orders so you can test queries that join everything together.

Add three sample customers by running:

-- Insert customers
INSERT INTO customers (name, email) VALUES
    ('Alice Johnson', 'alice@example.com'),
    ('Bob Martinez', 'bob@example.com'),
    ('Carol Chen', 'carol@example.com');

What does this do?

This inserts three customers. You do not need to specify the id column because SERIAL auto-generates it. Alice gets id 1, Bob gets id 2, and Carol gets id 3.

Verify the customers were inserted by running:

SELECT * FROM customers;

You should see a table with three rows showing Alice Johnson, Bob Martinez, and Carol Chen with their auto-generated IDs.

Insert four orders by running:

-- Insert orders
INSERT INTO orders (customer_id, status) VALUES
    (1, 'completed'),
    (2, 'completed'),
    (1, 'pending'),
    (3, 'completed');

What does this do?

This creates four orders. Alice (customer 1) has two orders, Bob (customer 2) has one, and Carol (customer 3) has one. Notice you only supply customer_id and status. The order_date column fills in automatically with the current timestamp.

You should see INSERT 0 4 confirming all four orders were added.

Now for the most detailed insert. Each order can contain multiple products, and every line item records the product, quantity, and the price at the time of purchase.

Insert the order items for all four orders by running:

-- Insert order items
-- Order 1: Alice buys headphones and a charging cable
INSERT INTO order_items (order_id, product_id, quantity, unit_price_cents) VALUES
    (1, 1, 1, 7999),
    (1, 2, 2, 1299);

-- Order 2: Bob buys the SQL book and a water bottle
INSERT INTO order_items (order_id, product_id, quantity, unit_price_cents) VALUES
    (2, 4, 1, 2499),
    (2, 6, 1, 2199);

-- Order 3: Alice buys a t-shirt and running shorts
INSERT INTO order_items (order_id, product_id, quantity, unit_price_cents) VALUES
    (3, 8, 2, 1499),
    (3, 9, 1, 2799);

-- Order 4: Carol buys headphones, the frying pan, and a book
INSERT INTO order_items (order_id, product_id, quantity, unit_price_cents) VALUES
    (4, 1, 1, 7999),
    (4, 7, 1, 3499),
    (4, 5, 2, 1899);

What does this data represent?

Each row is one line item in an order. The order_id ties it to a specific order, and product_id ties it to a specific product. The unit_price_cents is the price at the moment of purchase, locked in regardless of future price changes.

For example, Alice's first order (order 1) has two items: one pair of headphones at $79.99 and two charging cables at $12.99 each.

You should see multiple INSERT 0 2 and INSERT 0 3 confirmations, one for each INSERT statement.

Seeing a foreign key violation?

Make sure you inserted customers before orders, and orders before order items. Foreign keys require the referenced row to exist first.
Double check that the customer_id, order_id, and product_id values match the IDs that were auto-generated. If you dropped and re-created a table, the IDs may have shifted.
help me fix a foreign key violation error in psql

Now comes the payoff. You have customers, orders, and order items spread across three tables. A single JOIN query can pull all of that together into one readable view.

Before you run the next query, think about what you expect to see. How many rows should appear if there are 9 order items across 4 orders?

Run this multi-table JOIN query to view complete orders:

-- View a complete order with customer name, products, quantities, and line totals
SELECT
    cu.name AS customer,
    o.id AS order_id,
    o.order_date,
    o.status,
    p.name AS product,
    oi.quantity,
    oi.unit_price_cents / 100.0 AS unit_price,
    (oi.quantity * oi.unit_price_cents) / 100.0 AS line_total
FROM orders o
JOIN customers cu ON o.customer_id = cu.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON oi.product_id = p.id
ORDER BY o.id, p.name;

What does this query do?

It joins four tables: orders to customers (to get the customer name), orders to order_items (to get what was purchased), and order_items to products (to get the product name).
oi.unit_price_cents / 100.0 converts cents to dollars for readability.
(oi.quantity * oi.unit_price_cents) / 100.0 calculates the line total for each item in the order.

You should see 9 rows. Each row shows one line item: the customer name, order ID, date, status, product name, quantity, unit price in dollars, and the line total in dollars.

For example, Alice Johnson's first order should show Wireless Bluetooth Headphones at $79.99 and two USB-C Charging Cables at $12.99 each ($25.98 total for that line).

Seeing zero rows or an error?

Zero rows usually means the INSERT statements for orders or order_items did not run. Run SELECT COUNT(*) FROM order_items; to check. You should see 9.
If you see a column-not-found error, check for typos in the column aliases. The query uses cu for customers, o for orders, oi for order_items, and p for products.
help me debug my multi-table JOIN query in psql

Your storefront database now tracks real purchases from start to finish. Next up, you will write queries that answer the kind of business questions Amazon runs every day: top sellers, revenue by category, and low-stock alerts.

Query Your Storefront Like Amazon Does

Your storefront database is fully wired up. Products belong to categories, customers place orders, and order items link purchases to products with prices locked in at the time of sale.

Amazon runs business queries like these thousands of times a second: what's selling best, which category generates the most revenue, what's running low on stock. So far you have only written basic SELECT and JOIN queries. To answer questions that summarize data across many rows, you need a new set of tools: aggregate functions and GROUP BY.

In this step, get ready to:

Write aggregate queries using SUM and GROUP BY to find top-selling products and revenue by category.
Build inventory alerts and customer order history reports.

Find your top-selling products

You want to know which product sold the most units across all orders. Your first instinct might be to join order_items with products and look at the quantities. Before you learn the fix, try that approach and see what happens.

In your storefront=> prompt, run this query to see product names and their quantities from every order:

Before you run this, what do you think you will see? A total per product, or something else?

-- Attempt: show product names and quantities
SELECT p.name AS product, oi.quantity
FROM order_items oi
JOIN products p ON oi.product_id = p.id
ORDER BY p.name;

You see individual rows for each order line item. Wireless Bluetooth Headphones appears twice because two different customers ordered it, but there is no total. This is the wall: a plain SELECT with a JOIN cannot summarize data across multiple rows.

What are aggregate functions?

Aggregate functions like SUM, COUNT, and AVG collapse many rows into one summary value. GROUP BY tells the database which column to group the rows by before applying the aggregate.

Together, they turn "show me each order line" into "show me the total units sold per product."

Find your top-selling products by total units sold by running this query:

-- Top-selling products by units sold
SELECT
    p.name AS product,
    SUM(oi.quantity) AS total_units_sold
FROM order_items oi
JOIN products p ON oi.product_id = p.id
GROUP BY p.name
ORDER BY total_units_sold DESC;

What does this query do?

SUM(oi.quantity) adds up all the quantities for each product across every order.
GROUP BY p.name collapses rows so you get one row per product instead of one row per order line.
ORDER BY total_units_sold DESC ranks the products from highest to lowest, so your best seller appears first.

You should see a ranked list with Wireless Bluetooth Headphones and Cotton Crew Neck T-Shirt at the top with 2 units each, and the rest at 1 unit.

Seeing an error about GROUP BY?

Every column in your SELECT that is not inside an aggregate function must appear in the GROUP BY clause. Make sure p.name is in both places.
Check that the query ends with a semicolon. Without it, psql waits for more input instead of running the query.

Still stuck? Help me fix my GROUP BY query in psql. I am trying to find top-selling products by summing order item quantities.

Calculate revenue by category

Knowing which products sell the most units is useful, but Amazon also needs to know which categories bring in the most money. This query joins across three tables to connect order items to their product categories.

Calculate total revenue per category by running this query:

Before you run this, which category do you predict will have the highest revenue?

-- Revenue by category
SELECT
    c.name AS category,
    SUM(oi.quantity * oi.unit_price_cents) / 100.0 AS total_revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.id
JOIN categories c ON p.category_id = c.id
GROUP BY c.name
ORDER BY total_revenue DESC;

What does this query do?

SUM(oi.quantity * oi.unit_price_cents) multiplies the quantity by the price for each order line, then sums those values per group.
The query chains two JOINs: order_items to products (to get the category ID), then products to categories (to get the category name).
Dividing by 100.0 converts cents back to dollars for readability.

You should see a ranked list with category names and total revenue amounts. Electronics should be at the top because it includes higher-priced items like the headphones.

Query returns no rows?

Make sure both JOIN conditions are correct: oi.product_id = p.id and p.category_id = c.id. Swapping column names will silently return zero rows.
Confirm your order items data exists by running SELECT COUNT(*) FROM order_items; and checking it returns 9.

Need help? Help me debug my revenue-by-category query that joins order_items, products, and categories.

Build stock alerts and customer order history

Revenue and top sellers are great, but Amazon also needs to know when inventory is running low. A low-stock alert prevents selling products that are almost out of stock.

Find all products with stock below 50 units by running this query:

-- Low stock alert: products with stock below 50
SELECT
    p.name AS product,
    p.stock,
    c.name AS category
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE p.stock < 50
ORDER BY p.stock ASC;

What does this query do?

WHERE p.stock < 50 filters down to only the products that are running low. In a real storefront, this threshold might trigger a restocking alert.
ORDER BY p.stock ASC puts the most urgent items first, with the lowest stock at the top.

You should see two products: Non-Stick Frying Pan 10 inch with 35 in stock and Wireless Bluetooth Headphones with 45 in stock.

The final query brings everything together: a customer's complete order history with totals. This is the kind of query that powers an "Order History" page on Amazon.

Pull up Alice's full order history with order totals by running this query:

-- Customer order history for Alice
SELECT
    o.id AS order_id,
    o.order_date,
    o.status,
    SUM(oi.quantity * oi.unit_price_cents) / 100.0 AS order_total
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.customer_id = 1
GROUP BY o.id, o.order_date, o.status
ORDER BY o.order_date DESC;

What does this query do?

WHERE o.customer_id = 1 filters to only Alice's orders.
GROUP BY o.id, o.order_date, o.status groups order items by order, so each order collapses into a single row with a total.
SUM(oi.quantity * oi.unit_price_cents) / 100.0 calculates the dollar total for each order by summing all the line items within that group.

You should see two orders for Alice: one completed order and one pending order, each with its dollar total.

Seeing more orders than expected?

Make sure the WHERE clause filters on o.customer_id = 1. Without the filter, you will see all customers' orders.
If you see duplicate rows for the same order, check that all three columns (o.id, o.order_date, o.status) are in the GROUP BY clause.

Need help? Help me debug my customer order history query that groups orders with totals.

✔️ Awesome, I've got everything!

Great. You have run all four aggregate queries successfully in your psql session.

ⓧ I'd like to double check the full code

Scroll back through this step and confirm you ran each of these four queries and got results:

Top-selling products by units sold (the SUM(oi.quantity) query grouped by product name).
Revenue by category (the three-table JOIN query with SUM grouped by category name).
Low stock alert (products with stock < 50).
Customer order history for Alice (orders filtered by customer_id = 1 with order totals).

Secret mission

Add Reviews and Ratings

Amazon's storefront is not just products and orders. Customer reviews drive purchasing decisions. Can you extend your database with a reviews table and write queries to find the highest-rated products?

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. Your RDS instance will incur charges on a paid plan once your AWS credits are depleted, so choose the option that fits your plans.

Cost warning

Your db.t3.micro RDS instance is covered by AWS Sandbox credits initially. Once credits are depleted, compute and storage charges apply while the instance is running. If you are done with this project, stop or delete the instance to avoid ongoing costs.

Resources you used:

Amazon RDS PostgreSQL DB instance (storefront-db, db.t3.micro).
libpq PostgreSQL client (installed via Homebrew).

Keep everything running

No action needed. Choose this if you are still actively building on your database or want to experiment with more queries.

Your RDS instance stays live and accessible from your terminal.
Monitor your costs in the AWS Billing console to track how much you are spending.
libpq stays installed on your Mac for future PostgreSQL projects.

Pause - I'll come back to this later

Stop your RDS instance to pause compute charges while keeping your data intact. Storage charges continue, but compute charges stop for up to 7 days.

If you are still connected to psql, type \q and press Enter to disconnect.
In the RDS console, select your storefront-db instance.
Click Actions, then Stop temporarily.
Confirm the stop action when prompted.

What happens when I stop my instance?

Stopping an RDS instance pauses compute charges, but you are still charged for provisioned storage and backup storage. AWS will auto-restart your instance after 7 days.

When you are ready to come back, select your instance in the RDS console, click Actions, then Start. Reconnect with the same psql command you used earlier.

Delete - I don't want to use this again

Remove your RDS instance and all its data permanently. This stops all charges related to the instance.

If you are still connected to psql, type \q and press Enter to disconnect.
In the RDS console, select your storefront-db instance.
Click Actions, then Delete.
Uncheck Create final snapshot and Retain automated backups to avoid leftover storage costs.
Type delete me in the confirmation field, then click Delete.

To remove the PostgreSQL client from your Mac, run this command in your terminal:

brew uninstall libpq

Nice Work!

You just built a fully functional e-commerce database on AWS RDS PostgreSQL, connected to it from your Mac terminal, and queried it like a real storefront.

You've learned how to:

Spin up a live PostgreSQL database on AWS RDS and connect to it from your terminal using psql.
Design a relational schema with five core tables (categories, products, customers, orders, order_items) linked by primary keys, foreign keys, and constraints.
Write SQL queries that mirror real storefront operations: JOINs for product browsing and order tracking, GROUP BY with aggregate functions for top sellers and revenue reports, and WHERE clauses for low-stock alerts and customer history.
Secret Mission: Extended the schema with a reviews table and wrote advanced aggregate queries with HAVING to find the highest-rated products.

Ready to quiz yourself?