Unguided Exercises - Stored Procedures - Usage of stored procedures in MySQL using the classicmodels database

[akash-coded]

Let's focus on formulating complex and well-explained exercises centered around the usage of stored procedures in MySQL using the classicmodels database.

---

Exercise 1: Bulk Update for Customer Status

Context and Problem Statement:

Suppose your organization classifies customers into different categories ("Platinum", "Gold", "Silver", "Bronze") based on their total purchases in a given year. As part of the yearly accounting tasks, you are required to update this status for all customers. Write a stored procedure that performs this bulk update based on the total sales per customer in the last year.

• "Platinum": Total Sales > $100,000
• "Gold": $50,000 < Total Sales <= $100,000
• "Silver": $20,000 < Total Sales <= $50,000
• "Bronze": Total Sales <= $20,000

Inputs and Outputs:

• The procedure should not require any input.
• The procedure should update the customerStatus column in a customers table, which already exists in your database.

---

Exercise 2: Archiving Old Orders and Corresponding Order Details

Context and Problem Statement:

Your organization's orders and orderdetails tables have grown significantly, affecting the database performance. Management has decided that orders older than two years should be moved to separate archive_orders and archive_orderdetails tables. Create a stored procedure to perform this archival task.

Inputs and Outputs:

• The procedure should not take any inputs.
• It should move rows from orders and orderdetails tables to archive_orders and archive_orderdetails tables based on the orderDate.

---

Exercise 3: Employee Performance Metrics

Context and Problem Statement:

The HR department wants a quarterly report of employee performance, specifically, the total sales made by each employee. The report should include the employee's name, quarter, and total sales. Create a stored procedure to generate this data.

Inputs and Outputs:

• The stored procedure should take the year as input.
• The procedure should generate a temporary table filled with the employee names, quarter, and their total sales for that quarter in the specified year.

---

Exercise 4: Inventory Reorder Alert

Context and Problem Statement:

The warehouse wants to know when to reorder products. A product should be reordered if the stock falls below a specific level and the product has been ordered in the last month. Create a stored procedure to identify such products.

Inputs and Outputs:

• The procedure should not take any inputs.
• It should generate a list with productCode, productName, and quantityInStock, and save this in a new temporary table named ReorderAlerts.

---

Exercise 5: Calculate Customer Lifetime Value (CLV)

Context and Problem Statement:

The marketing department is interested in understanding the Customer Lifetime Value. The formula they want to use is CLV = (Average Order Value * Purchase Frequency) / Churn Rate. Create a stored procedure to calculate CLV for each customer.

Inputs and Outputs:

• The procedure should not take any inputs.
• It should update the customerCLV column in the customers table.

---

These exercises are designed to simulate real-world scenarios that a data engineer might encounter. The problems are intentionally complex and designed to be solved through MySQL stored procedures.

---

---

Let's continue with more complex exercise scenarios involving stored procedures and the classicmodels database.

---

Exercise 6: Product Profitability Report

Context and Problem Statement:

The finance department wants a monthly report on the profitability of each product. Profitability is defined as (selling price - cost) * quantity sold. Create a stored procedure to generate this report.

Inputs and Outputs:

• The stored procedure should take the year and the month as inputs.
• It should output a temporary table with columns productCode, productName, month, year, and profitability.

---

Exercise 7: Dynamic Discounting System

Context and Problem Statement:

To boost sales, the sales department wishes to offer a discount system where loyal customers get better discounts. Create a stored procedure that updates the discount column in the customers table based on their total purchase amount for the last quarter.

• "Super Loyal": Total Purchase > $30,000: 20% discount
• "Loyal": $10,000 < Total Purchase <= $30,000: 10% discount
• "Occasional": Total Purchase <= $10,000: 5% discount

Inputs and Outputs:

• The procedure should not require any input.
• It should update the discount column in the customers table.

---

Exercise 8: Generating Monthly Financial Statements

Context and Problem Statement:

Your organization needs to generate a financial statement at the end of each month. Create a stored procedure that summarizes sales, costs, and profitability for each month.

Inputs and Outputs:

• The procedure should take the year as an input.
• It should generate a temporary table with columns year, month, totalSales, totalCosts, and profitability.

---

Exercise 9: Anomaly Detection in Sales Data

Context and Problem Statement:

Management is concerned about fraudulent activities. Create a stored procedure that flags customer orders which are significantly higher than their average order amount in the past year.

Inputs and Outputs:

• The stored procedure should not take any input.
• It should generate a temporary table with columns customerNumber, orderNumber, and totalAmount, flagging the orders that are significantly higher than the customer’s past average.

---

Exercise 10: Automated Data Cleansing

Context and Problem Statement:

You have observed that sometimes sales reps enter erroneous data, leading to a mismatch between the orders and orderdetails tables. Create a stored procedure to identify and flag such discrepancies.

Inputs and Outputs:

• The procedure should not require any input.
• It should flag the orders where the total amount in the orders table doesn’t match the sum of quantityOrdered * priceEach in the orderdetails table. Generate a temporary table with these flagged orders.

---

These exercises are built to be challenging and relevant to the kinds of problems you might face in a data engineering role. Each one simulates a distinct real-world scenario, and they are designed to be tackled using stored procedures in MySQL.

---

---

Certainly, let's explore some complex exercises that involve different types of stored procedure parameters (IN, OUT, INOUT) in MySQL while working with the classicmodels database.

---

Exercise 11: Tiered Sales Commissions

Context and Problem Statement:

The company wants to reward sales representatives with a tiered commission system based on their monthly sales. Create a stored procedure that calculates the commission for a given sales representative for a specific month and year.

Parameters:

• IN: salesRepEmployeeNumber, year, month
• OUT: commission

Commission Tiers:

• Sales > $100,000: 15% commission
• $50,000 < Sales <= $100,000: 10% commission
• Sales <= $50,000: 5% commission

---

Exercise 12: Update Customer Credit Limit

Context and Problem Statement:

Customer service representatives need an easy way to update a customer's credit limit and automatically notify the sales team. Create a stored procedure that updates a customer's credit limit and returns the new and old credit limits for confirmation.

Parameters:

• IN: customerNumber, newCreditLimit
• OUT: oldCreditLimit, updatedCreditLimit

---

Exercise 13: Employee Performance Review

Context and Problem Statement:

HR wants to run performance reviews on employees. Create a stored procedure that accepts an employee's number and updates their performance rating based on the number of sales made. It should return the old and new performance ratings.

Parameters:

• IN: employeeNumber
• INOUT: performanceRating

Performance Rating Scale:

• Excellent (Sales > 40)
• Good (30 < Sales <= 40)
• Average (20 < Sales <= 30)
• Below Average (Sales <= 20)

---

Exercise 14: Customer Lifetime Value

Context and Problem Statement:

Marketing wants to segment customers based on lifetime value. Create a stored procedure that calculates and updates the lifetime value of a customer. Return the old and new lifetime values.

Parameters:

• IN: customerNumber
• OUT: oldLifetimeValue, newLifetimeValue

---

Exercise 15: Calculate and Apply Discount

Context and Problem Statement:

The system needs to apply discounts at the end of the month based on total purchases. Create a stored procedure that accepts a customer number and a discount percentage. It should update all the relevant orders and return the total amount discounted.

Parameters:

• IN: customerNumber, discountPercentage
• OUT: totalDiscountedAmount

---

These exercises are designed to be realistic and challenging, each focusing on a different aspect of stored procedures and their parameter types in MySQL. The complexity is aimed at experienced data engineers who are expected to integrate such logic into broader data workflows.

---

---