1z0-071 Exam Dumps - Oracle PL Questions and Answers

In Oracle Database 12c SQL, regarding date/time functions and considering a session where NLS_DATE_FORMAT is set to DD-MON-YYYY SH24:MI:SS:

C. CURRENT_DATE returns the current date and time as per the session time zone. This is correct as CURRENT_DATE returns the current date and time in the time zone of the current SQL session, as set by the ALTER SESSION command.

D. SYSDATE and CURRENT_DATE return the current date and time set for the operating system of the database server. This is partially correct. SYSDATE returns the current date and time from the operating system of the database server. However, CURRENT_DATE returns the date and time set for the client's operating system environment, adjusted to the session time zone.

Options A, B, E, and F are incorrect based on Oracle's documentation:

A is incorrect because SYSDATE is independent of the NLS_DATE_FORMAT setting.

B is incorrect because CURRENT_TIMESTAMP includes time zone information, which can differ from CURRENT_DATE.

E is incorrect because CURRENT_TIMESTAMP differs from SYSDATE by including fractional seconds and time zone.

F is incorrect as SYSDATE can be queried in any SELECT statement, not just from DUAL.

In Oracle SQL, aggregation functions such as AVG and MAX cannot be nested directly inside each other and must be used in conjunction with GROUP BY on the column(s) that are not included in the aggregate function. Also, the HAVING clause filters groups after aggregation is applied.

A. This query will not execute successfully because it improperly nests MAX inside AVG. Oracle SQL does not allow this type of nested aggregation without a subquery.

B. This query will not execute successfully for the same reason as A, it improperly nests MAX inside AVG.

C. Similar to A and B, this query improperly nests SUM inside MAX, which is not allowed without a subquery.

D. This query will execute successfully. It filters rows based on the HIRE_DATE using a correct date format (assuming '9' refers to '09' or '1999' due to the NLS_DATE_FORMAT being 'DD-MON-RR'), then groups the remaining rows by DEPT_ID and calculates the sum of SALARY for each department.

E. This query will not execute successfully because it improperly nests MAX inside AVG without a subquery, and it incorrectly attempts to GROUP BY SALARY, which is already being aggregated.

References:

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "Aggregate Functions"

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

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

C: True. A SQL statement may include multiple single row subqueries in different parts of the statement, such as in the SELECT list, WHERE clause, or HAVING clause. Each subquery must independently satisfy the requirement of returning a single row to avoid runtime errors.

D: True. Single row subqueries can be used in the HAVING clause. This allows for filtering groups based on conditions evaluated against individual or aggregated values returned by the subquery. The subquery must return a single value to be valid in this context.

E: True. Single row subqueries are often placed on the right side of the comparison operator in a SQL condition. This positioning is typical because the left side often references a column or an expression related to the main query, while the subquery on the right side dynamically provides a value for comparison.

Non-equi joins are joins where the join condition is based on something other than equality. In Oracle SQL, you can perform non-equi joins using various clauses:

A. The ON clause can be used: True, the ON clause can specify any condition for the join, including non-equijoins. It is not limited to equijoins.

E. The Oracle join syntax can be used: The traditional Oracle join syntax, which uses the WHERE clause to specify the join condition, can be used for all types of joins, including non-equijoins.

References:

Oracle Database SQL Language Reference 12c, especially the sections on join clauses including the ON clause and Oracle's proprietary join syntax.