Practical Exercises to Master MySQL Stored Procedures and Triggers

Stored Procedures and Triggers Exercises with solutions [20 exercises with solution]

1. Create a Stored Procedure to Insert a New Employee

Write a MySQL query to create a stored procedure that inserts a new employee into the Employees table.

Click me to see the solution

2. Call the Stored Procedure to Insert an Employee

Write a MySQL query to call the InsertEmployee stored procedure to add a new employee.

Click me to see the solution

3. Create a Stored Procedure to Update Employee Salary

Write a MySQL query to create a stored procedure that updates an employee's salary.

Click me to see the solution

4. Call the Stored Procedure to Update Salary

Write a MySQL query to call the UpdateEmployeeSalary stored procedure to update an employee's salary.

Click me to see the solution

5. Create a Stored Procedure to Delete an Employee

Write a MySQL query to create a stored procedure that deletes an employee from the Employees table.

Click me to see the solution

6. Call the Stored Procedure to Delete an Employee

Write a MySQL query to call the DeleteEmployee stored procedure to delete an employee.

Click me to see the solution

7. Create a Trigger to Log Salary Changes

Write a MySQL query to create a trigger that logs salary changes in the Employees table to a SalaryLog table.

Click me to see the solution

8. Create a Trigger to Prevent Invalid Salary Updates

Write a MySQL query to create a trigger that prevents updating an employee's salary to a negative value.

Click me to see the solution

9. Create a Stored Procedure to Calculate Average Salary

Write a MySQL query to create a stored procedure that calculates the average salary of employees in a department.

Click me to see the solution

10. Call the Stored Procedure to Calculate Average Salary

Write a MySQL query to call the CalculateAverageSalary stored procedure and retrieve the average salary.

Click me to see the solution

11. Create a Trigger to Update Last Modified Date

Write a MySQL query to create a trigger that updates the LastModified column in the Employees table whenever a row is updated.

Click me to see the solution

12. Create a Stored Procedure to Count Employees in a Department

Write a MySQL query to create a stored procedure that counts the number of employees in a specific department.

Click me to see the solution

13. Call the Stored Procedure to Count Employees

Write a MySQL query to call the CountEmployeesInDepartment stored procedure and retrieve the employee count.

Click me to see the solution

14. Create a Trigger to Prevent Deleting Active Employees

Write a MySQL query to create a trigger that prevents deleting employees who are marked as active.

Click me to see the solution

15. Stored Procedure to Calculate Total Salary by Department

Write a MySQL query to create a stored procedure that calculates the total salary for a specific department.

Click me to see the solution

16. Call the Stored Procedure to Calculate Total Salary

Write a MySQL query to call the CalculateTotalSalaryByDepartment stored procedure and retrieve the total salary.

Click me to see the solution

17. Create a Trigger to Enforce Maximum Salary

Write a MySQL query to create a trigger that prevents inserting or updating an employee's salary if it exceeds a maximum value.

Click me to see the solution

18. Create a Stored Procedure to Archive Old Employees

Write a MySQL query to create a stored procedure that moves old employees to an ArchivedEmployees table.

Click me to see the solution

19. Call the Stored Procedure to Archive Old Employees

Write a MySQL query to call the ArchiveOldEmployees stored procedure.

Click me to see the solution

20. Create a Trigger to Log Employee Deletions

Write a MySQL query to create a trigger that logs deleted employees to a DeletedEmployeesLog table.

Click me to see the solution

More to Come !

Do not submit any solution of the above exercises at here, if you want to contribute go to the appropriate exercise page.