Sample SQL database

4 tables1,782 order items, 1,000 orders, 300 customers, 56 products

Updated 3 November 2026. Fictional U.S. businesses and people, generated with a fixed seed.

Download .sql CSV and XLSX per table

textnumberdateyes/no

Rows3,138across 4 tables

Columns24with a data dictionary below

Dates2024-01-01 – 2024-12-29range in the file

LicenceCC0synthetic, free for any use

What is in it

A small online-store database in one .sql file that loads into PostgreSQL, MySQL or SQLite, with primary and foreign keys: 300 customers, 56 products, 1,000 orders and their order items. CSV and Excel copies of each table are included.

First rows

orders table

order_idnumber	customer_idtext	order_datedate	statustext	ship_statetext
1	CU10244	2024-09-17	Delivered	NY
2	CU10026	2024-06-20	Delivered	NC
3	CU10086	2024-11-24	Delivered	NY
4	CU10195	2024-08-31	Delivered	CO
5	CU10130	2024-09-16	Delivered	TX
6	CU10166	2024-11-21	Returned	NJ
7	CU10099	2024-09-30	Delivered	CT
8	CU10037	2024-10-01	Delivered	IN

Data dictionary

customers 300 rows

Column	Type	Meaning
customer_id	text	Customer number
first_name	text	Fictional first name
last_name	text	Fictional last name
email	text	example.com address
city	text	City
state	text	State code
signup_date	date	Account created
segment	text	Consumer, Small Business or Enterprise

products 56 rows

Column	Type	Meaning
product_id	text	Product number
category	text	Category
sub_category	text	Sub-category
product_name	text	Generic product name
unit_price	decimal	List price (USD)
unit_cost	decimal	Cost (USD)

orders 1,000 rows

Column	Type	Meaning
order_id	integer	Order number
customer_id	text	FK to customers.customer_id
order_date	date	Order date (2024)
status	text	Delivered, Shipped, Processing, Cancelled or Returned
ship_state	text	State shipped to

order_items 1,782 rows

Column	Type	Meaning
item_id	integer	Line number
order_id	integer	FK to orders.order_id
product_id	text	FK to products.product_id
quantity	integer	Units
unit_price	decimal	Price charged per unit (USD), after any discount

Practice questions

answers checked in SQL and in Python

Revenue by product category, excluding cancelled and returned orders?

Technology $537,612.64; Furniture $200,325.49; Office Supplies $67,416.80

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 NOT IN ('Cancelled','Returned')
GROUP BY p.category
ORDER BY revenue DESC

File: store-database

Top 5 customers by revenue (excluding cancelled and returned orders)?

Steven Lewis (CU10118) $21,983.66; Patricia Khan (CU10105) $15,506.69; Elizabeth Flores (CU10237) $14,375.98; Mei Nelson (CU10039) $14,273.55; Carol Clark (CU10292) $14,080.03

SQL

SELECT c.customer_id, c.first_name || ' ' || c.last_name AS name, 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 NOT IN ('Cancelled','Returned')
GROUP BY c.customer_id, name
ORDER BY revenue DESC
LIMIT 5

File: store-database

How many customers have never placed an order?

8 customers

SQL

SELECT COUNT(*)
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL

File: store-database

Orders by status?

Delivered 804; Shipped 66; Cancelled 51; Processing 47; Returned 32

SQL

SELECT status, COUNT(*) FROM orders GROUP BY status ORDER BY COUNT(*) DESC

File: store-database

What is the list price of product P0012?

Corner Bookcase, $188.81

Excel

=XLOOKUP("P0012",A:A,E:E), or =VLOOKUP("P0012",A:F,5,FALSE), on the products sheet

SQL

SELECT product_name, unit_price FROM products WHERE product_id = 'P0012'

File: store-database