exercise_04_database_functions.sql

-- Exercise 4: MySQL Database Functions with SQLAlchemy
-- Complete the following exercises using SQLAlchemy functions (not raw SQL)

-- ===================================================================
-- EXERCISE 4.1: DATE AND TIME FUNCTIONS
-- ===================================================================

-- Using SQLAlchemy, create Python code that:

-- 1. Get current date, time, and timestamp
-- Your SQLAlchemy code here:

-- 2. Extract year, month, day from order dates
-- Your SQLAlchemy code here:

-- 3. Calculate the age of each order in days
-- Your SQLAlchemy code here:

-- 4. Find orders from the last 30 days
-- Your SQLAlchemy code here:

-- 5. Format order dates in different ways (ISO, readable, day name)
-- Your SQLAlchemy code here:

-- 6. Add 30 days to order dates and show future dates
-- Your SQLAlchemy code here:

-- 7. Find orders by day of the week
-- Your SQLAlchemy code here:

-- 8. Calculate business days between order date and current date
-- Your SQLAlchemy code here:

-- ===================================================================
-- EXERCISE 4.2: STRING MANIPULATION FUNCTIONS
-- ===================================================================

-- Using SQLAlchemy, create Python code that:

-- 1. Concatenate first name and last name to create full names
-- Your SQLAlchemy code here:

-- 2. Extract domain names from email addresses
-- Your SQLAlchemy code here:

-- 3. Convert usernames to uppercase and lowercase
-- Your SQLAlchemy code here:

-- 4. Find the length of email addresses
-- Your SQLAlchemy code here:

-- 5. Extract first 5 characters of usernames
-- Your SQLAlchemy code here:

-- 6. Replace underscores with spaces in usernames
-- Your SQLAlchemy code here:

-- 7. Find the position of '@' symbol in email addresses
-- Your SQLAlchemy code here:

-- 8. Trim whitespace from user data
-- Your SQLAlchemy code here:

-- 9. Create formatted usernames (First Last)
-- Your SQLAlchemy code here:

-- 10. Generate display names using COALESCE
-- Your SQLAlchemy code here:

-- ===================================================================
-- EXERCISE 4.3: MATHEMATICAL FUNCTIONS
-- ===================================================================

-- Using SQLAlchemy, create Python code that:

-- 1. Round order amounts to 2 decimal places
-- Your SQLAlchemy code here:

-- 2. Calculate ceiling and floor values of order amounts
-- Your SQLAlchemy code here:

-- 3. Calculate absolute values of negative numbers
-- Your SQLAlchemy code here:

-- 4. Calculate square root of order amounts
-- Your SQLAlchemy code here:

-- 5. Calculate power of 2 for order amounts
-- Your SQLAlchemy code here:

-- 6. Calculate modulo 10 for order amounts
-- Your SQLAlchemy code here:

-- 7. Generate random numbers between 1 and 100
-- Your SQLAlchemy code here:

-- 8. Calculate percentage of total for each order
-- Your SQLAlchemy code here:

-- 9. Calculate running totals for orders
-- Your SQLAlchemy code here:

-- 10. Calculate moving averages for order amounts
-- Your SQLAlchemy code here:

-- ===================================================================
-- EXERCISE 4.4: CONDITIONAL FUNCTIONS
-- ===================================================================

-- Using SQLAlchemy, create Python code that:

-- 1. Categorize orders as 'High' or 'Low' value based on amount
-- Your SQLAlchemy code here:

-- 2. Use nested IF functions to create tier categories (Premium, High, Medium, Low)
-- Your SQLAlchemy code here:

-- 3. Handle NULL values in user names with IFNULL
-- Your SQLAlchemy code here:

-- 4. Use COALESCE to get first non-NULL value from multiple columns
-- Your SQLAlchemy code here:

-- 5. Create CASE statements for order status descriptions
-- Your SQLAlchemy code here:

-- 6. Use CASE with multiple conditions for complex categorization
-- Your SQLAlchemy code here:

-- 7. Create conditional calculations based on order status
-- Your SQLAlchemy code here:

-- 8. Use NULLIF to convert empty strings to NULL
-- Your SQLAlchemy code here:

-- 9. Create conditional formatting for display
-- Your SQLAlchemy code here:

-- 10. Use CASE for priority assignment based on multiple criteria
-- Your SQLAlchemy code here:

-- ===================================================================
-- EXERCISE 4.5: AGGREGATE FUNCTIONS
-- ===================================================================

-- Using SQLAlchemy, create Python code that:

-- 1. Calculate total count of orders
-- Your SQLAlchemy code here:

-- 2. Calculate sum, average, min, max of order amounts
-- Your SQLAlchemy code here:

-- 3. Count orders by status
-- Your SQLAlchemy code here:

-- 4. Calculate total amount by status
-- Your SQLAlchemy code here:

-- 5. Find average order amount by user
-- Your SQLAlchemy code here:

-- 6. Calculate percentage of orders by status
-- Your SQLAlchemy code here:

-- 7. Find top 5 users by total order amount
-- Your SQLAlchemy code here:

-- 8. Calculate running totals by date
-- Your SQLAlchemy code here:

-- 9. Find orders above average amount
-- Your SQLAlchemy code here:

-- 10. Calculate cumulative percentages
-- Your SQLAlchemy code here:

-- ===================================================================
-- EXERCISE 4.6: CUSTOM FUNCTION CREATION
-- ===================================================================

-- Using SQLAlchemy, create Python code that:

-- 1. Create a custom function to format currency
-- Your SQLAlchemy code here:

-- 2. Create a custom function to calculate order age in weeks
-- Your SQLAlchemy code here:

-- 3. Create a custom function to generate order summaries
-- Your SQLAlchemy code here:

