Data Insertion & Automated Calculations 🚀

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

---

💻 Implementation: The Payroll Lab

Oracle SQL

-- 🛒 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

• ← Back: Data Handling
• Next: Conditional Salary Deduction →

---