Online store sample database for MySQL
A ready-to-run MySQL script with four related tables (customers, products, orders, order items) for JOIN practice. Tested on MySQL 8.4.13. Updated 19 November 2026.
Online store database for MySQL
Four related tables (customers, products, orders, order items) for JOIN practice.
This page has it as a MySQL script: 4 related tables and 3,138 rows, with column types chosen for MySQL, 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 MySQL 8.4.13 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
mysql client
mysql -u root -p -e "CREATE DATABASE practice"
mysql -u root -p practice < store-database-mysql.sqlMySQL Workbench
File > Open SQL Script, choose the database with USE practice; then Execute.
Tables and columns
customers 300 rows
| Column | MySQL type |
|---|---|
| customer_id | VARCHAR(10) |
| first_name | VARCHAR(20) |
| last_name | VARCHAR(20) |
| VARCHAR(40) | |
| city | VARCHAR(20) |
| state | VARCHAR(10) |
| signup_date | DATE |
| segment | VARCHAR(20) |
products 56 rows
| Column | MySQL type |
|---|---|
| product_id | VARCHAR(10) |
| category | VARCHAR(20) |
| sub_category | VARCHAR(20) |
| product_name | VARCHAR(40) |
| unit_price | DECIMAL(9,2) |
| unit_cost | DECIMAL(9,2) |
orders 1,000 rows
| Column | MySQL type |
|---|---|
| order_id | INT |
| customer_id | VARCHAR(10) |
| order_date | DATE |
| status | VARCHAR(20) |
| ship_state | VARCHAR(10) |
order_items 1,782 rows
| Column | MySQL type |
|---|---|
| item_id | INT |
| order_id | INT |
| product_id | VARCHAR(10) |
| quantity | INT |
| unit_price | DECIMAL(9,2) |
Practice questions
answers from MySQL 8.4.13
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, CONCAT(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;CONCAT(a, b, ...) joins strings; || is not string concatenation by default.
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 DATE_FORMAT(o.order_date, '%Y-%m') 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 DATE_FORMAT(o.order_date, '%Y-%m')
ORDER BY month;DATE_FORMAT(date, '%Y-%m') formats a date as 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.00 | 38,620.50 |
| Desktop Copier | 82.00 | 34,134.62 |
| Cordless Phone Set | 58.00 | 19,166.43 |
| Label Printer | 79.00 | 18,494.60 |
| Desk Phone | 68.00 | 15,666.66 |
MySQL differences used here
- CONCAT(a, b, ...) joins strings; || is not string concatenation by default.
- LIMIT n at the end returns the first n rows.
- DATE_FORMAT(date, '%Y-%m') formats a date as year-month text.