Guided Exercises - Stored Procedures: Part-1 - MySQL - Practical Use Cases

[akash-coded]

Let's delve into some complex scenario-based exercises revolving around stored procedures in MySQL, particularly modeled on the classicmodels database. These exercises are intended to mimic real-world problems that you might face in a data engineering role.

Exercise 1: Implement Real-Time Inventory Management

Context:

Suppose you are working for a company that relies heavily on having an accurate inventory system. This system is crucial for sales, reporting, and stock replenishment. In the classicmodels database, you have the products and orderdetails tables. You've been assigned the task to create a real-time inventory management system.

Problem Statement:

1. Create a stored procedure that updates the product quantity in the products table every time an order is placed or modified. The procedure should also raise an alert if the product quantity falls below a certain threshold.
2. Implement a rollback mechanism in the stored procedure if any part of the transaction fails.

---

Exercise 2: Personalized Marketing Campaigns Based on Customer Purchasing Behavior

Context:

Marketing wants to roll out a personalized campaign for customers. To do this, they need intricate details like what categories of products a customer usually buys, their average spending, etc., all of which are available in the customers, orders, and orderdetails tables.

Problem Statement:

1. Create a stored procedure that will populate a new table CustomerBehavior. The table should contain columns for customerNumber, mostPurchasedCategory, and averageSpend.
2. The stored procedure should be efficient enough to update this table in real-time every time a new order is placed.

---

Exercise 3: Employee Performance Metrics for Quarterly Reviews

Context:

It's time for quarterly reviews, and the HR department needs to assess employee performance. They are interested in metrics like the total sales made by each employee, the number of new customers brought in, etc.

Problem Statement:

1. Create a stored procedure to calculate these metrics for each employee in the employees table.
2. The metrics should be stored in a new table, EmployeePerformance, which should be updated only at the end of every quarter.

---

Exercise 4: Archiving Old Data for Audit Compliance

Context:

Your company has a policy to archive orders that are more than two years old. These orders should not be deleted but moved to an archive table, and this action should be logged for audit compliance.

Problem Statement:

1. Create a stored procedure to move orders older than two years from the orders table to a new table ArchivedOrders.
2. Log each archiving action, including the timestamp and the number of records moved, into an AuditLog table.

---

Each of these exercises simulates real-world complexities and nuances you may face in a typical data engineering role. The solutions to these would require a deep understanding of stored procedures, transaction controls, and efficient database operations. They would challenge you to think not just about the SQL syntax but also about optimization, reliability, and compliance.

[akash-coded]

Let's tackle these exercises with detailed solutions. We'll also explore multiple approaches where applicable.

Exercise 1: Implement Real-Time Inventory Management

Approach 1: After Update Trigger

Rationale: Since the inventory needs to be updated real-time, triggers are a natural fit. An AFTER UPDATE trigger on the orderdetails table will make sure the inventory in the products table is updated accordingly.

SQL Code:

DELIMITER //
CREATE TRIGGER UpdateInventory
AFTER UPDATE ON orderdetails
FOR EACH ROW
BEGIN
    UPDATE products
    SET quantityInStock = quantityInStock - NEW.quantityOrdered + OLD.quantityOrdered
    WHERE productCode = NEW.productCode;
    
    DECLARE current_stock INT;
    SET current_stock = (SELECT quantityInStock FROM products WHERE productCode = NEW.productCode);
    
    IF current_stock < 10 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Inventory below threshold';
    END IF;
END;
//
DELIMITER ;

Approach 2: Stored Procedure with Transaction

Rationale: If more control over the transaction is desired, a stored procedure can be used. This will allow the inclusion of a rollback mechanism.

SQL Code:

