Oracle PL 1z0-071 Exam Questions and Answers PDF

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.

Self joins in Oracle Database 12c SQL have these characteristics:

• Option D: They can use INNER JOIN and LEFT JOIN.
• Option E: They require table aliases.

Options A, B, C, and F are incorrect:

• Option A is incorrect because self joins can be non-equijoins as well.
• Option B is incorrect because self joins do not require the NOT EXISTS operator. They may require a condition, but NOT EXISTS is not a necessity.
• Option C is incorrect because a join condition is needed to relate the two instances of the same table in a self join.
• Option F is incorrect for the same reason as B; the EXISTS operator is not a requirement for self joins.

The SQL LIKE operator is used in a WHERE clause to search for a specified pattern in a column. Here are the evaluations of the options:

• A: The pattern ' % a % ’ will match any customer names that contain the letter 'a' anywhere in the name, not necessarily those starting with 'Ma'.
• B: This is correct as 'Ma%' will match any customer names that start with 'Ma'.
• C: In SQL, the wildcard character is '%' not '*', therefore, the pattern 'Ma' is incorrect.
• D: This is the correct syntax. 'UPPER (customer_ name ) LIKE 'MA%'' will match customer names starting with 'Ma' in a case-insensitive manner.
• E: Again, '*' is not a valid wildcard character in SQL.
• F: The character '&' is not a wildcard in SQL.
• G: The operator 'KIKE' is a typo, and '.Ma' is not valid SQL pattern syntax.

Both ROUND and TRUNC functions can be applied to numbers, and MOD is a function that returns the remainder of a division. The ROUND function rounds a number to a specified number of decimal places, which can be positive, zero, or negative. The TRUNC function truncates a number to a specified number of decimal places.

ROUND(MOD(25,3),-1) rounds the result of MOD(25,3), which is 1, to tens place, which results in 0. TRUNC(MOD(25,3),-1) truncates the result of MOD(25,3), which is 1, to tens place, which also results in 0.

Both are valid, but in this specific case, they give the same result because the remainder (1) when rounded or truncated to tens place (-1) will be 0.