Changed 1z0-071 Exam Questions

For external tables in Oracle:

• C. DML statements cannot be used on them: External tables are read-only in Oracle. They allow access to data in external sources as if it were within a table in the database, but they do not support DML operations (INSERT, UPDATE, DELETE).
• D. Their data can be retrieved by using only SQL or PL/SQL: You can query the data in external tables using SQL or PL/SQL, just as you would with regular database tables.

Incorrect options are:

• A: You cannot create indexes directly on external tables.
• B: External tables cannot be populated using the CREATE TABLE AS SELECT command since they are primarily meant for accessing data stored outside the database.
• E: Only the metadata of an external tables is stored in the database; the actual data remains in external files.

The function ADD_MONTHS(hire_date, 6) adds 6 months to the hire_date. The function NEXT_DAY(date, 'day_name') finds the date of the first specified day_name after the date given. In this case, 'MONDAY' is used to find the date of the first Monday after the hire_date plus 6 months.

Option A is correct as it accurately composes both ADD_MONTHS and NEXT_DAY functions to fulfill the requirement.

Options B, C, and D do not provide a valid use of the NEXT_DAY function, either because of incorrect syntax or incorrect logic in calculating the required date.

The Oracle Database SQL Language Reference for 12c specifies how these date functions should be used.

C. True. A transaction that is interrupted by a system failure is automatically rolled back by Oracle to ensure data consistency. This is part of the ACID (Atomicity, Consistency, Isolation, Durability) properties that Oracle adheres to.

D. True. A ROLLBACK without specifying a savepoint rolls back the entire transaction and removes all savepoints within that transaction. This restores the data to the state it was in at the beginning of the transaction.

Regarding creating tables in an Oracle database:

• B. The same table name can be used for tables in different schemas: In Oracle, a schema is essentially a namespace within the database; thus, the same table name can exist in different schemas without conflict, as each schema is distinct.
• C. A system privilege is required: To create tables, a user must have the necessary system privileges, typically granted explicitly or through roles such as CREATE TABLE or administrative privileges depending on the environment setup.

Incorrect options for all three repeated questions:

• A: Oracle SQL does not allow specifying the maximum number of rows directly in a CREATE TABLE statement; this is controlled by storage allocation and database design rather than table creation syntax.
• D: Creating an external table does not create the physical file. It merely creates a table structure that allows access to data stored in an external file specified in the directory; the file itself must already exist or be managed outside of Oracle.
• E: A primary key constraint is not mandatory for creating tables. While it is a common practice to define a primary key to enforce entity integrity, it is not required by the Oracle SQL syntax for table creation.

These answers and explanations are aligned with Oracle Database 12c SQL documentation and standard practices.