Chapter 9 Quiz: Data Modification

Test your understanding of Chapter 9. Attempt all questions from memory before revealing the answers. The act of retrieval -- even when you get it wrong -- strengthens learning more than re-reading.

Scoring Guide: - 20-25 correct: Excellent -- you have strong command of the material - 15-19 correct: Good -- review the sections where you missed questions - 10-14 correct: Fair -- re-read the chapter with the quiz questions in mind - Below 10: Re-read the chapter carefully, then retake this quiz in 48 hours

Multiple Choice Questions

Question 1

Why should you always include a column list in an INSERT statement?

A) DB2 requires it; INSERT without a column list is a syntax error B) It improves performance by telling the optimizer which columns to write C) It makes the intent explicit, survives schema changes, and is self-documenting D) It prevents duplicate key violations

Question 2

In a multi-row INSERT, what is the primary performance advantage over separate single-row INSERT statements?

A) Multi-row INSERT bypasses constraint checking B) Multi-row INSERT uses less disk space C) Reduced network round trips, more efficient logging, and fewer lock acquisitions D) Multi-row INSERT automatically runs in parallel

Question 3

What is the "Halloween Problem" in the context of INSERT...SELECT?

A) A performance bug that occurs on October 31st due to date formatting B) An INSERT reading from the same table it writes to might see its own newly inserted rows and loop C) A concurrency issue where two sessions insert the same row simultaneously D) A bug where DEFAULT values are not applied to inserted rows

Question 4

In an UPDATE with SET, when are the expressions in the SET clause evaluated?

A) Left to right, so later columns see the effects of earlier assignments B) Right to left, so earlier columns see the effects of later assignments C) All expressions are evaluated using the row's pre-update values D) Each expression is evaluated independently in an unpredictable order

Question 5

What happens if you execute an UPDATE without a WHERE clause?

A) DB2 raises an error and prevents the statement from executing B) DB2 prompts for confirmation before proceeding C) Every row in the table is updated D) Only the first row in the table is updated

Question 6

What is the key difference between DELETE and TRUNCATE TABLE?

A) DELETE is faster because it does not log the operation B) TRUNCATE fires triggers for each row; DELETE does not C) DELETE logs each row individually and fires triggers; TRUNCATE uses minimal logging and does not fire triggers D) There is no difference; TRUNCATE is just an alias for DELETE

Question 7

Which ON DELETE action prevents deletion of a parent row when child rows exist?

A) ON DELETE CASCADE B) ON DELETE SET NULL C) ON DELETE RESTRICT D) ON DELETE IGNORE

Question 8

What does the MERGE statement combine into a single operation?

A) SELECT and INSERT B) INSERT and UPDATE (and optionally DELETE) based on match conditions C) CREATE TABLE and INSERT D) DELETE and TRUNCATE

Question 9

In a MERGE statement, why is it important that the ON clause references indexed columns on the target table?

A) DB2 requires an index for MERGE to compile B) Without an index, MERGE performs a table scan for every source row, which is catastrophic for large tables C) Indexes prevent the MERGE from inserting duplicate rows D) The ON clause does not affect performance

Question 10

After a COMMIT, what is the only way to "undo" the changes?

A) ROLLBACK B) ROLLBACK TO SAVEPOINT C) Execute compensating SQL (reverse INSERT/UPDATE/DELETE statements) D) It is impossible to undo committed changes

Question 11

What is the DB2 term for a transaction?

A) Logical session B) Work packet C) Unit of work (UOW) D) Commit scope

Question 12

What happens if a DB2 for LUW connection drops unexpectedly during a transaction?

A) The pending changes are automatically committed B) The pending changes are automatically rolled back C) The changes are saved in a pending state until the next connection D) The behavior is undefined

Question 13

On DB2 for z/OS, what happens to pending DML changes when you execute a CREATE TABLE statement?

A) The pending changes are implicitly committed before the DDL executes B) The DDL is part of the current unit of work and can be rolled back along with the DML C) DB2 raises an error; you must COMMIT before executing DDL D) The pending changes are automatically rolled back

Question 14

What is the primary risk of running a large batch UPDATE in a single transaction without committing?

A) The data changes will not be written to disk B) Active log space may be exhausted, causing a log-full condition that halts all database activity C) DB2 will automatically partition the UPDATE into smaller batches D) Other users will see the partially updated data

Question 15

What does ROLLBACK TO SAVEPOINT do?

A) Rolls back the entire transaction to the beginning B) Rolls back only the changes made after the named savepoint, leaving earlier changes intact C) Commits all changes up to the savepoint and rolls back the rest D) Deletes the savepoint without affecting any data

