Design an E-Commerce Database

Build a normalized e-commerce schema with 7 tables in SQLite.

Introduction

30 Second Summary

Every time you add something to an online shopping cart and check out, dozens of connected database tables work together behind the scenes to track your order, payment, and delivery.

In this project, you will design and build a complete e-commerce database from scratch using SQLite. You'll create 7 interconnected tables, load them with sample data, and run real business queries that prove your design works.

What You'll Build

A fully functional e-commerce database schema modeled after real online stores, complete with customers, products, orders, payments, and business intelligence queries.

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

  • 7 normalized tables with proper constraints, primary keys, and foreign keys connecting your data together.
  • Sample data representing a small online electronics store, with real business queries that answer questions like "What are my best-selling products?" and "What's the total revenue per category?".
  • A reusable .sql script file you can extend for future database projects.
  • Secret Mission: Add a shopping cart system with cart expiration logic.

Are there any prerequisites?

You only need basic familiarity with what a database table is (rows and columns). No deep SQL knowledge is required.

Before We Start

Before We Start...

Set Up SQLite on Windows

Before you can design a database, you need the tool that creates and manages it.

In this step, you'll install SQLite, a lightweight database engine that runs directly from the command line. No server to configure, no installer to run. You download a zip, extract it, and you're ready to write SQL.

In this step, get ready to:
  • Download and extract SQLite command-line tools.
  • Add SQLite to your system PATH so you can run it from anywhere.
  • Create your project folder, open it in VS Code, and start the SQLite shell.
Download and extract the SQLite tools

SQLite distributes its Windows tools as a single zip file containing the command-line shell. You'll download this zip and extract it to a dedicated folder on your C: drive.

  • Open your web browser and go to the SQLite download page:
  • https://sqlite.org/download.html
  • Scroll down to the Precompiled Binaries for Windows section.
  • Download the file named sqlite-tools-win-x64-3530100.zip.
  • Open File Explorer and create a new folder at C:\sqlite.
  • Open the downloaded zip file and extract all its contents into C:\sqlite.

What did you just download?

The zip contains sqlite3.exe, the command-line shell you'll use to create tables, insert data, and run queries. It also includes a few other utilities, but sqlite3.exe is the one you'll use throughout this project.

  • Confirm the file exists by opening C:\sqlite in File Explorer.
  • You should see sqlite3.exe listed in the folder.

Don't see sqlite3.exe?

Make sure you extracted the zip contents, not the zip file itself. Right-click the zip and select Extract All. Some zip extractors create a subfolder inside the destination. If you see a nested folder like C:\sqlite\sqlite-tools-win-x64-3530100, move the files up one level so sqlite3.exe sits directly inside C:\sqlite.

Help me find sqlite3.exe after extracting the zip

Add SQLite to your system PATH

Windows needs to know where sqlite3.exe lives so you can run it from any folder. Adding C:\sqlite to your PATH environment variable makes this possible.

  • Press the Windows key to open your search bar.
  • Type View advanced system settings and press Enter.
  • In the System Properties window that opens, click the Environment Variables button at the bottom.
  • In the System variables section (the bottom half), find and select the row labeled Path.
  • Click Edit.
  • In the Edit environment variable window, click New.
  • Type C:\sqlite and press Enter.
  • Click OK on each open dialog to save your changes (three times total).

Why add to PATH?

The PATH variable tells Windows which folders to search when you type a command. Without this step, you'd have to type the full path C:\sqlite\sqlite3 every time. Adding the folder to PATH lets you just type sqlite3 from any location.

Now verify that Windows can find SQLite from a fresh Command Prompt.

  • Press the Windows key, type cmd, and press Enter to open a new Command Prompt.
  • Confirm SQLite is accessible by running this command:
sqlite3 --version

✔️ I see version 3.53.1

You should see output starting with 3.53.1 followed by a date and hash. SQLite is ready to use.

ⓧ I see an older version

You may have a different version of SQLite already on your system. The older version's folder appears earlier in your PATH. To fix this:

  • Go back into Environment Variables and edit the Path variable.
  • Select the C:\sqlite entry you added and click Move Up until it is at or near the top of the list.
  • Click OK on all dialogs.
  • Close your Command Prompt and open a new one.
  • Run sqlite3 --version again. You should now see 3.53.1.

ⓧ Command not found

Windows cannot find sqlite3.exe. Double-check these things:

  • Confirm that sqlite3.exe is inside C:\sqlite (not in a subfolder).
  • Confirm that the PATH entry you added is exactly C:\sqlite with no typos or extra spaces.
  • Make sure you opened a new Command Prompt after saving the PATH change. Existing windows do not pick up PATH updates.
  • If it still does not work, try restarting your computer and running the command again in a fresh Command Prompt.

Help me get sqlite3 --version working on Windows

Create your project folder and start the SQLite shell

With SQLite installed, you need a project folder to hold your SQL script and database file. You'll create both, then launch the SQLite shell connected to a new database.

  • Move to your Desktop by running this command:
cd %USERPROFILE%\Desktop
  • Create your project folder and move into it by running these commands:
mkdir ecommerce-db
cd ecommerce-db
  • Confirm the folder exists by running:
dir

You should see the ecommerce-db directory is empty (no files listed yet).

  • Open this folder in VS Code by running:
code .

What does code . do?

The code command launches VS Code. The dot (.) tells it to open the current folder as a workspace. This gives you a file explorer sidebar showing your project.

  • In VS Code, right-click in the Explorer sidebar and select New File.
  • Name the file ecommerce.sql and press Enter.

This file will hold all of your SQL code throughout the project. Leave it empty for now.

  • Switch back to your Command Prompt (which should still be in the ecommerce-db folder).
  • Create your database and enter the SQLite shell by running:
sqlite3 store.db

What does this command do?

This creates a new database file called store.db in your current folder and opens the interactive SQLite shell. You'll type SQL commands directly into this shell in the next steps.

You'll know it worked when your prompt changes to sqlite> instead of the normal Windows command prompt.

You should see the sqlite> prompt, which means you're inside the SQLite shell and connected to store.db.

Not seeing the sqlite> prompt?

If you see an error instead of the sqlite> prompt, make sure your Command Prompt is inside the ecommerce-db folder. Run cd %USERPROFILE%\Desktop\ecommerce-db and try the command again.

Help me get the SQLite shell running

Your environment is ready. Next up, you'll design your first two database tables and start loading real data into them.

Build the Foundation Tables

Every online store needs to track people and the things they sell. Before you can process orders or payments, you need a solid foundation of who your customers are and what you're selling them.

In this step, you'll create three tables that form the base layer of your e-commerce database: customers, categories, and addresses. You'll also load sample data so you can see the tables in action.

