1Z0-149: Managing Tables Using DML Statements

What this domain tests

DML changes table data. The exam tests INSERT, UPDATE, DELETE, MERGE, transaction control, and the difference between DML and DDL.

Core rules

• INSERT INTO t (columns) VALUES (...) adds one row; omitted columns receive their default or NULL. INSERT INTO t SELECT ... adds many rows.
• UPDATE t SET col = value WHERE ... changes matching rows. Without WHERE, every row is updated.
• DELETE FROM t WHERE ... removes matching rows. TRUNCATE TABLE is DDL, cannot be rolled back, and is not DML.
• MERGE performs insert or update based on a join condition; it can also include a delete clause.
• COMMIT makes changes permanent; ROLLBACK undoes uncommitted changes; SAVEPOINT marks a point for partial rollback.
• DDL statements such as CREATE, ALTER, and TRUNCATE issue an implicit commit.
• Constraints are checked at statement level unless deferred; a failed statement rolls back that statement.

Worked example

UPDATE employees SET salary = salary * 1.10 WHERE department_id = 50;

The WHERE clause restricts the update to department 50. The expression on the right uses the old salary for each row. If the update violates a check constraint, the whole statement fails and no rows in that statement are changed. A later ROLLBACK would undo the update if it had succeeded and had not been committed.

Traps

• Running UPDATE or DELETE without WHERE.
• Expecting TRUNCATE to be rolled back.
• Forgetting that DDL implicitly commits pending DML.
• Assuming a sequence value is rolled back with a transaction; it is not.
• Using DEFAULT in an UPDATE as if it were a normal value; use SET col = DEFAULT.