Oracle SQL Certification 1Z0-071 Course | Section 5: Data Creation and Manipulation

Now we're already populating, deleting and updating data in tables. And learn about transaction control

In this section of the Oracle SQL 1Z0-071 certification course, we focus on Data Manipulation Language (DML) and Transaction Control Language (TCL). These topics form the backbone of working with real data in Oracle databases and are guaranteed to appear on the exam.

You will learn how to insert, modify, delete, and permanently remove data, as well as how Oracle controls transactions using COMMIT, ROLLBACK, and SAVEPOINT. Understanding not just the syntax, but also the behavior behind these commands, is critical for both exam success and real-world database work.

Inserting Data with INSERT

The INSERT statement is used to add new rows to a table. It is a DML command, which means all changes are temporary until a COMMIT is issued.

Core INSERT Concepts

You can insert data in several ways:

  • Insert a single row using VALUES
  • Insert multiple rows using INSERT ALL
  • Insert data from another table using INSERT INTO … SELECT
  • Insert data with or without explicitly listing columns

When columns are not listed, values must be provided for every column in the exact table order. When columns are listed, order matters only within the column list.

Functions and Implicit Conversion

Oracle allows functions such as SYSDATE inside INSERT statements. It also performs implicit data type conversion, such as converting strings to NUMBER or DATE, but only when the conversion is valid. Invalid conversions raise runtime errors such as ORA-01722.

Exam Notes

  • INSERT is a DML statement
  • Data is not permanent until COMMIT
  • INSERT INTO … SELECT is a very common exam pattern
  • Implicit conversion works only when Oracle can safely convert the value

Updating Existing Data with UPDATE

The UPDATE statement modifies existing rows in a table and is also a DML operation.

UPDATE Syntax and Behavior

An UPDATE statement consists of:

  • UPDATE table_name
  • SET column assignments
  • Optional WHERE clause

The WHERE clause controls which rows are updated. If it is omitted, all rows in the table are updated.

You can update multiple columns in a single statement, perform arithmetic calculations, and use subqueries to derive new values.

Constraints and Updates

Oracle prevents updates that violate constraints such as PRIMARY KEY or UNIQUE. Attempting to set a NOT NULL column to NULL will also fail.

Updates can be performed through views, but only when the view meets specific requirements.

Exam Notes

  • UPDATE without WHERE updates every row
  • Constraint violations prevent updates
  • Subqueries inside UPDATE are allowed
  • Changes remain uncommitted until COMMIT

Deleting Data with DELETE

The DELETE statement removes entire rows from a table. It does not remove individual column values.

DELETE Behavior

DELETE supports:

  • Deleting a single row
  • Deleting multiple rows
  • Deleting all rows in a table

The WHERE clause controls which rows are removed. If no rows match the condition, Oracle reports zero rows deleted without raising an error.

The FROM keyword is optional in Oracle DELETE syntax.

Exam Notes

  • DELETE is transactional and can be rolled back
  • DELETE removes rows, not column values
  • Omitting WHERE deletes all rows
  • Zero-row deletes do not cause errors

Transaction Control with COMMIT

A transaction in Oracle is a sequence of DML operations that ends with COMMIT or ROLLBACK.

How COMMIT Works

COMMIT makes all DML changes permanent and visible to other sessions. Until COMMIT is issued, changes are visible only within the current session.

Oracle also performs implicit commits when executing DDL statements such as CREATE or ALTER.

Even a failed DDL statement performs an implicit commit of previous DML.

Exam Notes

  • DML changes are session-private until committed
  • DDL always performs an implicit commit
  • Closing SQL Developer normally commits work
  • Failed DDL still commits prior changes

Transaction Control with ROLLBACK

ROLLBACK reverses uncommitted changes made since the last COMMIT.

ROLLBACK Rules

  • Only affects the current session
  • Does not undo committed data
  • Can undo INSERT, UPDATE, and DELETE

ROLLBACK WORK behaves the same as ROLLBACK. The WORK keyword is optional.

Exam Notes

  • ROLLBACK undoes changes since the last COMMIT
  • Committed data cannot be rolled back
  • Transaction boundaries are defined by COMMIT and ROLLBACK

Partial Rollbacks with SAVEPOINT

SAVEPOINT allows you to mark a point within a transaction and roll back to that point without undoing the entire transaction.

SAVEPOINT Behavior

You can create multiple savepoints within a transaction. If multiple savepoints share the same name, Oracle rolls back to the most recent one.

After a COMMIT, all savepoints are removed and cannot be used.

Exam Notes

  • SAVEPOINT allows partial rollback, not partial commit
  • ROLLBACK TO SAVEPOINT works only before COMMIT
  • After COMMIT, all savepoints are lost
  • Rolling back to a savepoint after COMMIT is not possible

Removing All Data with TRUNCATE TABLE

TRUNCATE TABLE removes all rows from a table in a single operation.

TRUNCATE vs DELETE

DELETE:

  • Removes rows one by one
  • Can be rolled back
  • Requires explicit COMMIT
  • Fires triggers

TRUNCATE:

  • Removes all rows at once
  • Much faster
  • Performs an implicit COMMIT
  • Cannot be rolled back
  • Does not fire triggers

The table structure, indexes, and triggers remain after TRUNCATE.

Constraints and TRUNCATE

TRUNCATE fails if the table is referenced by a foreign key constraint unless CASCADE is explicitly used.

Exam Notes

  • TRUNCATE is a DDL statement
  • DDL performs an implicit commit
  • TRUNCATE cannot be rolled back
  • Table structure remains intact

Final Exam Strategy for This Section

This section is heavily tested in scenario-based questions. Common traps include:

  • Missing COMMIT statements
  • Implicit commits caused by DDL
  • UPDATE or DELETE without WHERE
  • Confusion between DELETE and TRUNCATE
  • SAVEPOINT usage after COMMIT
  • Data visibility across multiple sessions

Make sure you practice each command hands-on and always think in terms of transaction boundaries.

Now already involved in this SQL journey, you can subscribe to the YouTube channel and maybe subscribe to newsletter below?

Join ForgeFuse Newsletter