In this step, get ready to:
  • Create a customers table with unique email enforcement.
  • Create a categories table and populate it with sample data.
  • Create an addresses table linked to customers with a foreign key.
Create the customers table

Every relational database needs structure before it can hold data. You'll start by enabling foreign key enforcement (which SQLite has off by default) and then creating the first table.

  • Switch to VS Code and open ecommerce.sql.
  • Add the following code to set up the file header, enable foreign keys, and define the customers table:
-- E-Commerce Database Schema
-- Run with: sqlite3 store.db < ecommerce.sql

-- Enable foreign key enforcement (SQLite has this off by default)
PRAGMA foreign_keys = ON;

-- ============================================
-- FOUNDATION TABLES
-- ============================================

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

What does this code do?

PRAGMA foreign_keys = ON tells SQLite to actually enforce relationships between tables. Without this line, SQLite silently ignores foreign key rules.

The customers table stores one row per customer. INTEGER PRIMARY KEY makes id auto-increment automatically. UNIQUE on email prevents duplicate accounts for the same email address.

Why does SQLite use TEXT for dates?

Unlike MySQL or PostgreSQL, SQLite doesn't have a dedicated DATE type. It stores dates as TEXT in ISO 8601 format. The datetime('now') function generates the current timestamp when a new row is inserted.

  • Save ecommerce.sql.

You won't see a change yet. You'll run the file against the database after all three tables are written.

Create the categories table with sample data

Products need to be organized into groups so customers can browse by type. The categories table provides that structure.

  • Add the categories table below the customers table in ecommerce.sql:
CREATE TABLE categories (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    description TEXT
);

What does this code do?

This table is simpler than customers. It only needs a unique name (no two categories should be called the same thing) and an optional description. The description column has no NOT NULL constraint, so it can be left empty.

  • Now add 4 sample categories below the CREATE TABLE statement:
-- Categories
INSERT INTO categories (name, description) VALUES
    ('Electronics', 'Phones, laptops, and gadgets'),
    ('Accessories', 'Cases, cables, and add-ons'),
    ('Audio', 'Headphones, speakers, and earbuds'),
    ('Wearables', 'Smartwatches and fitness trackers');

What does this INSERT do?

A single INSERT INTO statement can add multiple rows at once by listing each row's values in parentheses, separated by commas. You don't need to specify id because SQLite auto-generates it.

  • Save ecommerce.sql.

One more table to go, then you'll run everything together and verify the results.

Create the addresses table and insert customers and addresses

Customers need shipping and billing addresses. Instead of cramming address fields into the customers table, you'll create a separate addresses table linked back to customers with a foreign key. This is a one-to-many relationship: one customer can have many addresses.

  • Add the addresses table below the categories INSERT in ecommerce.sql:
CREATE TABLE addresses (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    address_type TEXT NOT NULL CHECK(address_type IN ('shipping', 'billing')),
    street TEXT NOT NULL,
    city TEXT NOT NULL,
    state TEXT NOT NULL,
    postal_code TEXT NOT NULL,
    country TEXT NOT NULL DEFAULT 'US',
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
);

What does this code do?

The CHECK constraint on address_type limits values to only 'shipping' or 'billing'. Any other value gets rejected by the database.

The FOREIGN KEY (customer_id) REFERENCES customers(id) line creates the link between addresses and customers. ON DELETE CASCADE means if a customer is deleted, their addresses are automatically removed too.

  • Now add the sample customers below the addresses table:
-- Customers
INSERT INTO customers (email, first_name, last_name) VALUES
    ('alice@example.com', 'Alice', 'Johnson'),
    ('bob@example.com', 'Bob', 'Smith'),
    ('carol@example.com', 'Carol', 'Williams'),
    ('dave@example.com', 'Dave', 'Brown'),
    ('eve@example.com', 'Eve', 'Davis');

Why insert customers after the addresses table?

All three CREATE TABLE statements come first so that the database structure is complete before any data is inserted. The addresses INSERT references customer_id values, so customers must be inserted before addresses.

  • Finally, add the sample addresses below the customers INSERT:
-- Addresses
INSERT INTO addresses (customer_id, address_type, street, city, state, postal_code) VALUES
    (1, 'shipping', '123 Main St', 'Portland', 'OR', '97201'),
    (1, 'billing', '123 Main St', 'Portland', 'OR', '97201'),
    (2, 'shipping', '456 Oak Ave', 'Seattle', 'WA', '98101'),
    (3, 'shipping', '789 Pine Rd', 'Denver', 'CO', '80201'),
    (4, 'shipping', '321 Elm Blvd', 'Austin', 'TX', '73301');

What should I notice about this data?

Customer 1 (Alice) has two addresses: one for shipping and one for billing. This demonstrates the one-to-many relationship in action. The country column is not listed because it defaults to 'US' automatically.

  • Save ecommerce.sql.
  • Switch back to your Command Prompt where the SQLite shell is running.
  • Run the SQL file against your database by entering this command in the SQLite shell:
.read ecommerce.sql
  • Verify the tables were created by running:
.tables

You should see addresses, categories, and customers listed.

  • Confirm the sample data loaded by running this query:
SELECT * FROM categories;

You should see 4 rows: Electronics, Accessories, Audio, and Wearables.

  • Run one more query to verify the foreign key relationship works:
SELECT c.first_name, a.city, a.address_type
FROM customers c
JOIN addresses a ON a.customer_id = c.id;

You should see 5 rows showing each customer matched with their address. Alice appears twice because she has both a shipping and billing address.

Seeing an error when running .read?

  • If you see a "no such file" error, make sure you launched sqlite3 store.db from the same folder where ecommerce.sql lives.
  • If you see a "table already exists" error, you may have run the file twice. Exit the shell with .quit, delete store.db, then re-run sqlite3 store.db and try .read ecommerce.sql again.
  • If you see a foreign key error on the addresses INSERT, check that the customers INSERT comes before the addresses INSERT in your file.

Help me debug my .read error in SQLite

✔️ Awesome, I've got everything!

Great. Double check you've saved ecommerce.sql and that your query results match the expected output.

ⓧ I'd like to double check the full code

Here is the complete ecommerce.sql file so far. Compare it with yours:

-- E-Commerce Database Schema
-- Run with: sqlite3 store.db < ecommerce.sql

-- Enable foreign key enforcement (SQLite has this off by default)
PRAGMA foreign_keys = ON;

-- ============================================
-- FOUNDATION TABLES
-- ============================================

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE categories (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    description TEXT
);

