Oracle SQL DML: INSERT, UPDATE and DELETE — 1Z0-071 Exam Guide

1Z0-071 Exam Prep Chapter 10 Volume 2

DML — Data Manipulation Language — covers the statements that add, change, and remove rows. The 1Z0-071 exam tests INSERT, UPDATE, and DELETE syntax carefully, including multi-row operations, subqueries inside DML, and what happens when a WHERE clause is omitted.

Transaction control — COMMIT, ROLLBACK, and SAVEPOINT — is also part of this topic and appears on its own in exam questions.

In this post

1. INSERT — adding rows
2. INSERT with a subquery
3. UPDATE — changing existing rows
4. UPDATE with a subquery
5. DELETE — removing rows
6. The missing WHERE clause trap
7. TRUNCATE vs DELETE
8. Transaction control — COMMIT, ROLLBACK, SAVEPOINT
9. Read consistency during a transaction
10. Quick reference
11. Practice questions

01 — INSERT — adding rows

INSERT adds one or more rows to a table. There are two forms — explicit column list and positional — and the exam tests the rules around both.

-- Explicit column list (recommended and most testable form)
INSERT INTO departments (department_id, department_name, location_id)
VALUES (280, 'Training', 1800);

-- Positional form — values must match every column in table definition order
INSERT INTO departments
VALUES (290, 'Consulting', NULL, 1900);
-- All 4 columns must be supplied in creation order
-- NULL must be explicitly written for optional columns

-- Inserting a NULL value explicitly
INSERT INTO employees (employee_id, last_name, email, hire_date, job_id, commission_pct)
VALUES (300, 'Harris', 'HARRIS', SYSDATE, 'SA_REP', NULL);

-- Inserting a NULL by omitting the column (implicit NULL)
INSERT INTO employees (employee_id, last_name, email, hire_date, job_id)
VALUES (301, 'Singh', 'SINGH', SYSDATE, 'SA_REP');
-- commission_pct omitted — stored as NULL automatically

Column list vs positional: Without an explicit column list, VALUES must supply a value for every column in the exact order they were defined in CREATE TABLE. If the table structure ever changes, positional inserts break silently or error. The exam uses this to test whether you know the difference.

02 — INSERT with a subquery

Instead of VALUES, you can use a SELECT statement to insert multiple rows in one operation. The column count and data types from the subquery must match the target columns.

-- Copy all employees from department 50 into a history table
INSERT INTO emp_history (employee_id, last_name, salary, department_id)
SELECT employee_id, last_name, salary, department_id
FROM   employees
WHERE  department_id = 50;
-- Inserts as many rows as the subquery returns
-- No VALUES keyword when using a subquery

No VALUES keyword: When using a subquery with INSERT, the VALUES keyword is not used. Writing INSERT INTO ... VALUES (SELECT ...) is a syntax error. The SELECT replaces the VALUES clause entirely.

03 — UPDATE — changing existing rows

UPDATE modifies column values in existing rows. The WHERE clause controls which rows are changed. Without WHERE, every row in the table is updated.

-- Update a single row
UPDATE employees
SET    salary = 7000
WHERE  employee_id = 110;

-- Update multiple columns at once
UPDATE employees
SET    salary        = salary * 1.1,
       commission_pct = 0.15
WHERE  department_id = 80;

-- Set a column to NULL explicitly
UPDATE employees
SET    manager_id = NULL
WHERE  employee_id = 100;

-- Update using a column's own current value
UPDATE employees
SET    salary = salary * 1.05
WHERE  job_id = 'IT_PROG';

04 — UPDATE with a subquery

The SET clause and the WHERE clause in an UPDATE can both use subqueries. This is one of the more complex DML patterns the exam tests.

-- Set salary equal to the average salary of department 80
UPDATE employees
SET    salary = (SELECT AVG(salary)
                 FROM   employees
                 WHERE  department_id = 80)
WHERE  job_id = 'SA_REP';

-- Update multiple columns with one subquery using row value constructor
UPDATE employees
SET    (salary, commission_pct) = (SELECT salary, commission_pct
                                    FROM   employees
                                    WHERE  employee_id = 205)
