Assignment $4$: Advanced MS SQL Stored Procedures

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

Objective:

To design and implement stored procedures with error handling and transactions for a retail store

management system. You ate required create tables and write stored procedures to handle various

business transactions and generate reports.

Step $1$

Create Below Tables $($Make sure to use logically use correct Data Types, PKs$,$ FKs$,$ other

required constrains based on Step $2)$

A$.$ Customer

a$.$ CustomerID

b$.$ FirstName

c$.$ LastName

d$.$ Email

e$.$ Phone

B$.$ Products

a$.$ ProductID

b$.$ ProductName

c$.$ Price

d$.$ Stock

C$.$ Orders

a$.$ OrderID

b$.$ CustomerID

c$.$ OrderDate

D$.$ OrderDetails

a$.$ OrderDetailID

b$.$ OrderID

c$.$ ProductID

d$.$ Quantity

e$.$ Price

Step $3$

Populate all the above tables with below dummy data

$--$ Insert Dummy Data into Customers Table

INSERT INTO Customers $($FirstName$,$ LastName, Email, Phone$)$

VALUES

$(\text{'}$John$\text{'},$ 'Doe', 'john.doe@example.com', $\text{'}555-0100\text{'}),$

$(\text{'}$Jane$\text{'},$ 'Smith', 'jane.smith@example.com', $\text{'}555-0101\text{'}),$

Assignment $4$: Advanced MS SQL Stored Procedures

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

$(\text{'}$Jim$\text{'},$ 'Beam', 'jim.beam@example.com', $\text{'}555-0102\text{'}),$

$(\text{'}$Jill$\text{'},$ 'Valentine', 'jill.valentine@example.com', $\text{'}555-0103\text{'})$;

$--$ Insert Dummy Data into Products Table

INSERT INTO Products $($ProductName$,$ Price, Stock$)$

VALUES

$(\text{'}$Laptop$\text{'},1200\mathrm{.}00,50),$

$(\text{'}$Smartphone$\text{'},800\mathrm{.}00,100),$

$(\text{'}$Tablet$\text{'},300\mathrm{.}00,70),$

$(\text{'}$Headphones$\text{'},100\mathrm{.}00,200)$;

$--$ Insert Dummy Data into Orders Table

INSERT INTO Orders $($CustomerID$,$ OrderDate$)$

VALUES

$(1,\text{'}2024-07-20\text{'}),$

$(2,\text{'}2024-07-21\text{'}),$

$(3,\text{'}2024-07-22\text{'}),$

$(4,\text{'}2024-07-23\text{'})$;

$--$ Insert Dummy Data into OrderDetails Table

INSERT INTO OrderDetails $($OrderID$,$ ProductID, Quantity, Price$)$

VALUES

$(1,1,1,1200\mathrm{.}00),$

$(1,2,2,800\mathrm{.}00),$

$(2,3,1,300\mathrm{.}00),$

$(3,4,3,100\mathrm{.}00),$

$(4,2,1,800\mathrm{.}00),$

$(4,4,2,100\mathrm{.}00)$;

Assignment $4$: Advanced MS SQL Stored Procedures

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

Step $3$

Create below $10$ Stored Procedures

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

Customers table. Include error handling and transactions.

$2.$ Create a stored procedure AddProduct that inserts a new product into the Products

table. Include error handling and transactions.

$3.$ 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. Create a stored procedure

UpdateCustomer that updates the details of an existing customer. Include error

handling and transactions.

$4.$ Create a stored procedure DeleteCustomer that deletes a customer from the

Customers table. Include error handling and transactions.

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

product. Include error handling and transactions.

$6.$ Create a stored procedure DeleteProduct that deletes a product from the Products

table. Include error handling and transactions.

$7.$ Create a stored procedure ProcessReturn that processes a return of items by

updating the OrderDetails and Products tables. Include error handling and

transactions.

$8.$ Create a stored procedure GenerateSalesReport that generates a sales report for a

given date range.

$9.$ Create a stored procedure TrackInventory that lists products with low stock $($less

than $10).$

$10.$ Create a stored procedure AddCustomerFeedback to insert feedback into the

CustomerFeedback table. Ensure the CustomerFeedback table is created first.

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.

Assignment $4$: Advanced MS SQL Stored Procedures

Individual Assignment $|7\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