Oracle 1Z0-071: Manage Tables by Using DML Statements

Topic snapshot

Sample questions

These questions are original IT Mastery practice items aligned to this topic area. They are designed for self-assessment and are not official exam questions.

Question 1

Topic: Manage Tables by Using DML Statements

A developer executes the following statement:

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

The developer must:

• make the update permanent
• end the current transaction now
• avoid creating or altering any schema object

Which statement is the best choice?

Options:

• A. ROLLBACK;

• B. CREATE INDEX emp_dept_ix ON employees(department_id);

• C. SAVEPOINT sal_step;

• D. COMMIT;

Best answer: D

Explanation: COMMIT is the transaction-control statement that explicitly ends the current transaction and makes the DML changes permanent. It satisfies all stated requirements without introducing any schema change.

In Oracle, a transaction begins with the first executable DML statement and continues until it is ended. The clearest way to end it while preserving the UPDATE is COMMIT, which permanently saves the changes and starts the next transaction boundary.

A SAVEPOINT only marks a position inside the current transaction; it does not end it. ROLLBACK also ends the transaction, but it undoes the salary update instead of keeping it. A DDL statement such as CREATE INDEX causes an implicit commit in Oracle, but it also creates a schema object, which violates the requirement to avoid DDL.

The key distinction is explicit transaction end with COMMIT versus implicit transaction end caused by DDL.

• Savepoint confusion: a SAVEPOINT creates a marker for partial rollback but leaves the current transaction active.
• Wrong transaction intent: ROLLBACK ends the transaction by discarding the update rather than preserving it.
• Implicit commit trap: creating an index would commit implicitly, but it also changes the schema, which the task forbids.

Question 2

Topic: Manage Tables by Using DML Statements

Given this table definition:

sales_targets
-------------
salesperson_id   NUMBER
region_name      VARCHAR2(20)
quarterly_target NUMBER

Which statement correctly inserts salesperson_id 101, region_name EAST, and quarterly_target 50000 by using explicit column mapping?

Options:

• A. INSERT INTO sales_targets (quarterly_target, salesperson_id, region_name) VALUE (50000, 101, ‘EAST’);

• B. INSERT INTO sales_targets (quarterly_target, salesperson_id, region_name) VALUES (50000, 101, ‘EAST’);

• C. INSERT INTO sales_targets VALUES (50000, 101, ‘EAST’);

• D. INSERT sales_targets (quarterly_target, salesperson_id, region_name) VALUES (50000, 101, ‘EAST’);

Best answer: B

Explanation: In Oracle, an INSERT statement can use a column list to map values explicitly to target columns. When you provide the column names, the values must match that listed order, even if it differs from the table’s physical column order.

The core rule is that INSERT INTO table_name (column_list) VALUES (value_list) maps each value to the corresponding column in the specified column list. In this case, listing quarterly_target, salesperson_id, and region_name means 50000 goes to quarterly_target, 101 goes to salesperson_id, and 'EAST' goes to region_name.

Without a column list, Oracle expects values in the table’s default column order. Also, Oracle requires the INTO keyword and the VALUES keyword in this syntax.

Using an explicit column list is the safest approach when you do not want to rely on the table’s column order.

• No column list fails because the values must then match the table order: salesperson_id, region_name, quarterly_target.
• Missing INTO fails because Oracle INSERT syntax requires INSERT INTO for this statement form.
• Using VALUE fails because Oracle uses the keyword VALUES, even for a single row.

Question 3

Topic: Manage Tables by Using DML Statements

Examine this Oracle SQL statement:

MERGE INTO customers c
USING new_customers n
ON (c.customer_id = n.customer_id)
WHEN MATCHED THEN
  UPDATE SET c.customer_id = n.customer_id,
             c.credit_limit = n.credit_limit
WHEN NOT MATCHED THEN
  INSERT (customer_id, credit_limit)
  VALUES (n.customer_id, n.credit_limit);

Why is this MERGE statement invalid?

Options:

• A. A column used in the ON condition cannot be updated.

• B. The INSERT branch must include a WHERE clause.

• C. One MERGE statement cannot both update and insert.

• D. USING must reference a subquery, not a table.

Best answer: A

Explanation: In Oracle MERGE, the ON clause defines how source and target rows match. A target column named in that match condition cannot also be changed in the WHEN MATCHED THEN UPDATE clause, so updating c.customer_id makes the statement invalid.

MERGE is designed to compare source rows with target rows by using the ON condition, then apply UPDATE, INSERT, or both. Oracle enforces a key rule here: a target column referenced in the ON condition cannot be updated in the merge_update_clause.

In this statement, c.customer_id is part of the match condition:

• ON (c.customer_id = n.customer_id)
• UPDATE SET c.customer_id = n.customer_id

