Oracle 1Z0-071 Exam Dumps

Regarding single row functions in Oracle Database 12c:

C . CEIL: can be used for positive and negative numbers. This is true. The CEIL function rounds a number up to the nearest integer, and it works for both positive and negative values.

D . TRUNC: can be used with NUMBER and DATE values. This is correct. The TRUNC function can truncate numbers or dates to a specified precision.

Options A, B, and E are incorrect:

A is incorrect because CONCAT function typically concatenates two strings; to concatenate more, you must nest CONCAT calls or use the || operator.

B is incorrect because FLOOR returns the largest integer less than or equal to the specified number, not greater.

E is incorrect because MOD returns the remainder of a division operation, not the quotient.

To achieve the desired output for the query from the MEMBERS table:

C . ORDER BY 1, LNAME DESC: This clause correctly sorts the results first by the first column (city) in ascending order by default, and then by the alias LNAME (last_name) in descending order.

E . WHERE city LIKE '%AN%': This clause correctly filters the rows to include only those cities containing the string 'AN' anywhere in the city name, using the LIKE operator which is suitable for pattern matching.

Incorrect options:

A: This would order both columns in ascending order, which does not meet the requirement for last_name to be in descending order.

B: This misplaces the order priorities and uses explicit column names that contradict the sorting requirement specified.

D: Incorrect syntax for a LIKE clause; equality operator cannot be used with wildcards.

F: The IN operator is incorrectly used here with a pattern, which is not valid syntax.

In Oracle SQL, set operations like UNION, UNION ALL, INTERSECT, and MINUS can be used to combine results from different queries:

Option A:

Combining results using UNION ALL followed by ORDER BY will execute successfully because UNION ALL allows duplicate rows and ORDER BY can be used to sort the combined result set.

Option E:

Similar to option A, UNION ALL combines all rows from the two selects and allows ordering of the results.

Option G:

UNION combines the results from two queries and removes duplicates, and ORDER BY can be used to sort the final result set.

Options B, C, D, and F are incorrect because:

Option B: You cannot intersect different columns (ORDER_ID with INVOICE_ID).

Option C: Incorrect column names and syntax with ORDER BY.

Option D: ORDER BY cannot be used before a set operator like INTERSECT.

Option F: ORDER BY cannot be used directly after a MINUS operator without wrapping the MINUS operation in a subquery.

A . This statement is incorrect because TO_NUMBER expects a character string as an argument, not a date. Directly converting a date to a number without an intermediate conversion to a character string would result in an error. B. This statement is incorrect. Multiplying a date by a number does not make sense in SQL, and attempting to convert such an expression to a date will also result in an error. C. This statement is correct. Subtracting two dates in Oracle SQL results in the number of days between those dates, hence the result is a number. D. This statement is correct. Subtracting a number from a date in Oracle SQL will subtract that number of days from the date, returning another date. E. This statement is incorrect. As stated in C, subtracting a date from SYSDATE correctly returns the number of days between those two dates, not an error.

These concepts are explained in the Oracle Database SQL Language Reference, which details date arithmetic in SQL.

Self joins in Oracle Database 12c SQL have these characteristics:

Option D: They can use INNER JOIN and LEFT JOIN.

Self joins can indeed use various join types, including inner and left outer joins. A self join is a regular join, but the table is joined with itself.

Option E: They require table aliases.

When a table is joined to itself, aliases are required to distinguish between the different instances of the same table within the same query.

Options A, B, C, and F are incorrect:

Option A is incorrect because self joins can be non-equijoins as well.

Option B is incorrect because self joins do not require the NOT EXISTS operator. They may require a condition, but NOT EXISTS is not a necessity.

Option C is incorrect because a join condition is needed to relate the two instances of the same table in a self join.

Option F is incorrect for the same reason as B; the EXISTS operator is not a requirement for self joins.