Manipulating Data (DML) and Transaction Control practice questions

Worked examples for Manipulating Data (DML) and Transaction Control

1. Question 1

   In Oracle Database, a session runs the following, in order, with no other statements between them and no error from any of the INSERTs. AUDIT was empty to start and no other session is involved: ```sql INSERT INTO audit (id) VALUES (1); SAVEPOINT sp; INSERT INTO audit (id) VALUES (2); SAVEPOINT sp; INSERT INTO audit (id) VALUES (3); ROLLBACK TO sp; COMMIT; ``` Note that the savepoint name `sp` is used twice. After the COMMIT completes, which rows are persisted in AUDIT?

   1. A. Only row 1 persists; rows 2 and 3 are rolled back.

      This is the 'first savepoint wins' misconception: it assumes ROLLBACK TO sp returns to the original SAVEPOINT sp declared before row 2. But a reused savepoint name erases the earlier savepoint, so only the second definition (after row 2) survives; the rollback cannot reach a position that no longer exists.

   2. B. The second `SAVEPOINT sp` fails with an error because savepoint names must be unique within a transaction.

      This invents a prohibition that does not exist. Oracle explicitly permits reusing a savepoint identifier and defines the behaviour — the earlier savepoint is silently erased and the name is redefined — rather than raising an error, so all three INSERTs and the reused SAVEPOINT execute cleanly.

   3. C. Rows 1 and 2 persist; only row 3 is rolled back.Correct answer

      Reusing an existing savepoint identifier erases the earlier savepoint and relocates the name to the new position (immediately after row 2's insert). ROLLBACK TO sp therefore rewinds only to that later point, discarding row 3, while rows 1 and 2 remain in the still-open transaction and are made permanent by the COMMIT. The reference states that creating a second savepoint with the same identifier as an earlier one erases the earlier savepoint.

   4. D. All three rows persist, because the later COMMIT re-applies the work that ROLLBACK TO sp had set aside.

      This misreads ROLLBACK TO SAVEPOINT as a reversible marker that a COMMIT can undo. Rolling back to a savepoint permanently discards the changes made after it (row 3 here); a subsequent COMMIT persists only the work that still survives in the transaction, it cannot restore rolled-back changes.

   Explanation

   Reusing a savepoint identifier neither raises an error nor creates two distinct savepoints: the later declaration erases the earlier one, so the name refers only to its most recent position. A rollback to that name therefore discards just the work performed after the second declaration, leaving everything before it intact within the still-open transaction, and the following COMMIT makes that surviving work durable. The overlooked rule is that the original savepoint no longer exists once the name is reused — assuming it does, or assuming a duplicate name is illegal, flips the answer.

2. Question 2

   The tables hold the rows below. How many rows does the following statement modify? **products:** | product_id | name | unit_price | |------------|-----------|------------| | 1 | Gadget | 25.00 | | 2 | Gizmo | 40.00 | | 3 | Doohickey | 12.50 | | 4 | Widget | 99.00 | **orders (product_id per row):** 1000→1, 1001→1, 1002→2, 1003→3, 1004→3 ```sql UPDATE orders SET quantity = quantity + 1 WHERE product_id IN (SELECT product_id FROM products WHERE unit_price < 30) ```

   1. A. 2

      2 counts the orders for only one qualifying product. The subquery returns TWO products under 30 (Gadget = 25.00 and Doohickey = 12.50, product_ids 1 and 3), and the orders for BOTH are updated: 1000, 1001 (product 1) plus 1003, 1004 (product 3) = 4 rows.

   2. B. 3

      This undercounts by one. Product 1 has two orders (1000, 1001) and product 3 has two orders (1003, 1004); both products cost under 30, so all four of those orders are updated, not three.

   3. C. 4Correct answer

      The subquery returns product_ids with unit_price < 30: Gadget (25.00, id 1) and Doohickey (12.50, id 3). Gizmo (40.00) and Widget (99.00) are excluded. Orders with product_id 1 (1000, 1001) and product_id 3 (1003, 1004) match the IN list, so 4 rows are updated.

   4. D. 5

      5 is the total number of orders. Order 1002 (product 2, Gizmo at 40.00) is NOT under 30, so it is excluded from the IN list; only the four orders for products 1 and 3 are updated.

   Explanation

   The subquery (SELECT product_id FROM products WHERE unit_price < 30) returns the product_ids of Gadget (25.00) and Doohickey (12.50) — that is, 1 and 3. The UPDATE then modifies every orders row whose product_id is in that list: orders 1000 and 1001 (product 1) and orders 1003 and 1004 (product 3), for 4 rows. Order 1002 belongs to Gizmo (40.00), which is excluded, so it is not updated.

3. Question 3

   The `orders` table currently holds 5 rows (order_ids 1000–1004); products 1, 2, and 3 exist, and no order has an order_id of 2000, 2001, or 2002. How many rows does the following statement insert? ```sql INSERT ALL INTO orders (order_id, product_id, quantity, order_date) VALUES (2000, 1, 1, DATE '2025-05-01') INTO orders (order_id, product_id, quantity, order_date) VALUES (2001, 2, 2, DATE '2025-05-01') INTO orders (order_id, product_id, quantity, order_date) VALUES (2002, 3, 3, DATE '2025-05-01') SELECT * FROM DUAL ```

   1. A. 3Correct answer

      An unconditional INSERT ALL inserts one row per INTO clause for each row returned by the driving SELECT. The SELECT * FROM DUAL returns exactly one row, and there are three INTO clauses, so 3 rows are inserted (one per INTO). All three carry valid, existing product_ids and non-null required columns.

   2. B. 1

      This counts the single driving row from SELECT * FROM DUAL. INSERT ALL multiplies that by the number of INTO clauses: one driving row × three INTO clauses = 3 inserted rows, not 1.

   3. C. 0

      Zero rows would be inserted only if the driving SELECT returned no rows. SELECT * FROM DUAL always returns exactly one row, so each INTO clause fires once and 3 rows are inserted.

   4. D. 6

      This doubles the count, perhaps assuming each INTO both evaluates and re-inserts. Each INTO clause inserts exactly one row per driving row; with one driving row and three INTO clauses the total is 3, not 6.

   Explanation

   An unconditional INSERT ALL executes every INTO clause once for each row produced by the driving subquery. Here the driving query SELECT * FROM DUAL yields a single row, and three INTO clauses target the orders table, so the statement inserts 3 rows (order_ids 2000, 2001, 2002). The number of inserted rows is (driving rows) × (INTO clauses that fire), which is 1 × 3.

4. Question 4

   The `products` table has `product_id` as its PRIMARY KEY and currently holds these rows: | product_id | name | |------------|-----------| | 1 | Gadget | | 2 | Gizmo | | 3 | Doohickey | | 4 | Widget | What happens when the following statement is executed? ```sql INSERT INTO products (product_id, name, unit_price) VALUES (3, 'Sprocket', 8.00) ```

   1. A. ORA-02291: integrity constraint violated - parent key not found

      ORA-02291 fires when a child row references a foreign-key parent that does not exist. This INSERT targets products itself with an otherwise valid row and involves no foreign key, so ORA-02291 does not apply.

   2. B. ORA-00001: unique constraint violatedCorrect answer

      product_id is the PRIMARY KEY and value 3 already exists (Doohickey). The duplicate key value violates the unique constraint backing the primary key, so Oracle raises ORA-00001 and inserts nothing.

   3. C. ORA-01400: cannot insert NULL

      ORA-01400 fires only when a NOT NULL column receives a NULL. All three columns (product_id, name, unit_price) are given non-null values here, so no NOT NULL constraint is violated.

   4. D. The statement succeeds and inserts one row

      Assumes a new product row is added, overlooking that product_id 3 already exists. The PRIMARY KEY forbids duplicate key values, so the insert is rejected rather than applied.

   Explanation

   A PRIMARY KEY enforces uniqueness through an implicit unique index. Inserting a row whose primary-key value already exists in the table violates that constraint, and Oracle rejects the entire statement with ORA-00001 rather than storing a duplicate. The supplied values are otherwise valid (all non-null, in range), so no NOT NULL or foreign-key error applies.

5. Question 5

   A session runs the following, in order, with no other statements between them: ```sql INSERT INTO staging (id) VALUES (1); SAVEPOINT s1; INSERT INTO staging (id) VALUES (2); INSERT INTO staging (id) VALUES (3); ROLLBACK TO s1; COMMIT; ``` Assuming STAGING was empty to start and no other session is involved, which rows are persisted after the COMMIT?

   1. A. None — ROLLBACK TO s1 followed by COMMIT leaves the table empty.

      This is the 'ROLLBACK TO SAVEPOINT discards pre-savepoint work too' misconception. ROLLBACK TO s1 undoes only the changes made AFTER s1; the row 1 inserted before s1 remains pending and is then made permanent by COMMIT.

   2. B. Rows 1, 2, and 3 all persist — ROLLBACK TO s1 only marks a point and undoes nothing.

      This treats ROLLBACK TO SAVEPOINT as a no-op that merely re-marks the savepoint. In fact ROLLBACK TO s1 reverses everything done after s1, so rows 2 and 3 are undone before the COMMIT ever runs.

   3. C. Only row 1 persists.Correct answer

      ROLLBACK TO s1 undoes only the work done after the savepoint (rows 2 and 3), leaving row 1 — inserted before s1 — still pending in the open transaction; COMMIT then makes row 1 permanent. The reference states ROLLBACK TO SAVEPOINT rolls back only the portion of the transaction after the named savepoint.

   4. D. Only rows 2 and 3 persist — row 1 is rolled back because it precedes the savepoint.

      This inverts the semantics, assuming ROLLBACK TO undoes the work BEFORE the savepoint and keeps the work after it. The opposite is true: pre-savepoint work (row 1) is retained and post-savepoint work (rows 2, 3) is discarded.

   Explanation

   Rolling back to a savepoint reverses only the changes made after that savepoint was established; any work performed before it remains pending in the still-open transaction. A subsequent COMMIT then makes exactly that surviving pre-savepoint work permanent. The savepoint therefore acts as a partial undo boundary, not an all-or-nothing reset of the transaction.

6. Question 6

   The `orders` table currently contains the five rows shown below. | order_id | discount | |----------|----------| | 1000 | 0.10 | | 1001 | NULL | | 1002 | 0.05 | | 1003 | 0.20 | | 1004 | NULL | How many rows does the following statement modify? ```sql UPDATE orders SET discount = 0.10 WHERE discount IS NULL OR discount < 0.10 ```

   1. A. 5

      Assuming that NULL < 0.10 evaluates to TRUE and therefore IS NULL is redundant, causing all five rows to be updated. In Oracle three-valued logic, any relational operator applied to NULL yields UNKNOWN, not TRUE or FALSE, so the < 0.10 branch alone never matches NULL rows. IS NULL is required — and correct — to capture them, but it does not extend to rows where discount equals the threshold.

   2. B. 2

      Counting only the two NULL rows (order_id 1001 and 1004) while ignoring that order 1002, with discount 0.05, independently satisfies the second branch (0.05 < 0.10). The OR operator evaluates each branch separately for every row; a row needs to satisfy only one branch to be updated.

   3. C. 3Correct answer

      Three rows qualify: order 1001 and 1004 satisfy IS NULL; order 1002 satisfies 0.05 < 0.10. Order 1000 has discount = 0.10, which does not satisfy strict less-than (it is equal, not less). Order 1003 has discount 0.20, which satisfies neither branch. Total: three rows modified.

   4. D. 4

      Misreading the strict less-than operator as less-than-or-equal. Order 1000 has discount exactly 0.10; 0.10 < 0.10 is FALSE (strict inequality), so that row is excluded. Treating < as <= inflates the count by one.

   Explanation

   In Oracle three-valued logic, applying a relational operator such as < to a NULL operand yields UNKNOWN, which the WHERE clause treats as not-qualifying. Only the IS NULL predicate reliably selects NULL rows. The < operator is also strict: a row whose discount equals the threshold (0.10) satisfies neither IS NULL nor less-than, so it is excluded. Evaluating both branches of the OR against each of the five rows yields exactly three matches: the two rows with NULL discounts and the row with discount 0.05.

7. Question 7

   Session 1 runs `UPDATE products SET price = price * 1.1 WHERE category = 'X';` and does NOT commit or roll back. While that transaction is still open, Session 2 (a different session) queries and modifies the same category-'X' rows. Which TWO statements are true?

   1. A. A SELECT in Session 2 of those rows returns the new, increased prices.

      This is the 'other sessions see uncommitted changes' misconception. Oracle's read consistency hides Session 1's uncommitted change, so Session 2's query returns the pre-update prices, not the new ones.

   2. B. A SELECT in Session 2 of those rows returns the original, pre-update prices.Correct answer

      Uncommitted DML is invisible to other sessions; under read consistency Session 2 sees the last committed values, i.e. the pre-update prices, until Session 1 commits.

   3. C. An UPDATE in Session 2 of the same rows blocks until Session 1 commits or rolls back.Correct answer

      Session 1's uncommitted UPDATE holds row locks on those rows; a competing UPDATE in Session 2 must wait for those locks to be released, which happens only when Session 1 commits or rolls back.

   4. D. An UPDATE in Session 2 of the same rows fails immediately with a row-lock error.

      This is the 'concurrent write fails fast' misconception. A plain UPDATE against locked rows waits (blocks) rather than erroring out; only a non-waiting request such as SELECT ... FOR UPDATE NOWAIT would fail immediately.

   Explanation

   Changes made by an open, uncommitted transaction are invisible to other sessions, which continue to read the last committed values under Oracle's read consistency. The uncommitted transaction does, however, hold row locks, so another session attempting to modify the same rows waits until the first session commits or rolls back rather than seeing the new data or failing outright. Isolation thus governs both what other sessions read and how their writes are serialized.

8. Question 8

   The `orders` table contains 5 rows. How many rows does the following statement modify? ```sql UPDATE orders SET discount = (SELECT MAX(discount) FROM orders) ```

   1. A. 3

      This assumes only rows whose discount actually changes are counted. Oracle counts every row the UPDATE touches — all 5, since there is no WHERE clause — regardless of whether the new value equals the old one.

   2. B. 0

      Zero would apply only if a WHERE clause matched no rows. This UPDATE has no WHERE clause, so it targets every row in the table; the scalar subquery in SET supplies the value but does not filter which rows are updated.

   3. C. 5Correct answer

      An UPDATE with no WHERE clause modifies every row in the table — all 5. The scalar subquery (SELECT MAX(discount)...) is evaluated once against a read-consistent snapshot and assigns that single value to each row; even rows already holding that value count as modified.

   4. D. The statement raises an error because the subquery reads the same table being updated.

      A scalar subquery in the SET clause may read the table being updated; Oracle evaluates it against a read-consistent snapshot taken before the update, so no mutating-table error occurs for this SQL-level UPDATE. All 5 rows are modified.

   Explanation

   An UPDATE statement with no WHERE clause modifies every row in the table, so all 5 orders rows are updated. Oracle's rows-modified count includes every targeted row, even those whose value is unchanged by the assignment. The scalar subquery (SELECT MAX(discount) FROM orders) is legal against the same table and is evaluated once on a read-consistent snapshot (MAX = 0.20), which is then assigned to all rows.