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

A: This statement is false. You cannot add a foreign key to a global temporary table because the data in a global temporary table is session-specific, while a foreign key constraint implies a permanent relationship.

B: This statement is false. The ON COMMIT PRESERVE ROWS clause specifies that the data in the table should be preserved until the end of the session, not deleted at the end of the transaction.

C: This statement is false. You can drop a global temporary table without truncating it first.

D: This statement is true. You can alter a global temporary table to add columns within a session.

E: This statement is false. Data in a global temporary table with the ON COMMIT PRESERVE ROWS clause is private to the session that inserted the data, and cannot be seen by other sessions.

The ORDER BY clause with a CASE statement allows for conditional sorting based on the results of the CASE expression.

A. This is not true, as the 'A' in the THEN clause would cause the employees earning the maximum salary to be sorted at the top, but it does not determine the order in which their last names would appear.

B. This is true, the 'A' in the THEN clause will result in all maximum salary earners to be sorted at the top, and since 'A' comes before any last names alphabetically, they will be in ascending order.

C. This is not true, as the ELSE clause sorts the remaining employees by last_name but does not specify an order; hence, it defaults to ascending which contradicts the second part of the ORDER BY clause which specifies DESC.

D. This is not true, as the order of the remaining employee names is specified by the ORDER BY clause, which states last_name DESC.

E. This is true, after the employees with the maximum salary are sorted, the remaining employees' names will appear in descending order due to the last_name DESC in the ORDER BY clause.

F. This statement is not true as the specified 'A' in the CASE statement provides a definite order for employees earning the maximum salary.

References:

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "ORDER BY Clause"

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "CASE Expression"

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.

A: This statement is false. A table can only have one primary key, although the primary key can consist of multiple columns (composite key).

B: This statement is true. A table can have multiple foreign keys referencing the primary keys of other tables or the same table.

C: This statement is false. A NUMBER column without data is NULL, not zero.

D: This statement is false. A column definition must specify exactly one data type.

E: This statement is true. A VARCHAR2 column without data defaults to NULL, not an empty string.