Skip to content

Data Insertion & Automated Calculations 🚀

Prerequisites: PL/SQL Basics, SQL DML (INSERT)

Mentor's Note: Why do math manually when the database can do it for you? Using PL/SQL to calculate tax or allowances during insertion ensures your data is always accurate and consistent! 💡

🌟 The Scenario: The Automated Accountant 💼

Imagine you are building a Payroll System for a small company in Surat.

  • The Logic: When you hire a new employee, you only enter their Basic Salary. 📦
  • The Magic: The database automatically calculates:
    • HRA (Home Rent Allowance) = 15% 🏠
    • DA (Dearness Allowance) = 50% 💲
    • PF (Provident Fund) = 12% 🛡️
  • The Result: You save the Net Pay instantly without using a calculator. ✅

📖 Concept Explanation

1. Variables for Math

We declare local variables in the DECLARE section to hold temporary math results before pushing them into the table.

2. The & Substitution

We use the ampersand & symbol to ask the user for input at runtime.

🎨 Visual Logic: The Payroll Pipe

graph LR
    A[Input: Basic Salary 📥] --> B{Accountant Logic ⚙️}
    B -- "x 0.15" --> C[HRA 🏠]
    B -- "x 0.50" --> D[DA 💲]
    B -- "x 0.12" --> E[PF 🛡️]
    C --> F[Final Calculation]
    D --> F
    E --> F
    F --> G[INSERT INTO TABLE ✅]

💻 Implementation: The Payroll Lab

-- 🛒 Scenario: Hiring a new developer
-- 🚀 Action: Auto-calculating HRA, DA, and PF

DECLARE
    v_emp_id   NUMBER := &ID;
    v_name     VARCHAR2(50) := '&Name';
    v_basic    NUMBER := &Basic_Salary;
    v_hra      NUMBER;
    v_da       NUMBER;
    v_pf       NUMBER;
    v_net      NUMBER;
BEGIN
    -- ⚙️ 1. Logic: Perform the math
    v_hra := v_basic * 0.15; -- 15%
    v_da  := v_basic * 0.50; -- 50%
    v_pf  := v_basic * 0.12; -- 12%
    v_net := (v_basic + v_hra + v_da) - v_pf;

    -- 📥 2. Database Action: Insert everything
    INSERT INTO employee_payroll (emp_id, emp_name, basic, hra, da, pf, net_pay)
    VALUES (v_emp_id, v_name, v_basic, v_hra, v_da, v_pf, v_net);

    DBMS_OUTPUT.PUT_LINE('Success! Net Pay for ' || v_name || ' is ₹' || v_net);
    COMMIT;
END;
/

📊 Sample Dry Run

Input: Basic = 10,000

Component Math Value Status
Basic Input 10,000 📦
HRA 10,000 * 0.15 1,500 🏠
DA 10,000 * 0.50 5,000 💲
PF 10,000 * 0.12 1,200 🛡️
NET (10k + 1.5k + 5k) - 1.2k 15,300 ✅

📉 Complexity Analysis

  • Time Complexity: \(O(1)\) - Math and single insertion are constant time operations. ⏱️
  • Space Complexity: \(O(1)\) - Only uses a fixed set of local variables. 💾

🎯 Practice Problems

Easy Level 🟢

  • Add a "Special Bonus" of ₹500 to the Net Pay if the Basic Salary is less than ₹5,000.

Medium Level 🟡

  • Modify the block to print an error if the user enters a negative Basic Salary.

💡 Interview Tip 👔

"Interviewers often ask: 'Should calculations happen in the app or the database?' Answer: Business logic like tax is often safer in the database because it ensures all apps (Mobile, Web, Desktop) follow the exact same rules!"

📈 Learning Path