WHERE  employee_id = 110;

-- Subquery in WHERE clause — update employees in the same dept as King
UPDATE employees
SET    salary = salary * 1.1
WHERE  department_id = (SELECT department_id
                        FROM   employees
                        WHERE  last_name = 'King'
                        AND    job_id = 'AD_PRES');

Subquery in SET must return one row: When a subquery is used in the SET clause to assign a value, it must return exactly one row. If it returns multiple rows, Oracle raises ORA-01427. This is the same rule as single-row subqueries in WHERE clauses.

05 — DELETE — removing rows

DELETE removes rows from a table. Like UPDATE, the WHERE clause controls which rows are affected. Without WHERE, all rows are deleted — but the table structure remains.

-- Delete a specific row
DELETE FROM employees
WHERE  employee_id = 300;

-- Delete all employees in a department
DELETE FROM employees
WHERE  department_id = 50;

-- Delete using a subquery
DELETE FROM employees
WHERE  department_id = (SELECT department_id
                        FROM   departments
                        WHERE  department_name = 'Shipping');

-- Delete all rows (no WHERE) — table structure and storage remain
DELETE FROM employees;

FROM is optional: DELETE FROM table and DELETE table are both valid Oracle syntax. The exam uses both forms — either is correct.

06 — The missing WHERE clause trap

The most dangerous DML mistake — and one the exam specifically tests — is running UPDATE or DELETE without a WHERE clause. There is no automatic confirmation. Oracle executes immediately and affects every row.

-- Updates EVERY row in the table
UPDATE employees
SET    salary = 5000;
-- All 107 employees now have salary = 5000
-- Can be undone with ROLLBACK if not yet committed

-- Deletes EVERY row in the table
DELETE FROM employees;
-- All rows gone — table structure still exists
-- Can be undone with ROLLBACK if not yet committed

Exam trap: The exam will show an UPDATE or DELETE without a WHERE clause and ask how many rows are affected. The answer is always all rows in the table. It then asks whether the change can be reversed — yes, with ROLLBACK, as long as COMMIT has not been issued.

07 — TRUNCATE vs DELETE

Both TRUNCATE and DELETE (without WHERE) remove all rows from a table. The exam tests the differences between them precisely.

	DELETE (no WHERE)	TRUNCATE
Language type	DML	DDL
Can be rolled back?	Yes — until COMMIT	No — auto-commits immediately
WHERE clause	Supported	Not supported
Fires row triggers?	Yes	No
Resets sequence?	No	No (unless RESTART IDENTITY used)
Speed	Slower — logs each row deletion	Faster — releases extent markers
Table structure	Preserved	Preserved

-- TRUNCATE: DDL — cannot be rolled back
TRUNCATE TABLE emp_history;
-- All rows gone instantly, no undo possible

-- DELETE without WHERE: DML — can be rolled back
DELETE FROM emp_history;
ROLLBACK;  -- rows are restored

08 — Transaction control — COMMIT, ROLLBACK, SAVEPOINT

A transaction is a unit of work. It starts automatically with the first DML statement and ends with COMMIT or ROLLBACK. The exam tests all three transaction control statements and the implicit commit scenarios.

-- Basic transaction flow
INSERT INTO departments VALUES (300, 'New Dept', NULL, NULL);
UPDATE employees SET department_id = 300 WHERE employee_id = 200;
COMMIT;   -- makes both changes permanent

-- Rolling back all changes since the last commit
DELETE FROM employees WHERE department_id = 300;
ROLLBACK;  -- DELETE is undone — rows are restored

-- SAVEPOINT — partial rollback
INSERT INTO departments VALUES (310, 'Alpha', NULL, NULL);
SAVEPOINT sp1;
INSERT INTO departments VALUES (320, 'Beta', NULL, NULL);
ROLLBACK TO sp1;
-- The insert of dept 320 is undone
-- The insert of dept 310 is still pending (not yet committed)
COMMIT;    -- only dept 310 is saved permanently

