Exercises

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 customer from the Addresses table.

If these statements execute successfully, commit the changes. Otherwise, roll back the changes.

Write a script that creates and calls a stored procedure named test. This procedure should include

these statements coded as a transaction:

INSERT INTO orders VALUES

$($DEFAULT$,3,$ NOW$(),\text{'}10\mathrm{.}00\text{'},\text{'}0\mathrm{.}00\text{'},$ NULL$4$

'American Express', $\text{'}378282246310005\text{'},\text{'}04/2016\text{'},4)$;

SELECT LAST$\_$INSERT$\_$ID$()$

INTO order$\_$d;

INSERT INTO order items VALUES

$($DEFAULT$,$ order$\_,6,\text{'}415\mathrm{.}00\text{'},\text{'}161\mathrm{.}85\text{'},1)$;

INSERT INTO order items VALUES

$($DEFAULT$,$ order$\_,1,\text{'}699\mathrm{.}00\text{'},\text{'}209\mathrm{.}70\text{'},1)$;

Here, the LAST$\_$INSERT$\_$ID function is used to get the order ID value that's automatically generated

when the first INSERT statement inserts an order.

If these statements execute successfully, commit the changes. Otherwise, roll back the changes.

Write a script that creates and calls a stored procedure named test. This procedure should use a

transaction that includes the statements necessary to combine two customers. These statements should

do the following:

Select a row from the Customers table for the customer with a customer$\_$id value of $6.$ This

statement should lock the row so other transactions can't read or modify it until the transaction

commits, and it should fail immediately if the row is locked from another session.

Update the Orders table so any orders for the selected customer are assigned to the customer with

a customer$\_$id value of $3.$

Update the Addresses table so any addresses for the selected customer are assigned to the

customer with a customer$\_$id value of $3.$

Delete the selected customer from the Customers table.

If these statements execute successfully, commit the changes. Otherwise, rollback the changes.