CREATE TABLE addresses (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    address_type TEXT NOT NULL CHECK(address_type IN ('shipping', 'billing')),
    street TEXT NOT NULL,
    city TEXT NOT NULL,
    state TEXT NOT NULL,
    postal_code TEXT NOT NULL,
    country TEXT NOT NULL DEFAULT 'US',
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
);

-- Categories
INSERT INTO categories (name, description) VALUES
    ('Electronics', 'Phones, laptops, and gadgets'),
    ('Accessories', 'Cases, cables, and add-ons'),
    ('Audio', 'Headphones, speakers, and earbuds'),
    ('Wearables', 'Smartwatches and fitness trackers');

-- Customers
INSERT INTO customers (email, first_name, last_name) VALUES
    ('alice@example.com', 'Alice', 'Johnson'),
    ('bob@example.com', 'Bob', 'Smith'),
    ('carol@example.com', 'Carol', 'Williams'),
    ('dave@example.com', 'Dave', 'Brown'),
    ('eve@example.com', 'Eve', 'Davis');

-- Addresses
INSERT INTO addresses (customer_id, address_type, street, city, state, postal_code) VALUES
    (1, 'shipping', '123 Main St', 'Portland', 'OR', '97201'),
    (1, 'billing', '123 Main St', 'Portland', 'OR', '97201'),
    (2, 'shipping', '456 Oak Ave', 'Seattle', 'WA', '98101'),
    (3, 'shipping', '789 Pine Rd', 'Denver', 'CO', '80201'),
    (4, 'shipping', '321 Elm Blvd', 'Austin', 'TX', '73301');

Your foundation is in place. You have customers who can have multiple addresses, and categories ready to organize products. Next up, you'll build the product catalog and see how products connect to categories through foreign keys.

Build the Product Catalog

Your foundation is solid. You have customers who can sign up, categories to organize inventory, and addresses for shipping.

Now it's time to fill those shelves. Products are the core of any e-commerce store. In this step, you'll create a products table that links back to your categories, load it with sample inventory, and write queries to browse the catalog.

In this step, get ready to:
  • Create the products table with pricing constraints and a category foreign key.
  • Load 8 sample products and query them with their category names.
  • Filter and sort products to simulate catalog browsing.
Create the products table

Every product in an online store needs a unique identifier for inventory systems, a carefully stored price, and a link to its category. The products table brings all of this together.

  • Switch back to ecommerce.sql in VS Code.
  • Find the line that says -- Categories (the first INSERT comment). Add the following code above it:
-- ============================================
-- PRODUCT CATALOG
-- ============================================

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    sku TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL CHECK(price > 0),
    stock_quantity INTEGER NOT NULL DEFAULT 0 CHECK(stock_quantity >= 0),
    category_id INTEGER,
    is_active INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
);

What does this table do?

  • sku TEXT NOT NULL UNIQUE gives each product a stock-keeping unit code that no two products can share. This is how inventory systems track items.
  • price DECIMAL(10,2) NOT NULL CHECK(price > 0) stores the price with two decimal places and ensures it's always positive.
  • stock_quantity defaults to 0 and can never go negative thanks to the CHECK constraint.
  • category_id is a foreign key pointing to the categories table. ON DELETE SET NULL means if a category is deleted, the product stays but loses its category link.
  • is_active lets you "hide" products from the catalog without deleting them.

Why DECIMAL(10,2) instead of a float?

Floating-point numbers (like REAL in SQLite) can introduce tiny rounding errors. A price of $19.99 might be stored as 19.9899999997. For money, you always want exact decimal precision.

DECIMAL(10,2) declares your intent: 10 total digits, 2 after the decimal point. SQLite stores this as REAL internally, but the declaration documents the schema's purpose for anyone reading your code.

  • Save ecommerce.sql.
  • Switch back to your SQLite shell and paste the CREATE TABLE products statement (without the comment header) to execute it.
  • Confirm the table was created by running:
.tables

You should see products listed alongside addresses, categories, and customers.

Don't see the products table?

  • Make sure you pasted the entire CREATE TABLE statement, including the closing ); on the last line. SQLite waits for the semicolon before executing.
  • If you see an error about the table already existing, you may have run the statement twice. That's fine. Run .tables to confirm it's there.

Help me troubleshoot my CREATE TABLE statement in SQLite.

Insert sample products and query them with categories

An empty table doesn't tell you much. Let's fill it with 8 products spanning your Audio, Accessories, and Wearables categories, then use a JOIN to see everything together.

  • In ecommerce.sql, scroll to the bottom of the file (after the INSERT INTO addresses block). Add the following:
-- Products
INSERT INTO products (sku, name, description, price, stock_quantity, category_id) VALUES
    ('ELEC-001', 'Wireless Bluetooth Headphones', 'Over-ear noise cancelling headphones', 79.99, 50, 3),
    ('ELEC-002', 'USB-C Fast Charger', '65W GaN charger with 3 ports', 34.99, 120, 2),
    ('ELEC-003', 'Smartwatch Pro', 'Fitness tracking with GPS and heart rate', 199.99, 30, 4),
    ('ELEC-004', 'Phone Case - Clear', 'Slim transparent protective case', 14.99, 200, 2),
    ('ELEC-005', 'Portable Bluetooth Speaker', 'Waterproof 20W speaker', 49.99, 75, 3),
    ('ELEC-006', 'Laptop Stand - Aluminum', 'Adjustable ergonomic stand', 44.99, 60, 2),
    ('ELEC-007', 'Wireless Earbuds', 'True wireless with 8hr battery', 59.99, 90, 3),
    ('ELEC-008', 'Fitness Band Lite', 'Step counter and sleep tracker', 29.99, 150, 4);

What does this data represent?

These 8 products form a small electronics catalog. Notice how category_id uses the numeric IDs from your categories table: 2 = Accessories, 3 = Audio, 4 = Wearables.

Each SKU follows a consistent pattern (ELEC-001 through ELEC-008) so they're easy to reference in a real inventory system.

  • Save ecommerce.sql.
  • Switch to your SQLite shell and paste the INSERT statement to execute it.
  • Verify 8 products were inserted by running:
SELECT count(*) FROM products;

You should see 8 as the result.

Seeing 0 rows or an error?

  • If you get a "no such table" error, go back and run the CREATE TABLE statement first.
  • If you see a foreign key constraint error, make sure you ran PRAGMA foreign_keys = ON; in this shell session. The categories must exist before products can reference them.

Help me debug my INSERT INTO products statement.

Now let's see your products alongside their category names. This is where foreign keys pay off.

  • Run this JOIN query in your SQLite shell:
SELECT p.name, p.price, c.name AS category
FROM products p
JOIN categories c ON p.category_id = c.id;

What does this query do?

