Free Access Oracle 1z0-071 New Release

A. Incorrect. An uncommitted transaction is not automatically committed if the user exits SQL*Plus. It is rolled back unless otherwise specified. B. Correct. A DML statement such as INSERT, UPDATE, or DELETE will implicitly start a new transaction if there is no current transaction running in the session. C. Incorrect. A user cannot see uncommitted updates made by another session; a session can only see its own uncommitted changes. D. Incorrect. A Data Definition Language (DDL) statement automatically commits any outstanding transactions in the session, not just the changes to the data dictionary. E. Correct. A session can always see its own uncommitted updates. This is because Oracle uses a multiversion consistency model, allowing each user to see a consistent view of the data including their own changes. F. Incorrect but close. If a session has an uncommitted transaction, then a DDL statement does issue a COMMIT before executing and starting a new implicit transaction, not just for the data dictionary updates but for all pending changes in the session.

This information can be verified in the Oracle Database SQL Language Reference and Oracle Database Concepts documentation, which discuss transaction management and the behavior of DML and DDL statements within transactions.

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.

Views in Oracle have specific properties regarding how they can be used and what limitations apply to them:

A. Oracle does not allow indexing directly on views. However, a view can display data from indexed tables.

B. The WITH CHECK OPTION clause on a view prevents DML operations that would result in rows that do not satisfy the view’s filter criteria. It doesn't prevent certain rows from being updated or inserted in the underlying table through the view per se.

C. The tables (or other views) referenced in the creating query of a view must exist at the time the view is created, making this statement true.

D. Views do not require privileges to be re-granted when the view is updated; they are separate from the underlying object privileges, making this statement true.

E. The WITH CHECK OPTION clause is used to ensure that all data modifications through the view are consistent with the view's defining query, not to prevent rows from being displayed.

References:

• Oracle Database SQL Language Reference, 12c Release 1 (12.1): "CREATE VIEW"
• Oracle Database Concepts, 12c Release 1 (12.1): "Views"

For question 134, the correct options are B and F based on the capabilities and design of SQL:

• B. It best supports relational databases: SQL is fundamentally designed to manage and query data in relational databases. It is the standard language used for managing relational database management systems (RDBMS) and for performing all types of data operations within them.
• F. It provides independence for logical data structures being manipulated from the underlying physical data storage: SQL allows users to interact with the data at a logical level without needing to know how the data is physically stored. This is known as logical data independence, which is a key feature of SQL in managing databases.

Other options are incorrect because:

• A: SQL itself doesn't guarantee ACID properties; these are provided by the database management system's transaction control mechanisms.
• C, D, E: These statements are incorrect as SQL does not inherently support object-oriented concepts like encapsulation and polymorphism, is not limited to hierarchical data storage, and is not the only language used for both relational and object-oriented databases.