Manipulating Data (DML) and Transaction Control practice questions

Worked examples for Manipulating Data (DML) and Transaction Control

1. Question 1

   The `products` table currently holds these rows: ```text PRODUCT_ID NAME UNIT_PRICE ---------- ---------- ---------- 1 Gadget 25.00 2 Gizmo 40.00 3 Doohickey 12.50 4 Widget 99.00 ``` The `orders` table has no row whose `ORDER_ID` is 2000 or greater, and `ORDER_ID` is its primary key. How many rows does the following statement insert? ```sql INSERT FIRST WHEN unit_price >= 25 THEN INTO orders (order_id, product_id, quantity, order_date) VALUES (2000 + product_id, product_id, 1, DATE '2025-04-01') INTO orders (order_id, product_id, quantity, order_date) VALUES (3000 + product_id, product_id, 2, DATE '2025-04-01') WHEN unit_price >= 90 THEN INTO orders (order_id, product_id, quantity, order_date) VALUES (4000 + product_id, product_id, 3, DATE '2025-04-01') SELECT product_id, unit_price FROM products ```

   1. A. 6Correct answer

      For each source row Oracle evaluates the WHEN clauses top to bottom and executes only the INTO list of the first true condition. 25.00, 40.00 and 99.00 all satisfy `>= 25` first, and that clause holds two INTO clauses, so each contributes 2 rows; 12.50 satisfies no condition and, with no ELSE clause, is discarded — 3 x 2 + 0 = 6 rows inserted.

   2. B. 3

      Assumes INSERT FIRST produces at most one target row per source row. The unit of work is the INTO clause, not the WHEN clause: a single WHEN ... THEN may carry a list of INTO clauses and every one of them fires for a row that reaches it, so the three qualifying source rows yield two rows each.

   3. C. 5

      Routes the 99.00 row to the `>= 90` branch — it treats INSERT FIRST as choosing the most specific matching condition. FIRST means positionally first: 99.00 already satisfies `>= 25`, which is listed earlier, so the `>= 90` branch is never reached by any row.

   4. D. 7

      Applies INSERT ALL semantics, where every WHEN clause is evaluated independently, so 99.00 would fire both branches (2 + 1 rows) for a total of 7. INSERT FIRST explicitly skips all remaining WHEN clauses once one evaluates to TRUE.

   Explanation

   In a conditional multitable insert, `FIRST` makes the WHEN clauses mutually exclusive per source row: Oracle evaluates them in the order written and executes only the INTO list belonging to the first condition that is TRUE, skipping every later clause for that row. A single WHEN ... THEN may contain several INTO clauses, and each of them inserts its own row, so the total is counted per INTO clause rather than per source row. A source row for which no WHEN condition is TRUE is simply not inserted unless an ELSE clause supplies a target.

2. Question 2

   The `orders` table holds exactly five rows, whose `discount` values are: ```text | order_id | discount | |----------|----------| | 1000 | 0.10 | | 1001 | (null) | | 1002 | 0.05 | | 1003 | 0.20 | | 1004 | (null) | ``` The following statement is executed. How many rows does it update? ```sql UPDATE orders SET discount = 0.15 WHERE discount NOT IN (0.05, 0.10, 0.20) ```

   1. A. 2

      Incorrect. This assumes the two null-discount rows are 'not in' the list simply because null is not one of the listed literals. Comparing a null to any value yields UNKNOWN rather than TRUE, so those rows are skipped exactly like the non-matching ones.

   2. B. 0Correct answer

      Correct. The three rows that have a discount recorded (0.10, 0.05, 0.20) all match a value in the list, so `NOT IN` is FALSE for them. For the two rows whose discount is null, `discount <> 0.05 AND discount <> 0.10 AND discount <> 0.20` evaluates to UNKNOWN, not TRUE. UPDATE modifies only rows whose WHERE condition is TRUE, so no row qualifies and zero rows are updated.

   3. C. 3

      Incorrect. Three rows would be updated by `IN (0.05, 0.10, 0.20)`. Reading `NOT IN` as though it selected the listed values inverts the condition; the three rows carrying those discounts are precisely the ones the predicate excludes.

   4. D. 5

      Incorrect. An UPDATE with no WHERE clause would touch all five rows, but this WHERE clause is evaluated per row and never yields TRUE for any of them, so the statement is not equivalent to an unrestricted update.

   Explanation

   UPDATE applies its SET clause only to rows for which the WHERE condition evaluates to TRUE; rows evaluating to FALSE or UNKNOWN are left untouched. `x NOT IN (a, b, c)` expands to `x <> a AND x <> b AND x <> c`, so when x is null every conjunct is UNKNOWN and the whole condition is UNKNOWN — null rows can never be selected by NOT IN. Rows whose value does appear in the list evaluate to FALSE. With every row either FALSE or UNKNOWN, the statement succeeds but changes nothing; only `IS NULL` can target the null rows.