JOIN combines rows from two tables where a condition matches. Here, it connects each product to its category using p.category_id = c.id.

The aliases p and c are shorthand for the table names, making the query easier to read. AS category renames the output column so you don't get two columns both called name.

You should see 8 rows, each showing a product name, its price, and which category it belongs to.

Filter active products under $50

Real e-commerce catalogs let customers browse by price range and only show products that are currently available. Let's simulate that.

  • Run this filtered query in your SQLite shell:
SELECT name, price, stock_quantity
FROM products
WHERE is_active = 1 AND price < 50
ORDER BY price;

What does this query do?

WHERE is_active = 1 AND price < 50 filters to only show products that are live in the store and cost less than $50.

ORDER BY price sorts results from cheapest to most expensive. You should see 5 products: Phone Case ($14.99), Fitness Band Lite ($29.99), USB-C Fast Charger ($34.99), Laptop Stand ($44.99), and Portable Bluetooth Speaker ($49.99).

How would indexes help at scale?

With 8 products, this query is instant. With 100,000 products, SQLite would scan every single row to check the WHERE conditions. That's slow.

An index on category_id and is_active would let the database jump directly to matching rows instead of reading the entire table. Think of it like the index in the back of a textbook. You don't read every page to find a topic.

✔️ Awesome, I've got everything!

Great. Double check you've saved ecommerce.sql with both the CREATE TABLE and INSERT statements.

ⓧ I'd like to double check the full code

-- E-Commerce Database Schema
-- Run with: sqlite3 store.db < ecommerce.sql

-- Enable foreign key enforcement (SQLite has this off by default)
PRAGMA foreign_keys = ON;

-- ============================================
-- FOUNDATION TABLES
-- ============================================

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE categories (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    description TEXT
);

CREATE TABLE addresses (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    address_type TEXT NOT NULL CHECK(address_type IN ('shipping', 'billing')),
    street TEXT NOT NULL,
    city TEXT NOT NULL,
    state TEXT NOT NULL,
    postal_code TEXT NOT NULL,
    country TEXT NOT NULL DEFAULT 'US',
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
);

-- ============================================
-- PRODUCT CATALOG
-- ============================================

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    sku TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL CHECK(price > 0),
    stock_quantity INTEGER NOT NULL DEFAULT 0 CHECK(stock_quantity >= 0),
    category_id INTEGER,
    is_active INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
);

-- Categories
INSERT INTO categories (name, description) VALUES
    ('Electronics', 'Phones, laptops, and gadgets'),
    ('Accessories', 'Cases, cables, and add-ons'),
    ('Audio', 'Headphones, speakers, and earbuds'),
    ('Wearables', 'Smartwatches and fitness trackers');

-- Customers
INSERT INTO customers (email, first_name, last_name) VALUES
    ('alice@example.com', 'Alice', 'Johnson'),
    ('bob@example.com', 'Bob', 'Smith'),
    ('carol@example.com', 'Carol', 'Williams'),
    ('dave@example.com', 'Dave', 'Brown'),
    ('eve@example.com', 'Eve', 'Davis');

-- Addresses
INSERT INTO addresses (customer_id, address_type, street, city, state, postal_code) VALUES
    (1, 'shipping', '123 Main St', 'Portland', 'OR', '97201'),
    (1, 'billing', '123 Main St', 'Portland', 'OR', '97201'),
    (2, 'shipping', '456 Oak Ave', 'Seattle', 'WA', '98101'),
    (3, 'shipping', '789 Pine Rd', 'Denver', 'CO', '80201'),
    (4, 'shipping', '321 Elm Blvd', 'Austin', 'TX', '73301');

-- Products
INSERT INTO products (sku, name, description, price, stock_quantity, category_id) VALUES
    ('ELEC-001', 'Wireless Bluetooth Headphones', 'Over-ear noise cancelling headphones', 79.99, 50, 3),
    ('ELEC-002', 'USB-C Fast Charger', '65W GaN charger with 3 ports', 34.99, 120, 2),
    ('ELEC-003', 'Smartwatch Pro', 'Fitness tracking with GPS and heart rate', 199.99, 30, 4),
    ('ELEC-004', 'Phone Case - Clear', 'Slim transparent protective case', 14.99, 200, 2),
    ('ELEC-005', 'Portable Bluetooth Speaker', 'Waterproof 20W speaker', 49.99, 75, 3),
    ('ELEC-006', 'Laptop Stand - Aluminum', 'Adjustable ergonomic stand', 44.99, 60, 2),
    ('ELEC-007', 'Wireless Earbuds', 'True wireless with 8hr battery', 59.99, 90, 3),
    ('ELEC-008', 'Fitness Band Lite', 'Step counter and sleep tracker', 29.99, 150, 4);

Your product catalog is live. Next up, you'll build the order system that connects customers to the products they buy.

Build the Order System

You have customers who can browse products, but there is no way for them to actually buy anything yet.

Orders are the heart of any e-commerce database because they connect customers to products through a purchase event. In this step, you will build the order system using two tables that work together to track what each customer bought, how much they paid, and when.

In this step, get ready to:
  • Create the orders table with status constraints and a customer foreign key.
  • Create the order_items junction table that connects orders to products with price snapshots.
  • Insert sample orders and run a query that reconstructs a full order with product names and line totals.
Create the orders table

A customer can place many orders, but each order belongs to exactly one customer. This is a one-to-many relationship. The orders table stores a foreign key pointing back to the customer who placed it.

  • In VS Code, open ecommerce.sql.
  • Find the closing ); of the products table (the line just above the -- Categories comment).
  • Add the following code between the products table and the -- Categories comment:
-- ============================================
-- ORDER SYSTEM
-- ============================================

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending', 'paid', 'shipped', 'delivered', 'cancelled')),
    total_amount DECIMAL(10,2) NOT NULL CHECK(total_amount >= 0),
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

What does this code do?

Each column serves a specific purpose in tracking an order through its lifecycle.

  • customer_id INTEGER NOT NULL links every order to the customer who placed it. The FOREIGN KEY at the bottom enforces that this ID must exist in the customers table.
  • CHECK(status IN (...)) restricts the status column to only five valid values. This prevents typos and invalid states from entering your database.
  • total_amount stores the order total. The CHECK(total_amount >= 0) prevents negative order values.

Why do orders link to customers, not the other way around?

You might wonder why you do not add an order_id column to the customers table. The answer is that one customer can have many orders. You cannot store multiple order IDs in a single column cleanly.

