Oracle 1Z0-071 Exam Dumps

In Oracle 12c SQL, the DELETE statement is used to remove rows from a table based on a condition. Given the constraints and the information provided, let's evaluate the options:

A . DELETE FROM orders WHERE order_total<1000;: This statement is correctly formatted and will delete rows from the ORDERS table where ORDER_TOTAL is less than 1000. If there is a DELETE CASCADE constraint, corresponding rows in the ORDER_ITEMS table will also be deleted.

B . DELETE * FROM orders WHERE order_total<1000;: This syntax is incorrect. The asterisk (*) is not used in the DELETE statement.

C . DELETE orders WHERE order_total<1000;: This statement is also correctly formatted and is a shorthand version of the DELETE statement without the FROM clause.

D . DELETE FROM orders;: This statement will delete all rows from the ORDERS table, and with DELETE CASCADE, it will also delete all related rows in the ORDER_ITEMS table.

E . DELETE order_id FROM orders WHERE order_total<1000;: This syntax is incorrect because you cannot specify a column after the DELETE keyword.

Oracle Database SQL Language Reference 12c Release 1 (12.1), DELETE Statement

B . SELECT product_id, (unit_price * 0.15 / (4.75 + 552.25)) FROM products; C. SELECT product_id, (expiry_date - delivery_date) * 2 FROM products; E. SELECT product_id, unit_price, unit_price + surcharge FROM products;

Comprehensive and Detailed Explanation WITH all Reference:

A . This is invalid because 'Discount' is a string literal and cannot be used without quotes in an arithmetic operation. Also, there is a typo in unit_pricer, and 'discount' is not a defined column in the table. B. This is valid. It shows a mathematical calculation with unit_price, which is of NUMBER type. Division and multiplication are valid operations on numbers. C. This is valid. The difference between two DATE values results in the number of days between them, and multiplying this value by a number is a valid operation. D. This is invalid because expiry_date is of DATE type and cannot be multiplied by a number. Also, there's a typo: 'SPLECT' should be 'SELECT'. E. This is valid. Both unit_price and surcharge are NUMBER types, and adding them together is a valid operation. F. This is invalid because concatenation operator || is used between a number (unit_price) and a string literal 'Discount', which is not enclosed in single quotes, and 'discount' is not a defined column in the table.

In SQL, arithmetic operations on numbers and date arithmetic are valid expressions. Concatenation is also a valid expression when used correctly between string values or literals. Operations that involve date types should not include multiplication or division by numbers directly without a proper interval type in Oracle SQL.

These rules are detailed in the Oracle Database SQL Language Reference, where expressions, datatype precedence, and operations are defined.

For the provided UNION ALL query:

Option C: It will return rows from both SELECT statements including duplicate rows.

UNION ALL is used to combine the results of two SELECT statements and does not eliminate duplicates.

Options A, B, and D are incorrect because:

Option A: UNION ALL does not eliminate duplicate rows, unlike UNION.

Option B: This would be true for INTERSECT, not UNION ALL.

Option D: This would be true for EXCEPT or MINUS, not UNION ALL.

A: This statement is correct. It uses the TO_DATE function with a proper date string, format mask, and NLS_DATE_LANGUAGE setting.

B: This statement will not execute successfully because the syntax of the TO_CHAR function is incorrect. The date string should be a DATE data type when used with TO_CHAR, and the format mask and NLS parameter are incorrectly specified.

C: This statement will not execute successfully because it is redundant to use TO_CHAR and then immediately convert it back to a date with TO_DATE without specifying a proper format mask.

D: This statement is correct. It converts a string to a DATE using TO_DATE and then back to a string with TO_CHAR, without specifying a format which defaults to the session's NLS_DATE_FORMAT.

E: This statement will not execute successfully because TO_CHAR is used incorrectly; the first argument must be of DATE data type when you're using a date format mask.

Reference for the TO_DATE and TO_CHAR functions and their proper usage can be found in the Oracle Database SQL Language Reference 12c documentation.

Regarding the properties and capabilities of virtual columns in Oracle Database:

C . They can be indexed: In Oracle, virtual columns can indeed be indexed. This allows for performance optimization on queries that utilize these columns, despite the fact that their values are derived and not stored physically.

D . They cannot have a data type explicitly specified: The data type of a virtual column is derived from the expression used to define it. Therefore, it is not specified explicitly but is inferred from the expression itself.

Incorrect options:

A: Virtual columns cannot be referenced in the WHERE clause of UPDATE or DELETE statements because they are not stored and cannot be individually modified.

B: Virtual columns cannot be referenced in the SET clause of an UPDATE statement since they are derived from other column values and cannot be updated directly.

E: Virtual columns cannot reference other virtual columns in their expressions due to the potential for recursive and complex dependencies.