3. Question 3

   The `orders` table contains exactly the rows shown below at the start of the session, and no other session is modifying it: ``` ORDER_ID PRODUCT_ID -------- ---------- 1000 1 1001 1 1002 2 1003 3 1004 3 ``` The script below is executed in one session with autocommit disabled. How many rows does the final `DELETE FROM orders` statement remove? ```sql DELETE FROM orders WHERE product_id = 3; SAVEPOINT sp1; DELETE FROM orders WHERE product_id = 1; SAVEPOINT sp2; INSERT INTO orders (order_id, product_id, quantity, discount, order_date) VALUES (1005, 2, 5, 0.15, DATE '2025-03-10'); ROLLBACK TO SAVEPOINT sp1; DELETE FROM orders; ```

   1. A. 5

      Reads ROLLBACK TO SAVEPOINT as an unqualified ROLLBACK, assuming it undoes everything done since the transaction began and restores all five original rows. ROLLBACK TO SAVEPOINT rolls back only the work performed after the named savepoint, so the DELETE that ran before sp1 was established remains in effect.

   2. B. 1

      Assumes the rollback always returns to the most recently established savepoint (sp2), so only the INSERT is reversed and just order 1002 survives. The savepoint named in the statement determines the rollback point, not the latest one; naming sp1 also erases sp2.

   3. C. 3Correct answer

      ROLLBACK TO SAVEPOINT sp1 undoes exactly the work done after sp1 — the second DELETE and the INSERT — while leaving the first DELETE (rows 1003 and 1004) in effect and the transaction still open, so orders holds 1000, 1001 and 1002 and the final DELETE removes 3 rows (ROLLBACK ... TO SAVEPOINT, SQL Language Reference).

   4. D. 4

      Assumes a row inserted after a savepoint survives a rollback to it, i.e. that ROLLBACK TO SAVEPOINT reverses only modifications to pre-existing rows, so order 1005 remains. An INSERT is ordinary DML and is undone like any other change made after the savepoint.

   Explanation

   ROLLBACK TO SAVEPOINT is a partial rollback: it discards only the changes made after the named savepoint, keeps every change made before it, releases the locks acquired after it, and leaves the transaction open and uncommitted. Work performed before the savepoint was created therefore survives and still needs a COMMIT or a full ROLLBACK to be resolved. Naming an earlier savepoint also erases any savepoints established after it, so the rollback point is chosen by name rather than by recency.

4. Question 4

   The `orders` table holds exactly these five rows (`ORDER_ID` is the primary key, `PRODUCT_ID` references `PRODUCTS`, and `DISCOUNT` is the only nullable column): ```text ORDER_ID PRODUCT_ID QUANTITY DISCOUNT ORDER_DATE -------- ---------- -------- -------- ----------- 1000 1 4 0.10 05-JAN-2025 1001 1 2 (null) 11-JAN-2025 1002 2 1 0.05 02-FEB-2025 1003 3 10 0.20 14-FEB-2025 1004 3 3 (null) 01-MAR-2025 ``` The statement below is executed once. None of the generated `ORDER_ID` values collide with an existing key or with each other, so no constraint is violated. How many rows does the statement insert in total? ```sql INSERT ALL WHEN quantity >= 3 THEN INTO orders (order_id, product_id, quantity, discount, order_date) VALUES (order_id + 100, product_id, quantity, discount, order_date) WHEN discount IS NULL THEN INTO orders (order_id, product_id, quantity, discount, order_date) VALUES (order_id + 200, product_id, quantity, discount, order_date) SELECT order_id, product_id, quantity, discount, order_date FROM orders ```

   1. A. 4

      This is the count `INSERT FIRST` would produce: with FIRST, evaluation of a source row stops at the first `WHEN` whose condition is true, so order 1004 would be inserted only once instead of twice, giving 4. The statement uses ALL, which does not short-circuit.

   2. B. 3

      This counts only the rows matching the first `WHEN` condition (orders 1000, 1003 and 1004), as if the later `WHEN` clause were an alternative branch reached only when the first fails. Every `WHEN` clause in a multitable INSERT is a real insert target, not an ELSE branch.

   3. C. 10

      This assumes `INSERT ALL` inserts each source row into every `INTO` target unconditionally (5 rows × 2 targets). The `WHEN` conditions still filter each target independently; only an unconditional multitable INSERT, which has no `WHEN` clauses at all, behaves that way.

   4. D. 5Correct answer

      Correct. `INSERT ALL` evaluates every `WHEN` clause independently against every row returned by the subquery, and inserts once for each condition that is true. Order 1000 (quantity 4) and order 1003 (quantity 10) satisfy only the quantity test; order 1001 (discount NULL, quantity 2) satisfies only the discount test; order 1004 (quantity 3, discount NULL) satisfies BOTH and is therefore inserted twice; order 1002 (quantity 1, discount 0.05) satisfies neither. That is 2 + 1 + 2 + 0 = 5 inserted rows.

   Explanation

   A conditional multitable INSERT sends each row of the subquery through the `WHEN` clauses in order. With `ALL`, every condition is tested for every source row and an insert happens for each one that evaluates to TRUE, so a single source row can produce several inserted rows; with `FIRST`, evaluation stops at the first TRUE condition, so a source row produces at most one insert. Rows satisfying no condition are discarded (there is no ELSE clause here). The affected-row count of the whole statement is the total number of rows inserted across all `INTO` targets, not the number of rows in the subquery.