Instead, each order row stores the customer_id of who placed it. To find all of Alice's orders, you query the orders table filtered by her ID. This is how one-to-many relationships work in relational databases.

  • Save ecommerce.sql.
  • Switch to your Command Prompt where the SQLite shell is running. Copy and paste the CREATE TABLE orders statement (including the semicolon) and press Enter to execute it.
  • Confirm the table was created by running:
.tables

You should see orders listed alongside customers, categories, addresses, and products.

Seeing a syntax error?

  • Make sure you copied the full statement including the final ); on its own line. SQLite waits for the semicolon before executing.
  • If you see ...> in the shell, SQLite is waiting for the statement to finish. Type ); and press Enter to complete it.

Help me fix a syntax error in my CREATE TABLE orders statement.

Create the order_items junction table

A single order can contain multiple products, and a single product can appear in many different orders. This is a many-to-many relationship. Relational databases solve this with a junction table that sits between the two tables and connects them.

  • In ecommerce.sql, add the following code directly below the orders table you just wrote (still above the -- Categories comment):
CREATE TABLE order_items (
    id INTEGER PRIMARY KEY,
    order_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL CHECK(quantity > 0),
    unit_price DECIMAL(10,2) NOT NULL CHECK(unit_price > 0),
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

Why store unit_price here instead of looking it up from products?

Product prices change over time. A store might run a sale, increase prices, or discontinue items. If you looked up the price from the products table every time, an order placed six months ago would show today's price instead of what the customer actually paid.

Storing unit_price directly in order_items creates a price snapshot. It freezes the price at the moment of purchase. This is how every real e-commerce system works.

  • Save ecommerce.sql.
  • In the SQLite shell, paste and run the CREATE TABLE order_items statement.
  • Run .tables again. You should now see both orders and order_items in the list.

Table already exists?

If you accidentally ran the statement twice, SQLite will reject it because the table already exists. You can safely ignore this error and move on. The table is already created from your first run.

Help me fix a table creation error in SQLite.

Insert sample orders and query a full order

With the table structures in place, you can now add sample data that represents real purchase activity. You will insert 4 orders, then add the individual items for each order.

  • In ecommerce.sql, scroll to the bottom of the file (after the products INSERT statement). Add the following orders data:
-- Orders
INSERT INTO orders (customer_id, status, total_amount, created_at) VALUES
    (1, 'delivered', 129.98, '2026-05-01 10:30:00'),
    (2, 'shipped', 249.98, '2026-05-15 14:20:00'),
    (1, 'paid', 44.99, '2026-05-28 09:15:00'),
    (3, 'pending', 94.98, '2026-06-01 16:45:00');

What does this data represent?

  • Customer 1 (Alice) has two orders: one delivered and one recently paid.
  • Customer 2 (Bob) has one shipped order.
  • Customer 3 (Carol) has one pending order that has not been paid yet.
  • Each order's total_amount matches the sum of its items (which you will insert next).
  • Directly below the orders INSERT, add the order items data:
-- Order Items
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
    (1, 1, 1, 79.99),
    (1, 5, 1, 49.99),
    (2, 3, 1, 199.99),
    (2, 5, 1, 49.99),
    (3, 6, 1, 44.99),
    (4, 7, 1, 59.99),
    (4, 2, 1, 34.99);

What does this data represent?

Each row connects an order to a product. Order 1 contains Wireless Bluetooth Headphones (product 1, $79.99) and a Portable Bluetooth Speaker (product 5, $49.99). Together they total $129.98, matching the order's total_amount.

Notice that product 5 (Portable Bluetooth Speaker) appears in both order 1 and order 2. This is the many-to-many relationship in action. One product, multiple orders.

  • Save ecommerce.sql.
  • In the SQLite shell, paste and run both INSERT statements (the orders INSERT and the order_items INSERT).
  • Verify your data loaded by running:
SELECT COUNT(*) FROM orders;

You should see 4 as the result.

  • Now run the exciting part. Paste this query into the SQLite shell to reconstruct Order #1 with full product names and line totals:
SELECT
    o.id AS order_id,
    c.first_name || ' ' || c.last_name AS customer,
    p.name AS product,
    oi.quantity,
    oi.unit_price,
    (oi.quantity * oi.unit_price) AS line_total,
    o.status
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON oi.product_id = p.id
WHERE o.id = 1;

You should see two rows showing Alice Johnson's delivered order with Wireless Bluetooth Headphones ($79.99) and a Portable Bluetooth Speaker ($49.99).

What does this query do?

This query uses JOIN to reassemble data that normalization split across multiple tables. It connects four tables in one query.

  • JOIN customers c ON o.customer_id = c.id pulls in the customer's name.
  • JOIN order_items oi ON oi.order_id = o.id finds all items belonging to this order.
  • JOIN products p ON oi.product_id = p.id fetches the product name for each item.
  • (oi.quantity * oi.unit_price) AS line_total calculates the cost per line item. This uses the snapshot price, not the current product price.

Seeing no rows returned?

  • Make sure you ran both INSERT statements (orders AND order_items). The query needs data in both tables to produce results.
  • Check that foreign keys are enabled. Run PRAGMA foreign_keys; in the shell. If it returns 0, run PRAGMA foreign_keys = ON; first.

Help me debug my JOIN query that returns no results.

✔️ Awesome, I've got everything!

Great. Double check you have saved ecommerce.sql with both new CREATE TABLE statements and both new INSERT statements.

ⓧ I'd like to double check the full code

Here is the complete ecommerce.sql file as it should look at the end of this step.

-- E-Commerce Database Schema
-- Run with: sqlite3 store.db < ecommerce.sql

-- Enable foreign key enforcement (SQLite has this off by default)
PRAGMA foreign_keys = ON;

-- ============================================
-- FOUNDATION TABLES
-- ============================================

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE categories (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    description TEXT
);

CREATE TABLE addresses (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    address_type TEXT NOT NULL CHECK(address_type IN ('shipping', 'billing')),
    street TEXT NOT NULL,
    city TEXT NOT NULL,
    state TEXT NOT NULL,
    postal_code TEXT NOT NULL,
    country TEXT NOT NULL DEFAULT 'US',
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
);

-- ============================================
-- PRODUCT CATALOG
-- ============================================

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    sku TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL CHECK(price > 0),
    stock_quantity INTEGER NOT NULL DEFAULT 0 CHECK(stock_quantity >= 0),
    category_id INTEGER,
    is_active INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
);

