Test 2 Hands-On-Part

.docx

School

Greenville Technical College *

*We aren’t endorsed by this school

Course

278

Subject

Industrial Engineering

Date

Apr 3, 2024

Type

docx

Pages

Uploaded by theunderminded

IST 278 Test 2 Hands-On Part Name:_____________________ Date: ____________ Test Instructions 1. Type your name and the date in the spaces provided. 2. Use the SQL Server Management Studio and the IST278EagleCorp database. 3. Complete the 3 exercises in this document. Make sure to scroll down. This document has several pages to it. 4. Upload and submit a completed copy of this test document before the due date. Exercise 1 (a table valued function exercise): Use the IST278EagleCorp13-1 database and create a table-valued function named fnEmployeeBirthDaysXX where the XX are your initials. This function requires one parameters of data type INT. Do not validate the input parameter. Have the function return a result set that consists of the LastName, FirstName, and BirthDate for each active employee (employee that has not been released) that has a birthdate in the month specified by the parameter value passed to the function. For example: If a 0 is passed to the function, the function is to return an empty result set. if a 1 is passed to the function, it is to return the LastName, FirstName, and BirthDate of each active employee born in January. if a 2 is passed to the function, it is to return the LastName, FirstName, and BirthDate of each active employee born in February. --- Paste below this line the code you wrote to create the fnEmployeeBirthDaysXX function Paste here

Test the Function you created for exercise one by coding and executing a select statement that invokes the function from within a SELECT statement to return the data for active employees born in July. --- Paste below this line the Select statement you wrote to test the fnEmployeeBirthDayXX function Paste here --- Paste below this line the run results from executing the select you wrote to test the fnEmployeeBirthDayXX function-- Paste here Exercise 2 (a Trigger exercise): 2a. Use the IST278EagleCorp database and create a new table named SalaryWageChangeTrackingXX where the XX are your initials. Create this table with the following columns: SalaryWageChangeNo INT NOT NULL IDENTITY PRIMARY KEY, EmployeeID VARCHAR(10), LastName VARCHAR(20),

FirstName VARCHAR(15), DateandTimeOfChange SMALLDATETIME, PriorSalaryWage DECIMAL(9,2), NewSalaryWage DECIMAL(9,2) --- Paste below this line the create table code you wrote and executed for exercise 2a. -- Paste here 2b. Use the IST278EagleCorp database and create a trigger named TRSalaryWageChangedXX where the XX are your initials. Code this trigger so that it automatically inserts a record into the SalaryWageChangeTrackingXX table every time an employee’s SalaryWage value is updated. The trigger is to populate the SalaryWageChangeTrackingXX table’s columns as follows: Attribute Value to assign to attribute EmployeeID EmployeeID associated with the updated Employee record LastName LastName associated with the updated Employee record FirstName FirstName associated with the updated Employee record DateandTimeOfChange Date and time that the update occurred PriorSalaryWage SalaryWage value the Employee had before the update NewSalaryWage SalaryWage value the Employee has after the update This trigger should fire every time an Employee record is updated, but it should only write a record to the SalaryWageChangeTrackingXX table when the update resulted in a change to the SalaryWage value.

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help