MySQL Workbook - Day 3 Part 2: Advanced Administration &

Enterprise Features 🚀
Chapter 6: Transaction Error Handling & Isolation Levels 🛡️
User: What happens when something goes wrong in the middle of a transaction? And how do I handle
multiple users working with the database simultaneously?

Expert: Let's dive into advanced transaction handling! First, let's create a robust procedure with proper
error handling:

Transaction with Error Handling

sql
 DELIMITER //
CREATE PROCEDURE SafeOrderProcessing(
IN p_customer_id INT,
IN p_product_id INT,
IN p_quantity INT
)
BEGIN
DECLARE v_stock INT DEFAULT 0;
DECLARE v_price DECIMAL(10,2) DEFAULT 0;
DECLARE v_order_id INT DEFAULT 0;

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN
ROLLBACK;
RESIGNAL; -- Re-throw the error
END;

START TRANSACTION;

-- Check stock availability

SELECT stock_quantity, price INTO v_stock, v_price
FROM products WHERE product_id = p_product_id FOR UPDATE; -- Lock the row

IF v_stock < p_quantity THEN

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient stock';
END IF;

-- Create order
INSERT INTO orders (customer_id, total_amount)
VALUES (p_customer_id, v_price * p_quantity);
SET v_order_id = LAST_INSERT_ID();

-- Add order items

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (v_order_id, p_product_id, p_quantity, v_price);

-- Update stock
UPDATE products SET stock_quantity = stock_quantity - p_quantity
WHERE product_id = p_product_id;

COMMIT;
SELECT v_order_id as order_id, 'Success' as status;
END //
DELIMITER ;

Detailed Line Explanation:

 DECLARE EXIT HANDLER FOR SQLEXCEPTION : Automatic error handling

FOR UPDATE : Locks the row to prevent other transactions from modifying it

RESIGNAL : Re-throws the original error after rollback

If ANY operation fails, entire transaction is automatically rolled back

Isolation Levels

sql

-- View current isolation level

SELECT @@transaction_isolation;

-- Set isolation level for session

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Different isolation levels:

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- Fastest, least safe
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -- Good balance
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- MySQL default
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- Slowest, most safe

Line Explanation:

READ UNCOMMITTED : Can read other transaction's uncommitted changes (dirty reads)

READ COMMITTED : Only reads committed data

REPEATABLE READ : Same query returns same results within transaction

SERIALIZABLE : Full isolation, transactions run as if sequential

Savepoints for Partial Rollbacks

sql
 START TRANSACTION;

INSERT INTO customers (first_name, last_name, email)

VALUES ('Test', 'Customer', 'test@[Link]');

SAVEPOINT after_customer_insert;

INSERT INTO orders (customer_id, total_amount)

VALUES (LAST_INSERT_ID(), 100.00);

-- Oops, let's undo just the order, keep the customer

ROLLBACK TO after_customer_insert;

-- Add a different order instead

INSERT INTO orders (customer_id, total_amount)
VALUES (LAST_INSERT_ID(), 200.00);

COMMIT; -- Saves customer and second order

Line Explanation:

SAVEPOINT after_customer_insert : Creates checkpoint within transaction

ROLLBACK TO after_customer_insert : Rolls back to specific checkpoint

Allows partial rollbacks within larger transactions

Tip & Trick: Always use transactions for operations that modify multiple related tables. It's better to
be safe than to have inconsistent data!

Chapter 7: User Management and Security 🔐

User: TechShop is hiring more people, and I need to control who can access what parts of the database.
How do I manage users and permissions?

Expert: Database security is crucial! Let's set up proper user management and permissions:

Creating Database Users

sql
 -- Create different user types for TechShop
-- Read-only user for reporting
CREATE USER 'techshop_reports'@'localhost' IDENTIFIED BY 'SecurePassword123!';

-- Application user with limited permissions

