Examples and Notes by Week

↑ Up to Parent directory
View Create_GetCustomerLevel_proc.sql code.	Open/Save Create_GetCustomerLevel_proc.sql.
View Create_and_test_beforeDelete_trig.sql code.	Open/Save Create_and_test_beforeDelete_trig.sql.
View Create_before_employee_update_trig.sql code.	Open/Save Create_before_employee_update_trig.sql.
View Create_createEmailList_proc.sql code.	Open/Save Create_createEmailList_proc.sql.
View Create_employees_audit_table.sql code.	Open/Save Create_employees_audit_table.sql.
View Create_trigger1.sql code.	Open/Save Create_trigger1.sql.
View Create_trigger2.sql code.	Open/Save Create_trigger2.sql.
View Create_trigger3.sql code.	Open/Save Create_trigger3.sql.
View CustomerLevel_Func.sql code.	Open/Save CustomerLevel_Func.sql.
View Test_before_employee_update.sql code.	Open/Save Test_before_employee_update.sql.
View notes.txt code.	Open/Save notes.txt.
View outputEmailList.sql code.	Open/Save outputEmailList.sql.

The code for CustomerLevel_Func.sql is:
DROP FUNCTION IF EXISTS CustomerLevel; DELIMITER $$ CREATE FUNCTION CustomerLevel( credit DECIMAL(10,2) ) RETURNS VARCHAR(20) DETERMINISTIC BEGIN DECLARE customerLevel VARCHAR(20); IF credit > 50000 THEN SET customerLevel = 'PLATINUM'; ELSEIF (credit <= 50000 AND credit >= 10000) THEN SET customerLevel = 'GOLD'; ELSEIF credit < 10000 THEN SET customerLevel = 'SILVER'; END IF; -- return the customer level RETURN (customerLevel); END$$ DELIMITER ; SHOW FUNCTION STATUS WHERE db = 'classicmodels'; SELECT CustomerLevel(5000); SELECT CustomerLevel(15000); SELECT CustomerLevel(50001); SELECT customerName, CustomerLevel(creditLimit) FROM customers ORDER BY customerName;