IST474 Update, View, and Trigger

The exercises below is using the database from Assignment 2.

Create a file to record SQL queries, results and explanations on following questions.

• List all project names and manager names in charge projects.
• Calculate total hours to all projects based on each employee.
• Suppose there are 408 work hours per year, create a view to display employee hourly rate.
• Find out the total labor cost per employee, per project.
• Alter the table employees and add column to store date of birth and hire date. Update the table with the provided data.
• Create a new table named emp_proj_overtime which have three columns of

• 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.
• If the overtime pay is twice of regular hourly rate, modify the query from question 4 and add the factor of overtime.

Employee Data