CREATE USER 'techshop_app'@'%' IDENTIFIED BY 'AppPassword456!';

-- Admin user for maintenance

CREATE USER 'techshop_admin'@'localhost' IDENTIFIED BY 'AdminPassword789!';

-- View all users

SELECT User, Host FROM [Link] WHERE User LIKE 'techshop%';

Detailed Line Explanation:

'techshop_reports'@'localhost' : User can only connect from localhost

'techshop_app'@'%' : User can connect from any host (% is wildcard)

IDENTIFIED BY 'password' : Sets the user's password

Strong passwords are essential for database security!

Granting Permissions

sql
 -- Reports user - read-only access to specific tables
GRANT SELECT ON [Link] TO 'techshop_reports'@'localhost';
GRANT SELECT ON [Link] TO 'techshop_reports'@'localhost';
GRANT SELECT ON [Link] TO 'techshop_reports'@'localhost';
GRANT SELECT ON techshop.order_items TO 'techshop_reports'@'localhost';

-- Grant access to views for easy reporting

GRANT SELECT ON techshop.customer_order_summary TO 'techshop_reports'@'localhost';
GRANT SELECT ON techshop.product_sales_summary TO 'techshop_reports'@'localhost';

-- Application user - full access to data but not structure

GRANT SELECT, INSERT, UPDATE, DELETE ON [Link] TO 'techshop_app'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON [Link] TO 'techshop_app'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON [Link] TO 'techshop_app'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON techshop.order_items TO 'techshop_app'@'%';

-- Allow calling stored procedures

GRANT EXECUTE ON techshop.* TO 'techshop_app'@'%';

-- Admin user - full control (use carefully!)

GRANT ALL PRIVILEGES ON techshop.* TO 'techshop_admin'@'localhost';

-- Apply changes
FLUSH PRIVILEGES;

Line Explanation:

GRANT SELECT : Allows reading data

GRANT SELECT, INSERT, UPDATE, DELETE : Standard data manipulation permissions

GRANT EXECUTE : Allows calling stored procedures and functions

GRANT ALL PRIVILEGES : Full control (dangerous - use sparingly!)

FLUSH PRIVILEGES : Reloads permission tables

Role-Based Security (MySQL 8.0+)

sql
 -- Create roles for different job functions
CREATE ROLE 'sales_team', 'managers', 'read_only';

-- Configure role permissions

GRANT SELECT, INSERT, UPDATE ON [Link] TO 'sales_team';
GRANT SELECT, INSERT, UPDATE ON [Link] TO 'sales_team';
GRANT SELECT ON [Link] TO 'sales_team';

GRANT SELECT ON techshop.* TO 'read_only';

GRANT ALL PRIVILEGES ON techshop.* TO 'managers';

-- Assign roles to users

GRANT 'sales_team' TO 'techshop_app'@'%';
GRANT 'read_only' TO 'techshop_reports'@'localhost';
GRANT 'managers' TO 'techshop_admin'@'localhost';

-- Activate roles
SET DEFAULT ROLE ALL TO 'techshop_app'@'%';
SET DEFAULT ROLE ALL TO 'techshop_reports'@'localhost';

Line Explanation:

CREATE ROLE : Creates reusable permission sets

GRANT 'role_name' TO 'user'@'host' : Assigns role to user

SET DEFAULT ROLE : Automatically activates role when user connects

Security Best Practices

sql

-- View user permissions

SHOW GRANTS FOR 'techshop_app'@'%';

-- Remove permissions
REVOKE INSERT ON [Link] FROM 'techshop_app'@'%';

-- Change user password

ALTER USER 'techshop_app'@'%' IDENTIFIED BY 'NewSecurePassword123!';

-- Lock user account

ALTER USER 'techshop_old_employee'@'%' ACCOUNT LOCK;

-- Drop user
DROP USER 'techshop_old_employee'@'%';
User: What about protecting against SQL injection attacks?

