Oracle Updated 1z0-071 Exam Questions and Answers by heather

Regarding the DROP TABLE command:

• A. ALL constraints defined on HR.EMPLOYEES are dropped: Dropping a table will automatically drop all constraints associated with that table.
• B. The HR.EMPLOYEES table may be moved to the recycle bin: In Oracle, unless explicitly using PURGE, dropped tables go to the recycle bin, allowing recovery.
• E. All indexes defined on HR, EMPLOYEES are dropped: When a table is dropped, all its indexes are also automatically dropped.

Incorrect options:

• C: Synonyms are not automatically dropped when the table is dropped; they become invalid.
• D: Sequences are independent objects and are not dropped when a table is dropped.
• F: Views are not dropped; however, they will return errors upon being queried if their base table is dropped.

The correct answers regarding the MERGE statement are:

• B. The WHEN NOT MATCHED clause can be used to specify the inserts to be performed. This is true. When a row from the source does not match any row in the target, the WHEN NOT MATCHED clause is where you specify the insert operation.
• E. The WHEN MATCHED clause can be used to specify the updates to be performed. This is true as well. The WHEN MATCHED clause is where you specify the update (or delete) operation to be performed when the source and target rows match.

Options A, C, and D are incorrect:

• A is incorrect because WHEN NOT MATCHED does not handle deletions, it is for inserts.
• C is incorrect as inserts are not specified in the WHEN MATCHED clause but in the WHEN NOT MATCHED clause.
• D is incorrect because updates are specified in the WHEN MATCHED clause, not the WHEN NOT MATCHED clause.

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 SET VERIFY ON command is related to how SQL*Plus and SQL Developer display information about substitution variables:

• A. It displays values for variables created by the DEFINE command: When VERIFY is set to ON, SQL*Plus and SQL Developer will display the old and new values of a substitution variable when it is redefined using the DEFINE command or when a new value is provided for it during the session.
• B. It can be used in SQL Developer and SQL*Plus: While traditionally associated with SQL*Plus, the SET VERIFY command is also supported in SQL Developer, allowing you to control the display of substitution variable values in both environments.

References:

• Oracle SQL*Plus User's Guide and Reference, especially the section on the SET command and substitution variables.