Inventory Management Dataset for SQL

• inventory_levels.product_id →references products.product_idmany to one: each Inventory levels row points to one Products row
• inventory_levels.warehouse_id →references warehouses.warehouse_idmany to one: each Inventory levels row points to one Warehouses row
• transactions.product_id →references products.product_idmany to one: each Transactions row points to one Products row
• transactions.warehouse_id →references warehouses.warehouse_idmany to one: each Transactions row points to one Warehouses row

1. Write a SQL query to identify products that have not had any outgoing transactions in the last 90 days.

   Filter transactions by type and date, then find products not present in the result.

   tables: products, transactions

2. Write a SQL query to calculate the total stock quantity for each product across all warehouses.

   Join products and inventory_levels, then group by product and sum the quantity.

   tables: products, inventory_levels

3. Write a SQL query to find the average stock quantity per warehouse for each product category.

   Join all three tables, group by category and warehouse, then calculate the average stock quantity.

   tables: products, inventory_levels, warehouses

4. Write a SQL query to list all products and their total stock quantity, showing 0 if a product has no inventory records.

   Use a LEFT JOIN from products to inventory_levels and sum quantities, handling NULLs.

   tables: products, inventory_levels

Table names match the SQLite file and the SQL script.

SELECT p.product_name, SUM(il.stock_quantity) AS total_stock
FROM products p
JOIN inventory_levels il ON p.product_id = il.product_id
GROUP BY p.product_name
ORDER BY total_stock DESC
LIMIT 20;

SELECT p.product_name
FROM products p
LEFT JOIN (
    SELECT DISTINCT product_id
    FROM transactions
    WHERE transaction_type = 'OUT' AND transaction_date >= DATE('now', '-90 day')
) AS recent_outbound ON p.product_id = recent_outbound.product_id
WHERE recent_outbound.product_id IS NULL
LIMIT 20;

SELECT w.warehouse_name, p.category, AVG(il.stock_quantity) AS average_stock
FROM warehouses w
JOIN inventory_levels il ON w.warehouse_id = il.warehouse_id
JOIN products p ON il.product_id = p.product_id
GROUP BY w.warehouse_name, p.category
ORDER BY w.warehouse_name, p.category
LIMIT 20;

SELECT p.product_name, COALESCE(SUM(il.stock_quantity), 0) AS total_stock
FROM products p
LEFT JOIN inventory_levels il ON p.product_id = il.product_id
GROUP BY p.product_name
ORDER BY p.product_name
LIMIT 20;

SQL

The dataset is available as an SQLite database file. You can load this file into any SQLite-compatible tool or convert it to other SQL formats. Primary keys and foreign keys are defined to facilitate joins between tables.

• CSV (zip)One CSV file per table, zipped
• Excel workbookOne worksheet per table
• SQLite databaseA ready-to-query database file with every table
• Parquet (zip)One Parquet file per table, zipped
• SQL scriptCREATE TABLE with primary and foreign keys, then INSERTs
• CSVA single CSV file
• JSONA single JSON file