1z0-071 Dumps - Oracle Database 12c SQL Practice Exam Questions

For Transaction Control Language (TCL) operations:

A.  

COMMIT erases all the transaction’s savepoints and releases its locks: When a COMMIT operation is executed, it finalizes all changes made during the transaction, removes any savepoints that have been set during the transaction, and releases any locks held on the database objects by the transaction.

B.  

COMMIT ends the transaction and makes all its changes permanent: A COMMIT command completes the transaction, making all data modifications permanent and visible to other sessions.

E.  

ROLLBACK without the TO SAVEPOINT clause undoes all the transaction's changes, releases its locks, and erases all its savepoints: The ROLLBACK command, when used without specifying a SAVEPOINT, undoes all changes made during the transaction, releases any locks held, and removes any savepoints that were established during the transaction.

Incorrect options:

C: ROLLBACK, without any specification, not only undoes all the transaction's changes but also releases all locks held by the transaction.

D: ROLLBACK to a SAVEPOINT only undoes changes up to the named savepoint and does not end the transaction; the transaction continues until a COMMIT or a full ROLLBACK is issued.

F: ROLLBACK without a TO SAVEPOINT clause also erases all savepoints created during the transaction.

About the roles and behavior of subqueries in SQL:

A.  

A subquery can be used in a WHERE clause: Subqueries are often used in WHERE clauses to filter rows based on a condition evaluated against a set of returned values.

B.  

A subquery can be used in a HAVING clause: Similar to WHERE, subqueries can be used in HAVING clauses to filter groups based on aggregate conditions.

D.  

<ANY returns true if the argument is less than the highest value returned by the subquery: The

Incorrect options:

C: =ANY evaluates true if the argument matches any single value returned by the subquery, irrespective of the number of values.

E: A subquery can indeed be used in a FROM clause, known as a derived table or inline view.

F:

G: A subquery can be used in a SELECT list, particularly when the subquery is designed to return a single value (scalar subquery).