Question

1 List all project names and manager names in charge projects. 2 Calculate total hours to all projects based on each employee. 3 Suppose there are 408 work hours per year, create a view to display employee hourly rate. 4 Find out the total labor cost per employee per project. 5 Alter the table employees and add a column to store date of birth and hire date. Update the table with the provided data. 6 Create a new table named emr.droi overtime, which have three columns of Details of the hours worked by the employee on each project EMP PROJ_0 VERTIME empNo lempNo, proiNo) proiNg Unique id, format 9999 Unique id, format 9999 Number of overtime hours spent by the employee in the project hourot w 7 Assume each employee has a cap of 100 hours per project, develop a trigger to track overtime hours when employee exceed the cap for the project. 8 If the overtime pay is twice of regular hourly rate, modify the query from question 4 and add the factor of overtime. Employee Data empNo DOB Hire date 1000 04/30/1995 05/01/2010 1007 12/31/1986 01/02/2018 2002 07/12/1964 10/15/2014 1760 02/28/1976 06/15/2009 1740 09/10/1990 09/02/2011 2060 06/25/1980 04/01/2013 2000 05/05/1999 08/01/2019 1444 01/24/1993 07/15/2012

Need help with Questions 6-8 specifically number 8.

Here is what I've got for question 4 which needs to be modified to answer question 8.

This is what I got so far for questions 6-8

Please help me modify 8. Also do note that the first statement is 6 then the second is 7 and the last statement that is highlighted is question 8 in the following screencap:

Answer 1

6.

Create Table emp_proj_overtime(empNo number(6) NOT NULL, hourOt number(6), projNo number(6) NOT NULL, primary key(empNo,projNo), foreign key empNo References Emp(empNo), foreign key projNo References Proj(projNo));

7.

Select empNo,projNo, hourOt from emp_proj_overtime where hourOt > 100;

8.

Select emp.empNo,proj.projNo,sum(hourSpent*(salary/408) + hourOt*2*(salary/408)) as 'total labor cost' from emp,proj,emp_proj , emp_proj_overtime where emp.empNo = emp_proj.empNo and emp_proj.projNo = proj.projNo and proj.projNo = emp_proj_overtime.projNo and emp.empNo = emp_proj_overtime.empNo group by emp.empNo,proj.projNo;

Do ask if any doubt.