DataBrighten

    Inventory sample database for MySQL

    1,000rows · 1 table · 6 questions with answers

    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.

    textnumberdateyes/no

    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.sql

    MySQL Workbench

    File > Open SQL Script, choose the database with USE practice; then Execute.

    Tables and columns

    inventory 1,000 rows

    ColumnMySQL type
    skuVARCHAR(20)
    product_nameVARCHAR(40)
    categoryVARCHAR(20)
    warehouseVARCHAR(20)
    quantity_on_handINT
    reorder_pointINT
    reorder_qtyINT
    unit_costDECIMAL(9,2)
    unit_priceDECIMAL(9,2)
    stock_valueDECIMAL(11,2)
    supplierVARCHAR(10)
    last_restock_dateDATE
    needs_reorderBOOLEAN

    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

    warehouseskusunitsstock_value
    WH1 Newark28026,911.004,470,329.76
    WH2 Chicago27226,014.003,685,288.21
    WH4 Reno22419,666.003,350,580.77
    WH3 Dallas22421,832.003,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

    warehouseneeds_reorder
    WH4 Reno54.00
    WH1 Newark52.00
    WH2 Chicago48.00
    WH3 Dallas46.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

    skuproduct_nameunit_margin
    P0056-WH1Workgroup Copier610.05
    P0056-WH1-1Workgroup Copier610.05
    P0056-WH1-2Workgroup Copier610.05
    P0056-WH1-3Workgroup Copier610.05
    P0056-WH1-4Workgroup Copier610.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

    categoryavg_markup_pct
    Office Supplies58.63
    Technology53.12
    Furniture48.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

    monthrestocks
    2024-0175
    2024-0276
    2024-0389
    2024-0490
    2024-0588
    2024-0674
    2024-0776
    2024-0888
    2024-0970
    2024-10101
    2024-1186
    2024-1287

    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.