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

When using the GROUP BY clause, every column in the SELECT clause that is not an aggregate function must be included in the GROUP BY clause:

A. SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id, join_date: This is a valid query because all non-aggregate columns in the SELECT list (dept_id and join_date) are included in the GROUP BY clause.

Queries B and C are invalid because they attempt to nest aggregate functions, which is not allowed. Query D is invalid because join_date is not included in the GROUP BY clause.

References:

Oracle Database SQL Language Reference 12c, specifically the section on GROUP BY clause constraints.

B. True. CURRENT_TIMESTAMP returns the current date and time in the session time zone, which is determined by the SESSIONTIMEZONE setting.

E. True. CURRENT_TIMESTAMP returns a value of the data type TIMESTAMP WITH TIME ZONE.

A and D are incorrect because CURRENT_TIMESTAMP reflects the session time zone, not DBTIMEZONE. C is incorrect because CURRENT_DATE returns the current date with no time component, whereas CURRENT_TIMESTAMP includes both date and time. F is incorrect because SYSTIMESTAMP returns the current date and time from the system clock in the time zone of the database server, which can differ from the session's time zone affecting CURRENT_TIMESTAMP.

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.

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.

When comparing the performance of different SQL join types and conditions, Oracle SQL optimizations generally ensure that performance is consistent across different syntactical forms of expressing the same logic:

Option A: False. The BETWEEN condition does not inherently perform worse than using >= and <=. Oracle's optimizer typically evaluates these conditions similarly, optimizing the underlying execution based on the data distribution and available indexes.

Option B: False. Oracle's optimizer is designed to handle both Oracle-specific join syntax and ANSI join syntax with equal competence. Performance differences would typically be negligible because the optimizer translates both into an optimal execution plan based on the same underlying mechanisms.

Option C: True. The join syntax used (whether Oracle's traditional syntax or ANSI standard syntax) generally does not affect the performance. Oracle's query optimizer is adept at translating different syntaxes into efficient execution plans.

Option D: False. The assertion that BETWEEN always performs better than >= and <= is incorrect. Performance depends more on factors like indexing, the specific data and distribution, and the Oracle optimizer's capabilities than on the mere choice of syntax.

Option E: False. While table aliases help improve query readability and can prevent ambiguity in SQL queries, they do not inherently improve performance. Their use is a best practice for code clarity and maintenance, not performance enhancement.

To format a date in Oracle SQL, TO_CHAR function is used to convert date types to string with a specified format:

Option A: SELECT TO_CHAR(SYSDATE, 'FMDAY, DD MONTH, YYYY') FROM DUAL;

This correctly applies the date format model 'FMDAY, DD MONTH, YYYY', which will display the date as requested: "MONDAY, 21 MAY, 2019". FM modifier is used to remove padding blanks or leading zeros.

Options B, C, and D do not achieve the desired output:

Option B is incorrect because TO_DATE converts a string to a date, not format a date.

Option C and D have incorrect format strings that do not match the required output format.

The correct way to compute the total annual compensation, which includes the monthly salary and the monthly commission (if any), is:

Option C: SELECT last_name, (monthly_salary * 12) + (monthly_salary * 12 * NVL(monthly_commission_pct, 0)) AS annual_comp FROM employees;

This statement takes the monthly salary and multiplies it by 12 to get the annual salary, and then adds the annual commission which is the monthly salary multiplied by the commission percentage (if any, else 0) and then by 12.

Options A, B, and D are incorrect because:

Option A: Does not handle the case where the commission percentage is NULL which would result in NULL for the entire expression when added to the monthly salary.

Option B: Does not consider that the commission percentage might be NULL which could lead to incorrect calculations (or NULL values if commission is NULL).

Option D: Incorrectly adds the monthly commission percentage directly to the annual salary without considering that the percentage needs to be applied to the salary.

In SQL, the correct usage of conversion functions is crucial when performing operations on dates. Oracle uses the TO_DATE function to convert a string to a date, and the TO_CHAR function to convert dates or numbers to strings.

Statement C is correct: WHERE order_date > TO_DATE('JUL 10 2018','MON DD YYYY'); is a proper use of the TO_DATE function. It converts the string 'JUL 10 2018' to a date type, with the format 'MON DD YYYY', which is then used to compare with the order_date.

Statements A, B, D, and E are incorrect or misuse conversion functions:

A is incorrect because TO_CHAR is used to convert dates or numbers to strings, not the other way around, and therefore should not be compared with order_date.

B is incorrect because order_date is of type DATE, and you should not compare a DATE with a string without converting it; the TO_CHAR here should be TO_DATE.

D is incorrect because it mixes TO_DATE and TO_CHAR in the same IN list, which should contain date types only.

E is incorrect because TO_DATE should take a string as an argument, not a date returned by ADD_MONTHS.

Set operators allow you to combine multiple queries into a single query. The key points for each option are:

A. An expression in the first SELECT list does not need to have a column alias; however, the column alias given to an expression in the first SELECT list is used as the column name of the output.

B. This statement is true. When CHAR columns of different lengths are combined with a set operator, Oracle will pad the shorter values with spaces to match the length of the longest value. This behavior is consistent with the SQL standard and Oracle's treatment of the CHAR datatype.

C. Oracle Database performs implicit conversion between compatible data types where necessary when using set operators. For example, a NUMBER can be implicitly converted to a VARCHAR2 and vice versa if the context requires it.

D. This is true. When multiple set operators are used in a single query, the INTERSECT operator takes precedence over the UNION and UNION ALL operators. This order of operation can be overridden by using parentheses.

E. Not all set operators work with all data types. For instance, LOB and LONG RAW data types cannot be used with set operators.

References:

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "Set Operators"

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "Data Types"