$1.$ Write a script that creates a stored procedure named spInsertCategory. The procedure should accept

one parameter for a category name, which it uses to add a new row to the Categories table.

In the same script, code two EXEC statements to test your newly created procedure. One should

succeed and one should fail. $($Hint: Note that the Categories table doesn$$t allow duplicate category

names.$)$

$2.$ Write a script that creates a function named fnDiscountPrice that calculates the discount price of an

item in the OrderItems table $($the discount amount subtracted from the item price$).$ To do that, this

function should accept one parameter for the item ID$,$ and it should return the value of the discount

price for that item.

In the same script, code a SELECT statement that returns the ItemID, ItemPrice, and

DiscountAmount columns, as well as the value returned by the fnDiscountPrice function for each

item.

$3.$ Write a script that creates a function named fnItemTotal that calculates the total amount of an item in

the OrderItems table $($discount price multiplied by quantity$).$ To do that, this function should accept

one parameter for the item ID$,$ use the fnDiscountPrice function that you created in exercise $2,$ and

return the value of the total for that item.

In the same script, code a SELECT statement that returns the ItemID, ItemPrice, and

DiscountAmount columns, the value returned by the fnDiscountPrice function for each item, the

quantity of each item, and the value returned by the fnItemTotal function.

$4.$ Write a script that creates a stored procedure named spInsertProduct that inserts a row into the

Products table. This stored procedure should accept a parameter for each of these columns:

CategoryID, ProductCode, ProductName, ListPrice, and DiscountPercent.

This stored procedure should set the Description column to an empty string, and it should set the

DateAdded column to the current date.

If the value for the ListPrice column is a negative number, the stored procedure should raise an error

that indicates that this column doesn$$t accept negative numbers. Similarly, the procedure should raise

an error if the value for the DiscountPercent column is a negative number.

Code two EXEC statements that test this procedure, one which succeeds and one which fails.

$5.$ Write a script that creates a stored procedure named spUpdateProductDiscount that updates the

DiscountPercent column in the Products table. This procedure should have input parameters for the

product ID and the discount percent.

If the value for the DiscountPercent column is a negative number, the stored procedure should raise

an error that indicates that the value for this column must be a positive number.

Code two EXEC statements that test this procedure, one which succeeds and one which fails.

$6.$ Create a trigger named Products$\_$UPDATE that checks the new value for the DiscountPercent column

of the Products table. This trigger should raise an error with an appropriate message if the discount

percent is greater than $100$ or less than $0.$

If the new discount percent is between $0$ and $1,$ this trigger should modify the new discount percent

by multiplying it by $100.$ That way, a discount percent of $\mathrm{.}2$ becomes $20.$

Test this trigger with an UPDATE statement that sets the discount percent to $\mathrm{.}25.$

$7.$ Create a trigger named Products$\_$INSERT that inserts the current date for the DateAdded column of

the Products table if the value for that column is null.

Test this trigger with an INSERT statement that does not include a value for the DateAdded column.

$8.$ Create a table named ProductsAudit. This table should have all columns of the Products table, except

the Description column. Also, it should have an AuditID column for its primary key, and the

DateAdded column should be changed to DateUpdated.

Create a trigger named Products$\_$UPDATE. This trigger should insert the old data about the product

into the ProductsAudit table after the row is updated and set the DateUpdated column to the current

date and time.

Test this trigger with an appropriate UPDATE statement.

$9.$ Re$-$run the script named CreateMyGuitarShop.sql to restore the database to its original state.