Expert: While stored procedures help, the real protection comes from your application code using
prepared statements. But here are database-level protections:

sql

-- Use stored procedures to limit direct table access

-- Revoke direct table access from application users
REVOKE SELECT, INSERT, UPDATE, DELETE ON techshop.* FROM 'techshop_app'@'%';

-- Only allow procedure execution

GRANT EXECUTE ON techshop.* TO 'techshop_app'@'%';

-- Application can only call procedures, not run arbitrary SQL

Fun Fact: The principle of least privilege means giving users only the minimum permissions they need
to do their job. This dramatically reduces security risks!

Chapter 8: Events - Scheduled Database Tasks ⏰

User: I need to run maintenance tasks automatically, like cleaning old logs or updating statistics. How can
I schedule database jobs?

Expert: MySQL Events are perfect for scheduled tasks! Think of them as cron jobs that run inside the
database:

Enabling the Event Scheduler

sql

-- Check if event scheduler is running

SHOW VARIABLES LIKE 'event_scheduler';

-- Enable event scheduler

SET GLOBAL event_scheduler = ON;

Basic Event Creation

sql
 -- Clean old audit logs every day
DELIMITER //
CREATE EVENT daily_cleanup
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP
DO
BEGIN
-- Delete audit logs older than 90 days
DELETE FROM customer_audit_log
WHERE change_date < DATE_SUB(NOW(), INTERVAL 90 DAY);

-- Log the cleanup

INSERT INTO maintenance_log (task_name, run_date, records_affected)
VALUES ('daily_cleanup', NOW(), ROW_COUNT());
END //
DELIMITER ;

Detailed Line Explanation:

ON SCHEDULE EVERY 1 DAY : Runs once per day

STARTS CURRENT_TIMESTAMP : Begins immediately

DATE_SUB(NOW(), INTERVAL 90 DAY) : 90 days ago from now

ROW_COUNT() : Returns number of rows affected by last statement

Advanced Scheduling

sql
 -- Update product statistics weekly on Sunday at 2 AM
DELIMITER //
CREATE EVENT weekly_product_stats
ON SCHEDULE EVERY 1 WEEK
STARTS '2024-01-07 02:00:00' -- Next Sunday at 2 AM
DO
BEGIN
-- Create or update product performance stats
INSERT INTO product_weekly_stats (product_id, week_start, units_sold, revenue)
SELECT
p.product_id,
DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY) as week_start,
COALESCE(SUM([Link]), 0) as units_sold,
COALESCE(SUM([Link] * oi.unit_price), 0) as revenue
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
LEFT JOIN orders o ON oi.order_id = o.order_id
WHERE o.order_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
GROUP BY p.product_id
ON DUPLICATE KEY UPDATE
units_sold = VALUES(units_sold),
revenue = VALUES(revenue);
END //
DELIMITER ;

-- One-time event to archive old orders

CREATE EVENT archive_old_orders
ON SCHEDULE AT '2024-12-31 23:59:59'
DO
BEGIN
INSERT INTO orders_archive
SELECT * FROM orders
WHERE order_date < '2024-01-01';

DELETE FROM orders

WHERE order_date < '2024-01-01';
END;

Managing Events

sql
 -- View all events
SHOW EVENTS FROM techshop;

-- View event details

SELECT
EVENT_NAME,
EVENT_DEFINITION,
INTERVAL_VALUE,
INTERVAL_FIELD,
STATUS
FROM information_schema.EVENTS
WHERE EVENT_SCHEMA = 'techshop';

-- Disable an event
ALTER EVENT daily_cleanup DISABLE;

-- Enable an event
ALTER EVENT daily_cleanup ENABLE;

-- Drop an event
DROP EVENT IF EXISTS old_unused_event;

Chapter 9: Database Backup and Recovery 💾

User: What happens if our database crashes or gets corrupted? How do I protect TechShop's data?

