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¶

graph LR
    A[Table Record 📋] --> B[Get Price & Qty 📥]
    B --> C{Calculate Total ⚙️}
    C --> D[UPDATE Table Record ✅]
    D --> E[COMMIT Changes 🏁]

💻 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!"