Solved–Chapter 14B & Chapter 15 –Solution

Description

How to use transactions and locking

Exercises

1. 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.

Chapter 15

How to create stored procedures and functions

Exercises

2. 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:

CALL insert_category(‘Guitar’);

— Test success:

CALL insert_category(‘New Category’);

— Clean up:

DELETE FROM categories WHERE category_id = LAST_INSERT_ID();

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. Test with sql statement below:

SELECT item_id, item_price, discount_amount,

discount_price(item_id)

FROM order_items;