Assignment 3
Stored Procedures
Total Points: 30
Download payroll.sql from e-learning and create the database as per the
instruction.
Use payroll
select distinct ‘NetId’ ,group_number from timesheet
order by rand() limit 1;
screenshot:1
1.
Create a View for the group_number using the timesheet table. (2 points)
a. Name the views YOUR_ID_GROUP_VIEW
b. Select count (*) from YOUR_ID_GROUP_VIEW
c. What happens if a few records are deleted/inserted in the timesheet table for your
group, will it reflect in the new view created? Try a few scenarios to understand how
the view works.
2. Create a procedure to display all the employees belonging to the group_number you got in the
first screenshot. (4 Points)
3. Get the output of the below SQL ( 4 Points)
select distinct ‘NetId’ , emp_no from timesheet
where group_number = group_number
order by rand() limit 1;
Note down the employee number you received from the above SQL.
Create a function to display the employeeàfirst_name and last_name for the emp_no you
received. Repeat the above steps for a few emp_nos and see whether you correct result.
4. Create a Stored Procedure to Calculate the Salary for all records that exists for the
group_number ( 20 Points)
Stored Program Logic
1. Select all 20 employees for your group_number from timesheet table
2. Identify the hours worked
3. get the job_category for each employee from employees4able
3. Get the hourly rate from job_category table
4. Calculate the gross pay based on hourly rate , if hours more than 80, give 1.5
times of hourly rate
6. Identify all deductions.
•
ˆealth plan deduction 4ake the health plan id from the employee and
check the employee_health_plan for deduction
payroll standard deduction !ll the deductions must be done
retierment saving – percentage of salary saved by employees for each
pay period
7. Gross pay – all deductions = netpay
9. Store all the details in a payslip table
10. update the salary table with netpay for the period; whenever salary table is
updated trigger should fire and create the data for the salary_audit table.
All steps should be included in the stored program.
Finally, create a schedule to run your program every two weeks.
Expected Output:
•
•
first screenshot for the group_number randomly selected
Screenshot of outputs for each question.
SQL code for each question
Output from payslip and salary_audit tables.

Purchase answer to see full
attachment