SQL Programming Resources on Telegram: “🚀 SQL Project Series #31: Supply Chain & Procur…”

🚀 SQL Project Series #31: Supply Chain & Procurement Analytics 🚚

Analyze suppliers, purchase orders, deliveries, procurement costs, and supplier performance using SQL to identify cost-saving opportunities and improve supply chain efficiency.

🎯 Business Objectives

✅ Analyze purchase orders

✅ Track supplier performance

✅ Measure procurement spending

✅ Identify delayed deliveries

✅ Analyze purchase costs

✅ Monitor order fulfillment

✅ Evaluate supplier quality

✅ Identify cost-saving opportunities

📂 Step 1: Create Database

CREATE DATABASE procurement_db;
USE procurement_db;

📂 Step 2: Create Suppliers Table

CREATE TABLE suppliers (
    supplier_id INT PRIMARY KEY,
    supplier_name VARCHAR(100),
    city VARCHAR(50),
    supplier_category VARCHAR(50)
);

📂 Step 3: Create Products Table

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    category VARCHAR(50),
    standard_cost DECIMAL(10,2)
);

📂 Step 4: Create Purchase Orders Table

CREATE TABLE purchase_orders (
    po_id INT PRIMARY KEY,
    supplier_id INT,
    po_date DATE,
    expected_date DATE,
    actual_delivery_date DATE,
    po_status VARCHAR(30),
    FOREIGN KEY (supplier_id)
        REFERENCES suppliers(supplier_id)
);

📂 Step 5: Create Purchase Order Items Table

CREATE TABLE purchase_order_items (
    po_item_id INT PRIMARY KEY,
    po_id INT,
    product_id INT,
    quantity INT,
    unit_cost DECIMAL(10,2),
    FOREIGN KEY (po_id)
        REFERENCES purchase_orders(po_id),
    FOREIGN KEY (product_id)
        REFERENCES products(product_id)
);

📂 Step 6: Insert Sample Suppliers

INSERT INTO suppliers VALUES
(1,'ABC Suppliers','Mumbai','Electronics'),
(2,'Global Traders','Delhi','Office Supplies'),
(3,'Prime Distributors','Pune','Electronics'),
(4,'Reliable Wholesale','Bangalore','Furniture'),
(5,'Metro Supplies','Hyderabad','General');

📂 Step 7: Insert Sample Products

INSERT INTO products VALUES
(101,'Laptop','Electronics',55000),
(102,'Monitor','Electronics',15000),
(103,'Keyboard','Accessories',1200),
(104,'Office Chair','Furniture',6500),
(105,'Printer','Office Equipment',18000);

📂 Step 8: Insert Sample Purchase Orders

INSERT INTO purchase_orders VALUES
(1001,1,'2025-01-05','2025-01-10','2025-01-09','Delivered'),
(1002,2,'2025-01-07','2025-01-12','2025-01-15','Delivered'),
(1003,3,'2025-01-10','2025-01-18','2025-01-22','Delayed'),
(1004,4,'2025-01-12','2025-01-20','2025-01-19','Delivered'),
(1005,5,'2025-01-15','2025-01-25',NULL,'Pending');

📂 Step 9: Insert Sample Purchase Order Items

INSERT INTO purchase_order_items VALUES
(1,1001,101,20,54000),
(2,1001,102,30,14500),
(3,1002,103,100,1100),
(4,1003,101,15,53500),
(5,1003,105,10,17500),
(6,1004,104,40,6200),
(7,1005,105,20,17200);