That makes the statement invalid. This restriction prevents changing the target column that Oracle is using to determine whether rows match. The closest distractors confuse normal MERGE capabilities: USING can reference a table, and a single MERGE can both update matched rows and insert unmatched rows.

• USING source can be a table, view, or subquery; it is not limited to subqueries.
• Combined actions are a core purpose of MERGE, so update and insert can appear in the same statement.
• INSERT filter is optional in a MERGE; the insert branch does not require its own WHERE clause.

Question 4

Topic: Manage Tables by Using DML Statements

A user executes these statements in one session and has not issued COMMIT:

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

DELETE FROM employees
WHERE employee_id = 300;

The user now wants to discard all changes made in the current transaction and return the data to its last committed state. Which statement should be executed?

Options:

• A. COMMIT;

• B. ROLLBACK;

• C. SAVEPOINT undo_pt;

• D. ROLLBACK TO undo_pt;

Best answer: B

Explanation: ROLLBACK is the Oracle transaction control statement that cancels uncommitted DML in the current transaction. Because no commit has occurred, it reverses both the UPDATE and the DELETE and returns the session to the last committed state.

In Oracle, a transaction includes DML changes such as INSERT, UPDATE, and DELETE until they are ended by COMMIT or undone by ROLLBACK. Here, the session made two DML changes and has not committed them, so they are still uncommitted.

Using ROLLBACK without a savepoint name reverses the entire current transaction:

• the salary update is undone
• the employee delete is undone
• the data returns to the last committed state

COMMIT would make the changes permanent, not discard them. A savepoint only marks a position inside a transaction, and ROLLBACK TO works only when that savepoint already exists.

• COMMIT persists changes because it ends the transaction by making the UPDATE and DELETE permanent.
• SAVEPOINT marks only a position and does not undo any work by itself.
• ROLLBACK TO undo_pt needs an existing savepoint and would target only work after that point, not necessarily the whole transaction.

Question 5

Topic: Manage Tables by Using DML Statements

Examine the table definition.

DESC departments
Name             Null?    Type
DEPARTMENT_ID    NOT NULL NUMBER(4)
DEPARTMENT_NAME  NOT NULL VARCHAR2(30)
MANAGER_ID                NUMBER(6)
LOCATION_ID               NUMBER(4)

You need to add a department with ID 280 and name Training. The manager and location are not known yet. Which statement best satisfies this requirement?

Options:

• A. DELETE FROM departments WHERE department_id = 280

• B. INSERT INTO departments (department_id, department_name) VALUES (280, ‘Training’)

• C. INSERT INTO departments VALUES (280, ‘Training’)

• D. UPDATE departments SET department_name = ‘Training’ WHERE department_id = 280

Best answer: B

Explanation: The requirement is to add a new row, so an INSERT statement is needed. Because only DEPARTMENT_ID and DEPARTMENT_NAME are required, the safest statement uses a column list and provides values only for those columns.

This tests basic INSERT syntax for row maintenance. When a table has nullable and non-nullable columns, the best practice is to name the target columns explicitly. Here, DEPARTMENT_ID and DEPARTMENT_NAME are the only NOT NULL columns shown, so inserting values for those two columns satisfies the requirement, while MANAGER_ID and LOCATION_ID can remain NULL.

Using VALUES without a column list requires a value for every column in table order. UPDATE changes an existing row rather than adding one, and DELETE removes rows. The key takeaway is to choose INSERT ... (column_list) VALUES ... when adding a partial row based on required columns.

• The VALUES form without a column list fails because the table has four columns, so Oracle expects four values in table order.
• The UPDATE option is for modifying an existing department, not creating a new one.
• The DELETE option does the opposite of the stated requirement by removing a row.

Question 6

Topic: Manage Tables by Using DML Statements

A developer must send each row from staging_orders to exactly one target table based on order_total. After running the statement below, all three target tables contain the same number of rows as staging_orders.

INSERT ALL
  INTO small_orders (order_id, order_total)
    VALUES (order_id, order_total)
  INTO medium_orders (order_id, order_total)
    VALUES (order_id, order_total)
  INTO large_orders (order_id, order_total)
    VALUES (order_id, order_total)
SELECT order_id, order_total
FROM staging_orders;

What is the best fix?

Options:

• A. Replace it with MERGE on order_id.

• B. Keep INSERT ALL and add DISTINCT to the subquery.

• C. Use INSERT FIRST with WHEN clauses on order_total.

• D. Add GROUP BY order_id, order_total to the subquery.

Best answer: C

Explanation: The problem is not duplicate source rows; it is the use of an unconditional multi-table insert. INSERT ALL sends every selected row to every listed target table, so a conditional form is needed when each row must go to only one table.

