1z0-071 Dumps with Practice Exam Questions Answers

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.

Roles are named collections of privileges in Oracle databases.

A. False. Roles cannot be assigned to other roles using the ALTER ROLE statement.

B. False. Roles can be assigned to both users and other roles.

C. True. Roles are assigned to users using the ALTER USER statement, but this is not the only method.

D. True. A single role can be assigned to multiple users, simplifying the management of user privileges.

E. True. A single user can be assigned multiple roles.

F. False. Privileges are not assigned to a role using the ALTER ROLE statement.

G. True. Privileges are assigned to a role using the GRANT statement.

C: True. A SQL statement may include multiple single row subqueries in different parts of the statement, such as in the SELECT list, WHERE clause, or HAVING clause. Each subquery must independently satisfy the requirement of returning a single row to avoid runtime errors.

D: True. Single row subqueries can be used in the HAVING clause. This allows for filtering groups based on conditions evaluated against individual or aggregated values returned by the subquery. The subquery must return a single value to be valid in this context.

E: True. Single row subqueries are often placed on the right side of the comparison operator in a SQL condition. This positioning is typical because the left side often references a column or an expression related to the main query, while the subquery on the right side dynamically provides a value for comparison.

Regarding the provided SQL INTERSECT query:

D. 1 ROW: The INTERSECT operation will compare the two SELECT statements and return rows that are identical between them. Both queries are designed to return the same values ('1' for the ID, 'John' for the name, and NULL for the commission), hence one row that is identical between the two datasets will be returned.

Incorrect options:

A: Only one identical row exists between the two datasets.

B: There is an identical row; thus, it is not zero.

C: There is no error in the syntax or execution of the query.

To calculate the total annual compensation, you need to sum the annual salary with the annual commission. The annual commission is the monthly commission percentage times the monthly salary times 12 (months). The NVL function is used to replace NULL with 0 for commission percentage when calculating the commission.

A: This statement is incorrect because it does not handle NULL values for 'monthly_commission_pct', which will result in NULL for the entire expression if 'monthly_commission_pct' is NULL.

B: This statement is syntactically incorrect because it lacks commas and proper parentheses to separate the column name 'last_ name' from the calculation.

C: This is the correct statement as it uses NVL to replace NULL with 0 and calculates the total annual compensation correctly.

D: This statement is incorrect because, like in option A, it does not handle NULL values in 'monthly_commission_pct', resulting in NULL if 'monthly_commission_pct' is NULL.