Oracle 1z0-071 Exam With Confidence Using Practice Dumps

The WITH GRANT OPTION clause in Oracle SQL allows the grantee to grant the privilege they have received to another user or role.

E. It cannot be used to pass on privileges to PUBLIC by the grantee: The WITH GRANT OPTION does not allow the grantee to pass on privileges to PUBLIC. Only the object's owner or a user with the GRANT ANY OBJECT PRIVILEGE system privilege can grant privileges to PUBLIC.

F. It can be used to pass on privileges to other users by the grantee: This is true. When a user receives privileges with the WITH GRANT OPTION, they can grant that privilege to another user or role.

References:

Oracle Database SQL Language Reference 12c, specifically sections on user privileges and the WITH GRANT OPTION.

C: True. To display the number of products whose PROD_LIST_PRICE is more than the average PROD_LIST_PRICE, you would need to use a subquery to first calculate the average PROD_LIST_PRICE and then use that result to compare each product’s list price to the average.

E: True. Displaying products whose PROD_MIN_PRICE is more than the average PROD_LIST_PRICE of all products and whose status is orderable would require a subquery. The subquery would be used to determine the average PROD_LIST_PRICE, and then this average would be used in the outer query to filter the products accordingly.

Subqueries are necessary when the computation of a value relies on an aggregate or a result that must be obtained separately from the main query, and cannot be derived in a single level of query execution.

References:Oracle's SQL documentation provides guidelines for using subqueries in scenarios where an inner query's result is needed to complete the processing of an outer query.

In Oracle Database 12c, when dealing with NULL values in arithmetic expressions, the functions NVL, NVL2, and equivalent techniques using NULLIF or arithmetic operations with NULL can be crucial.

Option A: SELECT * FROM employees WHERE salary + NULLIF(commission, 0) >= 20000;

NULLIF(value1, value2) returns NULL if value1 is equal to value2, otherwise it returns value1. In this case, if commission is NULL, it treats it as 0.

This query correctly adds salary and commission (assuming 0 when commission is NULL) and checks if the total is at least 20000.

Option B: SELECT * FROM employees WHERE salary + NVL2(commission, commission, 0) >= 20000;

NVL2(expr1, expr2, expr3) returns expr2 if expr1 is not NULL; otherwise, it returns expr3. Here, if commission is not NULL, it uses commission, otherwise 0.

This condition correctly calculates salary + commission (assuming commission as 0 if it is NULL) and checks if the total is at least 20000.

Option D: SELECT * FROM employees WHERE salary + NVL(commission, 0) >= 20000;

NVL(expr1, expr2) returns expr2 if expr1 is NULL; otherwise, it returns expr1. This query treats commission as 0 if it is NULL.

It correctly sums salary and commission and compares the result against 20000.

Options C, E, and F contain either syntax errors or logical errors in handling NULLs and comparisons.