IN$-$Class Lab Assignment on MS SQL Advanced Stored Procedures

Individual Assignment $|2\mathrm{.}5\%$

Warning:

Objective:

To practice creating stored procedures with error handling and transactions for a retail store

management system. Students will create tables and write stored procedures to handle various

business transactions and generate reports.

Step $1$: Create Tables

Write SQL scripts to create the following tables:

$1.$ Customers table with columns: CustomerID, FirstName, LastName, Email, Phone

$2.$ Products table with columns: ProductID, ProductName, Price, Stock

$3.$ Orders table with columns: OrderID, CustomerID, OrderDate

$4.$ OrderDetails table with columns: OrderDetailID, OrderID, ProductID, Quantity, Price

$5.$ CustomerFeedback table with columns: FeedbackID, CustomerID, FeedbackDate,

Comments

Step $2$: Create Stored Procedures

$1.$ Add a New Customer

a$.$ Create a stored procedure AddCustomer that inserts a new customer into

the Customers table. Include error handling and transactions.

$2.$ Create a New Order

a$.$ Create a stored procedure CreateOrder that inserts a new order and its

details into the Orders and OrderDetails tables, respectively. It should also

update the product stock. Include error handling and transactions.

$3.$ Update Product Details

a$.$ Create a stored procedure UpdateProduct that updates the details of an

existing product. Include error handling and transactions.

$4.$ Generate Sales Report

a$.$ Create a stored procedure GenerateSalesReport that generates a sales

report for a given date range.

Submission Instructions:

$1.$ Complete each task and create the stored procedures as outlined.

$2.$ Test each stored procedure to ensure it works correctly and handles errors appropriately.

$3.$ Submit a single SQL script file containing all your stored procedures.

$4.$ Include a screenshot of the output for each stored procedure as evidence of

successful execution.

IN$-$Class Lab Assignment on MS SQL Advanced Stored Procedures

Individual Assignment $|2\mathrm{.}5\%$

Evaluation Criteria:

$$ Correctness of SQL syntax and logic

$$ Implementation of error handling and transactions

$$ Successful execution of stored procedures

$$ Clarity and organization of submitted SQL script