1z0-071 Questions Bank

The CONCAT function and FLOOR function in Oracle SQL have specific behaviors:

A. CONCAT function joins two or more character strings into one string, making this statement true.

B. FLOOR function returns the largest integer that is less than or equal to the specified number, making this statement true.

C. While CONCAT can join two strings together, this statement is incomplete as it can join more than two.

D. INSTR can find the offset of a substring within a string, not just a single character.

E. INSTR starts searching the string from position 1 in Oracle SQL, not position 0.

F. FLOOR does return the largest integer less than or equal to the specified number, but it can be any integer, not just positive ones.

References:

• Oracle Database SQL Language Reference, 12c Release 1 (12.1): "Single-Row Functions"

• A: CURRENT_DATE returns the current date in the session time zone, not with a TIMESTAMP WITH TIME ZONE data type.
• B: CURRENT_TIMESTAMP returns the current date and time in the session time zone with a TIMESTAMP WITH TIME ZONE data type. This is correct.
• C: SYSDATE returns the current date and time from the operating system of the database server in the DATE data type.
• D: LOCALTIMESTAMP returns the current date and time in the session time zone with a TIMESTAMP data type, without the TIME ZONE.

The TIMESTAMP WITH TIME ZONE data type and relevant functions are documented in the Oracle Database SQL Language Reference 12c.

For the sorting of output in compound queries (INTERSECT, UNION, etc.):

• D. The first column in the first SELECT of the compound query: By default, Oracle does not automatically sort the results of SET operations unless an ORDER BY clause is explicitly stated. However, if an ORDER BY is implied or specified without explicit columns, the default sorting would logically involve the first column specified in the first SELECT statement of the compound query.

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.