In Oracle, an unconditional multi-table insert uses INSERT ALL without WHEN clauses, so every row returned by the subquery is inserted into every INTO target. That exactly matches the symptom: each target table ends up with the full source row count.

When rows must be routed by business rules, use conditional multi-table insert logic. For mutually exclusive categories where each row belongs in only one table, INSERT FIRST is the clearest choice because Oracle evaluates the WHEN clauses in order and inserts the row into only the first matching target.

DISTINCT, GROUP BY, and MERGE address different problems, so they do not fix unconditional routing behavior.

• DISTINCT confusion fails because removing duplicate source rows does not change the fact that each remaining row is still inserted into every target.
• MERGE mismatch fails because MERGE is for updating or inserting into one target table based on a match condition, not routing one source row across multiple tables.
• GROUP BY misuse fails because aggregation changes the source result set and still does not create per-table conditional routing logic.

Question 7

Topic: Manage Tables by Using DML Statements

new_orders contains order_id and customer_id. You must copy every row from new_orders into both order_history and order_audit in a single SQL statement. No row-routing conditions are required. Which statement is the best solution?

Options:

• A. ```sql INSERT ALL INTO order_history (order_id, customer_id) VALUES (order_id, customer_id) INTO order_audit (order_id, customer_id) VALUES (order_id, customer_id) SELECT order_id, customer_id FROM new_orders;

- B. ```sql
INSERT ALL
  WHEN 1 = 1 THEN
    INTO order_history (order_id, customer_id)
      VALUES (order_id, customer_id)
  ELSE
    INTO order_audit (order_id, customer_id)
      VALUES (order_id, customer_id)
SELECT order_id, customer_id
FROM new_orders;

• C. ```sql MERGE INTO order_history h USING new_orders n ON (h.order_id = n.order_id) WHEN NOT MATCHED THEN INSERT (order_id, customer_id) VALUES (n.order_id, n.customer_id);

- D. ```sql
INSERT FIRST
  WHEN 1 = 1 THEN
    INTO order_history (order_id, customer_id)
      VALUES (order_id, customer_id)
  WHEN 1 = 1 THEN
    INTO order_audit (order_id, customer_id)
      VALUES (order_id, customer_id)
SELECT order_id, customer_id
FROM new_orders;

Best answer: A

Explanation: An unconditional multi-table INSERT ALL is used when each source row must be inserted into every listed target table. Because no branching logic is needed here, the statement with only INTO clauses is the correct choice.

Oracle supports two multi-table insert styles. An unconditional multi-table insert uses INSERT ALL followed by one or more INTO clauses and a single source query; Oracle applies every INTO clause to each row returned by the subquery. A conditional multi-table insert uses WHEN and optional ELSE logic to route rows, and INSERT FIRST stops at the first true condition.

Here, the requirement is simple duplication: every row from new_orders must go to both order_history and order_audit. That is exactly what unconditional INSERT ALL does in one statement. The version using INSERT FIRST is the closest trap, but it inserts each row into only the first matching branch.

• INSERT FIRST trap fails because Oracle inserts into only the first true WHEN branch for each source row.
• Conditional routing with WHEN ... ELSE sends a row down one branch, not automatically to both target tables.
• MERGE misuse targets one table for synchronization logic and does not satisfy inserting every row into both tables.

Question 8

Topic: Manage Tables by Using DML Statements

Exhibit:

CREATE TABLE orders (
  order_id    NUMBER PRIMARY KEY,
  customer_id NUMBER NOT NULL,
  order_date  DATE DEFAULT SYSDATE NOT NULL,
  status      VARCHAR2(10) DEFAULT 'NEW' NOT NULL
);

You must add one row with order_id = 5001 and customer_id = 42, while allowing Oracle to assign the default values for order_date and status. Which statement correctly applies the INSERT rule?

Options:

• A. INSERT INTO orders VALUES (5001, 42)

• B. INSERT INTO orders SET order_id = 5001, customer_id = 42

• C. INSERT INTO orders (customer_id, order_id) VALUES (5001, 42)

• D. INSERT INTO orders (order_id, customer_id) VALUES (5001, 42)

Best answer: D

Explanation: In Oracle, an INSERT with a column list maps each value to the named columns, not to the table’s physical column order. Because order_date and status have defaults, leaving them out lets Oracle populate them automatically.

The core rule is that INSERT INTO table_name (column_list) VALUES (...) maps values by the specified column names and order in that list. Here, naming only order_id and customer_id correctly stores 5001 in order_id and 42 in customer_id.

Because order_date and status are omitted and both have DEFAULT values, Oracle supplies SYSDATE and 'NEW' for them. This is the correct way to insert a partial row when omitted columns either allow NULL or have defaults.

If no column list is provided, Oracle expects a value for every column in table order. The key takeaway is to use an explicit column list whenever you want precise value mapping or want defaults to fill omitted columns.

