Oracle PL Changed 1z0-071 Questions

Single-row functions in SQL return one result per row, and the following statements are true:

A: The data type of the returned value can indeed be different from the data type of the argument provided to the function. For example, the TO_CHAR function can take a numeric input and return a character string.

B: Single-row functions can be nested within each other to any level that is supported by Oracle. This means you can have a function call as an argument to another function, and so on.

E: The argument to a single-row function can be a column name, a variable, a literal, or an expression. This flexibility allows these functions to be very powerful in SQL expressions.

The incorrect options are:

C: Single-row functions do not return a single result row per table; they return a result for each row that is processed.

D: They are not limited to accepting only one argument. Some functions, like NVL, accept multiple arguments.

F: They are not limited to use in the WHERE clause; single-row functions can be used in any part of a SQL statement, including SELECT and ORDER BY clauses.

References:

Oracle Documentation on Single-Row Functions: SQL Functions

A: 'CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 NOCACHE;' is correct for ensuring unique values without gaps as much as possible by not caching sequence numbers, which might otherwise be lost in a system crash.

B: CYCLE allows the sequence to restart when its max/min value is reached, which does not help with requirement 3 (duplicates are never permitted). Therefore, B is incorrect.

C: This lacks the necessary attributes like START WITH and INCREMENT BY which are crucial to defining a sequence. Thus, statement C is incorrect.

D: 'CREATE SEQUENCE emp_seq START WITH 1 CACHE;' might introduce gaps due to the caching of sequence numbers. This statement is somewhat contrary to requirement 5.

E: 'CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 CACHE;' will provide continuous unique values, but may include gaps when the cache is lost due to a restart, yet it is more efficient and still generally aligns with the requirements. Hence, statement E is considered correct.

F: This lacks detail and is too ambiguous, lacking the necessary parameters. Therefore, F is incorrect.

The function ADD_MONTHS(hire_date, 6) adds 6 months to the hire_date. The function NEXT_DAY(date, 'day_name') finds the date of the first specified day_name after the date given. In this case, 'MONDAY' is used to find the date of the first Monday after the hire_date plus 6 months.

Option A is correct as it accurately composes both ADD_MONTHS and NEXT_DAY functions to fulfill the requirement.

Options B, C, and D do not provide a valid use of the NEXT_DAY function, either because of incorrect syntax or incorrect logic in calculating the required date.

The Oracle Database SQL Language Reference for 12c specifies how these date functions should be used.

The ORDER BY clause in Oracle SQL is used to sort the result set of a query by one or more columns, and it comes with its own set of rules and behaviors:

D. Column aliases can be used In the ORDER BY clause: True, column aliases that are specified in the SELECT clause can be used in the ORDER BY clause to refer to the columns to sort the results by.

E. In a character sort, the values are case-sensitive: Oracle sorts results in case-sensitive order by default when using the ORDER BY clause with character columns, unless otherwise specified by the NLS_SORT parameter.

References:

Oracle Database SQL Language Reference 12c, which provides details on sorting query results using ORDER BY.