SQL problem

CIS276DB Module 9 Assignment For this assignment you will use the PR database. Download the PR.zip folder from the Module, extract the database from the folder and attach it to the server.

The PR database is a “raw data” payroll database comprised of 6 tables with the following fields and schema:

The function of each table is as follows:

Benefits: this table contains insurance plan information and costs EmpData: this table contains employee information including whether they have a spouse (bit datatype- 1 equals yes), how many children (dependents) and the employees withholding allowance for payroll tax purposes. Work: this table contains current payrate information including start and end dates

for the payrate, and whether the raise was for cost of living (COLA- bit data type). Department: this table contains information for the different departments an employee is assigned. Hours: this table contains payperiod hours worked including ancillary pay (vacation, holiday, sick, etc.)

PayPeriod: this table contains individual payperiod information.

This is a typical layout for a payroll database- information regarding gross pay, net pay, tax withholding, insurance costs, etc. will be generated by creating and using views.

I highly suggest first setting up each view as a SELECT statement (sans the CREATE VIEW AS statement) and check the result sets against your SELECT for

accuracy.

After successfully creating the views in this assignment, please do not delete them as they will be used again in upcoming assignments. Create the base view for calculating payroll:

1. Create a view named vwPayroll that returns the following columns:

Column Table Notes

PerFrom PayPeriod

PerThru PayPeriod

PayID Hours

PPID PayPeriod You’ll need to qualify

EmpName Calculated Concatenation of FirstName and LastName

PayRate Work

OTRate Calculated Calculated column that determines time and a half pay rate

WorkHours Hours

HolHours Hours

SickHours Hours

VacHours Hours

PersHours Hours

OTHours Calculated Column that uses a conditional function to determine how many of the current WorkHours qualify as overtime (take into consideration if a work week has a holiday, then 32 hours should be limit- hint if there are HolHours present)

BaseCost Benefits

SpouseCost Benefits

DepCost Benefits

DentalCost Benefits

Spouse EmpData

Dependants EmpData

WHAllowance EmpData

SpouseIns Calculated Column that uses a conditional to determine spouse insurance cost; if an employee has a spouse then the SpouseIns column should contain SpouseCost value, otherwise 0

DepIns Calculated Column that uses a conditional to determine dependent insurance cost; if an employee has greater than 0 dependents then the DepIns column should contain Dependants x DepCost, otherwise 0

TaxRate Calculated Column that uses a nested conditional to

determine an employee’s tax rate based on the value in WHAllowance; if = 1 then .33, if = 2 then .25, if = 3 then .17, if =4 then .11 otherwise = .08

Create the view that calculates base pay values and insurance costs:

2. Create a view named vwPay that returns the following columns from vwPayroll:

Column Notes

PayID

PPID

EmpName

RegPay Calculated column that displays regular work hours multiplied by regular pay rate

OTPay Calculated column that displays OT hours multiplied by OT pay rate

HolPay Calculated column that displays holiday hours multiplied by

regular pay rate

SickPay Calculated column that displays sick hours multiplied by regular pay rate

VacPay Calculated column that displays vacation hours multiplied by regular pay rate

PersPay Calculated column that displays personal hours multiplied by regular pay rate

InsCost Calculated column that displays the total of all insurance costs

(hint- there are two additional columns besides SpouseIns and DepIns)

Create the view that calculates gross pay, total tax withholding and brings in the remaining columns needed to finish calculating weekly payroll:

3. Create a view named vwPayCalc that returns the following columns from vwPayroll and vwPay (hint- you will need to join them on a common field)

Column Notes

PerFrom

PerThru

PayID You’ll need to qualify

PPID You’ll need to qualify

EmpName You’ll need to qualify

GrossPay Calculated column that adds all of the pay columns from vwPay

TaxWithHolding Calculated column that displays the tax withholding for GrossPay (all pay columns multiplied by the calculated column created in vwPayroll)

InsCost

Finally, create the following SELECT statement that will calculate payroll for a specific week:

4. Write a SELECT statement that returns the following columns from vwPayCalc:

a. PerFrom

b. PerThru c. EmpName

d. Gross (GrossPay column formatted into decimal format) e. Taxes (TaxWithHolding column formatted into decimal format) f. InsCost g. NetPay (calculated column that displays pay after taxes and insurance

have been deducted, formatted into decimal format)

Using a subquery, filter the results so that the most recent pay period is displayed. Sort the results from largest NetPay to smallest.