Oracle Updated 1z0-071 Exam Questions and Answers by andrei

In Oracle Database 12c, regarding a full outer join:

• A. It includes rows that are returned by an inner join. This is true. A full outer join includes all rows from both joined tables, matching wherever possible. When there's a match in both tables (as with an inner join), these rows are included.
• D. It returns matched and unmatched rows from both tables being joined. This is correct and the essence of a full outer join. It combines the results of both left and right outer joins, showing all rows from both tables with matching rows from the opposite table where available.

Options B, C, and E are incorrect:

• B is incorrect because the Oracle join operator (+) is used for syntax in older versions and cannot implement a full outer join by using (+) on both sides. Proper syntax uses the FULL OUTER JOIN keyword.
• C is incorrect as a Cartesian product is the result of a cross join, not a full outer join.
• E is incorrect because it only describes the scenario of a full anti-join, not a full outer join.

Analyzing the given SQL statements on the ORDER_ITEMS table:

• D. Statement 2 may return multiple rows of output: Statement 2 groups the results by ORDER_ID, which means it calculates the maximum UNIT_PRICE * QUANTITY for each ORDER_ID, potentially returning multiple rows depending on the number of unique ORDER_IDs in the table.
• E. Statement 1 returns only one row of output: Statement 1 computes the maximum product of UNIT_PRICE and QUANTITY across all entries in the ORDER_ITEMS table, returning a single row with the maximum value.

Incorrect options:

• A: Since Statement 2 groups by ORDER_ID, it does not necessarily return just one row; it returns one row per ORDER_ID.
• B: These statements do not yield the same output; Statement 1 returns a single maximum value, while Statement 2 returns the maximum value per ORDER_ID.
• C: If either UNIT_PRICE or QUANTITY is NULL, the product for that row will be NULL, but the MAX function ignores NULL values in its calculation unless all rows are NULL, in which case it returns NULL.

To display unique promotion costs in each promotion category, the correct queries that can be used are:

• C. SELECT DISTINCT promo_category ||' has '|| promo_cost AS COSTS FROM promotions ORDER BY 1;This query concatenates the promo_category with the literal ' has ' and promo_cost, giving a unique string for each combination of promo_category and promo_cost, which is what we are interested in when we want to list unique costs per category.
• D. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;This query selects distinct combinations of promo_category and promo_cost, which is exactly what's required to display unique promotion costs in each category.

Options A, B, and E are incorrect:

• A is incorrect because it does not use the DISTINCT keyword to ensure uniqueness.
• B has incorrect syntax; the DISTINCT keyword should appear directly after SELECT and applies to all columns, not just one.
• E is syntactically incorrect and would not execute because the DISTINCT keyword is not used correctly. It should not appear twice in the SELECT clause.

A. This statement is incorrect. An INVISIBLE index is maintained during DML operations just like a VISIBLE index. The difference is that an INVISIBLE index is not used by the optimizer unless explicitly hinted. B. This statement is correct. When creating a table, you can define indexes on one or more columns as part of the table definition. C. This statement is incorrect. While a DROP INDEX statement will drop the index, it does not always prevent updates to the table. If the index is marked as unusable or is an invisible index, for example, updates can still be performed. D. This statement is correct. It is possible to have both a UNIQUE index and a non-unique index on the same column. The UNIQUE index enforces the uniqueness of column values, while the non-unique index does not. E. This statement is correct to some extent. Descending indexes are not function-based indexes per se, but they are indexes on which the data is sorted in descending order, as opposed to the default ascending order. However, descending indexes are conceptually related to function-based indexes because they alter the way the indexed data is stored. F. This statement is incorrect. The use of an index in query execution depends on the optimizer's decision, which is based on statistics and the cost associated with using the index. There are situations where the optimizer may choose a full table scan even if there is an index on the filter column.

References can be found in the Oracle Database Concepts Guide and the SQL Language Reference documentation, which detail the behavior of indexes and how they are managed within the Oracle database.