Product Sales Update 🚀

Mentor's Note: In a real shop, the "Total Amount" on a shelf doesn't update itself. You need a bit of logic to multiply the Price and Quantity. This lab shows you how to automate that update in your database! 💡

---

🌟 The Scenario: The Candy Shop 🍬

Imagine you are managing a candy shop inventory.

• The Data: You have Price per unit ($2) and Quantity Sold (5). 📦
• The Logic: The Total Sale column is currently empty. You need to calculate $2 \times 5 = 10$. ⚙️
• The Result: The database record is updated automatically with the correct total. ✅

---

📖 Concept Explanation

1. Derived Columns

A Derived Column is a column whose value comes from other columns. We usually don't store them, but sometimes we "materialize" them (save them) to make reporting faster. 🏃‍♂️💨

2. The Transaction Rule

Whenever you change data using UPDATE, you should either COMMIT (save permanently) or ROLLBACK (undo if there's a mistake).

---

🎨 Visual Logic: The Update Cycle

---

💻 Implementation: The Sales Lab

Oracle SQL

-- 🛒 Scenario: Closing the day at the candy shop
-- 🚀 Action: Updating the Total_Sale for a specific product

DECLARE
    v_prod_id  NUMBER := &ID;
    v_price    NUMBER;
    v_qty      NUMBER;
    v_total    NUMBER;
BEGIN
    -- 🔍 1. Fetch current values
    SELECT unit_price, quantity_sold 
    INTO v_price, v_qty
    FROM products
    WHERE product_id = v_prod_id;

    -- ⚙️ 2. Logic: Multiply
    v_total := v_price * v_qty;

    -- 🔄 3. Update the record
    UPDATE products
    SET total_sales = v_total
    WHERE product_id = v_prod_id;

    DBMS_OUTPUT.PUT_LINE('Product ' || v_prod_id || ' updated! Total: $' || v_total);
    COMMIT;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('❌ Error: Product ID not found.');
END;
/

---

📊 Sample Dry Run

Product ID: 501 | Price: 10 | Qty: 4

Step	Action	Logic	Result
1	Fetch Data	Get 10 and 4	v_price=10, v_qty=4 📦
2	Calculate	10 * 4	v_total=40 ⚙️
3	Update	SET total=40	Row 501 updated! ✅

---

📉 Complexity Analysis

• Time Complexity: $O(1)$ - Single row lookup and update. ⏱️
• Space Complexity: $O(1)$ - Constant memory usage. 💾

---

🎯 Practice Problems

Easy Level 🟢

• Modify the block to also apply a 5% Discount to the Total Sale if the quantity sold is greater than 100.

Medium Level 🟡

• Use a Cursor to update the total_sales for ALL products in the table at once.

---

💡 Interview Tip 👔

"Interviewers often ask: 'Why store total_sales if you can calculate it in a SELECT?' Answer: For huge tables, calculating every time is slow. Storing it (pre-calculation) makes reports run faster!"

---

📈 Learning Path

• ← Back: Patient Retrieval
• Next: Employee Project Management →

---