Oracle PL 1z0-071 Oracle Study Notes

For the given SQL commands:

• Option A: ROLLBACK;
• Option C: ROLLBACK TO SAVEPOINT post_insert;

Global temporary tables (GTTs) in Oracle have unique properties related to session-based visibility and data persistence:

• B. A TRUNCATE command issued in a session causes all rows in a GLOBAL TEMPORARY TABLE for the issuing session to be deleted: True, TRUNCATE is session-specific for GTTs and will remove all rows inserted by the session that issues the TRUNCATE command.
• D. A GLOBAL TEMPORARY TABLE's definition is available to multiple sessions: The structure of a GTT is persistent and shared across sessions, but the data within is session-specific.
• E. Any GLOBAL TEMPORARY TABLE rows existing at session termination will be deleted: The data in GTTs is private for the session, and when the session ends, the data is deleted automatically.

References:

• Oracle Database Concepts and SQL Language Reference 12c, especially the sections on temporary tables.

The INTERSECT operator in SQL returns the results that are common to both of the SELECT statements. It functions similarly to a set intersection in mathematics. When comparing rows for the INTERSECT operation, Oracle Database uses all the expressions in the SELECT lists to derive the result set. NULL values are considered equal for the INTERSECT operator.

Evaluating the provided data from the ORDERS and INVOICES tables, let's see which rows have the same ORDER_ID and ORDER_DATE in both tables:

• A: Order ID 3 has a NULL order date in the ORDERS table and does not match with any row in the INVOICES table, so it will not be returned.
• B: Order ID 2 has a NULL order date in the ORDERS table but has a non-NULL order date in the INVOICES table, so it will not be returned.
• C: Order ID 1 has a NULL order date in both tables, but INTERSECT considers NULLs as equal, so this will be returned.
• D: Order ID 5 has a date of 01-MAR-2019 in the ORDERS table and 01-APR-2019 in the INVOICES table, so it will not be returned since the dates do not match.
• E: Order ID 4 has a date of 01-FEB-2019 in both tables, so this row will be returned as it matches in both.
• F: Order ID 3 has a NULL order date in the ORDERS table but has 01-JAN-2019 in the INVOICES table, so it will not be returned.

Based on this analysis, the query will return:

• Order ID 1 with a NULL order date.
• Order ID 4 with an order date of 01-FEB-2019.

So the correct answer is:

Examining the execution of SELECT statements in Oracle SQL requires understanding correct syntax, especially regarding aliases and string literals:

• Option A: Incorrect. Although the syntax might seem plausible, using DATE as an alias without quotes can lead to ambiguity because DATE is a reserved keyword in Oracle SQL. This might cause an error or unexpected behavior unless it is enclosed in double quotes.
• Option B: Incorrect. Single quotes are used for string literals in SQL, not for aliasing column names. Using single quotes for aliasing, as in 'CUSTOMER-ID', 'DUES', is syntactically incorrect and will result in an error.
• Option C: Correct. This statement uses valid syntax with no reserved keywords as aliases and no misuse of string literal notation. Aliases are provided without quotes, which is acceptable as long as they are not reserved words or contain special characters.
• Option D: Correct. It correctly uses double quotes for aliasing, which is necessary when using reserved words like "DATE" or special characters. This is a proper use of SQL syntax for aliasing in Oracle.
• Option E: Incorrect. This statement attempts to use aliases with hyphens without enclosing them in double quotes, which leads to syntax errors. Also, customer id seems to be missing an underscore or another connector to properly reference a column name, indicating a typo or error.

For these queries, ensuring correct alias syntax and avoiding reserved keywords without appropriate quoting are key elements for successful execution.