SQL Programming Resources on Telegram: “🚀 SQL Project Series #25 Manufacturing Producti…”

🚀 SQL Project Series #25

Manufacturing Production Analytics 🏭

Analyze production, machines, inventory, quality, and maintenance using SQL to improve operational efficiency, reduce downtime, and optimize manufacturing performance.

🎯 Business Objectives

✅ Monitor production output

✅ Analyze machine utilization

✅ Track product quality

✅ Measure production efficiency

✅ Identify production bottlenecks

✅ Analyze inventory consumption

✅ Monitor machine maintenance

✅ Build executive manufacturing dashboards

📂 Step 1: Create Database

CREATE DATABASE manufacturing_db;
USE manufacturing_db;

📂 Step 2: Create Products Table

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

📂 Step 3: Create Machines Table

CREATE TABLE machines (
    machine_id INT PRIMARY KEY,
    machine_name VARCHAR(100),
    production_line VARCHAR(50),
    installation_date DATE
);

📂 Step 4: Create Production Table

CREATE TABLE production (
    production_id INT PRIMARY KEY,
    product_id INT,
    machine_id INT,
    production_date DATE,
    units_produced INT,
    defective_units INT,
    production_hours DECIMAL(5,2),
    FOREIGN KEY (product_id) REFERENCES products(product_id),
    FOREIGN KEY (machine_id) REFERENCES machines(machine_id)
);

📂 Step 5: Create Maintenance Table

CREATE TABLE maintenance (
    maintenance_id INT PRIMARY KEY,
    machine_id INT,
    maintenance_date DATE,
    maintenance_type VARCHAR(50),
    downtime_hours DECIMAL(5,2),
    maintenance_cost DECIMAL(10,2),
    FOREIGN KEY (machine_id) REFERENCES machines(machine_id)
);

📂 Step 6: Insert Sample Products

INSERT INTO products VALUES
(101,'Laptop','Electronics',45000),
(102,'Smartphone','Electronics',25000),
(103,'Tablet','Electronics',18000),
(104,'Smart Watch','Wearables',8000),
(105,'Wireless Earbuds','Accessories',3500);

📂 Step 7: Insert Sample Machines

INSERT INTO machines VALUES
(201,'Assembly Line A','Line 1','2022-01-15'),
(202,'Assembly Line B','Line 1','2022-06-20'),
(203,'Packaging Machine','Line 2','2023-02-10'),
(204,'Quality Inspection','Line 3','2023-08-18');

📂 Step 8: Insert Sample Production Data

INSERT INTO production VALUES
(1001,101,201,'2025-01-05',250,5,8.5),
(1002,102,202,'2025-01-05',420,8,9.0),
(1003,103,201,'2025-01-06',180,3,7.5),
(1004,104,203,'2025-01-06',520,10,8.0),
(1005,105,204,'2025-01-07',650,6,7.0);

📂 Step 9: Insert Sample Maintenance Data

INSERT INTO maintenance VALUES
(1,201,'2025-01-08','Preventive',2.5,12000),
(2,202,'2025-01-10','Corrective',5.0,25000),
(3,203,'2025-01-12','Preventive',1.5,9000),
(4,204,'2025-01-15','Inspection',1.0,5000);