Skip to content

Product Sales Update 🚀

Prerequisites: SQL UPDATE Statement, PL/SQL Basics

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

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