Inventory Management System

Inventory Management System

(Stock Control & Sales Management)

This system manages:

Products (what we sell)
Suppliers (who supply products)
Sales (when product is sold)

Business Logic:

When product is sold → stock reduces
Low stock alert if quantity < minimum level
Total sales revenue calculation

STEP 1: Create Database

				
					CREATE DATABASE inventory_db;

-- Verify database
SHOW DATABASES;

STEP 2: Use Database

				
					USE inventory_db;

-- Check current database
SELECT DATABASE();

STEP 3: Create Suppliers Table

Stores supplier details.

Fields:

supplier_id
supplier_name
phone
city

				
					CREATE TABLE suppliers (
    supplier_id INT PRIMARY KEY AUTO_INCREMENT,
    supplier_name VARCHAR(100) NOT NULL,
    phone VARCHAR(15),
    city VARCHAR(50)
);

-- Check structure
DESC suppliers;

STEP 4: Insert Suppliers

				
					INSERT INTO suppliers (supplier_name, phone, city) VALUES
('Tech Distributors', '9876543210', 'Delhi'),
('Global Electronics', '9876501234', 'Mumbai'),
('Smart Supplies', '9876512345', 'Jaipur');

-- View suppliers
SELECT * FROM suppliers;

STEP 5: Create Products Table

Stores product details and stock information.

Fields:

product_id
product_name
supplier_id (Foreign Key)
purchase_price
selling_price
stock_quantity
minimum_stock_level

				
					CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100) NOT NULL,
    supplier_id INT,
    purchase_price DECIMAL(10,2),
    selling_price DECIMAL(10,2),
    stock_quantity INT,
    minimum_stock_level INT,

    FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id)
);

-- Check structure
DESC products;

STEP 6: Insert Products

				
					INSERT INTO products 
(product_name, supplier_id, purchase_price, selling_price, stock_quantity, minimum_stock_level) 
VALUES
('Laptop', 1, 40000, 45000, 10, 3),
('Printer', 2, 8000, 10000, 5, 2),
('Mouse', 3, 200, 350, 50, 10);

-- View products
SELECT * FROM products;

STEP 7: Create Sales Table

Stores sales transactions.

Fields:

sale_id
product_id (Foreign Key)
quantity_sold
sale_date
total_amount (auto calculated)

				
					CREATE TABLE sales (
    sale_id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT,
    quantity_sold INT,
    sale_date DATE,

    total_amount DECIMAL(10,2) GENERATED ALWAYS AS
    (quantity_sold * (SELECT selling_price 
                      FROM products 
                      WHERE products.product_id = sales.product_id)) STORED,

    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- Check structure
DESC sales;
--(Note: Some MySQL versions do not allow subquery in generated column.
--If error occurs, we calculate total during insert as shown below.)

STEP 8: Record a Sale & Update Stock

When a product is sold:

Insert sale record
Reduce stock

Example: Selling 2 Laptops

				
					INSERT INTO sales (product_id, quantity_sold, sale_date)
VALUES (1, 2, '2026-03-10');

UPDATE products
SET stock_quantity = stock_quantity - 2
WHERE product_id = 1;

-- Check updated sales
SELECT * FROM sales;

-- Check updated stock
SELECT product_name, stock_quantity FROM products;

STEP 9: Sell More Products

				
					INSERT INTO sales (product_id, quantity_sold, sale_date)
VALUES
(2, 1, '2026-03-10'),
(3, 5, '2026-03-10');

UPDATE products
SET stock_quantity = stock_quantity - 1
WHERE product_id = 2;

UPDATE products
SET stock_quantity = stock_quantity - 5
WHERE product_id = 3;

-- Verify stock after sales
SELECT product_name, stock_quantity FROM products;

STEP 10: Low Stock Alert Query

Display products where stock is below minimum level.

				
					SELECT product_name, stock_quantity, minimum_stock_level
FROM products
WHERE stock_quantity < minimum_stock_level;

-- Shows low stock products

STEP 11: Total Sales Revenue Calculation

Calculate total revenue earned.

				
					SELECT SUM(sales.quantity_sold * products.selling_price) AS total_revenue
FROM sales
JOIN products ON sales.product_id = products.product_id;

-- Total revenue displayed

STEP 12: Product-wise Sales Report

				
					SELECT products.product_name,
       SUM(sales.quantity_sold) AS total_quantity_sold,
       SUM(sales.quantity_sold * products.selling_price) AS total_sales_value
FROM sales
JOIN products ON sales.product_id = products.product_id
GROUP BY products.product_name;

-- Product-wise sales summary

STEP 13: Supplier-wise Product List

				
					SELECT suppliers.supplier_name,
       products.product_name,
       products.stock_quantity
FROM products
JOIN suppliers ON products.supplier_id = suppliers.supplier_id;

-- Supplier-wise product list

🎯 Concepts Covered in This Project

Primary Key
Foreign Key
Stock control logic
UPDATE queries
JOIN
GROUP BY
SUM
Inventory tracking
Revenue calculation
Business rule implementation
Multi-table relationship