***SQL SERVER***

Use this for the database, https://www.dropbox.com/s/e4t28owml1u8z3i/PR.mdf?dl=0.

For each exercise, include a header that has your name, the date, and the module number and exercise number.

It should look like this: Joe Blow 3/25/13 Mod 10, #1

1. Write a script that declares 3 variables: TopRate (money), NewHire (smalldatetime) and TotEmps (int). Set the variables as follows: TopRate: Max PayRate from the appropriate table NewHire: Most recent HireDate from the appropriate table TotEmps: Count of current active employees Add 3 print statements that use the variables- use the convert function so that the values display properly. The print statements should output: The top payrate for the company is $(variable). The newest employee for the company started on (variable). There are currently (variable) employees working for the company.

2. (This script will determine active employees, their job titles and insurance plan name by inserting the records into a table variable) Write a script that declares a table variable named EmpTitleAndPlan that defines 3 columns; EmpName

(varchar(50)), EmpTitle (varchar(50)) and InsPlan (varchar(50)).

Using implicit join syntax, insert the following data into EmpTitleAndPlan:

Concatenation of FirstName and LastName Title PlanName

Filter the results so only rows are inserted for active employees. In the same script, select all rows from EmpTitleAndPlan.

3. (This script will determine employee overtime hours on a holiday week using 2 If/else conditionals) Write a script declares 5 int type variables; TotHours, TotEmps, HolHours, RegHours and OTHours. Set the variables as follows: TotHours: Sum of WorkHours where pay period ID is the last entered

(hint- create a subquery in the WHERE) TotEmps: Count of current active employees (see #1) HolHours: Sum of HolHours where pay period ID is the last entered (hint- create a subquery in the WHERE)

RegHours: TotEmps * 32 OTHours: Depends

Add conditional processing to the script. The first conditional will see if its indeed a holiday week (hint- check for a value in the HolHours variable) If the conditional is true (its a holiday week), we need to add another conditional to make sure we dont calculate negative ot hours:

The second conditional will see if RegHours (what should be the max total of regular hours during a holiday week) is greater than TotHours If the second condition is true, there are no OTHours. Set the OTHours to the appropriate value and RegHours to the

appropriate value Add 3 print statements that use the RegHours, HolHours and OTHours variables- use the convert function so the values display properly. The print statement should output: Total regular hours for the week: (variable). Total holiday hours for the week: (variable). Total OT Hours for the week: (variable).If the second condition is NOT true, there are OTHours. Set the OTHours variable equal to actual overtime hours for the week (TotHours) minus what should be the max of regular hours for the week (RegHours) Add 3 print statements that use the RegHours, HolHours and OTHours variables- use the convert function so the values display properly. The print statement should output: Total regular hours for the week: (variable). Total holiday hours for the week: (variable). Total OT Hours for the week: (variable).

If the first conditional is NOT true, add the print statement, This week had no holiday pay.

4. (This script will apply raises to employee pay rates using a looping mechanism with an if/else conditional used as a break) Write a script that adds 10 cents to the PayRate column for every employee while the average pay rate is still under $32 or until the maximum payrate is above $65. Test for the existence of a temporary table named TempWork. If it exists,

drop it.

Select everything from the Work table into TempWork where the EndDate column does not contain a value. Declare a money variable named AvgPayo Set the variable equal to the average for the PayRate column from the work table where the EndDate column does not contain a value. Create a repetitive processing loop that uses the following: o A test condition where the AvgPay variable is less than 32 o An update statement that adds 10 cents to the PayRate column of the

TempWork table o A conditional statement that breaks the loop if the maximum payrate in the TempWork table is greater than 65 Select all of the columns from the TempWork table and sort the results from largest payrate to smallest.

5. Write a script that uses a try-catch to insert a record into a temporary EmpData

table. Follow these steps:

Test for the existence of a temporary table named TempEmpData. If it exists, drop it. Select everything from the EmpData table into the TempEmpData table Using a try-catch, insert the following values into the TempEmpData table:

o William, Johnson, 10/8/2014, 1, 1, 3, NULL, NULL

Include the following message if the insert succeeds:

o Record was added.

Include the following message if the insert fails: o Failure: Record was not added.

o Error (*ERROR_NUMBER()): (ERROR_MESSAGE())

*Use the appropriate function to display the error number as a string