Excercises

$1.$ Write a script that includes two SQL statements coded as a transaction to delete the row with a student ID of $10$ from the Students table. To do this, you must first delete all courses for that student from the StudentCourses 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 Students

VALUES $(\text{'}$Smith$\text{'},$ 'John', GETDATE$(),$ NULL$)$;

SET @StudentID $=$ @@IDENTITY;

INSERT StudentCourses

VALUES $($@StudentID, @CourseID$)$;

$```$

Here, the @@IDENTITY variable is used to get the student ID value that's automatically generated when the first INSERT statement inserts a student.

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