Download Full Version 1z0-071 Oracle Exam

In Oracle, *_TABLES views provide information about tables.

B. USER_TABLES displays all tables owned by the current user, making this statement true. No additional privileges are required to see your own tables.

D. ALL_TABLES displays all tables that the current user has access to, either through direct ownership or through privileges, making this statement true.

A, C, E, and F are incorrect. Specifically:

• A and E are incorrect because you do not need ANY TABLE system privileges to view tables in DBA_TABLES or ALL_TABLES; you need the SELECT_CATALOG_ROLE or equivalent privileges.
• C is incorrect because as a user, you do not need additional privileges to see your own tables in USER_TABLES.
• F is incorrect because not all users can query DBA_TABLES; this requires specific privileges or roles.

References:

• Oracle Database Reference, 12c Release 1 (12.1): "Static Data Dictionary Views"

A. True - The TO_CHAR function is used correctly here to convert the numeric value to a string, and NVL handles the case where cust_credit_limit might be NULL. The expression inside NVL computes 15% of the credit limit or displays 'Not Available' if the credit limit is NULL. The syntax is correct.

B. False - The NVL2 function requires three parameters: the expression to check for NULL, the value to return if it's not NULL, and the value to return if it is NULL. The given usage lacks the required parameters and syntax.

C. False - The NVL function expects both parameters to be of the same data type. Since the second parameter 'Not Available' is a string, it causes a data type conflict with the numerical result of the first parameter.

D. False - The keyword SELECT is misspelled as SLECT, making the syntax incorrect.

E. True - This query uses NVL2 correctly by checking if cust_credit_limit is not NULL, then applying TO_CHAR to compute 15% of it and converting it to string, or returning 'NOT Available' if it is NULL. The syntax and function usage are correct.

In Oracle SQL, substitution variables (&1) can be used to dynamically replace a value in a SQL statement at runtime. To avoid being prompted for a value when executing a query that includes a substitution variable:

• A. Use the DEFINE command before executing the query: By setting a value for a substitution variable using the DEFINE command, Oracle will use this value instead of prompting the user for input.
• D. Execute the SET VERIFY OFF command before executing the query: The SET VERIFY OFF command suppresses the prompt for substitution variables by turning off the verification feature. This makes Oracle use the currently defined value for the variable.

References:

• Oracle Database SQL Language Reference 12c, particularly the sections on substitution variables, the DEFINE command, and the SET command.

In the SQL statement provided, the error is caused by the GROUP BY clause. In Oracle SQL, when using GROUP BY, the expression in the GROUP BY clause must match exactly the expression in the SELECT list. The GROUP BY clause should reference the column name dept_no as mentioned in the SELECT list, not the alias department_id which is defined within the same SELECT statement.

The corrected SQL statement should be:

SELECT dept_no AS department_id, MAX(salary) As max_sal FROM employees WHERE salary > 10000 GROUP BY dept_no -- Changed from 'department_id' to 'dept_no' ORDER BY max_sal;

References:

• Oracle Documentation on GROUP BY clause: GROUP BY Clause