Data Manipulation Language (DML)
Domain 8 tests changing table data with INSERT, UPDATE, DELETE, and MERGE, plus transaction control. You must understand row counts, constraints, and what can be rolled back.
Core rules
- INSERT adds rows. It can use VALUES, a subquery, or DEFAULT for columns. Omitting a column lets its default or NULL apply.
- UPDATE changes existing rows. The WHERE clause determines which rows; without WHERE, every row is updated. SET can use expressions and subqueries.
- DELETE removes rows. Without WHERE, it removes all rows but leaves the table structure. DELETE is DML and can be rolled back before COMMIT.
- MERGE performs conditional insert, update, or delete in one statement using a source and a join condition.
- Transactions end with COMMIT or ROLLBACK. SAVEPOINT creates a marker for partial rollback. DDL statements implicitly commit.
- Implicit cursor attributes such as SQL%ROWCOUNT report affected rows for the most recent DML statement.
Worked example
UPDATE employees SET salary = salary * 1.05 WHERE department_id = 50;
This changes only employees in department 50. Each qualifying row's salary is multiplied by 1.05. If the statement updates 12 rows, SQL%ROWCOUNT returns 12 immediately afterward. The change remains uncommitted until COMMIT, so ROLLBACK can undo it if no intervening commit occurred.
Traps
Forgetting WHERE in UPDATE or DELETE affects the entire table. TRUNCATE is DDL, not DML, and cannot be rolled back in the same way. A constraint violation can roll back the statement, not necessarily the whole transaction. COMMIT makes changes permanent and ends the transaction. SQL%ROWCOUNT reflects the last DML statement, not a previous one.
Key points
- UPDATE or DELETE without WHERE affects all rows.
- DELETE is DML; TRUNCATE is DDL.
- COMMIT and ROLLBACK end transactions.
- SAVEPOINT enables partial rollback.
- SQL%ROWCOUNT reports rows affected by the last DML.