Most Recent Oracle 1Z0-071 Exam Questions & Answers

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.

A . True. The GROUP BY clause needs to include all non-aggregated columns from the SELECT list to provide the correct grouping for the output. The output shown in the image indicates that the data is grouped by channel_type, month, and country_code.

B, C, and D are incorrect because:

B includes a ROLLUP which would introduce subtotals that are not reflected in the output shown.

C specifies a CUBE, which would produce all possible combinations of groupings including the grand total, which is not shown in the output.

D specifies a ROLLUP on country_code only, which would not correctly group by channel_type and month.

INVISIBLE indexes are a feature in Oracle Database 12c and later versions that allow an index to be maintained but not used by the optimizer unless explicitly hinted.

A . False. An INVISIBLE index still consumes storage space as it is maintained in the background.

B . False. There is no such restriction. You can create multiple INVISIBLE indexes on the same column list.

C . False. The optimizer can consider INVISIBLE indexes if they are hinted at in the query.

D . True. You can alter the visibility of an index using the ALTER INDEX command to make an INVISIBLE index VISIBLE.

E . True. Even though they are invisible to the optimizer by default, all DML operations such as INSERT, UPDATE, and DELETE continue to maintain the index as they would with a VISIBLE index.

Oracle Documentation on Managing Indexes: https://docs.oracle.com/database/121/ADMIN/indexes.htm#ADMIN11741

Based on the business rule, the true statements regarding the Entity Relationship (ER) model are:

A . An associative table must be created with a composite key of STUDENT_ID and PROJECT_ID, which is the foreign key linked to the STUDENTS and PROJECTS entities. This is true because, in a many-to-many relationship, an associative (junction) table is used to maintain the associations between two entities.

D . The ER must have a many-to-many relationship between the STUDENTS and PROJECTS entities that must be resolved into 1-to-many relationships. This is correct because many-to-many relationships are resolved by introducing an associative table that breaks it down into two 1-to-many relationships.

Options B, C, and E are incorrect:

B is incorrect because PROJECT_ID should not be a foreign key in the STUDENTS entity in a many-to-many relationship.

C is incorrect as the rule indicates a many-to-many relationship, not 1-to-many.

E is incorrect because STUDENT_ID should not be a foreign key in the PROJECTS entity in a many-to-many relationship.

Global temporary tables in Oracle Database 12c have unique characteristics, primarily around their visibility and lifespan which is session-specific:

B . A TRUNCATE command issued in a session causes all rows in a GLOBAL TEMPORARY TABLE for the issuing session to be deleted: This is accurate as TRUNCATE in the context of a global temporary table only affects the rows inserted during the session that issues the command. The effect is isolated to the session.

D . A GLOBAL TEMPORARY TABLE's definition is available to multiple sessions: The definition (i.e., the structure of the table such as column names, data types, etc.) of a global temporary table is persistent and visible across sessions. However, the data within is session-specific.

E . Any GLOBAL TEMPORARY TABLE rows existing at session termination will be deleted: True, as the data in a global temporary table is designed to be temporary for the duration of a session. When the session ends, the data is automatically deleted.

Oracle Database Concepts and SQL Language Reference 12c, especially sections on temporary tables.