Expert: Backup and recovery planning is essential! Let's set up a comprehensive backup strategy:

Logical Backups with mysqldump

bash

# Complete database backup

mysqldump -u root -p --single-transaction --routines --triggers techshop > techshop_backup.sql

# Backup specific tables

mysqldump -u root -p techshop customers orders products > partial_backup.sql

# Backup with compression

mysqldump -u root -p --single-transaction techshop | gzip > techshop_backup.[Link]

# Backup structure only (no data)

mysqldump -u root -p --no-data techshop > techshop_structure.sql
Automated Backup Script

bash

#!/bin/bash
# backup_techshop.sh

DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/backups/techshop"
DB_NAME="techshop"
DB_USER="backup_user"
DB_PASSWORD="backup_password"

# Create backup directory if it doesn't exist

mkdir -p $BACKUP_DIR

# Perform backup
mysqldump -u $DB_USER -p$DB_PASSWORD \
--single-transaction \
--routines \
--triggers \
--events \
$DB_NAME | gzip > $BACKUP_DIR/techshop_$[Link]

# Keep only last 30 days of backups

find $BACKUP_DIR -name "techshop_*.[Link]" -mtime +30 -delete

echo "Backup completed: techshop_$[Link]"

Recovery Operations

sql

-- Restore complete database

-- mysql -u root -p techshop < techshop_backup.sql

-- Point-in-time recovery using binary logs

-- SHOW BINARY LOGS;
-- SHOW BINLOG EVENTS IN 'mysql-bin.000001';

-- Restore to specific point

-- mysqlbinlog --stop-datetime='2024-01-01 12:00:00' mysql-bin.000001 | mysql -u root -p
Chapter 10: Performance Monitoring and Optimization 📊
User: How do I monitor the database performance and identify bottlenecks?

Expert: Let's set up comprehensive monitoring to keep TechShop's database running smoothly:

Performance Schema Analysis

sql

-- Enable performance schema (restart required)

-- SET GLOBAL performance_schema = ON;

-- Top 10 slowest queries

SELECT
DIGEST_TEXT,
COUNT_STAR as execution_count,
AVG_TIMER_WAIT/1000000000 as avg_time_seconds,
MAX_TIMER_WAIT/1000000000 as max_time_seconds
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

-- Table I/O statistics

SELECT
OBJECT_SCHEMA,
OBJECT_NAME,
COUNT_READ,
COUNT_WRITE,
SUM_TIMER_WAIT/1000000000 as total_time_seconds
FROM performance_schema.table_io_waits_summary_by_table
WHERE OBJECT_SCHEMA = 'techshop'
ORDER BY SUM_TIMER_WAIT DESC;

Index Usage Analysis

sql
 -- Find unused indexes
SELECT
t.TABLE_SCHEMA,
t.TABLE_NAME,
t.INDEX_NAME
FROM information_schema.STATISTICS t
LEFT JOIN performance_schema.table_io_waits_summary_by_index_usage p
ON t.TABLE_SCHEMA = p.OBJECT_SCHEMA
AND t.TABLE_NAME = p.OBJECT_NAME
AND t.INDEX_NAME = p.INDEX_NAME
WHERE t.TABLE_SCHEMA = 'techshop'
AND p.INDEX_NAME IS NULL
AND t.INDEX_NAME != 'PRIMARY';

-- Most used indexes

SELECT
OBJECT_SCHEMA,
OBJECT_NAME,
INDEX_NAME,
COUNT_FETCH,
COUNT_INSERT,
COUNT_UPDATE,
COUNT_DELETE
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'techshop'
ORDER BY COUNT_FETCH DESC;

System Status Monitoring

sql
 -- Key performance metrics
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Questions';
SHOW STATUS LIKE 'Uptime';
SHOW STATUS LIKE 'Slow_queries';

-- InnoDB specific metrics

SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests';
SHOW STATUS LIKE 'Innodb_buffer_pool_reads';

