All 1z0-071 Test Inside Oracle Questions

The correct answers regarding the MERGE statement are:

• B. The WHEN NOT MATCHED clause can be used to specify the inserts to be performed. This is true. When a row from the source does not match any row in the target, the WHEN NOT MATCHED clause is where you specify the insert operation.
• E. The WHEN MATCHED clause can be used to specify the updates to be performed. This is true as well. The WHEN MATCHED clause is where you specify the update (or delete) operation to be performed when the source and target rows match.

Options A, C, and D are incorrect:

• A is incorrect because WHEN NOT MATCHED does not handle deletions, it is for inserts.
• C is incorrect as inserts are not specified in the WHEN MATCHED clause but in the WHEN NOT MATCHED clause.
• D is incorrect because updates are specified in the WHEN MATCHED clause, not the WHEN NOT MATCHED clause.

The statement that will fail is B. In Oracle SQL, the WHERE clause cannot contain aggregate functions directly. The HAVING clause is used instead to apply conditions that involve aggregates, but it is applied after the GROUP BY clause.

A. While the HAVING clause is used before the GROUP BY clause which is not standard SQL syntax, Oracle SQL may still execute it successfully due to its flexibility in syntax.

B. This statement will fail because it uses an aggregate function, COUNT(*), in the WHERE clause, which is not allowed. The correct approach is to use the HAVING clause to filter the results of aggregate functions after the GROUP BY clause.

C. This statement is correct; it places the HAVING clause after the GROUP BY clause, applying the filter on the aggregated count.

D. This is a correctly constructed statement with the WHERE clause filtering individual records before grouping, and the HAVING clause filtering groups based on the aggregate function.

References:

• Oracle Database SQL Language Reference, 12c Release 1 (12.1): "WHERE Clause"
• Oracle Database SQL Language Reference, 12c Release 1 (12.1): "HAVING Clause"

When joining tables in Oracle Database 12c, the USING clause has specific behaviors:

• Option C: It is used to specify an equijoin of columns that have the same name in both tables.
• Option D: It can never be used with a full outer join.

Options A, B, and E are incorrect:

• Option A is incorrect because when using the USING clause, you do not need to qualify the columns with table names or aliases in the select list; Oracle assumes that they are the same in both tables.
• Option B is incorrect because the USING clause can be used with natural joins.
• Option E is incorrect as the USING clause is not meant to specify explicit join conditions with operators; it's specifically for equijoins on columns of the same name.

To calculate the date from a given base date with intervals, Oracle allows you to add or subtract intervals from dates. Since the NLS_DATE_FORMAT is set to DD-MM-YYYY, the output is expected to be in that format.

Option B seems to calculate a date that is 3 months and 7 days ahead of October 29, 2019, and then subtracts 360 seconds (which is 6 minutes), resulting in a time that is still within the same day.

Here's how the calculation in option B would work out:

• Start date: 29-10-2019
• Add 3 months: 29-01-2020
• Add 7 days: 05-02-2020
• Subtract 360 seconds: Since it's only a few minutes, the date remains 05-02-2020.

However, this does not match the provided result of 02-JAN-2020. We would need to consider the exact amount of time being subtracted or added to find the correct answer.

But upon reviewing the options, they all have various syntax errors such as a missing TO_CHAR function, incorrect quotes, and date formats not matching the session's NLS_DATE_FORMAT. Therefore, we would need to correct these issues to find the right answer.