• No column list fails because Oracle then expects values for all table columns in table order.
• Reversed column names is syntactically valid, but it stores 5001 in customer_id and 42 in order_id, which does not meet the requirement.
• Using SET fails because SET belongs to UPDATE, not Oracle INSERT ... VALUES syntax.

Question 9

Topic: Manage Tables by Using DML Statements

A session starts with table orders containing:

order_id  status
1         NEW
2         NEW
3         NEW

The session then executes:

UPDATE orders SET status = 'PAID' WHERE order_id = 1;
SAVEPOINT s1;
DELETE FROM orders WHERE order_id = 2;
UPDATE orders SET status = 'SHIPPED' WHERE order_id = 1;
SAVEPOINT s2;
INSERT INTO orders VALUES (4, 'NEW');
ROLLBACK TO s1;
UPDATE orders SET status = 'CLOSED' WHERE order_id = 3;
COMMIT;

Which data state is finally persisted in orders?

Options:

• A. 1 NEW, 2 NEW, 3 NEW

• B. 1 PAID, 3 CLOSED, 4 NEW

• C. 1 SHIPPED, 2 NEW, 3 CLOSED

• D. 1 PAID, 2 NEW, 3 CLOSED

Best answer: D

Explanation: ROLLBACK TO SAVEPOINT reverses only the work done after that savepoint. Here, the delete of order 2, the change of order 1 to SHIPPED, and the insert of order 4 are undone, while the earlier change of order 1 to PAID remains. After that, order 3 is updated to CLOSED and the COMMIT makes those remaining changes permanent.

The core rule is that ROLLBACK TO savepoint_name does not cancel the entire transaction; it removes only the uncommitted changes made after that savepoint was created. In this sequence, the update of order 1 to PAID happens before s1, so it survives the rollback.

After s1, these actions occur and are later undone:

• delete order 2
• update order 1 to SHIPPED
• insert order 4

Then the session updates order 3 to CLOSED and issues COMMIT. So the final committed rows are order 1 as PAID, order 2 still present as NEW, and order 3 as CLOSED.

The key takeaway is that a savepoint rollback is partial, not a full transaction rollback.

• SHIPPED remains fails because that update occurred after s1, so ROLLBACK TO s1 undoes it.
• Order 4 stays inserted fails because the insert also occurred after s1, so it is rolled back.
• Everything returns to original fails because the earlier update to PAID happened before s1 and is still committed later.

Question 10

Topic: Manage Tables by Using DML Statements

Which SQL statement increases salary by 10% only for rows in employees where job_id is SA_REP?

Options:

• A. UPDATE employees SET salary = salary * 1.10

• B. UPDATE employees SET salary = salary * 1.10 AND job_id = 'SA_REP'

• C. UPDATE employees WHERE job_id = 'SA_REP' SET salary = salary * 1.10

• D. UPDATE employees SET salary = salary * 1.10 WHERE job_id = 'SA_REP'

Best answer: D

Explanation: In Oracle, an UPDATE statement uses SET to define new column values and WHERE to restrict which rows are changed. If the WHERE clause matches only SA_REP rows, only those rows are updated.

The core rule is that Oracle UPDATE syntax is UPDATE table_name SET column = expression WHERE condition. The SET clause specifies the new value, and the WHERE clause limits the update to the targeted rows.

If you omit WHERE, Oracle updates every row in the table. If you place WHERE before SET, the statement is syntactically wrong. Also, the SET clause assigns column values; it does not combine the search condition with AND.

So the valid targeted update is the one that sets salary first and then filters rows with job_id = 'SA_REP'.

• Wrong clause order: the option placing WHERE before SET does not follow Oracle UPDATE syntax.
• Bad SET usage: the option using AND inside SET confuses value assignment with row filtering.
• Missing filter: the option without a WHERE clause would increase salary for every row in employees.

Continue with full practice

Use the Oracle 1Z0-071 Practice Test page for the full IT Mastery route, mixed-topic practice, timed mock exams, explanations, and web/mobile app access.

Try Oracle 1Z0-071 on Web View Oracle 1Z0-071 Practice Test

Related focused pages

• Free Oracle 1Z0-071 Full-Length Practice Exam
• Relational Database Concepts
• SQL SELECT
• Restrict and Sort Data
• Single-Row Functions
• Conversion and CASE
• Group Functions
• Display Data from Multiple Tables
• Use Subqueries to Solve Queries
• Use Set Operators
• Indexes and Sequences
• DDL Tables and Relationships
• Manage Views
• Control User Access
• Data Dictionary Views
• Manage Data in Different Time Zones

Free review resource

Read the Oracle 1Z0-071 Cheat Sheet on Tech Exam Lexicon, then return to IT Mastery for timed practice.