creating a sql for the guitar shop You will use SQL and My Guitar Shop database to create SQL statements.

Save your select statements as a script. Place the semicolon at the end of each SQL statement.

Please number your answers from $1$ to $4$ in your script and comment out each number. As well include your name and student number as a comment at the top of your script.

Name your script Comp$2138$Assignment$2\_$YourName.

Please submit your script file by uploading it through Brightspace.

$1.$ Write a script that creates and calls a stored procedure named insert$\_$category. First, code a statement that creates a procedure that adds a new row to the Categories table. To do that, this procedure should have one parameter for the category name. Include exception handling block.

Code at least two CALL statements that test this procedure. $($Note that this table doesn't allow duplicate category names.$)$

$2.$ Write a script that creates and calls a stored function named discount$\_$price that calculates the discount price of an item in the Order$\_$Items table $($discount amount subtracted from 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.

Code a SELECT statement that tests this function.

$3.$ Create a trigger named products$\_$before$\_$update that checks the new value for the discount$\_$percent column of the Products table. This trigger should raise an appropriate error 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 $2$ becomes $20.$

Test this trigger with an appropriate UPDATE statement.

$4.$ Create a trigger named products before insert that inserts the current date for the date added column $1.$ Write a script that creates and calls a stored procedure named insert$\_$category. First, code a statement that creates a procedure that adds a new row to the Categories table. To do that, this procedure should have one parameter for the category name. Include exception handling block.

Code at least two CALL statements that test this procedure. $($Note that this table doesn't allow duplicate category names.$)$

$2.$ Write a script that creates and calls a stored function named discount$\_$price that calculates the discount price of an item in the Order$\_$Items table $($discount amount subtracted from 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.

Code a SELECT statement that tests this function.

$3.$ Create a trigger named products$\_$before$\_$update that checks the new value for the discount$\_$percent column of the Products table. This trigger should raise an appropriate error 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 appropriate UPDATE statement.

$4.$ Create a trigger named products$\_$before$\_$insert that inserts the current date for the date$\_$added column of the Products table if the value for that column is null.

Test this trigger with an appropriate INSERT statement.