Online store sample database for SQLite
A ready-to-run SQLite script with four related tables (customers, products, orders, order items) for JOIN practice. Tested on SQLite 3.37.2. Updated 19 November 2026.
Online store database for SQLite
Four related tables (customers, products, orders, order items) for JOIN practice.
This page has it as a SQLite script: 4 related tables and 3,138 rows, with column types chosen for SQLite, primary keys and foreign keys. The script drops the tables if they exist, creates them and inserts the rows in one transaction, so you can run it again to reset the data. It is the same fictional data, seed 42, as on the online store dataset page, where it is also available as CSV and Excel.
We loaded this exact file into SQLite 3.37.2 and ran every question below on it; the answers shown are that output. The same questions, written for SQLite, PostgreSQL, MySQL, SQL Server and DuckDB, return the same results on all five.
Load it
Command line
sqlite3 practice.db < store-database-sqlite.sql
sqlite3 practice.db ".tables"Or skip the script
Download store-database.sqlite and open it in DB Browser for SQLite, DBeaver or: sqlite3 store-database.sqlite
Tables and columns
customers 300 rows
| Column | SQLite type |
|---|---|
| customer_id | TEXT |
| first_name | TEXT |
| last_name | TEXT |
| TEXT | |
| city | TEXT |
| state | TEXT |
| signup_date | TEXT |
| segment | TEXT |
products 56 rows
| Column | SQLite type |
|---|---|
| product_id | TEXT |
| category | TEXT |
| sub_category | TEXT |
| product_name | TEXT |
| unit_price | REAL |
| unit_cost | REAL |
orders 1,000 rows
| Column | SQLite type |
|---|---|
| order_id | INTEGER |
| customer_id | TEXT |
| order_date | TEXT |
| status | TEXT |
| ship_state | TEXT |
order_items 1,782 rows
| Column | SQLite type |
|---|---|
| item_id | INTEGER |
| order_id | INTEGER |
| product_id | TEXT |
| quantity | INTEGER |
| unit_price | REAL |
Practice questions
answers from SQLite 3.37.2
How many rows does each table have?
SQL
SELECT 'customers' AS tbl, COUNT(*) AS n FROM customers
UNION ALL SELECT 'products', COUNT(*) FROM products
UNION ALL SELECT 'orders', COUNT(*) FROM orders
UNION ALL SELECT 'order_items', COUNT(*) FROM order_items
ORDER BY tbl;Result
| tbl | n |
|---|---|
| customers | 300 |
| order_items | 1782 |
| orders | 1000 |
| products | 56 |
What is revenue by product category, counting only delivered orders? (join 3 tables)
SQL
SELECT p.category, ROUND(SUM(i.quantity * i.unit_price), 2) AS revenue
FROM order_items i
JOIN orders o ON o.order_id = i.order_id
JOIN products p ON p.product_id = i.product_id
WHERE o.status = 'Delivered'
GROUP BY p.category
ORDER BY revenue DESC, p.category;Result
| category | revenue |
|---|---|
| Technology | 464,694.57 |
| Furniture | 178,055.14 |
| Office Supplies | 58,016.22 |
Who are the 5 customers with the highest delivered revenue?
SQL
SELECT c.customer_id, c.first_name || ' ' || c.last_name AS customer,
ROUND(SUM(i.quantity * i.unit_price), 2) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items i ON i.order_id = o.order_id
WHERE o.status = 'Delivered'
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY revenue DESC, c.customer_id
LIMIT 5;|| joins strings.
Result
| customer_id | customer | revenue |
|---|---|---|
| CU10118 | Steven Lewis | 21,983.66 |
| CU10237 | Elizabeth Flores | 14,375.98 |
| CU10292 | Carol Clark | 14,080.03 |
| CU10031 | Susan Carter | 13,974.42 |
| CU10039 | Mei Nelson | 13,634.07 |
How many customers have never placed an order? (LEFT JOIN)
SQL
SELECT COUNT(*) AS customers_without_orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;Result
| customers_without_orders |
|---|
| 8 |
What was delivered revenue in each month?
SQL
SELECT strftime('%Y-%m', o.order_date) AS month, COUNT(DISTINCT o.order_id) AS orders,
ROUND(SUM(i.quantity * i.unit_price), 2) AS revenue
FROM orders o
JOIN order_items i ON i.order_id = o.order_id
WHERE o.status = 'Delivered'
GROUP BY strftime('%Y-%m', o.order_date)
ORDER BY month;strftime('%Y-%m', date) turns a date into year-month text.
Result
| month | orders | revenue |
|---|---|---|
| 2024-01 | 59 | 49,376.78 |
| 2024-02 | 68 | 60,778.52 |
| 2024-03 | 69 | 62,303.52 |
| 2024-04 | 72 | 43,935.69 |
| 2024-05 | 75 | 66,275.68 |
| 2024-06 | 68 | 68,386.75 |
| 2024-07 | 58 | 49,342.41 |
| 2024-08 | 57 | 41,836.65 |
| 2024-09 | 69 | 45,975.24 |
| 2024-10 | 65 | 51,757.88 |
| 2024-11 | 68 | 70,541.49 |
| 2024-12 | 76 | 90,255.32 |
Which 5 products earn the highest gross margin (price minus cost) across all orders?
SQL
SELECT p.product_name, SUM(i.quantity) AS units,
ROUND(SUM(i.quantity * (i.unit_price - p.unit_cost)), 2) AS gross_margin
FROM order_items i
JOIN products p ON p.product_id = i.product_id
GROUP BY p.product_name
ORDER BY gross_margin DESC, p.product_name
LIMIT 5;LIMIT n at the end returns the first n rows.
Result
| product_name | units | gross_margin |
|---|---|---|
| Workgroup Copier | 65 | 38,620.50 |
| Desktop Copier | 82 | 34,134.62 |
| Cordless Phone Set | 58 | 19,166.43 |
| Label Printer | 79 | 18,494.60 |
| Desk Phone | 68 | 15,666.66 |
SQLite differences used here
- || joins strings.
- LIMIT n at the end returns the first n rows.
- strftime('%Y-%m', date) turns a date into year-month text.