-- ============================================
-- ORDER SYSTEM
-- ============================================

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending', 'paid', 'shipped', 'delivered', 'cancelled')),
    total_amount DECIMAL(10,2) NOT NULL CHECK(total_amount >= 0),
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE TABLE order_items (
    id INTEGER PRIMARY KEY,
    order_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL CHECK(quantity > 0),
    unit_price DECIMAL(10,2) NOT NULL CHECK(unit_price > 0),
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Categories
INSERT INTO categories (name, description) VALUES
    ('Electronics', 'Phones, laptops, and gadgets'),
    ('Accessories', 'Cases, cables, and add-ons'),
    ('Audio', 'Headphones, speakers, and earbuds'),
    ('Wearables', 'Smartwatches and fitness trackers');

-- Customers
INSERT INTO customers (email, first_name, last_name) VALUES
    ('alice@example.com', 'Alice', 'Johnson'),
    ('bob@example.com', 'Bob', 'Smith'),
    ('carol@example.com', 'Carol', 'Williams'),
    ('dave@example.com', 'Dave', 'Brown'),
    ('eve@example.com', 'Eve', 'Davis');

-- Addresses
INSERT INTO addresses (customer_id, address_type, street, city, state, postal_code) VALUES
    (1, 'shipping', '123 Main St', 'Portland', 'OR', '97201'),
    (1, 'billing', '123 Main St', 'Portland', 'OR', '97201'),
    (2, 'shipping', '456 Oak Ave', 'Seattle', 'WA', '98101'),
    (3, 'shipping', '789 Pine Rd', 'Denver', 'CO', '80201'),
    (4, 'shipping', '321 Elm Blvd', 'Austin', 'TX', '73301');

-- Products
INSERT INTO products (sku, name, description, price, stock_quantity, category_id) VALUES
    ('ELEC-001', 'Wireless Bluetooth Headphones', 'Over-ear noise cancelling headphones', 79.99, 50, 3),
    ('ELEC-002', 'USB-C Fast Charger', '65W GaN charger with 3 ports', 34.99, 120, 2),
    ('ELEC-003', 'Smartwatch Pro', 'Fitness tracking with GPS and heart rate', 199.99, 30, 4),
    ('ELEC-004', 'Phone Case - Clear', 'Slim transparent protective case', 14.99, 200, 2),
    ('ELEC-005', 'Portable Bluetooth Speaker', 'Waterproof 20W speaker', 49.99, 75, 3),
    ('ELEC-006', 'Laptop Stand - Aluminum', 'Adjustable ergonomic stand', 44.99, 60, 2),
    ('ELEC-007', 'Wireless Earbuds', 'True wireless with 8hr battery', 59.99, 90, 3),
    ('ELEC-008', 'Fitness Band Lite', 'Step counter and sleep tracker', 29.99, 150, 4);

-- Orders
INSERT INTO orders (customer_id, status, total_amount, created_at) VALUES
    (1, 'delivered', 129.98, '2026-05-01 10:30:00'),
    (2, 'shipped', 249.98, '2026-05-15 14:20:00'),
    (1, 'paid', 44.99, '2026-05-28 09:15:00'),
    (3, 'pending', 94.98, '2026-06-01 16:45:00');

-- Order Items
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
    (1, 1, 1, 79.99),
    (1, 5, 1, 49.99),
    (2, 3, 1, 199.99),
    (2, 5, 1, 49.99),
    (3, 6, 1, 44.99),
    (4, 7, 1, 59.99),
    (4, 2, 1, 34.99);

Your order system is live. You can see how normalization keeps each piece of data in exactly one place, while JOINs reassemble it into useful views. Next up, you will add payments and write business intelligence queries that prove this entire schema works together.

Add Payments and Run Business Queries

Your database has customers placing orders and items filling those orders. But there's a missing piece: how does money actually flow through the system?

In this step, you'll create the final table (payments) and then write queries that pull data from across all your tables at once. These multi-table queries are the payoff for all the careful normalization work you've done so far.

In this step, get ready to:
  • Create the payments table with CHECK constraints for method and status.
  • Write a multi-table JOIN query that produces a full order receipt.
  • Write business intelligence queries that answer real questions about the store.
Create the payments table

Every order needs a record of how it was paid. The payments table tracks the amount, method, status, and timestamp for each transaction.

  • Switch back to your ecommerce.sql file in VS Code.
  • Find the order_items CREATE TABLE statement (the last CREATE TABLE in your file). Add the following code directly below it, before any INSERT statements:
CREATE TABLE payments (
    id INTEGER PRIMARY KEY,
    order_id INTEGER NOT NULL,
    amount DECIMAL(10,2) NOT NULL CHECK(amount > 0),
    method TEXT NOT NULL CHECK(method IN ('card', 'paypal', 'bank_transfer')),
    status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending', 'completed', 'failed', 'refunded')),
    paid_at TEXT,
    FOREIGN KEY (order_id) REFERENCES orders(id)
);

What does this table do?

The payments table links to orders through order_id. Each payment records the method used (card, paypal, or bank transfer) and its current status (pending, completed, failed, or refunded).

Notice paid_at has no NOT NULL constraint. A pending payment hasn't been completed yet, so the timestamp can be empty until the payment goes through.

  • Copy this CREATE TABLE statement and paste it into your SQLite shell to create the table in store.db.
  • Confirm the table was created by running this command in the shell:
.tables

You should see payments listed alongside your other six tables.

Don't see the payments table?

Make sure you pasted the full CREATE TABLE statement including the closing }); and pressed Enter. If you see an error about the table already existing, you may have run it twice.

Help me troubleshoot

Insert sample payment data

Three of the four orders have been paid. Order 4 is still pending, so it has no payment record yet.

  • Add the following INSERT statement to ecommerce.sql after the order_items INSERT (at the end of your sample data):
-- Payments
INSERT INTO payments (order_id, amount, method, status, paid_at) VALUES
    (1, 129.98, 'card', 'completed', '2026-05-01 10:32:00'),
    (2, 249.98, 'paypal', 'completed', '2026-05-15 14:22:00'),
    (3, 44.99, 'card', 'completed', '2026-05-28 09:17:00');

Why does Order 4 have no payment?

Order 4 has status pending. It hasn't been paid yet, so there's no row in the payments table for it. This is normal in real e-commerce systems. Not every order results in a successful payment.

  • Copy the INSERT statement and paste it into your SQLite shell to load the data.
  • Verify the data loaded by running this query in the shell:
SELECT * FROM payments;

You should see 3 rows showing payments for orders 1, 2, and 3.

Seeing a foreign key error?

If you see a foreign key constraint failure, make sure you previously ran PRAGMA foreign_keys = ON; and that your orders table has rows with IDs 1, 2, and 3. Run SELECT id FROM orders; to check.

Help me debug this

Write a full order receipt query

This is where normalization pays off. Each piece of data lives in one place, but a single query can reassemble it into a complete picture. You'll join five tables together to produce a receipt for Order #1.

  • Add the following query to the bottom of ecommerce.sql under a new section comment, then paste it into your SQLite shell to run it:
