Description
1. Create a procedure to increase 10% the salary of all subordinates in a department, do it as many times as necessary, until the average salary difference between managers and their subordinates in the department is smaller than 5%.
2. Create a new table to keep the count of the number of subordinates of an employee. Create a trigger to keep this table up to date. Remove from this table the data of the employee if fired. Consider all scenarios.
3. Create a new log table and a trigger to keep track of any changes to the employees table. The table schema should be (log_event_id, date, description) and the contents should look as e.g. (1234, 04/05/17, “Employee 123 updated salary from 5000 to 10000”). Track salaries, managers, departments, and jobs. Consider all scenarios.
4. Create a procedure protected with a transaction to steal a percentage of the salary of the employees and transfer the money to the president’s salary. If the president’s new salary is bigger than $120k undo the changes.
5.Create a table for projects (title, manager, duration (days), cost), and check that the cost must be < 1000 per day nor bigger than the sum of the salaries of the department employees the manager works for. Consider all scenarios.
