• PartA:

• Create a trigger that when work on record is added (namely, an employee works on a new project so a new row is INSERTED) the trigger will increase the salary of THIS employee who works on THIS project by 1% if this employees' salary is less than 85000. (for example, when you add a row (1, 100, 20) into work on the table (Using INSERT statement), assuming employee 100 exists, your trigger code needs to find out (use Where clause) if the employee's salary is less than 85000 if so, increase his salary. ( Hint: use After trigger, and use: new to refer to the work on the table after the change ) see the examples in "notes of the trigger and its samples" posted at Canvas.

  show me:

  (1).trigger code.

  (2) the table contents of employee and work on before triggering event (insert).

  (3) table contents after triggering the event. This way I can see if the trigger works or not. Also, show your INSERT statement (insert a row into work on the table to test your trigger).



• 1) USING ORACLE SQL AND THIS TABLE:

  Division (DID, dname, managerID)

  Employee (empID, name, salary, DID)

  Project (PID, pname, budget, DID)

  Workon (PID, EmpID, hours)

  
  

  Part B

• Formulate the following queries:

  List the total number of projects that 'engineering' division employees are working on

  List the name of the division that has MOST of the employees working on it. (hint: use group by and having with subquery strategy )

  List the name of the division that has more employees whose salary is above than company's average salary than any other division.

  Increase the salary of the division manager if he/she work on another division's project (hint: need a co-related condition )

  List the name of the division that has more than 3 employees who work on the project

  List the name of the division that has an employee who works on another division's project

  List names of employees who work on more projects than Larry

  List the name of Larry's divisional colleagues who work on fewer projects than Larry

  List the name of and salary of chefs manager

• 
  

  •