DataBrighten

    Inventory sample database for PostgreSQL

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

    A ready-to-run PostgreSQL script with stock levels, reorder points and stock value across four warehouses. Tested on PostgreSQL 15.18. Updated 19 November 2026.

    textnumberdateyes/no

    Inventory database for PostgreSQL

    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 PostgreSQL script: one table and 1,000 rows, with column types chosen for PostgreSQL, 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 PostgreSQL 15.18 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

    psql

    createdb practice
    psql -d practice -f inventory-postgresql.sql

    pgAdmin or DBeaver

    Create a database, open the Query Tool, open the .sql file and run it.

    Tables and columns

    inventory 1,000 rows

    ColumnPostgreSQL type
    skuVARCHAR(20)
    product_nameVARCHAR(40)
    categoryVARCHAR(20)
    warehouseVARCHAR(20)
    quantity_on_handINTEGER
    reorder_pointINTEGER
    reorder_qtyINTEGER
    unit_costDECIMAL(9,2)
    unit_priceDECIMAL(9,2)
    stock_valueDECIMAL(11,2)
    supplierVARCHAR(10)
    last_restock_dateDATE
    needs_reorderBOOLEAN

    Practice questions

    answers from PostgreSQL 15.18

    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 Newark280269114,470,329.76
    WH2 Chicago272260143,685,288.21
    WH4 Reno224196663,350,580.77
    WH3 Dallas224218323,200,945.35
    How many SKUs need reordering in each warehouse?

    SQL

    SELECT warehouse, SUM(CASE WHEN needs_reorder THEN 1 ELSE 0 END) AS needs_reorder
    FROM inventory
    GROUP BY warehouse
    ORDER BY needs_reorder DESC, warehouse;

    Yes/no columns are real BOOLEAN values, so they can be used directly in WHERE and CASE.

    Result

    warehouseneeds_reorder
    WH4 Reno54
    WH1 Newark52
    WH2 Chicago48
    WH3 Dallas46
    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 (quantity_on_hand <= reorder_point) <> (needs_reorder);

    Yes/no columns are real BOOLEAN values, so they can be used directly in WHERE and CASE.

    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(100 * (unit_price - unit_cost) / unit_cost), 2) AS avg_markup_pct
    FROM inventory
    GROUP BY category
    ORDER BY avg_markup_pct DESC, category;

    AVG of an integer column returns a numeric, so no cast is needed.

    Result

    categoryavg_markup_pct
    Office Supplies58.63
    Technology53.12
    Furniture48.89
    How many restocks happened in each month?

    SQL

    SELECT to_char(last_restock_date, 'YYYY-MM') AS month, COUNT(*) AS restocks
    FROM inventory
    GROUP BY to_char(last_restock_date, 'YYYY-MM')
    ORDER BY month;

    to_char(date, 'YYYY-MM') 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

    PostgreSQL differences used here

    • AVG of an integer column returns a numeric, so no cast is needed.
    • Yes/no columns are real BOOLEAN values, so they can be used directly in WHERE and CASE.
    • LIMIT n at the end returns the first n rows.
    • to_char(date, 'YYYY-MM') formats a date as year-month text.