Oracle SQL Oracle SQL DML Operations 1 — Questions and Answers
Question 1: Which Oracle SQL statement inserts multiple rows in a single operation using a subquery?
- INSERT ALL
- INSERT INTO ... VALUES ()()()
- INSERT INTO ... SELECT (Correct answer)
- BULK INSERT
Correct answer: INSERT INTO ... SELECT
INSERT INTO ... SELECT inserts all rows returned by a SELECT query into the target table in a single DML operation.
Question 2: What does the MERGE statement do in Oracle SQL?
- Combines two tables permanently
- Performs INSERT, UPDATE, or DELETE based on matching conditions in a single statement (Correct answer)
- Merges duplicate rows within a table
- Joins two schemas together
Correct answer: Performs INSERT, UPDATE, or DELETE based on matching conditions in a single statement
MERGE (also called UPSERT) conditionally inserts or updates rows based on whether a match exists between source and target tables.
Question 3: Which Oracle SQL statement removes ALL rows from a table without logging individual row deletions?
- DELETE FROM table
- DROP TABLE table
- TRUNCATE TABLE table (Correct answer)
- REMOVE ALL FROM table
Correct answer: TRUNCATE TABLE table
TRUNCATE TABLE removes all rows without generating individual undo records, making it much faster than DELETE but non-transactional.
Question 4: In Oracle SQL, which DML statement modifies existing rows in a table?
- MODIFY
- ALTER
- UPDATE (Correct answer)
- CHANGE
Correct answer: UPDATE
The UPDATE statement modifies values in existing rows based on a WHERE condition, affecting all rows if WHERE is omitted.
Question 5: What does the RETURNING clause do in an Oracle SQL DML statement?
- Rolls back the DML operation
- Returns affected row values into a variable or bind variable (Correct answer)
- Returns the number of rows affected
- Returns to the previous transaction state
Correct answer: Returns affected row values into a variable or bind variable
The RETURNING clause captures column values from rows affected by INSERT, UPDATE, or DELETE into variables for use in PL/SQL.
Question 6: Which Oracle SQL statement permanently saves changes made by DML operations?
- SAVE
- APPLY
- COMMIT (Correct answer)
- FINALIZE
Correct answer: COMMIT
COMMIT makes all changes made since the last COMMIT or ROLLBACK permanent and visible to other sessions.
Oracle SQL Oracle SQL DML Operations 1
Which Oracle SQL statement inserts multiple rows in a single operation using a subquery?