1Z0-071 Data Manipulation Language (DML) 1 — Questions and Answers
Question 1: Which DML statement adds a new row to a table?
- UPDATE
- MERGE
- INSERT (Correct answer)
- CREATE
Correct answer: INSERT
INSERT adds one or more new rows to a table using either the VALUES clause or a subquery.
Question 2: What does the COMMIT statement do in Oracle SQL?
- Rolls back all changes since the last COMMIT
- Saves all pending DML changes to the database permanently (Correct answer)
- Creates a savepoint
- Locks the table for exclusive access
Correct answer: Saves all pending DML changes to the database permanently
COMMIT makes all DML changes since the last COMMIT or ROLLBACK permanent and visible to other sessions.
Question 3: Which statement correctly updates the salary of employee 100 to 9000?
- UPDATE employees SET salary = 9000 WHERE employee_id = 100 (Correct answer)
- MODIFY employees SET salary = 9000 WHERE employee_id = 100
- UPDATE employees VALUES (9000) WHERE employee_id = 100
- ALTER employees SET salary = 9000 WHERE employee_id = 100
Correct answer: UPDATE employees SET salary = 9000 WHERE employee_id = 100
UPDATE uses the SET clause to assign new values and a WHERE clause to target specific rows.
Question 4: What happens if you issue a DELETE without a WHERE clause?
- An error is raised
- Only the first row is deleted
- All rows in the table are deleted (Correct answer)
- Only duplicate rows are deleted
Correct answer: All rows in the table are deleted
DELETE without a WHERE clause removes every row from the table while keeping the table structure intact.
Question 5: Which DML statement can both insert new rows and update existing rows in a single operation?
- UPSERT
- INSERT OR UPDATE
- MERGE (Correct answer)
- REPLACE
Correct answer: MERGE
MERGE (also called an upsert) matches source rows to target rows and inserts new ones or updates existing ones conditionally.
Question 6: What does ROLLBACK do in a DML transaction?
- Saves changes permanently
- Undoes all changes since the last COMMIT or ROLLBACK (Correct answer)
- Deletes all rows in a table
- Closes the database session
Correct answer: Undoes all changes since the last COMMIT or ROLLBACK
ROLLBACK reverses all uncommitted DML changes, restoring the data to its state at the last COMMIT.
1Z0-071 Data Manipulation Language (DML) 1
Which DML statement adds a new row to a table?