-- ============================================
-- BUSINESS QUERIES
-- ============================================

-- Query 1: Full order receipt for Order #1
SELECT
    o.id AS order_id,
    c.first_name || ' ' || c.last_name AS customer,
    p.name AS product,
    oi.quantity,
    oi.unit_price,
    (oi.quantity * oi.unit_price) AS line_total,
    o.status,
    pay.method AS payment_method
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON oi.product_id = p.id
LEFT JOIN payments pay ON pay.order_id = o.id
WHERE o.id = 1;

What does this query do?

This query connects five tables to build a complete order receipt. It starts from the orders table and JOINs outward to get the customer name, each product ordered, the quantities and prices, and the payment method.

The LEFT JOIN on payments means the query still works even if an order has no payment yet. A regular JOIN would hide unpaid orders entirely.

The || operator concatenates strings in SQLite. It combines first_name and last_name with a space between them.

You should see two rows (one for each item in Order #1): Wireless Bluetooth Headphones at 79.99 and Portable Bluetooth Speaker at 49.99, both paid by card.

Seeing zero rows?

Double check that your WHERE o.id = 1 clause is at the end. Also confirm your orders table has data by running SELECT * FROM orders; first. If that returns nothing, re-run the orders INSERT statement from earlier.

Help me figure out why my query returns no rows

Write business intelligence queries

Real businesses use their databases to answer questions like "What sells best?" and "Who are my most valuable customers?" These queries use aggregate functions (SUM, COUNT) combined with GROUP BY to crunch numbers across many rows.

  • Add this query below your receipt query in ecommerce.sql, then paste it into the SQLite shell:
-- Query 2: Top 3 products by units sold
SELECT
    p.name,
    SUM(oi.quantity) AS units_sold,
    SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.id
GROUP BY p.id
ORDER BY units_sold DESC
LIMIT 3;

What does this query do?

This query counts how many units of each product have been ordered by summing the quantity column. GROUP BY p.id collapses all order_items rows for the same product into a single row.

The ORDER BY units_sold DESC LIMIT 3 sorts by highest sales first and only shows the top 3.

You should see Portable Bluetooth Speaker at the top with 2 units sold, followed by two other products with 1 unit each.

  • Add the revenue per category query below in ecommerce.sql, then paste it into the shell:
-- Query 3: Revenue per category
SELECT
    cat.name AS category,
    SUM(oi.quantity * oi.unit_price) AS category_revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.id
JOIN categories cat ON p.category_id = cat.id
GROUP BY cat.id
ORDER BY category_revenue DESC;

What does this query do?

This query chains two JOINs: from order_items to products, then from products to categories. This lets it group revenue by category even though order_items has no direct link to categories.

Each row shows a category name and the total revenue from all products in that category. Audio should lead with the highest revenue.

  • Add the customer lifetime value query below in ecommerce.sql, then paste it into the shell:
-- Query 4: Customer lifetime value
SELECT
    c.first_name || ' ' || c.last_name AS customer,
    COUNT(DISTINCT o.id) AS order_count,
    SUM(o.total_amount) AS lifetime_spend
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id
ORDER BY lifetime_spend DESC;

What does this query do?

This query starts from customers and LEFT JOINs to orders. The LEFT JOIN ensures customers who have never ordered still appear in the results (with NULL spend).

The COUNT(DISTINCT o.id) counts unique orders per customer. Alice should appear at the top with 2 orders and the highest lifetime spend.

  • Save ecommerce.sql.

You should see all five customers listed, with Alice Johnson at the top (2 orders, 174.97 lifetime spend) and two customers (Dave and Eve) showing NULL because they haven't placed any orders.

Seeing a syntax error on the queries?

Make sure each query ends with a semicolon. In the SQLite shell, a query isn't executed until it sees the ; character. If your prompt shows ...> instead of sqlite>, the shell is still waiting for more input. Type ; and press Enter to finish the statement.

Help me troubleshoot my query syntax

✔️ Awesome, I've got everything!

Great. Double check you've saved ecommerce.sql with all the new code.

ⓧ I'd like to double check the full code

Here is the complete ecommerce.sql file at this point:

-- E-Commerce Database Schema
-- Run with: sqlite3 store.db < ecommerce.sql

-- Enable foreign key enforcement (SQLite has this off by default)
PRAGMA foreign_keys = ON;

-- ============================================
-- FOUNDATION TABLES
-- ============================================

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE categories (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    description TEXT
);

CREATE TABLE addresses (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    address_type TEXT NOT NULL CHECK(address_type IN ('shipping', 'billing')),
    street TEXT NOT NULL,
    city TEXT NOT NULL,
    state TEXT NOT NULL,
    postal_code TEXT NOT NULL,
    country TEXT NOT NULL DEFAULT 'US',
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
);

-- ============================================
-- PRODUCT CATALOG
-- ============================================

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    sku TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL CHECK(price > 0),
    stock_quantity INTEGER NOT NULL DEFAULT 0 CHECK(stock_quantity >= 0),
    category_id INTEGER,
    is_active INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
);

