Oracle Updated 1z0-071 Exam Questions and Answers by lily-mae

The actions possible using the ALTER TABLE command:

• E. Rename a table: The ALTER TABLE command can be used to rename a table, although the syntax typically involves RENAME TO as part of table management.
• F. Enable or disable constraints on a table: This is a standard use of the ALTER TABLE command, allowing for constraints to be enabled or disabled, which is critical for managing table integrity during data loads or maintenance.

Incorrect options:

• A: You cannot drop pseudo columns from a table as they are not explicitly defined or managed like regular columns.
• B: The ALTER TABLE command does not directly restrict DML statements; this would typically be managed through security roles or other database mechanisms.
• C: Dropping all columns from a table simultaneously is not possible; a table must retain at least one column.
• D: Locking a set of rows is performed with SQL commands like SELECT FOR UPDATE, not with ALTER TABLE.

In Oracle Database 12c, the behavior of the ORDER BY clause is guided by several rules:

• Option B: An ORDER BY clause can perform a binary sort.
• Option C: An ORDER BY clause can perform a linguistic sort.
• Option D: By default, an ORDER BY clause sorts rows in ascending order.

Options A and E are incorrect:

• Option A is not universally true; the position of NULL values in the sort order can be controlled by settings or specific SQL syntax (NULLS FIRST or NULLS LAST).
• Option E is incorrect as the HAVING clause filters groups after data has been grouped by the GROUP BY clause and cannot be logically placed before ORDER BY in processing order.

B: True. The Oracle Database MERGE statement can use a subquery that involves views to generate the source data for the merge operation. This allows for greater flexibility in specifying the data to be merged.

C: True. MERGE can conditionally combine rows from one or more source tables (or views, or the results of a subquery) to insert or update rows in a single target table based on whether or not a match exists.

D: True. MERGE can indeed use subqueries to produce source rows. The source data for a merge operation can be a table, view, or the result of a subquery.

The MERGE statement is a powerful SQL operation in Oracle that allows for conditional INSERT or UPDATE operations in one statement, often referred to as an "upsert" operation. It can handle complex scenarios where the decision to insert or update is based on whether the data matches between the target and source datasets.

References:Oracle SQL documentation on the MERGE statement provides information on how it can use different data sources, including subqueries and views, and perform conditional operations on the target table.

The correct statement for computing the total annual compensation for each employee is option C. This is because the monthly commission is a percentage of the monthly salary (indicated by the column name MONTHLY_COMMISSION_PCT). To calculate the annual compensation, we need to calculate the annual salary (which is monthly_salary * 12) and add the total annual commission to it.

Here's the breakdown of the correct statement, option C:

• (monthly_salary * 12) computes the total salary for the year.
• NVL(monthly_commission_pct, 0) replaces NULL values in the monthly_commission_pct column with 0, ensuring that the commission is only added if it exists.
• (monthly_salary * 12 * NVL(monthly_commission_pct, 0)) computes the annual commission by first determining the monthly commission (which is a percentage of the monthly salary), and then multiplying it by 12 to get the annual commission.
• Finally, (monthly_salary * 12) + (monthly_salary * 12 * NVL(monthly_commission_pct, 0)) adds the annual salary and the annual commission to get the total annual compensation.

The other options are incorrect:

• Option A is incorrect because it adds the monthly_commission_pct directly to the monthly_salary, which does not consider that monthly_commission_pct is a percentage.
• Option B is incorrect because it adds the commission percentage directly without first applying it to the monthly salary.
• Option D is incorrect because it does not handle the NULL values in the commission column, which would result in a NULL total annual compensation whenever the monthly_comission_pct is NULL.

References:

• Oracle Documentation on NVL function: NVL
• Oracle Documentation on Numeric Literals: Numeric Literals