Suppose there are 408 work hours per year, create a view to display employee hourly rate.

EMP TABLE ?

EMP_PROJ ?

PROJ ?

DEPT ?

Transcribed Image Text:

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. 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. EMPNO 1000 Steven 1007 Diana 2002 FNAME 2060 Pat William 2000 Jennifer Fay 1760 Jonathan Taylor 1740 Ellen 1444 Peter LNAME Download CSV King 731 Fondren Houston, TX 638Voss Bellaire, TX 3321 Castle Spring, TX 561 Rice Houston, TX 890 Stone Houston, TX 450 Berry Bellaire, TX 980 Fire Oak Humble, TX Vargas 975 Dallas, Houston TX M Lorentz Abel Gietz ADDRESS Whalen SEX M F F LL. M TI M F LL SALARY 30000 Programmer 24000 15000 60000 65000 65000 28000 20000 POSITION Clerk Sales Representative Manager Manager Manager Clerk Sales Representative DEPTNO 60 20 80 20 60 80 60 80 EMPNO 1000 30 PROJNO 1000 2002 10 1444 1760 1760 50 2060 20 10 20 1740 50 40 HOURSPENT 32.5 7.5 40 20 Ln 5 10 15 12 PROJNO 10 20 30 40 50 PROJNAME Product X Product Y Computerization Productz Mobile Apps DEPTNUM 20 20 60 se 60 DEPTNO 20 60 80 DEPTNAME Marketing IT Sales MGR 1760 1740 2060 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. 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. EMPNO 1000 Steven 1007 Diana 2002 FNAME 2060 Pat William 2000 Jennifer Fay 1760 Jonathan Taylor 1740 Ellen 1444 Peter LNAME Download CSV King 731 Fondren Houston, TX 638Voss Bellaire, TX 3321 Castle Spring, TX 561 Rice Houston, TX 890 Stone Houston, TX 450 Berry Bellaire, TX 980 Fire Oak Humble, TX Vargas 975 Dallas, Houston TX M Lorentz Abel Gietz ADDRESS Whalen SEX M F F LL. M TI M F LL SALARY 30000 Programmer 24000 15000 60000 65000 65000 28000 20000 POSITION Clerk Sales Representative Manager Manager Manager Clerk Sales Representative DEPTNO 60 20 80 20 60 80 60 80 EMPNO 1000 30 PROJNO 1000 2002 10 1444 1760 1760 50 2060 20 10 20 1740 50 40 HOURSPENT 32.5 7.5 40 20 Ln 5 10 15 12 PROJNO 10 20 30 40 50 PROJNAME Product X Product Y Computerization Productz Mobile Apps DEPTNUM 20 20 60 se 60 DEPTNO 20 60 80 DEPTNAME Marketing IT Sales MGR 1760 1740 2060

Answer rating: 100% (QA)

CREATE TABLE DEPARTMENT Dname varchar25 not null Dnumber int not null Mgrssn char9 not null Mgrstartdate date primary key dnumber UNIQUE dname CREATE ... View the full answer