How to use transactions and locking
Write a script that creates and calls a stored procedure named test. This procedure should include two SQL statements coded as a transaction to delete the row with a customer ID of 8 from the Customers table. To do this, you must first delete all addresses for that order from the Addresses table.
If these statements execute successfully, commit the changes. Otherwise, roll back the changes.
How to create stored procedures and functions
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 the following two CALL statements that test this procedure. (Note that this table doesn’t allow duplicate category names.)
— Test fail:
— Test success:
CALL insert_category(‘New Category’);
— Clean up:
DELETE FROM categories WHERE category_id = LAST_INSERT_ID();
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. Test with sql statement below:
SELECT item_id, item_price, discount_amount,