Oracle SQL Oracle SQL DML Operations 3

Question 1

What is the purpose of the FOR UPDATE clause in an Oracle SQL SELECT statement?

Accepted Answer

Locks selected rows to prevent other sessions from modifying them

Answer

SELECT ... FOR UPDATE locks the selected rows until the transaction ends, preventing other transactions from modifying those rows.

Question 2

In Oracle SQL, which aggregate function is NOT ignored on NULL values?

Accepted Answer

COUNT(*)

Answer

COUNT(*) counts all rows including those with NULL values, while COUNT(column), SUM, AVG, and MAX all ignore NULLs.

Question 3

What does the NOWAIT option in SELECT ... FOR UPDATE NOWAIT do in Oracle SQL?

Accepted Answer

Returns an error immediately if rows are already locked

Answer

NOWAIT causes Oracle to immediately raise an ORA-00054 error instead of waiting if any requested rows are already locked by another session.

Question 4

Which Oracle SQL UPDATE syntax uses a subquery to update multiple columns at once?

Accepted Answer

UPDATE t SET (col1,col2) = (SELECT v1,v2 FROM src WHERE ...)

Answer

Oracle allows updating multiple columns simultaneously using SET (col1, col2) = (subquery) to pull values from another table.

Question 5

What is the effect of omitting the WHERE clause in an Oracle SQL UPDATE statement?

Accepted Answer

All rows in the table are updated

Answer

An UPDATE without a WHERE clause applies the SET changes to every row in the table, which is often an unintentional mistake.

Oracle SQL Practice Test