Oracle Updated 1z0-071 Exam Questions and Answers by kaleb

Regarding privileges in Oracle:

• Option A: Dropping user FINANCE will automatically revoke SELECT on SCOTT.EMP from user FIN_CLERK.
• Option B: Revoking SELECT on SCOTT.EMP from user FINANCE will also revoke the privilege from user FIN_CLERK.

Options C, D, and E are incorrect because:

• Option C: User FINANCE can grant privileges it owns, but CREATE SESSION is a system privilege; unless FINANCE has the 'WITH ADMIN OPTION' for CREATE SESSION, it cannot grant this privilege.
• Option D: User FIN_CLERK can only grant privileges to others if it has been given those privileges with the 'WITH GRANT OPTION' and it was not given in this case.
• Option E: User FINANCE can grant privileges on SCOTT.EMP to other users only if it has those privileges with 'WITH GRANT OPTION'. If the privilege was just SELECT with GRANT OPTION, it cannot grant ALL privileges.

The INTERSECT operator in SQL returns the results that are common to both of the SELECT statements. It functions similarly to a set intersection in mathematics. When comparing rows for the INTERSECT operation, Oracle Database uses all the expressions in the SELECT lists to derive the result set. NULL values are considered equal for the INTERSECT operator.

Evaluating the provided data from the ORDERS and INVOICES tables, let's see which rows have the same ORDER_ID and ORDER_DATE in both tables:

• A: Order ID 3 has a NULL order date in the ORDERS table and does not match with any row in the INVOICES table, so it will not be returned.
• B: Order ID 2 has a NULL order date in the ORDERS table but has a non-NULL order date in the INVOICES table, so it will not be returned.
• C: Order ID 1 has a NULL order date in both tables, but INTERSECT considers NULLs as equal, so this will be returned.
• D: Order ID 5 has a date of 01-MAR-2019 in the ORDERS table and 01-APR-2019 in the INVOICES table, so it will not be returned since the dates do not match.
• E: Order ID 4 has a date of 01-FEB-2019 in both tables, so this row will be returned as it matches in both.
• F: Order ID 3 has a NULL order date in the ORDERS table but has 01-JAN-2019 in the INVOICES table, so it will not be returned.

Based on this analysis, the query will return:

• Order ID 1 with a NULL order date.
• Order ID 4 with an order date of 01-FEB-2019.

So the correct answer is:

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.

References:

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

The query uses two functions: ROUND and TRUNC. The ROUND function will round the number 156.00 to the nearest hundred because of the -2 which specifies the number of decimal places to round to. This will result in 200. Then the TRUNC function truncates this number to the nearest 10, due to the -1 argument, which will give us 200 as the result since truncation does not change the rounded value in this case.

• A. 16 (Incorrect)
• B. 160 (Incorrect)
• C. 150 (Incorrect)
• D. 200 (Incorrect)
• E. 100 (Incorrect)