Event	Effect on transaction
COMMIT	Makes all pending DML changes permanent
ROLLBACK	Undoes all pending DML changes back to last COMMIT
ROLLBACK TO savepoint	Undoes changes back to the named savepoint — earlier changes still pending
DDL statement (CREATE, DROP, ALTER, TRUNCATE)	Implicitly commits any pending DML before executing
Normal session exit	Implicitly commits pending DML
Abnormal session termination	Implicitly rolls back pending DML

DDL causes implicit commit: Running any DDL statement (CREATE TABLE, DROP TABLE, ALTER TABLE, TRUNCATE) automatically commits any uncommitted DML that came before it. The exam tests this — if you INSERT some rows and then run a CREATE TABLE without committing, the INSERT is permanently committed by the DDL.

09 — Read consistency during a transaction

Oracle guarantees that a query always sees a consistent snapshot of the data as it existed at the start of the query — even if another session commits changes while your query is running. This is called read consistency and it is built into Oracle using undo data.

-- Session A: starts a long-running query at 10:00
SELECT COUNT(*) FROM orders;  -- reads data as of 10:00

-- Session B: inserts and commits a new row at 10:01 (while A's query runs)
INSERT INTO orders VALUES (...);
COMMIT;

-- Session A's query still returns the count as of 10:00
-- It does not see Session B's committed insert
-- This is read consistency — the query result is not affected by concurrent changes

What read consistency means for DML: Your own uncommitted changes are visible to your own session. Other sessions do not see your uncommitted changes. Once you COMMIT, other sessions can see your changes in their subsequent queries.

10 — Quick reference

Rule	Detail
INSERT without column list	Must supply values for every column in definition order
INSERT with subquery	No VALUES keyword — SELECT replaces the VALUES clause
UPDATE without WHERE	Every row in the table is updated
DELETE without WHERE	Every row is deleted — table structure remains
TRUNCATE vs DELETE	TRUNCATE is DDL — cannot be rolled back; DELETE is DML — can be rolled back
Subquery in SET clause	Must return exactly one row — ORA-01427 if multiple rows
ROLLBACK TO savepoint	Undoes back to savepoint — earlier DML still pending
DDL causes implicit commit	Any DDL commits all preceding uncommitted DML
Abnormal exit	Pending DML is rolled back automatically
Read consistency	Query sees data as of its start time — not affected by concurrent commits

11 — Practice questions

Q1 — A developer runs these three statements in order without committing between them:

INSERT INTO departments VALUES (400, 'Test', NULL, NULL);
INSERT INTO departments VALUES (401, 'Test2', NULL, NULL);
CREATE TABLE temp_log (log_id NUMBER);

The developer then runs ROLLBACK. How many of the two INSERT rows are rolled back?

A) Both INSERTs are rolled back B) Neither INSERT is rolled back — the CREATE TABLE committed them both C) Only the second INSERT is rolled back — the first was saved by the savepoint D) Both INSERTs are rolled back but the CREATE TABLE is also undone

Q2 — Which statement correctly inserts multiple rows from a subquery?

A) INSERT INTO emp_backup VALUES (SELECT * FROM employees WHERE department_id = 50); B) INSERT INTO emp_backup SELECT * FROM employees WHERE department_id = 50; C) INSERT INTO emp_backup FROM (SELECT * FROM employees WHERE department_id = 50); D) INSERT INTO emp_backup SET SELECT * FROM employees WHERE department_id = 50;

Q3 — A developer runs DELETE FROM employees; then immediately runs ROLLBACK. What is the result?

A) All rows are permanently deleted — DELETE cannot be rolled back B) All rows are restored — DELETE is DML and ROLLBACK undoes uncommitted DML C) Half the rows are restored — ROLLBACK only undoes the most recent statement D) The ROLLBACK fails because DELETE without WHERE cannot be undone

Oracle SQL 1Z0-071 Exam Prep Guide — Complete 4-Volume Set

19 chapters  ·  1,480 practice questions  ·  1,249 SQL examples
Chapter 10 covers the full DML topic with practice sets for INSERT, UPDATE, DELETE, transaction control, and read consistency scenarios. Instant PDF download.

Get the full guide — $25 →

Published by https://www.apps-dba.com — Oracle DBA and certification resources.