ITEC 451 SQL Stored Procedures Assignment

ITEC 451 SQL Stored Procedures Assignment

Need Help Writing an Essay?

Tell us about your assignment and we will find the best writer for your paper.

Write My Essay For Me

The purpose of this assignment is to give students practice with writing stored procedures.  All stored procedures will be based on the database depicted above. Write the stored procedure for each question below and submit it to Blackboard as a word document.

• Create a stored procedure that lists all the information for all repairs that a customer has had over a given date range sorted by the VIN of the car.  The procedure should be named uspListRepairs and should include the customer first name, customer last name, and date range as input parameters.

CREATE PROCEDURE uspListRepairs @First nvarchar(30) = NULL, @Last nvarchar(30) = NULL, @Date(8) = NOT NULL

AS

SELECT *

FROM customer

WHERE First_Name LIKE ISNULL (@First, First_Name)

AND Last_Name LIKE ISNULL (@Last, Last_Name)

AND DateTime LIKE ISNULL (Date, DateTime) + ‘%’

ORDER BY VIN ASC;

GO

1. Create a stored procedure that calculates the total cost of repairs for both parts and labor on a given car owned by a given customer.  The stored procedure should be named uspCalculateTotalRepairCostByCar and should return a variable named @totalRepairCost. The stored procedure should take the customer first name, customer last name, and VIN number as input.
2. Create a stored procedure that adds a new repair to the database based on a car’s VIN number.  The stored procedure should be named uspAddCarRepair and include the repair code, VIN, Employee ID, and date/time as input variables. ITEC 451 SQL Stored Procedures Assignment

CREATE TABLE dbo.VIN_Info

(Repair_Code int NOT NULL,

VIN int NOT NULL,

Employee_ID int NOT NULL,

DateTime timestamp NOT NULL

CONSTRAINT PK_VIN_Info PRIMARY KEY CLUSTERED

(Repair_Code, VIN, Employee_ID, DateTime));

GO

IF EXISTS (SELECT*FROM sys.objects WHERE type = ‘TR’ AND name = ‘uspAddCarRepair’)

DROP TRIGGER uspAddCarRepair

GO

CREATE TRIGGER uspAddCarRepair on Car Repair

AFTER INSERT

AS

BEGIN

INSERT INTO VIN_Info

([Repair_Code], [VIN], [Employee_ID], [DateTime])

SELECT r.Repair_Code, v.VIN, e.Employee_ID, d.DateTime

FROM inserted AS i

INNER JOIN repair AS r ON i.Repair_code = r.Repair_code

INNER JOIN vin AS v ON i.VIN = v.VIN

INNER JOIN employee AS e ON i.Employee_ID = e.Employee_ID

END

GO

Extra Credit (10 points)

Create a trigger that updates the total parts and total labor cost of a repair in the Car Repair table when a new repair is added.  The stored procedure should be named uspUpdateCost and should include the repair code and VIN as input. Hint: The Repair table contains a set cost for each repair.  When a new repair is added, the cost for the repair can be obtained by selecting based on the repair code input. ITEC 451 SQL Stored Procedures Assignment

The post ITEC 451 SQL Stored Procedures Assignment appeared first on EssayPanthers.