Ace Your 1z0-071 Oracle PL Exam

A. True, the owner of an object in Oracle automatically has all privileges on that object. This means they can perform any operation on the object, including SELECT, INSERT, UPDATE, DELETE, and so forth.C. True, the REFERENCES privilege must be granted explicitly by the table owner to another user or role to allow the grantee to define foreign key constraints that reference the primary or unique key of the owner's table. This is crucial in scenarios involving relational integrity constraints across tables owned by different users.

References:

• Oracle documentation on object privileges: Oracle Database SQL Language Reference
• Explanation of REFERENCES privilege: Oracle Database Security Guide

When using the GROUP BY clause, every column in the SELECT clause that is not an aggregate function must be included in the GROUP BY clause:

• A. SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id, join_date: This is a valid query because all non-aggregate columns in the SELECT list (dept_id and join_date) are included in the GROUP BY clause.

Queries B and C are invalid because they attempt to nest aggregate functions, which is not allowed. Query D is invalid because join_date is not included in the GROUP BY clause.

References:

• Oracle Database SQL Language Reference 12c, specifically the section on GROUP BY clause constraints.

The statement true regarding the CREATE TABLE command:

• C. The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match: The SQL command tries to create a table with columns prod_id, cust_id, and order_date, but the SELECT statement specifies columns prod_id, cust_id, and time_id. The mismatch in column names and the number of columns specified will prevent the table from being created.

Incorrect options:

• A: It is possible to specify a DEFAULT value in the column definition when creating a table with the CREATE TABLE AS SELECT syntax.
• B: Not all NOT NULL constraints (or any other constraints, for that matter) are automatically passed to the new table unless explicitly stated in the CREATE TABLE statement.
• D: FOREIGN KEY constraints are not automatically included when creating a table using the CREATE TABLE AS SELECT syntax; they would need to be added explicitly afterwards.

Regarding object privileges in an Oracle database:

• B. Delete rows from tables in any schema except sys: Object privileges include DELETE on tables, which can be granted by the owner of the table or a user with adequate privileges, excluding system schemas like SYS due to their critical role.
• E. Execute a procedure or function in another schema: EXECUTE is a specific object privilege that can be granted on procedures and functions, allowing users to run these objects in schemas other than their own.

Incorrect options:

• A: Creation of roles is related to system privileges, not object privileges.
• C: Setting default and temporary tablespaces for a user involves system-level operations, not object-level privileges.
• D: Creation of foreign key constraints involves referencing rights, which, while related, are not directly granted through object privileges but need appropriate REFERENCES permission.