-- Calculate buffer pool hit ratio (should be > 95%)

SELECT
'Buffer Pool Hit Ratio' as metric,
(1 - (
(SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Innodb_buffer
(SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Innodb_buffer
)) * 100 as percentage;

 

Chapter 11: Capstone Project - Enterprise TechShop Dashboard 🏆

User: Let's put everything together! Can we create a comprehensive system that showcases all the
advanced features we've learned?

Expert: Absolutely! Let's build an enterprise-grade TechShop system with all the bells and whistles:

Complete System Setup

sql
 -- Create comprehensive audit system
CREATE TABLE system_audit_log (
log_id BIGINT AUTO_INCREMENT PRIMARY KEY,
table_name VARCHAR(50) NOT NULL,
operation VARCHAR(10) NOT NULL,
record_id INT,
old_values JSON,
new_values JSON,
user_name VARCHAR(50),
timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_audit_table_operation (table_name, operation),
INDEX idx_audit_timestamp (timestamp)
);

-- Advanced customer loyalty system

CREATE TABLE customer_loyalty (
customer_id INT PRIMARY KEY,
loyalty_level ENUM('Bronze', 'Silver', 'Gold', 'Platinum') DEFAULT 'Bronze',
points_earned INT DEFAULT 0,
points_used INT DEFAULT 0,
points_balance INT GENERATED ALWAYS AS (points_earned - points_used) STORED,
last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE
);

-- Product review system

CREATE TABLE product_reviews (
review_id INT AUTO_INCREMENT PRIMARY KEY,
product_id INT NOT NULL,
customer_id INT NOT NULL,
rating TINYINT CHECK (rating BETWEEN 1 AND 5),
review_text TEXT,
review_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
is_verified_purchase BOOLEAN DEFAULT FALSE,
FOREIGN KEY (product_id) REFERENCES products(product_id),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
UNIQUE KEY unique_customer_product_review (product_id, customer_id)
);

Advanced Business Logic Procedures

sql
-- Comprehensive order processing with loyalty points
DELIMITER //
CREATE PROCEDURE ProcessOrderWithLoyalty(
IN p_customer_id INT,
IN p_product_list JSON, -- [{"product_id": 1, "quantity": 2}, {...}]
IN p_use_points INT,
OUT p_order_id INT,
OUT p_final_amount DECIMAL(10,2),
OUT p_points_earned INT,
OUT p_message VARCHAR(500)
)
BEGIN
DECLARE v_order_total DECIMAL(10,2) DEFAULT 0;
DECLARE v_discount_amount DECIMAL(10,2) DEFAULT 0;
DECLARE v_available_points INT DEFAULT 0;
DECLARE v_loyalty_level VARCHAR(20);
DECLARE v_product_count INT;
DECLARE v_counter INT DEFAULT 0;
DECLARE v_current_product JSON;
DECLARE v_product_id INT;
DECLARE v_quantity INT;
DECLARE v_unit_price DECIMAL(10,2);
DECLARE v_stock_available INT;

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN
ROLLBACK;
SET p_message = 'Transaction failed due to error';
RESIGNAL;
END;

START TRANSACTION;

-- Validate customer and get loyalty info

SELECT COALESCE(points_balance, 0), COALESCE(loyalty_level, 'Bronze')
INTO v_available_points, v_loyalty_level
FROM customer_loyalty
WHERE customer_id = p_customer_id;

-- Create loyalty record if doesn't exist

IF v_loyalty_level IS NULL THEN
INSERT INTO customer_loyalty (customer_id) VALUES (p_customer_id);
SET v_available_points = 0;
SET v_loyalty_level = 'Bronze';
END IF;
-- Validate points usage
IF p_use_points > v_available_points THEN
SET p_message = CONCAT('Insufficient points. Available: ', v_available_points);
ROLLBACK;
LEAVE;
END IF;

-- Process each product in the order

SET v_product_count = JSON_LENGTH(p_product_list);

WHILE v_counter < v_product_count DO

SET v_current_product = JSON_EXTRACT(p_product_list, CONCAT('$[', v_counter, ']'));
SET v_product_id = JSON_UNQUOTE(JSON_EXTRACT(v_current_product, '$.product_id'));
SET v_quantity = JSON_UNQUOTE(JSON_EXTRACT(v_current_product, '$.quantity'));

-- Check stock and get price

SELECT stock_quantity, price INTO v_stock_available, v_unit_price
FROM products
WHERE product_id = v_product_id FOR UPDATE;

IF v_stock_available < v_quantity THEN

SET p_message = CONCAT('Insufficient stock for product ID ', v_product_id,
'. Available: ', v_stock_available, ', Requested: ', v_quantity);
ROLLBACK;
LEAVE;
END IF;

SET v_order_total = v_order_total + (v_quantity * v_unit_price);

SET v_counter = v_counter + 1;
END WHILE;

-- Apply loyalty discount

SET v_discount_amount = CalculateDiscount(p_customer_id, v_order_total);

-- Apply points discount (1 point = $0.01)

SET v_discount_amount = v_discount_amount + (p_use_points * 0.01);

SET p_final_amount = v_order_total - v_discount_amount;

-- Create order
INSERT INTO orders (customer_id, total_amount, discount_applied)
VALUES (p_customer_id, p_final_amount, v_discount_amount);
SET p_order_id = LAST_INSERT_ID();

-- Add order items and update stock

SET v_counter = 0;
WHILE v_counter < v_product_count DO
 SET v_current_product = JSON_EXTRACT(p_product_list, CONCAT('$[', v_counter, ']'));
SET v_product_id = JSON_UNQUOTE(JSON_EXTRACT(v_current_product, '$.product_id'));
SET v_quantity = JSON_UNQUOTE(JSON_EXTRACT(v_current_product, '$.quantity'));

SELECT price INTO v_unit_price FROM products WHERE product_id = v_product_id;

INSERT INTO order_items (order_id, product_id, quantity, unit_price)

VALUES (p_order_id, v_product_id, v_quantity, v_unit_price);

UPDATE products SET stock_quantity = stock_quantity - v_quantity

WHERE product_id = v_product_id;

SET v_counter = v_counter + 1;

END WHILE;

-- Calculate and award loyalty points (1% of order total)

SET p_points_earned = FLOOR(p_final_amount * 0.01);

-- Update loyalty points

UPDATE customer_loyalty
SET points_earned = points_earned + p_points_earned,
points_used = points_used + p_use_points
WHERE customer_id = p_customer_id;

-- Update loyalty level based on total spending

CALL UpdateCustomerLoyaltyLevel(p_customer_id);

SET p_message = 'Order processed successfully';

COMMIT;

END //
DELIMITER ;

Executive Dashboard Views

sql
-- Comprehensive executive dashboard
CREATE VIEW executive_dashboard AS
SELECT
'Total Revenue' as metric,
CONCAT('$', FORMAT(SUM(total_amount), 2)) as value,
'All Time' as period
FROM orders
UNION ALL
SELECT
'Monthly Revenue',
CONCAT('$', FORMAT(SUM(total_amount), 2)),
'Current Month'
FROM orders
WHERE YEAR(order_date) = YEAR(NOW()) AND MONTH(order_date) = MONTH(NOW())
UNION ALL
SELECT
'Active Customers',
FORMAT(COUNT(DISTINCT customer_id), 0),
'Last 90 Days'
FROM orders
WHERE order_date >= DATE_SUB(NOW(), INTERVAL 90 DAY)
UNION ALL
SELECT
'Average Order Value',
CONCAT('$', FORMAT(AVG(total_amount), 2)),
'Last 30 Days'
FROM orders
WHERE order_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)
UNION ALL
SELECT
'Top Product Category',
(SELECT [Link]
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY [Link]
ORDER BY SUM([Link] * oi.unit_price) DESC
LIMIT 1),
'By Revenue'
UNION ALL
SELECT
'Customer Satisfaction',
CONCAT(FORMAT(AVG(rating), 1), '/5.0'),
'Average Rating'
FROM product_reviews;
Advanced Analytics Functions

sql
-- Customer lifetime value prediction
DELIMITER //
CREATE FUNCTION PredictCustomerLTV(
p_customer_id INT,
p_months_to_predict INT
)
RETURNS DECIMAL(10,2)
READS SQL DATA
DETERMINISTIC
BEGIN
DECLARE v_avg_monthly_spending DECIMAL(10,2);
DECLARE v_months_active INT;
DECLARE v_predicted_ltv DECIMAL(10,2);

-- Calculate average monthly spending

SELECT
AVG(monthly_total),
COUNT(*)
INTO v_avg_monthly_spending, v_months_active
FROM (
SELECT
YEAR(order_date) as order_year,
MONTH(order_date) as order_month,
SUM(total_amount) as monthly_total
FROM orders
WHERE customer_id = p_customer_id
GROUP BY YEAR(order_date), MONTH(order_date)
) monthly_spending;

-- Simple prediction: average monthly * months to predict

SET v_predicted_ltv = COALESCE(v_avg_monthly_spending, 0) * p_months_to_predict;

-- Adjust based on loyalty level

SELECT
CASE loyalty_level
WHEN 'Platinum' THEN v_predicted_ltv * 1.5
WHEN 'Gold' THEN v_predicted_ltv * 1.3
WHEN 'Silver' THEN v_predicted_ltv * 1.1
ELSE v_predicted_ltv
END
INTO v_predicted_ltv
FROM customer_loyalty
WHERE customer_id = p_customer_id;

RETURN COALESCE(v_predicted_ltv, 0);

 END //
DELIMITER ;

Automated Maintenance Events

sql

-- Daily maintenance event

DELIMITER //
CREATE EVENT daily_techshop_maintenance
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 02:00:00'
DO
BEGIN
-- Clean old session data
DELETE FROM user_sessions WHERE last_activity < DATE_SUB(NOW(), INTERVAL 30 DAY);

-- Update product popularity scores

UPDATE products p
SET popularity_score = (
SELECT COALESCE(SUM([Link]), 0)
FROM order_items oi
JOIN orders o ON oi.order_id = o.order_id
WHERE oi.product_id = p.product_id
AND o.order_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)
);

