1z0-071 Exam Dumps : Oracle Database 12c SQL

Single-row functions in SQL operate on each row independently and can modify the returned value:

Option A: Incorrect. Single row functions can be used in multiple parts of a SELECT statement, including SELECT, WHERE, and ORDER BY clauses.

Option B: Incorrect. Single row functions can accept more than one argument, such as the CONCAT function, which can accept multiple string arguments.

Option C: Incorrect. They return one result for each row processed, not per table.

Option D: Correct. Single row functions can take various types of arguments including column names, literals, variables, and other expressions.

Option E: Correct. Functions can be nested within other functions, allowing complex expressions and calculations.

Option F: Correct. The data type of the result can differ from the arguments’ data types, such as the SUBSTR function returning a VARCHAR2 even when used on a number after converting it to a string.

The scenario requires prompting for column names and WHERE condition each time the query is executed, but only prompting for the table name once. This behavior is achievable through the use of substitution variables in SQL*Plus or SQL Developer:

A.

SELECT &col1, &col2 FROM &&table WHERE &condition;

This query uses & for columns and condition, which will prompt every time the query is run. &&table will prompt for the table name the first time and reuse the same value in the same session without re-prompting.

Options B, C, D, and E use incorrect syntax or variable types, leading to various errors or undesired behaviors, such as not maintaining the value of the table name across executions or incorrect usage of single quotes and comparison in SQL.

The ORDER BY clause is used in a SELECT statement to sort the returned rows by one or more columns.

A. This clause would attempt to order by ename as if it were a numeric column, which is not correct since ename is not numeric.

B. This is the correct answer. The NVL function replaces nulls with the specified value, in this case, 0. This clause sorts by comm, with nulls considered as 0 and appearing first, followed by the actual values, and then sorts by ename.

C. This clause is incorrect because it would place null values at the end, but in the output, rows with null comm appear at the beginning.

D. This clause would sort the comm in descending order with the nulls at the end, which is not consistent with the output shown.

References:

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "ORDER BY Clause"

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "NVL Function"

Please note that the correct formatting of SQL statements and clauses is crucial for the successful execution of queries.