Oracle SQL Oracle SQL DML Operations 2 — Questions and Answers
Question 1: What does ROLLBACK do in Oracle SQL?
- Undoes all uncommitted DML changes in the current transaction (Correct answer)
- Deletes all rows in a table
- Reverts DDL changes
- Restores a previous database backup
Correct answer: Undoes all uncommitted DML changes in the current transaction
ROLLBACK undoes all uncommitted DML changes in the current transaction, restoring the database to its state at the last COMMIT.
Question 2: Which Oracle SQL feature allows you to set a named point within a transaction to partially roll back?
- CHECKPOINT
- SAVEPOINT (Correct answer)
- BOOKMARK
- TRANSACTION MARK
Correct answer: SAVEPOINT
SAVEPOINT marks a named point in a transaction so that ROLLBACK TO SAVEPOINT can undo changes made after that point without canceling the entire transaction.
Question 3: In Oracle SQL, what happens to uncommitted DML changes when a DDL statement is executed?
- They are rolled back automatically
- They are committed automatically (Correct answer)
- They are saved to a temp table
- Nothing changes; DDL does not affect DML
Correct answer: They are committed automatically
Oracle automatically issues an implicit COMMIT before and after every DDL statement, permanently saving any pending DML changes.
Question 4: Which Oracle SQL INSERT syntax inserts data into specific columns only?
- INSERT INTO table VALUES (...) COLUMNS (...)
- INSERT INTO table (col1, col2) VALUES (val1, val2) (Correct answer)
- INSERT PARTIAL INTO table VALUES (val1, val2)
- INSERT INTO table SET col1=val1, col2=val2
Correct answer: INSERT INTO table (col1, col2) VALUES (val1, val2)
Listing column names in parentheses after the table name allows inserting values into specific columns; unspecified columns receive their default values or NULL.
Question 5: What does DELETE FROM employees WHERE department_id = 10 do in Oracle SQL?
- Drops the employees table
- Removes all rows where department_id equals 10 (Correct answer)
- Disables the department_id column
- Marks department_id 10 as inactive
Correct answer: Removes all rows where department_id equals 10
This DELETE statement removes only the rows in the employees table where department_id equals 10, leaving other rows intact.
Question 6: Which Oracle SQL multi-table INSERT directs rows to different tables based on conditions?
- INSERT ALL WHEN (Correct answer)
- CONDITIONAL INSERT
- INSERT SWITCH
- INSERT CASE
Correct answer: INSERT ALL WHEN
INSERT ALL with WHEN clauses conditionally inserts rows into different tables based on evaluated conditions, similar to a CASE statement.
Oracle SQL Oracle SQL DML Operations 2
What does ROLLBACK do in Oracle SQL?