-- 4. Create a custom function to categorize orders by multiple criteria
-- Your SQLAlchemy code here:

-- 5. Create a custom function to format user display names
-- Your SQLAlchemy code here:

-- 6. Create a custom function to calculate order priority scores
-- Your SQLAlchemy code here:

-- 7. Create a custom function to generate status descriptions
-- Your SQLAlchemy code here:

-- 8. Create a custom function to calculate business metrics
-- Your SQLAlchemy code here:

-- 9. Create a custom function to format dates in multiple ways
-- Your SQLAlchemy code here:

-- 10. Create a custom function to generate report headers
-- Your SQLAlchemy code here:

-- ===================================================================
-- EXERCISE 4.7: PERFORMANCE OPTIMIZATION
-- ===================================================================

-- Using SQLAlchemy, create Python code that:

-- 1. Optimize date range queries without functions in WHERE clause
-- Your SQLAlchemy code here:

-- 2. Use indexed columns efficiently with functions
-- Your SQLAlchemy code here:

-- 3. Cache frequently used function expressions
-- Your SQLAlchemy code here:

-- 4. Optimize aggregate queries with proper grouping
-- Your SQLAlchemy code here:

-- 5. Use functions in SELECT instead of WHERE when possible
-- Your SQLAlchemy code here:

-- 6. Optimize string function usage
-- Your SQLAlchemy code here:

-- 7. Use function aliases for reusability
-- Your SQLAlchemy code here:

-- 8. Optimize conditional function usage
-- Your SQLAlchemy code here:

-- 9. Use window functions efficiently
-- Your SQLAlchemy code here:

-- 10. Optimize complex function combinations
-- Your SQLAlchemy code here:

-- ===================================================================
-- EXERCISE 4.8: REAL-WORLD SCENARIOS
-- ===================================================================

-- Using SQLAlchemy, create Python code for:

-- 1. Generate monthly sales reports with formatted dates
-- Your SQLAlchemy code here:

-- 2. Create user activity summaries with time calculations
-- Your SQLAlchemy code here:

-- 3. Generate order status reports with conditional formatting
-- Your SQLAlchemy code here:

-- 4. Create financial summaries with currency formatting
-- Your SQLAlchemy code here:

-- 5. Generate customer profiles with formatted data
-- Your SQLAlchemy code here:

-- 6. Create inventory reports with calculated fields
-- Your SQLAlchemy code here:

-- 7. Generate performance metrics with percentages
-- Your SQLAlchemy code here:

-- 8. Create audit logs with formatted timestamps
-- Your SQLAlchemy code here:

-- 9. Generate dashboard data with aggregated metrics
-- Your SQLAlchemy code here:

-- 10. Create export-ready data with formatted fields
-- Your SQLAlchemy code here:

-- ===================================================================
-- EXERCISE 4.9: ERROR HANDLING AND VALIDATION
-- ===================================================================

-- Using SQLAlchemy, create Python code that:

-- 1. Handle division by zero errors in calculations
-- Your SQLAlchemy code here:

-- 2. Validate date ranges before calculations
-- Your SQLAlchemy code here:

-- 3. Handle NULL values in string concatenation
-- Your SQLAlchemy code here:

-- 4. Validate numeric values before mathematical operations
-- Your SQLAlchemy code here:

-- 5. Handle empty strings in string functions
-- Your SQLAlchemy code here:

-- 6. Validate email format before string manipulation
-- Your SQLAlchemy code here:

-- 7. Handle invalid date formats gracefully
-- Your SQLAlchemy code here:

-- 8. Validate user input before function application
-- Your SQLAlchemy code here:

-- 9. Handle overflow in mathematical calculations
-- Your SQLAlchemy code here:

-- 10. Create safe function wrappers with error handling
-- Your SQLAlchemy code here:

-- ===================================================================
-- EXERCISE 4.10: ADVANCED FUNCTION COMBINATIONS
-- ===================================================================

-- Using SQLAlchemy, create Python code that:

-- 1. Combine date and string functions for formatted reports
-- Your SQLAlchemy code here:

-- 2. Create complex conditional logic with multiple functions
-- Your SQLAlchemy code here:

-- 3. Combine mathematical and conditional functions
-- Your SQLAlchemy code here:

-- 4. Create nested function expressions
-- Your SQLAlchemy code here:

-- 5. Combine aggregate and conditional functions
-- Your SQLAlchemy code here:

-- 6. Create function chains for data transformation
-- Your SQLAlchemy code here:

-- 7. Combine window functions with other functions
-- Your SQLAlchemy code here:

-- 8. Create recursive function expressions
-- Your SQLAlchemy code here:

-- 9. Combine functions for data validation
-- Your SQLAlchemy code here:

-- 10. Create comprehensive data transformation pipelines
-- Your SQLAlchemy code here:

-- ===================================================================
-- BONUS EXERCISE: FUNCTION TESTING AND VALIDATION
-- ===================================================================

-- Using SQLAlchemy, create Python code that:

-- 1. Test all date functions with known values
-- Your SQLAlchemy code here:

-- 2. Validate string function results
-- Your SQLAlchemy code here:

-- 3. Test mathematical function accuracy
-- Your SQLAlchemy code here:

-- 4. Validate conditional function logic
-- Your SQLAlchemy code here:

-- 5. Test aggregate function correctness
-- Your SQLAlchemy code here:

-- 6. Create function performance benchmarks
-- Your SQLAlchemy code here:

-- 7. Test edge cases for all functions
-- Your SQLAlchemy code here:

-- 8. Validate function combinations
-- Your SQLAlchemy code here:

-- 9. Create comprehensive function test suite
-- Your SQLAlchemy code here:

-- 10. Document function usage and examples
-- Your SQLAlchemy code here: