1z0-071 Leak Questions

When you need to compare dates and calculate the difference in months between two dates, you use the MONTHS_BETWEEN function in Oracle SQL.

A. This statement is incorrect because MONTHS_BETWEEN takes the later date first, then the earlier date.

B. This statement is correct. The MONTHS_BETWEEN function returns the number of months between two dates. It takes the later date (in this case, SYSDATE) first and the earlier date (start_date) second. If the start_date is within the last 25 months, the result will be less than or equal to 25.

C. This statement is incorrect. The expression TO_NUMBER(start_date - SYSDATE) would not give the correct number of months between the dates.

D. This statement is incorrect. The ADD_MONTHS(start_date, 25) function adds 25 months to start_date, and comparing it to SYSDATE would show dates that are at least 25 months in the future.

A: True. TRUNCATE is generally faster than DELETE for removing all rows from a table because TRUNCATE is a DDL (Data Definition Language) operation that minimally logs the action and does not generate rollback information. TRUNCATE drops and re-creates the table, which is much quicker than deleting rows one by one as DELETE does, especially for large tables. Also, TRUNCATE does not fire triggers.

References:

• Oracle documentation specifies that TRUNCATE is faster because it doesn't generate redo logs for each row as DELETE would.
• TRUNCATE cannot be rolled back once executed, since it is a DDL command and does not generate rollback information as DML commands do.

A. True, a single table column can be included in both a unique index and a non-unique index, provided they are different indexes.

D. True, an invisible index is maintained during DML operations, which means it is updated when insert, update, or delete operations are performed on the table. However, it is not used by the optimizer by default for query execution unless explicitly hinted or the session is altered to consider invisible indexes.

E. True, an index can be specified in the CREATE TABLE statement using the INDEX clause to create an index on a specific column at the time of table creation.

B, C, and F are not correct: B. A descending index is simply an index that is sorted in descending order, not a type of function-based index. C. A DROP INDEX

The NULLIF and COALESCE functions in Oracle SQL serve specific purposes:

• Option A: Executes successfully. NULLIF returns NULL if the two arguments are equal; otherwise, it returns the first argument.
• Option B: Also correct. COALESCE returns the first non-null value among its arguments.
• Option C: This will not execute successfully because NULLIF requires both arguments to be of comparable data types. Comparing an integer to a character ('100' to 'A') is invalid.
• Option D: Executes successfully. NULLIF returns NULL because it compares NULL to a number, which is valid (though always yields NULL).
• Option E: Executes successfully. COALESCE accepts any data type as it returns the first non-null value, irrespective of type consistency among the arguments.