1. Create a table called Employee_Archives that has the same columns as employees table. It should also have a new column called Previous_Manager and a column that is called Update_Date

2. Create a Trigger on table Employees so that every time the employee manager is updated on table employees, the employee record is inserted in table Employee_Archives with the current date as Update_Date and the old value of Employeesupervisor column as Previous_Manager.

3. Now give an example of execution. Update the Employeesupervisor for one of the employees and see if the new entry is inserted correctly in Employee_Archives? What will happen if you update more than one records at the same time (using the same update commant) all records should appear in Employee_Archives?

Part 02

In this part we will learn how to use functions:

Create a functions that has the Salesperson_id as input and returns a number that represents the total cost of each orders for this salesperson. Notice that you will need to multiply the Order_Qty from table Orderline_T and the Productstandardprice from table Product_T to get the total cost of each order.

Now use the function: Write a select statement that selects all the saleperson and the total cost of the orders they have ever sold.

Part 03

Write a select statement that selects the second highest invoice_total.

Part 04

In this part we create a small procedure:

Write a procedure that has as input the customer_id. Then the procedure should delete the corresponding customer from table customers.

Notice that customer_id is a foreign key on table orders. Therefore the customer cannot be deleted if he has placed orders. To delete a record from customers you should first delete the corresponding orders and to do that you need to first delete the corresponding order_details.

Now execute the procedure to delete customer with id 1.

Part 05

Use the metadata tables for users to count how many tables and how many indexes you have created in your database.