Short Answer Questions

Question 16

Explain the difference between GENERATED ALWAYS and GENERATED BY DEFAULT for identity columns. When would you choose each?

Question 17

What is the purpose of CACHE on a sequence object? What happens to cached values if DB2 crashes?

Question 18

Describe the SELECT FROM INSERT pattern using FINAL TABLE. What problem does it solve?

SELECT customer_id FROM FINAL TABLE (
    INSERT INTO meridian.customer (first_name, last_name)
    VALUES ('David', 'Park')
);

Question 19

What is lock escalation? Why does it matter for batch UPDATE and DELETE operations?

Question 20

List the five data modification safety patterns described in Section 9.10. Briefly explain why each matters.

Application Questions

Question 21

A developer writes the following MERGE to update customer emails from a staging table:

MERGE INTO meridian.customer AS t
USING meridian.email_updates AS s
ON t.email = s.old_email
WHEN MATCHED THEN
    UPDATE SET t.email = s.new_email;

Identify at least two problems with this MERGE. How would you fix them?

MERGE INTO meridian.customer AS t
USING meridian.email_updates AS s
ON t.customer_id = s.customer_id
WHEN MATCHED THEN
    UPDATE SET t.email = s.new_email;

Question 22

Explain why Meridian Bank should use ON DELETE RESTRICT instead of ON DELETE CASCADE for the foreign key from meridian.account to meridian.customer. Give a specific scenario where CASCADE would cause a serious business problem.

Question 23

Design a stored procedure outline for a batch interest calculation that processes 500,000 savings accounts. Include error handling, commit frequency, restart capability, and logging. You do not need to write complete SQL -- pseudocode with key SQL statements is sufficient.

PROCEDURE calculate_monthly_interest(
    IN p_batch_date DATE,
    IN p_batch_size INTEGER DEFAULT 5000
)

-- Step 1: Check if this batch already ran (restart capability)
SELECT COUNT(*) INTO v_existing
FROM meridian.batch_control
WHERE batch_name = 'INTEREST'
  AND batch_date = p_batch_date
  AND status = 'COMPLETE';

IF v_existing > 0 THEN
    SIGNAL error 'Batch already completed for this date';
END IF;

-- Step 2: Get the last successfully processed account_id (for restart)
SELECT COALESCE(MAX(last_processed_id), 0) INTO v_last_processed
FROM meridian.batch_control
WHERE batch_name = 'INTEREST'
  AND batch_date = p_batch_date
  AND status = 'IN_PROGRESS';

-- Step 3: Open cursor for unprocessed accounts
DECLARE cur CURSOR FOR
    SELECT account_id, current_balance, interest_rate
    FROM meridian.account
    WHERE account_type = 'SAVINGS'
      AND status = 'ACTIVE'
      AND current_balance > 0
      AND account_id > v_last_processed
    ORDER BY account_id;

-- Step 4: Process in batches
SET v_count = 0;
FOR each row from cursor:
    -- Calculate interest
    SET v_interest = ROUND(current_balance * (interest_rate / 12), 2);

    -- Insert transaction record
    INSERT INTO meridian.transaction_history (...);

    -- Update balance
    UPDATE meridian.account SET current_balance = current_balance + v_interest
    WHERE account_id = current account_id;

    SET v_count = v_count + 1;

    IF MOD(v_count, p_batch_size) = 0 THEN
        -- Update batch control with checkpoint
        UPDATE meridian.batch_control
        SET last_processed_id = current account_id,
            rows_processed = v_count;
        COMMIT;
        -- Log progress
    END IF;
END FOR;

-- Step 5: Mark batch complete
UPDATE meridian.batch_control
SET status = 'COMPLETE', rows_processed = v_count;
COMMIT;

Question 24

Compare the total log volume generated by each approach for loading 1 million rows into a staging table:

A) Individual INSERT statements with autocommit ON B) Multi-row INSERT with 1,000 rows per statement, committing every 10 statements C) INSERT...SELECT from a source table D) LOAD utility E) INSERT with NOT LOGGED INITIALLY (LUW)

Rank them from most to least log volume and explain your ranking.

Question 25

A junior DBA asks: "Why can't sequences just keep track of which values were actually used and reclaim the ones from rolled-back transactions? That way there would be no gaps."

Write a response that explains: 1. Why this is technically difficult 2. Why it would harm performance 3. Why gaps in sequences do not actually matter for most applications

Return to Chapter 9 | Continue to Case Study 1