-- Archive old audit logs

INSERT INTO audit_archive
SELECT * FROM system_audit_log
WHERE timestamp < DATE_SUB(NOW(), INTERVAL 1 YEAR);

DELETE FROM system_audit_log

WHERE timestamp < DATE_SUB(NOW(), INTERVAL 1 YEAR);

-- Log maintenance completion

INSERT INTO maintenance_log (task_name, completion_time, status)
VALUES ('daily_maintenance', NOW(), 'SUCCESS');

END //
DELIMITER ;

Day 3 Summary & Final Reflection 🎯

Expert: Congratulations! You've completed an incredible journey from MySQL beginner to enterprise
database expert! Let's reflect on what you've accomplished over these three days:
Your MySQL Mastery Journey ✅
Day 1 Foundations:

Database and table creation

Data types and constraints

Basic CRUD operations

Simple queries and functions

Understanding primary and foreign keys

Day 2 Intermediate Skills:

Complex JOINs across multiple tables

Subqueries and advanced filtering

Views for reusable queries

Advanced GROUP BY and aggregate functions
Set operations with UNION

Day 3 Enterprise Features:

Stored procedures for business logic

Triggers for automated actions

Performance optimization with indexes

Transaction management and ACID properties

User security and access control
Scheduled maintenance with events

Comprehensive monitoring and analytics

Real-World Impact 💼
You've built