Using the My Guitar Shop database you installed in Module 1, develop the following queries.
- 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.
- Code at least two CALL statements that test this procedure. (Note that this table doesn’t allow duplicate category names.) Execute the query and take a screenshot of the query and the results.
- 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. Execute the query and take a screenshot of the query and the results.
- Write a script that creates and calls a stored function named item_total that calculates the total amount of an item in the Order_Items table (discount price multiplied by quantity). To do that, this function should accept one parameter for the item ID, it should use the discount_price function that you created in exercise 2, and it should return the value of the total for that item. Execute the query and take a screenshot of the query and the results.