-- ============================================
-- ORDER SYSTEM
-- ============================================

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending', 'paid', 'shipped', 'delivered', 'cancelled')),
    total_amount DECIMAL(10,2) NOT NULL CHECK(total_amount >= 0),
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE TABLE order_items (
    id INTEGER PRIMARY KEY,
    order_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL CHECK(quantity > 0),
    unit_price DECIMAL(10,2) NOT NULL CHECK(unit_price > 0),
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE payments (
    id INTEGER PRIMARY KEY,
    order_id INTEGER NOT NULL,
    amount DECIMAL(10,2) NOT NULL CHECK(amount > 0),
    method TEXT NOT NULL CHECK(method IN ('card', 'paypal', 'bank_transfer')),
    status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending', 'completed', 'failed', 'refunded')),
    paid_at TEXT,
    FOREIGN KEY (order_id) REFERENCES orders(id)
);

-- ============================================
-- SAMPLE DATA
-- ============================================

-- Categories
INSERT INTO categories (name, description) VALUES
    ('Electronics', 'Phones, laptops, and gadgets'),
    ('Accessories', 'Cases, cables, and add-ons'),
    ('Audio', 'Headphones, speakers, and earbuds'),
    ('Wearables', 'Smartwatches and fitness trackers');

-- Customers
INSERT INTO customers (email, first_name, last_name) VALUES
    ('alice@example.com', 'Alice', 'Johnson'),
    ('bob@example.com', 'Bob', 'Smith'),
    ('carol@example.com', 'Carol', 'Williams'),
    ('dave@example.com', 'Dave', 'Brown'),
    ('eve@example.com', 'Eve', 'Davis');

-- Addresses
INSERT INTO addresses (customer_id, address_type, street, city, state, postal_code) VALUES
    (1, 'shipping', '123 Main St', 'Portland', 'OR', '97201'),
    (1, 'billing', '123 Main St', 'Portland', 'OR', '97201'),
    (2, 'shipping', '456 Oak Ave', 'Seattle', 'WA', '98101'),
    (3, 'shipping', '789 Pine Rd', 'Denver', 'CO', '80201'),
    (4, 'shipping', '321 Elm Blvd', 'Austin', 'TX', '73301');

-- Products
INSERT INTO products (sku, name, description, price, stock_quantity, category_id) VALUES
    ('ELEC-001', 'Wireless Bluetooth Headphones', 'Over-ear noise cancelling headphones', 79.99, 50, 3),
    ('ELEC-002', 'USB-C Fast Charger', '65W GaN charger with 3 ports', 34.99, 120, 2),
    ('ELEC-003', 'Smartwatch Pro', 'Fitness tracking with GPS and heart rate', 199.99, 30, 4),
    ('ELEC-004', 'Phone Case - Clear', 'Slim transparent protective case', 14.99, 200, 2),
    ('ELEC-005', 'Portable Bluetooth Speaker', 'Waterproof 20W speaker', 49.99, 75, 3),
    ('ELEC-006', 'Laptop Stand - Aluminum', 'Adjustable ergonomic stand', 44.99, 60, 2),
    ('ELEC-007', 'Wireless Earbuds', 'True wireless with 8hr battery', 59.99, 90, 3),
    ('ELEC-008', 'Fitness Band Lite', 'Step counter and sleep tracker', 29.99, 150, 4);

-- Orders
INSERT INTO orders (customer_id, status, total_amount, created_at) VALUES
    (1, 'delivered', 129.98, '2026-05-01 10:30:00'),
    (2, 'shipped', 249.98, '2026-05-15 14:20:00'),
    (1, 'paid', 44.99, '2026-05-28 09:15:00'),
    (3, 'pending', 94.98, '2026-06-01 16:45:00');

-- Order Items
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
    (1, 1, 1, 79.99),
    (1, 5, 1, 49.99),
    (2, 3, 1, 199.99),
    (2, 5, 1, 49.99),
    (3, 6, 1, 44.99),
    (4, 7, 1, 59.99),
    (4, 2, 1, 34.99);

-- Payments
INSERT INTO payments (order_id, amount, method, status, paid_at) VALUES
    (1, 129.98, 'card', 'completed', '2026-05-01 10:32:00'),
    (2, 249.98, 'paypal', 'completed', '2026-05-15 14:22:00'),
    (3, 44.99, 'card', 'completed', '2026-05-28 09:17:00');

-- ============================================
-- BUSINESS QUERIES
-- ============================================

-- Query 1: Full order receipt for Order #1
SELECT
    o.id AS order_id,
    c.first_name || ' ' || c.last_name AS customer,
    p.name AS product,
    oi.quantity,
    oi.unit_price,
    (oi.quantity * oi.unit_price) AS line_total,
    o.status,
    pay.method AS payment_method
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON oi.product_id = p.id
LEFT JOIN payments pay ON pay.order_id = o.id
WHERE o.id = 1;

-- Query 2: Top 3 products by units sold
SELECT
    p.name,
    SUM(oi.quantity) AS units_sold,
    SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.id
GROUP BY p.id
ORDER BY units_sold DESC
LIMIT 3;

-- Query 3: Revenue per category
SELECT
    cat.name AS category,
    SUM(oi.quantity * oi.unit_price) AS category_revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.id
JOIN categories cat ON p.category_id = cat.id
GROUP BY cat.id
ORDER BY category_revenue DESC;

-- Query 4: Customer lifetime value
SELECT
    c.first_name || ' ' || c.last_name AS customer,
    COUNT(DISTINCT o.id) AS order_count,
    SUM(o.total_amount) AS lifetime_spend
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id
ORDER BY lifetime_spend DESC;

Your e-commerce database is complete. Seven tables, realistic sample data, and business queries that prove the whole design works together. Next up, you'll take on a bonus challenge that extends the schema even further.

Secret mission

Add a Shopping Cart System

Every online store needs a shopping cart. But carts are temporary. Items sit there while customers browse, and if they wait too long, the cart should expire. Can you extend your schema to handle this transition from browsing to buying?

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally, so there are no ongoing costs. Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

  • SQLite command-line tools installed at C:\sqlite.
  • Project folder containing ecommerce.sql, ecommerce_cart.sql, and store.db.

Keep everything running

No action needed. Choose this if you want to keep exploring your database or extend the schema further.

  • Your SQLite tools remain available system-wide for future projects.
  • Your store.db database is ready to reopen any time with sqlite3 store.db.
  • Your .sql script files can be reused or adapted for other projects.

Pause - I'll come back to this later

Exit the SQLite shell to free up the process, but keep all files in place.

  • If you still have the SQLite shell open, type .quit and press Enter to exit.
  • Close the Command Prompt window.
  • Your project folder and all files stay on disk. When you return, open Command Prompt in your project folder and run sqlite3 store.db to pick up where you left off.

Delete - I don't want to use this again

Remove all project files and optionally uninstall SQLite from your system.

  • If you still have the SQLite shell open, type .quit and press Enter to exit.
  • Delete your project folder (the one containing ecommerce.sql, ecommerce_cart.sql, and store.db). Right-click the folder in File Explorer and select Delete.
  • To remove SQLite from your system, delete the C:\sqlite folder. Open File Explorer, navigate to C:\sqlite, right-click it, and select Delete.
  • Remove C:\sqlite from your system PATH. Press the Windows key, search for Environment Variables, open Edit the system environment variables, click Environment Variables, select Path under User variables, click Edit, find and remove the C:\sqlite entry, then click OK on all dialogs.

Nice Work!

Nice Work!

You just designed a real e-commerce database from the ground up. Starting from an empty file, you built 7 interconnected tables, loaded them with realistic data, and proved they work with business intelligence queries.

You've learned how to:

  • Design normalized tables with proper primary keys, foreign keys, and CHECK constraints that enforce valid data at the database level.
  • Model one-to-many and many-to-many relationships using junction tables like order_items that connect orders to products.
  • Write JOIN queries and aggregate functions that answer real business questions like top-selling products, revenue by category, and customer lifetime value.
  • Built a shopping cart system with cart expiration logic and price snapshots that preserve the price at the moment a customer adds an item.

Ready to quiz yourself?