5. Question 5

   `orders.product_id` is declared NOT NULL and carries a foreign key to `products.product_id`, declared with no ON DELETE clause. The `orders` table currently holds these rows for product 3: ``` ORDER_ID PRODUCT_ID QUANTITY -------- ---------- -------- 1003 3 10 1004 3 3 ``` What is the result of executing the following statement? ```sql DELETE FROM products WHERE product_id = 3; ```

   1. A. ORA-02292 — integrity constraint violated - child record foundCorrect answer

      With the foreign key declared without ON DELETE CASCADE or ON DELETE SET NULL, the constraint is enforced with no compensating action, so deleting a parent row that still has dependent rows in orders raises ORA-02292 and the statement is rolled back.

   2. B. The statement succeeds and deletes one row; the referencing rows in orders have their product_id set to NULL.

      Assumes ON DELETE SET NULL is the default referential action. It is not a default — it must be declared on the constraint — and here product_id is NOT NULL, so nulling the child column would be impossible anyway.

   3. C. ORA-02291 — integrity constraint violated - parent key not found

      Confuses the two directions of a referential violation. ORA-02291 is raised on the child side, when an INSERT or UPDATE supplies a foreign key value with no matching parent row; deleting a parent that still has children is the opposite case.

   4. D. ORA-00001 — unique constraint violated

      Attributes the failure to the primary key on products because product_id is the key column. ORA-00001 signals a duplicate value being inserted or updated into a unique or primary key; a DELETE removes values and can never duplicate one.

   Explanation

   A foreign key with no ON DELETE clause enforces restricted delete semantics: a parent row cannot be removed while dependent rows still reference it. Automatic removal of the children requires ON DELETE CASCADE, and nulling their foreign key column requires ON DELETE SET NULL — neither is the default, and the latter is impossible against a NOT NULL child column. The failure is reported on the parent-delete side, which is a distinct error from the child-side violation raised when a foreign key value has no matching parent.

6. Question 6

   The ORDERS table contains exactly these rows (only the columns used below are shown): ``` ORDER_ID PRODUCT_ID QUANTITY DISCOUNT -------- ---------- -------- -------- 1000 1 4 0.10 1001 1 2 (null) 1002 2 1 0.05 1003 3 10 0.20 1004 3 3 (null) ``` The PRODUCTS table is empty of any row whose PRODUCT_ID is 10000 or greater, so no primary-key conflict can arise. How many rows in total does the following statement insert? ```sql INSERT ALL WHEN quantity >= 3 THEN INTO products (product_id, name, unit_price) VALUES (order_id + 10000, 'BULK-' || order_id, 1) WHEN discount IS NULL THEN INTO products (product_id, name, unit_price) VALUES (order_id + 20000, 'NODISC-' || order_id, 1) ELSE INTO products (product_id, name, unit_price) VALUES (order_id + 30000, 'PLAIN-' || order_id, 1) SELECT order_id, quantity, discount FROM orders ```

   1. A. 6Correct answer

      In a conditional INSERT ALL, Oracle evaluates every WHEN clause for every row returned by the subquery and executes each INTO whose condition is true. quantity >= 3 is true for 1000, 1003 and 1004 (3 rows); discount IS NULL is true for 1001 and 1004 (2 rows); only 1002 satisfies no WHEN, so ELSE fires once. 3 + 2 + 1 = 6 rows inserted, with order 1004 contributing two of them.

   2. B. 5

      Applies INSERT FIRST semantics to INSERT ALL — assuming each source row is routed to only the first branch whose condition is true, yielding one insert per source row. With ALL, every WHEN is evaluated independently, so order 1004 (quantity 3 and NULL discount) is inserted twice.

   3. C. 9

      Treats ELSE as firing for every row that fails at least one WHEN condition (orders 1000, 1001, 1002 and 1003), giving 3 + 2 + 4. ELSE executes only for rows for which no WHEN condition is true.

   4. D. 8

      Reads ELSE as the alternative to the immediately preceding WHEN only, so it fires for the three rows with a non-NULL discount (1000, 1002, 1003), giving 3 + 2 + 3. ELSE is the alternative to the entire set of WHEN clauses, not just the last one.

   Explanation

   A conditional INSERT ALL is not first-match-wins: each WHEN condition is tested independently against every row the subquery returns, and each INTO whose condition evaluates to TRUE performs its own insert, so one source row can produce several inserted rows. The ELSE clause is the alternative to all of the WHEN clauses together — it executes only for a row for which no WHEN condition is true. The statement's affected-row count is therefore the sum of the rows inserted by all branches, not the number of rows the subquery returned. Substituting INSERT FIRST would change the semantics so that each row stops at the first true WHEN, producing exactly one insert per source row.

