SQL Assignment
Create a file to record SQL queries, results and explanations on following questions. Upon the completion of lab works, convert the file to Assignment.sql and submit to blackboard.
These are the tables
create table EMP(empNo number(6) primary key,fname varchar(50),lname varchar(50),address varchar(50),
sex varchar(1),salary decimal(15,2), position varchar(50),deptNo number(4));
create table DEPT(deptNo number(6) primary key,deptName varchar(50),Mgr number(6));
create table PROJ(projNo number(6) primary key,projName varchar(50),deptNum number(6));
create table EMP_PROJ(empno number(6),projNo number(6) ,hourSpent number(6));
1. List all project names and manager names in charge projects.
2. Calculate total hours worked 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. The labor cost is hoursWorked per employee/project * employee hourly rate.
5. Alter the table employees and add column to store date of birth and hire date. Update the table with the provided data.
6. Create a new table named emp_proj_overtime which have three columns of of
|
EMP_PROJ_OVERTIME
|
|
(empNo, projNo)
|
Details of the hours worked by the employee on each project
|
|
|
empNo
|
|
Unique id, format 9999
|
|
|
projNo
|
|
Unique id, format 9999
|
|
|
hourOt
|
|
Number of overtime hours spent by the employee in the 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.
Format your assignment according to the following formatting requirements:
i) The answer should be typed, using Times New Roman font (size 12), double spaced, with one-inch margins on all sides.
ii) The response also includes a cover page containing the title of the assignment, the student's name, the course title, and the date. The cover page is not included in the required page length.
iii) Also include a reference page. The Citations and references must follow APA format. The reference page is not included in the required page length.