Pass the Oracle PL 1z0-071 Questions and answers with ExamsMirror

When the TRUNCATE TABLE command is used:

C. It retains the indexes defined on the table. This is true. The TRUNCATE TABLE operation does not drop the indexes on the table; it only removes all rows from the table.

D. It retains the integrity constraints defined on the table. This is correct. The TRUNCATE TABLE operation does not affect the integrity constraints; it retains them.

Options A, B, E, and F are incorrect:

A is incorrect because the TRUNCATE TABLE operation cannot be rolled back as it is a DDL operation and not a DML operation like DELETE.

B is incorrect because TRUNCATE TABLE does not drop any triggers; they remain in place.

E is incorrect as TRUNCATE TABLE may or may not release the space depending on whether the REUSE STORAGE clause is used.

F is incorrect because FLASHBACK TABLE cannot be used to retrieve data after a TRUNCATE TABLE operation since it does not generate redo logs that FLASHBACK relies upon.

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.

Regarding the SESSION_PRIVS dictionary view:

C. It contains the current system privileges available in the user session: This view displays the system privileges granted to the current user, making it a tool for understanding which actions the user is allowed to perform in the current session.

Incorrect options:

A and B: SESSION_PRIVS does not contain information about privileges granted to or by other users; it specifically lists the privileges available to the current session.

D: It does not contain object privileges; these are contained in other views such as USER_TAB_PRIVS.

The WITH GRANT OPTION clause in Oracle SQL allows the grantee to grant the privilege they have received to another user or role.

E. It cannot be used to pass on privileges to PUBLIC by the grantee: The WITH GRANT OPTION does not allow the grantee to pass on privileges to PUBLIC. Only the object's owner or a user with the GRANT ANY OBJECT PRIVILEGE system privilege can grant privileges to PUBLIC.

F. It can be used to pass on privileges to other users by the grantee: This is true. When a user receives privileges with the WITH GRANT OPTION, they can grant that privilege to another user or role.

References:

Oracle Database SQL Language Reference 12c, specifically sections on user privileges and the WITH GRANT OPTION.

C: True. In Oracle Database 12c, character sorts are case-sensitive by default, meaning that it differentiates between uppercase and lowercase letters when sorting data. This behavior aligns with the binary collation rules typically used, unless explicitly overridden by using different collation settings.

D: True. Column aliases can indeed be used in the ORDER BY clause in Oracle SQL. This allows for more readable and maintainable code, as you can define a column alias in the SELECT list and refer to that alias in the ORDER BY clause. This usage promotes clarity, especially when dealing with complex calculations or function applications in the select list.

When performing DML operations on a view without INSTEAD OF triggers:

Option C: Views cannot be used to add rows to an underlying table if the table has columns with NOT NULL constraints lacking default values which are not referenced in the defining query of the view.

This is true because the view would not provide values for NOT NULL columns without defaults, leading to an error.

Option D: Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains the DISTINCT keyword.

Using DISTINCT in the view’s defining query can make the view non-updatable, as it may aggregate multiple rows into one.

Option E: Delete statements can always be done on a table through a view.

Deletes through a view are typically unrestricted as long as the view does not involve aggregates, DISTINCT, or similar constructs that would logically preclude determining which underlying rows should be deleted.

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 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.

B: True. Multiple table insert statements, specifically the conditional INSERT ALL, can transform a single row from the source table into multiple rows in one or more target tables depending on the conditions specified in the WHEN clauses.

E: True. The unconditional INSERT ALL statement will insert rows into multiple target tables without any conditions. However, it does not require the same number of columns in both the source and target tables. The INSERT ALL syntax allows you to specify different columns for each target table into which rows will be inserted.

Multiple table insert operations allow for complex insert scenarios, where based on the data, one can insert into different tables or multiple times into the same table based on different conditions.

References:Oracle SQL reference details the use of INSERT ALL and INSERT FIRST clauses for inserting into multiple tables based on conditions specified.

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.