Multiple choice technology databases

Given below is the SQL statements executed in a user session: CREATE TABLE product (pcode NUMBER(2) ,pname VARCHAR2(10)); INSERT INTO product VALUES (1,'PEN'); INSERT INTO product VALUES (2,'PENCIL'); SAVEPOINT A; UPDATE product SET pcode=10 where pcode=1; SAVEPOINT B; DELETE FROM product where pcode=2; COMMIT; DELETE FROM product where pcode=10; ROLLBACK TO SAVEPOINT a; which statement describes the consequences?

  1. both the DELETE statements would be rolledback

  2. No SQL statements would be rolledback

  3. only the second DELETE statements would be rolledback

  4. both the DELETE statements and the update statements would be rolledback
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Once a COMMIT statement is executed, the transaction is finalized, making all previous changes permanent and destroying all active savepoints. The subsequent ROLLBACK TO SAVEPOINT A fails with an error, meaning no SQL statements are rolled back by it.

AI explanation

To answer this question, let's go through each statement and understand its consequences:

  1. CREATE TABLE product (pcode NUMBER(2), pname VARCHAR2(10));

    • This statement creates a table named "product" with two columns: "pcode" of type NUMBER(2) and "pname" of type VARCHAR2(10).
    • This statement does not have any consequences on the data in the table.

  2. INSERT INTO product VALUES (1,'PEN');

    • This statement inserts a row into the "product" table with values 1 and 'PEN' for the "pcode" and "pname" columns, respectively.
    • This statement adds a row to the "product" table.

  3. INSERT INTO product VALUES (2,'PENCIL');

    • This statement inserts another row into the "product" table with values 2 and 'PENCIL' for the "pcode" and "pname" columns, respectively.
    • This statement adds another row to the "product" table.

  4. SAVEPOINT A;

    • This statement creates a savepoint named "A" in the current transaction.
    • This savepoint allows us to rollback to this point later if needed.

  5. UPDATE product SET pcode=10 WHERE pcode=1;

    • This statement updates the "pcode" column of the "product" table, setting the value to 10 where the current value is 1.
    • This statement updates the value of one row in the "product" table.

  6. SAVEPOINT B;

    • This statement creates a savepoint named "B" in the current transaction.
    • This savepoint allows us to rollback to this point later if needed.

  7. DELETE FROM product WHERE pcode=2;

    • This statement deletes the row from the "product" table where the "pcode" column has a value of 2.
    • This statement removes one row from the "product" table.

  8. COMMIT;

    • This statement commits the current transaction, making all the changes permanent.
    • Once committed, the changes cannot be rolled back.

  9. DELETE FROM product WHERE pcode=10;

    • This statement attempts to delete the row from the "product" table where the "pcode" column has a value of 10.
    • However, since the previous update statement changed the value from 1 to 10, there is no row with a "pcode" value of 10.
    • As a result, this statement does not delete any rows from the "product" table.

  10. ROLLBACK TO SAVEPOINT A;

    • This statement rolls back the transaction to the savepoint named "A".
    • This means that all changes made after the savepoint (i.e., the update statement and the second delete statement) will be undone.

Based on the above analysis, the correct answer is: B) No SQL statements would be rolled back.

  • Since the COMMIT statement was executed before the ROLLBACK TO SAVEPOINT A statement, all the changes made before the COMMIT (i.e., the create table statement, insert statements, update statement, and the first delete statement) are permanent and cannot be rolled back.
  • The ROLLBACK TO SAVEPOINT A statement only rolls back the changes made after the savepoint, but since there were no changes made after the savepoint in this case, no SQL statements are rolled back.

Keep practicing — related questions