PartA: Create a trigger that when work on record is added (namely, an employee works on a
Question:
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
Data Modeling and Database Design
ISBN: 978-1285085258
2nd edition
Authors: Narayan S. Umanath, Richard W. Scammel