15.4 Practical Exercises
In this section, we'll cover some exercises that will help you solidify your understanding of advanced SQL concepts.
Exercise 1: Working with Subqueries
1. Write a query that finds the names of all employees whose salary is above the average salary.
2. Write a query to find the customer with the highest total purchase amount. Use a subquery to first calculate the total purchase amount for each customer.
Exercise 2: Creating and Using Stored Procedures
Write a stored procedure to increase the salary of an employee by a certain percentage. The procedure should take the employee id and the percentage as parameters.
You can then call this procedure with specific parameters like this:
Exercise 3: Triggers
1. Write a trigger to track changes in the Employees table. The trigger should insert a new row into the EmployeeAudit table whenever an employee's salary is updated. The EmployeeAudit table has fields for employee_id, old_salary, new_salary...
