Oracle 1z0-071 Exam Made Easy: Step-by-Step Preparation Guide

Substitution variables in Oracle are used to replace a value dynamically during the execution of SQL statements. The behavior of these variables is well-documented:

C. A substitution variable prefixed with & always prompts only once for a value in a session: This is true. In a session, when you use a single ampersand (&), SQL*Plus or SQL Developer will prompt for the value the first time the variable is encountered. The value for this variable will then be reused for the remainder of the session unless it is redefined.

D. A substitution variable can be used with any clause in a SELECT statement: Substitution variables can be placed in any part of a SQL statement, including the SELECT, WHERE, GROUP BY, ORDER BY, etc. They are not limited to any specific clause.

References:

Oracle SQL*Plus User's Guide and Reference, which discusses substitution variables.

In Oracle SQL, the following syntactical rules apply:

A. This query will result in an error because there is no comma separating the column names FIRST_NAME and LAST_NAME. The correct syntax should include a comma to separate the column names in the SELECT list.

B. This query is correctly formatted with a comma separating the column names, so it will not result in an error.

C. This query will result in an error because an alias defined in the SELECT list (ANNUAL_SALARY) cannot be used in the WHERE clause of the same query level. It must be repeated in the WHERE clause as 12 * SALARY.

D. This query will execute successfully because 12 * SALARY is directly used in the WHERE clause, and ANNUAL_SALARY is used in the ORDER BY clause, which is allowed.

E. This query is correct and will not result in an error. It uses 12 * SALARY in both the WHERE and ORDER BY clauses.

F. Similar to option C, this query will execute successfully because ANNUAL_SALARY is correctly used in the ORDER BY clause, and the WHERE clause does not attempt to reference the alias.

References:

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "Database Object Names and Qualifiers"

In Oracle Database 12c SQL, regarding date/time functions and considering a session where NLS_DATE_FORMAT is set to DD-MON-YYYY SH24:MI:SS:

C. CURRENT_DATE returns the current date and time as per the session time zone. This is correct as CURRENT_DATE returns the current date and time in the time zone of the current SQL session, as set by the ALTER SESSION command.

D. SYSDATE and CURRENT_DATE return the current date and time set for the operating system of the database server. This is partially correct. SYSDATE returns the current date and time from the operating system of the database server. However, CURRENT_DATE returns the date and time set for the client's operating system environment, adjusted to the session time zone.

Options A, B, E, and F are incorrect based on Oracle's documentation:

A is incorrect because SYSDATE is independent of the NLS_DATE_FORMAT setting.

B is incorrect because CURRENT_TIMESTAMP includes time zone information, which can differ from CURRENT_DATE.

E is incorrect because CURRENT_TIMESTAMP differs from SYSDATE by including fractional seconds and time zone.

F is incorrect as SYSDATE can be queried in any SELECT statement, not just from DUAL.

For conditional INSERT ALL in Oracle Database 12c:

D. A single WHEN condition can be used for multiple INTO clauses. This is true. A single WHEN condition in a multi-table insert can direct the insertion of a single row source into multiple target tables.

E. Each WHEN condition is tested for each row returned by the subquery. True, each row from the subquery is evaluated against each WHEN condition to determine into which table(s) the row should be inserted.

Options A, B, and C are incorrect:

A is incorrect because a row can indeed be directed into multiple tables based on the conditions.

B is incorrect as INSERT ALL can include an ELSE clause to handle rows that do not meet any of the specified conditions.

C is incorrect because not every row from the subquery necessarily results in a row insertion; it depends on the conditions being met.

In Oracle SQL, the ALTER TABLE statement is used to modify the definition of a table after it has been created. Specific changes are allowed depending on the current data type of the column and the data it contains.

A. You cannot change a VARCHAR2 column to NUMBER if the table contains rows with non-numeric values, which is the case with '123' as a VARCHAR2; it is a string, not a number.

B. You can reduce the size of a NUMBER column if the data will still fit within the new precision, but you cannot change a VARCHAR2 column to a smaller size if it contains values that exceed the new size limit.

C. You cannot change a NUMBER column to VARCHAR2 if the table contains rows because the existing data type of NUMBER does not match the new data type of VARCHAR2.