7. Question 7

   `orders.product_id` is a NOT NULL column with a foreign key to `products.product_id`, and every existing order references an existing product. No `ORDER_ID` of 9000 or greater is currently in use, so no generated key collides. What happens when the following statement is executed? ```sql MERGE INTO orders o USING products p ON (o.product_id = p.product_id) WHEN MATCHED THEN UPDATE SET o.quantity = o.quantity + 1, o.product_id = p.product_id WHEN NOT MATCHED THEN INSERT (order_id, product_id, quantity, order_date) VALUES (9000 + p.product_id, p.product_id, 1, DATE '2025-05-01') ```

   1. A. ORA-30926: unable to get a stable set of rows in the source tables

      Assumes any MERGE over a one-to-many relationship is unstable. ORA-30926 is raised only when a single target row is matched by more than one source row; here `product_id` is the primary key of the source, so each order matches at most one product and the join is deterministic.

   2. B. ORA-01779: cannot modify a column which maps to a non key-preserved table

      Treats the MERGE join like an updatable join view that must be key-preserved. MERGE modifies a named table directly, not a view, so the key-preservation rule for join views does not apply here.

   3. C. ORA-38104: Columns referenced in the ON Clause cannot be updatedCorrect answer

      The update clause assigns to `o.product_id`, which appears in the ON condition. Oracle forbids updating any target column referenced in the ON clause, so the statement is rejected before any row is touched — the assignment being a no-op value change is irrelevant, the restriction is syntactic.

   4. D. The statement succeeds, updating 5 rows and inserting 1 row.

      Assumes Oracle inspects the assigned expression and ignores an assignment that cannot change the value. The ON-clause restriction is checked at parse time on the column list alone, so the statement never executes and no rows are merged.

   Explanation

   A MERGE may not assign a new value to any target column that appears in its ON condition, because the ON condition determines matching and an update to it would change the row's own matching status mid-statement. Oracle enforces this restriction on the columns named in the SET list at parse time, so it fires even when the assigned expression would leave the value unchanged, and neither the matched nor the not-matched branch executes.

8. Question 8

   A session with autocommit disabled runs the script below as a single transaction. What happens when the last statement executes? ```sql UPDATE orders SET discount = 0.05 WHERE order_id = 1001; SAVEPOINT sp_a; DELETE FROM orders WHERE order_id = 1004; SAVEPOINT sp_b; UPDATE products SET unit_price = unit_price * 1.1 WHERE product_id = 4; ROLLBACK TO SAVEPOINT sp_a; ROLLBACK TO SAVEPOINT sp_b; ```

   1. A. The statement succeeds and the transaction is restored to the point just after the DELETE, so the DELETE is pending again and only the products UPDATE stays undone.

      Assumes savepoints remain addressable in any order, so a session can move forward again to a later savepoint after rolling back past it. A rollback can only move backwards: once the transaction returns to sp_a, the work after sp_a is gone and cannot be re-established by naming sp_b.

   2. B. ORA-02091: transaction rolled back

      Assumes that naming a savepoint the transaction can no longer reach forces Oracle to abort and roll back the whole transaction. The failing ROLLBACK TO SAVEPOINT raises a statement-level error only; the transaction stays open with the work up to sp_a intact.

   3. C. The statement succeeds as a no-op, because sp_b is already the current savepoint after the previous rollback.

      Assumes an unresolvable savepoint name is silently tolerated because the rollback would change nothing. Oracle validates the savepoint name before doing any work, and a name that is not currently established is an error rather than a no-op.

   4. D. ORA-01086: savepoint 'SP_B' never established in this session or is invalidCorrect answer

      sp_b was created after sp_a, and rolling back to sp_a erases every savepoint established after it, so the name sp_b no longer exists in the transaction and the statement fails with ORA-01086 (SAVEPOINT, SQL Language Reference).

   Explanation

   Savepoint names are valid only while the transaction still extends past the point where they were marked. Rolling back to an earlier savepoint erases every savepoint created after it, and a COMMIT or a full ROLLBACK erases all of them, so a savepoint can never be re-entered once the transaction has retreated past it. Referring to a savepoint that is no longer established raises ORA-01086 without aborting the transaction, which stays open with the surviving work still uncommitted.