SQL 1. Write a script that includes two SQL statements coded as a transaction to

Question

SQL

1.     Write a script that includes 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.

2.     Write a script that includes these statements coded as a transaction:

INSERT Orders

VALUES (3, GETDATE(), '10.00', '0.00', NULL, 4,

'American Express', '378282246310005', '04/2013', 4);

SET @OrderID = @@IDENTITY;

INSERT OrderItems

VALUES (@OrderID, 6, '415.00', '161.85', 1);

INSERT OrderItems

VALUES (@OrderID, 1, '699.00', '209.70', 1);

Here, the @@IDENTITY variable 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.

Explanation / Answer

1).
EXEC SQL
BEGIN
   if(delete from Addresses where customer_id=8) COMMIT
   else ROLLBACK
   if(delete from Customers where customer_id=8) COMMIT
   else ROLLBACK
END

2).
EXEC SQL
BEGIN
   if(
   INSERT Orders VALUES (3, GETDATE(), '10.00', '0.00', NULL, 4, 'American Express', '378282246310005', '04/2013', 4);
   ) COMMIT
   else ROLLBACK
  
   SET @OrderID = @@IDENTITY;

   if(
   INSERT OrderItems VALUES (@OrderID, 6, '415.00', '161.85', 1);
   ) COMMIT
   else ROLLBACK
  
   if(
   INSERT OrderItems VALUES (@OrderID, 1, '699.00', '209.70', 1);
   ) COMMIT
   else ROLLBACK
END

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.