Oracle Oracle PL 1z0-071 New Questions

Roles and privileges in Oracle manage access and capabilities within the database:

Option A: False. Roles are not "owned" in the traditional sense by the user who created them. They exist independently within the Oracle database and are assigned to users.

Option B: False. System privileges can be very granular, affecting specific types of operations or database objects, not always the entire database.

Option C: False. Roles are not owned by the SYS schema but are managed by database security and can be created by any user with sufficient privileges.

Option D: True. A role can indeed contain a combination of several privileges, including other roles, allowing for flexible and layered security configurations.

Option E: True. By default, a user has all object privileges for objects they own (i.e., objects in their schema).

Option F: False. PUBLIC is a special designation that applies to all users; individual privileges granted to PUBLIC cannot be revoked from a single user without revoking them from all users.

Option G: True. PUBLIC is a role granted by default to every user in an Oracle database, providing basic privileges necessary for general usability of the database.

D: True. This query selects the first name and calculates the due amount as 5% of the credit limit from the customers table where the income level is not null and the credit limit is also not null. The IS NOT NULL operator is used to check for non-null values correctly.

The IS NOT NULL operator is the correct way to check for non-null values in SQL. The <> NULL and != NULL comparisons are incorrect because NULL is not a value that can be compared using these operators; it represents the absence of a value.

There is no due_amount column in the customers table according to the structure provided, so any WHERE clause referencing due_amount is irrelevant and incorrect. The due_amount is a derived column in the SELECT clause.

The correct syntax to check for non-null values is IS NOT NULL, which is used in the WHERE clause of the correct answer.

References:

The use of IS NOT NULL in the WHERE clause to filter out null values is documented in Oracle's SQL reference.

NULL comparisons must be done with IS NULL or IS NOT NULL for correct logical evaluation in SQL.

C: True. A UNIQUE constraint in Oracle SQL allows for the inclusion of NULL values; specifically, it permits multiple NULLs in a column or set of columns but ensures that all non-NULL values are unique.

D: True. A PRIMARY KEY constraint can indeed be added to a table after it has been created and even after it has been populated, as long as the existing data does not violate the primary key constraint rules (i.e., all values must be unique and not NULL).

F: True. A UNIQUE constraint can utilize a pre-existing index on the columns it covers. If a suitable index already exists, Oracle can use this index to enforce the constraint, optimizing performance and resource utilization.

G: True. Columns that are part of a FOREIGN KEY constraint can contain NULL values. This is permissible under SQL standards and Oracle implementation, as a NULL foreign key value is considered to not refer to any row in the referenced table and thus does not violate referential integrity.

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.