DELIMITER //
CREATE PROCEDURE UpdateInventoryAndCheck (orderID INT)
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE cur CURSOR FOR SELECT productCode, quantityOrdered FROM orderdetails WHERE orderNumber = orderID;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO @productCode, @quantityOrdered;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        START TRANSACTION;
        UPDATE products SET quantityInStock = quantityInStock - @quantityOrdered WHERE productCode = @productCode;
        
        IF quantityInStock < 10 THEN
            ROLLBACK;
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Inventory below threshold, transaction rolled back';
            LEAVE read_loop;
        END IF;
        
        COMMIT;
    END LOOP;
    CLOSE cur;
END;
//
DELIMITER ;

Exercise 2: Personalized Marketing Campaigns Based on Customer Purchasing Behavior

Approach 1: Single Stored Procedure

Rationale: This method provides a centralized way to manage customer behavior data. Every time a new order is made, the stored procedure is called, updating the CustomerBehavior table.

SQL Code:

DELIMITER //
CREATE PROCEDURE UpdateCustomerBehavior(customerID INT)
BEGIN
    -- Logic to update CustomerBehavior table based on recent orders
END;
//
DELIMITER ;

Approach 2: Batch Update

Rationale: If real-time updates are not essential, you can run a batch update to populate the CustomerBehavior table. This can be scheduled to run during off-peak hours to minimize the load on the database.

In the batch update approach, you can create a stored procedure that compiles all the necessary metrics from various tables and updates the CustomerBehavior table in one go. This could be scheduled to run during off-peak hours to minimize system load.

Let's assume CustomerBehavior has the following columns: customerNumber, totalSpent, mostPurchasedProduct, leastPurchasedProduct.

Here is how you could implement this:

DELIMITER //
CREATE PROCEDURE BatchUpdateCustomerBehavior()
BEGIN
    -- Truncate the table to refill it with updated data
    TRUNCATE TABLE CustomerBehavior;
    
    -- Temporary variables
    DECLARE v_customerNumber INT;
    DECLARE v_totalSpent DECIMAL(10,2);
    DECLARE v_mostPurchasedProduct VARCHAR(50);
    DECLARE v_leastPurchasedProduct VARCHAR(50);
    
    -- Cursor and loop handling
    DECLARE done INT DEFAULT 0;
    DECLARE cur CURSOR FOR SELECT customerNumber FROM customers;
    
    -- Handlers
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO v_customerNumber;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- Calculate total amount spent by the customer
        SELECT SUM(amount) INTO v_totalSpent 
        FROM payments
        WHERE customerNumber = v_customerNumber;
        
        -- Find the most purchased product
        SELECT productCode INTO v_mostPurchasedProduct
        FROM orderdetails
        WHERE orderNumber IN (SELECT orderNumber FROM orders WHERE customerNumber = v_customerNumber)
        GROUP BY productCode
        ORDER BY SUM(quantityOrdered) DESC
        LIMIT 1;
        
        -- Find the least purchased product
        SELECT productCode INTO v_leastPurchasedProduct
        FROM orderdetails
        WHERE orderNumber IN (SELECT orderNumber FROM orders WHERE customerNumber = v_customerNumber)
        GROUP BY productCode
        ORDER BY SUM(quantityOrdered) ASC
        LIMIT 1;
        
        -- Insert into CustomerBehavior
        INSERT INTO CustomerBehavior(customerNumber, totalSpent, mostPurchasedProduct, leastPurchasedProduct)
        VALUES(v_customerNumber, v_totalSpent, v_mostPurchasedProduct, v_leastPurchasedProduct);
        
    END LOOP;
    CLOSE cur;
END;
//
DELIMITER ;

To run this stored procedure, you would simply execute CALL BatchUpdateCustomerBehavior();.

Here, the procedure first truncates the CustomerBehavior table to empty it (this may or may not be desirable based on your requirements). Then it populates it with fresh, aggregated data for each customer. You could schedule this procedure to run periodically depending on your needs.

Remember to modify this code according to the real structure of your tables and the metrics you are interested in.

These are just a couple of exercises to start. Each approach has its own pros and cons depending on the specific requirements and constraints of your database system. Triggers are great for real-time updates but can be trickier to manage and debug. Stored procedures provide more control and can be manually invoked or scheduled as needed.