D. Similar to A, you cannot change the data type from VARCHAR2 to NUMBER if non-numeric data is present.

E. You can change a VARCHAR2(10) column to VARCHAR2(5) if all existing rows have values that fit within the new size, and you can change a NUMBER column to have a different scale (NUMBER(12,2)).

References:

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "ALTER TABLE"

The UNION ALL operator in SQL combines the results of two or more SELECT statements into a single result set:

Option A: True. Since UNION ALL does not eliminate duplicates, it does not ignore NULLs during any duplicate checking; however, it's worth noting that UNION ALL does not perform duplicate checking at all.

Option B: False. Unlike the UNION operator, UNION ALL does not eliminate duplicates; it includes all duplicates in the output.

Option C: False. The column names do not need to be identical in each SELECT statement; however, the result set will use the column names from the first SELECT statement.

Option D: True. For UNION ALL to work correctly, the number of columns and their data types in each SELECT statement must match, but the column names can differ.

Option E: False. UNION ALL does not inherently sort the output; it merely combines the results as they are retrieved. Any sorting would need to be explicitly specified using an ORDER BY clause.

The provided SQL statement is an update statement that involves a subquery which is correlated to the main query.

A. The subquery is executed before the UPDATE statement is executed. (Incorrect)

This statement is not accurate in the context of correlated subqueries. A correlated subquery is one where the subquery depends on values from the outer query. In this case, the subquery is executed once for each row that is potentially updated by the outer UPDATE statement because it references a column from the outer query (o.customer_id).

B. All existing rows in the ORDERS table are updated. (Incorrect)

Without a WHERE clause in the outer UPDATE statement, this would typically be true. However, the correctness of this statement depends on the actual data and presence of matching customer_id values in both tables. If there are rows in the ORDERS table with customer_id values that do not exist in the CUSTOMERS table, those rows will not be updated.

C. The subquery is executed for every updated row in the ORDERS table. (Correct)

Because the subquery is correlated (references o.customer_id from the outer query), it must be executed for each row to be updated in the ORDERS table to get the corresponding cust_last_name from the CUSTOMERS table.

Regarding privileges in Oracle:

Option A: Dropping user FINANCE will automatically revoke SELECT on SCOTT.EMP from user FIN_CLERK.

Dropping a user will revoke all privileges that user has granted to others.

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

Since FINANCE granted SELECT on SCOTT.EMP to FIN_CLERK with grant option, revoking the privilege from FINANCE will also cascade and revoke it from 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.

To calculate the number of days between two dates in Oracle SQL, you can simply subtract the earlier date from the later date, and Oracle will return the number of days between them.

Option A uses SYSDATE to get the current date and TO_DATE to convert the string '01-JANUARY-2019' to a date using the default date format, which matches 'DD-MON-RR'. The subtraction here will correctly give the number of days between these two dates.

Option C is similar to A but includes the ROUND function, which is used to round the number to the nearest whole number. Since the difference between two dates in Oracle SQL is a decimal value representing the number of days and fractional days, rounding will give us the whole number of days, which is what we want.

Options B, D, and E are incorrect because they either use an invalid date format (the default format is 'DD-MON-RR', not 'DD/MONTH/YYYY' or 'DD-MON-YYYY'), perform invalid operations (such as subtracting a string from a date), or misuse SYSDATE.

In Oracle SQL, set operations like UNION, UNION ALL, INTERSECT, and MINUS can be used to combine results from different queries:

Option A:

Combining results using UNION ALL followed by ORDER BY will execute successfully because UNION ALL allows duplicate rows and ORDER BY can be used to sort the combined result set.

Option E:

Similar to option A, UNION ALL combines all rows from the two selects and allows ordering of the results.

Option G:

UNION combines the results from two queries and removes duplicates, and ORDER BY can be used to sort the final result set.

Options B, C, D, and F are incorrect because:

Option B: You cannot intersect different columns (ORDER_ID with INVOICE_ID).

Option C: Incorrect column names and syntax with ORDER BY.

Option D: ORDER BY cannot be used before a set operator like INTERSECT.

Option F: ORDER BY cannot be used directly after a MINUS operator without wrapping the MINUS operation in a subquery.