Inventory sample database for MySQL
A ready-to-run MySQL script with stock levels, reorder points and stock value across four warehouses. Tested on MySQL 8.4.13. Updated 19 November 2026.
Inventory database for MySQL
Each row is one SKU in one warehouse: quantity on hand, reorder point and quantity, unit cost and price, stock value, supplier code, last restock date and a needs_reorder flag you can check with a formula.
This page has it as a MySQL script: one table and 1,000 rows, with column types chosen for MySQL, primary keys. The script drops the table if it exists, creates it 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 inventory 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 < inventory-mysql.sqlMySQL Workbench
File > Open SQL Script, choose the database with USE practice; then Execute.
Tables and columns
inventory 1,000 rows
| Column | MySQL type |
|---|---|
| sku | VARCHAR(20) |
| product_name | VARCHAR(40) |
| category | VARCHAR(20) |
| warehouse | VARCHAR(20) |
| quantity_on_hand | INT |
| reorder_point | INT |
| reorder_qty | INT |
| unit_cost | DECIMAL(9,2) |
| unit_price | DECIMAL(9,2) |
| stock_value | DECIMAL(11,2) |
| supplier | VARCHAR(10) |
| last_restock_date | DATE |
| needs_reorder | BOOLEAN |
Practice questions
answers from MySQL 8.4.13
What is the stock value held in each warehouse?
SQL
SELECT warehouse, COUNT(*) AS skus, SUM(quantity_on_hand) AS units, ROUND(SUM(stock_value), 2) AS stock_value
FROM inventory
GROUP BY warehouse
ORDER BY stock_value DESC, warehouse;Result
| warehouse | skus | units | stock_value |
|---|---|---|---|
| WH1 Newark | 280 | 26,911.00 | 4,470,329.76 |
| WH2 Chicago | 272 | 26,014.00 | 3,685,288.21 |
| WH4 Reno | 224 | 19,666.00 | 3,350,580.77 |
| WH3 Dallas | 224 | 21,832.00 | 3,200,945.35 |
How many SKUs need reordering in each warehouse?
SQL
SELECT warehouse, SUM(CASE WHEN needs_reorder = 1 THEN 1 ELSE 0 END) AS needs_reorder
FROM inventory
GROUP BY warehouse
ORDER BY needs_reorder DESC, warehouse;BOOLEAN is stored as TINYINT(1): TRUE is 1 and FALSE is 0.
Result
| warehouse | needs_reorder |
|---|---|
| WH4 Reno | 54.00 |
| WH1 Newark | 52.00 |
| WH2 Chicago | 48.00 |
| WH3 Dallas | 46.00 |
Does needs_reorder agree with quantity_on_hand <= reorder_point? Count the rows where it does not.
SQL
SELECT COUNT(*) AS mismatches
FROM inventory
WHERE CASE WHEN quantity_on_hand <= reorder_point THEN 1 ELSE 0 END <> CASE WHEN needs_reorder = 1 THEN 1 ELSE 0 END;BOOLEAN is stored as TINYINT(1): TRUE is 1 and FALSE is 0.
Result
| mismatches |
|---|
| 0 |
Which 5 products have the highest margin per unit?
SQL
SELECT sku, product_name, ROUND(unit_price - unit_cost, 2) AS unit_margin
FROM inventory
ORDER BY unit_margin DESC, sku
LIMIT 5;LIMIT n at the end returns the first n rows.
Result
| sku | product_name | unit_margin |
|---|---|---|
| P0056-WH1 | Workgroup Copier | 610.05 |
| P0056-WH1-1 | Workgroup Copier | 610.05 |
| P0056-WH1-2 | Workgroup Copier | 610.05 |
| P0056-WH1-3 | Workgroup Copier | 610.05 |
| P0056-WH1-4 | Workgroup Copier | 610.05 |
What is the average markup percentage by category?
SQL
SELECT category, ROUND(AVG(CAST(100 * (unit_price - unit_cost) / unit_cost AS DECIMAL(18,4))), 2) AS avg_markup_pct
FROM inventory
GROUP BY category
ORDER BY avg_markup_pct DESC, category;The cast to DECIMAL keeps the average exact before rounding.
Result
| category | avg_markup_pct |
|---|---|
| Office Supplies | 58.63 |
| Technology | 53.12 |
| Furniture | 48.89 |
How many restocks happened in each month?
SQL
SELECT DATE_FORMAT(last_restock_date, '%Y-%m') AS month, COUNT(*) AS restocks
FROM inventory
GROUP BY DATE_FORMAT(last_restock_date, '%Y-%m')
ORDER BY month;DATE_FORMAT(date, '%Y-%m') formats a date as year-month text.
Result
| month | restocks |
|---|---|
| 2024-01 | 75 |
| 2024-02 | 76 |
| 2024-03 | 89 |
| 2024-04 | 90 |
| 2024-05 | 88 |
| 2024-06 | 74 |
| 2024-07 | 76 |
| 2024-08 | 88 |
| 2024-09 | 70 |
| 2024-10 | 101 |
| 2024-11 | 86 |
| 2024-12 | 87 |
MySQL differences used here
- The cast to DECIMAL keeps the average exact before rounding.
- BOOLEAN is stored as TINYINT(1): TRUE is 1 and FALSE is 0.
- LIMIT n at the end returns the first n rows.
- DATE_FORMAT(date, '%Y-%m') formats a date as year-month text.