Oracle Oracle PL 1z0-071 New Questions

B: True. This MERGE statement should execute without errors. It uses a conditionally filtered selection from the products table as a source to update or delete rows in the new_prices table based on whether the prod_id matches and the cost is greater than 150. The delete operation within a MERGE statement is allowed in Oracle when a WHEN MATCHED clause is specified.

The MERGE statement is correctly structured with a USING clause that includes a subquery with a valid WHERE condition, an ON condition that specifies how to match rows between the source and the target, and a WHEN MATCHED THEN clause that specifies the update and delete operations based on the cost condition.

References:Oracle SQL documentation specifies that within a MERGE statement, you can specify a WHEN MATCHED clause to update and/or delete rows in the target table based on the condition specified after the DELETE keyword.

After the execution of the given SQL statements:

• B. The primary key constraint will be enabled and IMMEDIATE. This is true. The last statement ENABLE CONSTRAINT emp_emp_no_pk; enables the primary key constraint immediately.
• E. The foreign key constraint will be enabled and IMMEDIATE. This is correct as well. The foreign key constraint emp_mgr_fk was not explicitly disabled, so it remains enabled and in the immediate checking mode.

Options A, C, and D are incorrect:

• A is incorrect because constraints are immediate by default; they are not deferred unless explicitly stated.
• C is incorrect because the foreign key constraint emp_mgr_fk was never disabled with the given statements.
• D is incorrect because there is no statement altering the foreign key constraint to a deferred state.

The data dictionary is a read-only set of tables that provides information about the database. Certain operations that modify the database structure will cause changes to the data dictionary:

• Option B: GRANT UPDATE ON scott.emp TO fin_manager;
• Option D: TRUNCATE TABLE emp;

Options A, C, and E do not cause changes to the data dictionary:

• Option A is incorrect because the DELETE command modifies data, not the data dictionary structure.
• Option C is incorrect because altering a session parameter does not change the data dictionary; it is a temporary change for the session.
• Option E is incorrect because a SELECT query does not change the database, it just retrieves information.

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.