Oracle PL 1z0-071 Reddit Questions

A. The constraint commission < salary implies that COMMISSION must be less than SALARY, so it can have a negative value if the salary is greater than zero, making this statement true.

B. The constraint named CR_MGR is listed as a Referential (R) constraint on the MANAGER column, which implies it is a foreign key that would reference the EMPNO column, making this statement true.

C. The SALARY column is part of a Check (C) constraint CC_COMM that requires the SALARY to be greater than 100, which means it must have a value and cannot be NULL, making this statement true.

D. The presence of a foreign key constraint does not automatically create an index on the foreign key column. While Oracle may implicitly create indexes for primary keys, it does not do so for foreign keys.

E. The constraint deptno > 9 prevents DEPTNO from containing values less than or equal to 9, so it cannot contain the value 1, making this statement false.

F. There is no NOT NULL constraint listed on the DEPTNO column in the constraints image, so it can contain NULL values, making this statement false.

G. There is no indication of a unique constraint on the DNAME column in the constraints image; the constraint DNAME IS NOT NULL only implies that the column cannot contain NULL values, making this statement false.

References:

• Oracle Database SQL Language Reference, 12c Release 1 (12.1): "Constraints"
• Oracle Database Concepts, 12c Release 1 (12.1): "Indexes and Index-Organized Tables"

Regarding primary and foreign key constraints:

• A. A table can have only one primary key but multiple foreign keys. This is true. A table is constrained to have only one primary key, which can consist of multiple columns, but can have several foreign keys referencing primary keys in other tables.
• C. Primary key and foreign key constraints can be defined at both the column and table level. True. Constraints can be defined inline with the column definition or separately at the end of the table definition.
• E. It is possible for child rows that have a foreign key to be deleted automatically from the child table at the time the parent row is deleted. This is also true if the foreign key is defined with the ON DELETE CASCADE option.

Options B, D, F, and G are incorrect:

• B is incorrect because if a parent row is deleted, the child rows cannot remain without violating the integrity unless the foreign key is defined with ON DELETE SET NULL or similar behavior.
• D is incorrect because both primary and foreign key constraints can be defined at both levels.
• F is incorrect as the names of the foreign key columns do not need to match the primary key column names.
• G is incorrect as a table can have multiple foreign keys.

In Oracle Database, when a transaction is not committed, the changes it makes are not visible to other sessions. This is due to Oracle's read consistency model, which means that Andrew will not be able to see the changes you have made until they are committed.

Option A is correct because, in Oracle, another session can modify rows that have not been locked by an uncommitted transaction.

Option C is incorrect because, as per Oracle's read consistency, the changes made by an uncommitted transaction are not visible to other users.

Option D is incorrect because Andrew can perform INSERT, UPDATE, or DELETE operations on the rows that you have not modified.

Option E is incorrect because, while Andrew will be able to SELECT from the table, he will still be able to modify rows that are not locked by your uncommitted update.

References:

• Oracle Documentation on Transactions: Transactions
• Oracle Documentation on Read Consistency: Read Consistency

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.

References:

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