Oracle 1z0-071 Dumps

In SQL, the ORDER BY clause can refer to columns by their alias defined in the SELECT clause or by their positional number in the SELECT list. It's important to understand that after a UNION, the column names in the ORDER BY clause refer to the first SELECT statement's column names and aliases:

Option A: ORDER BY 2, 1This is correct because it refers to the second and first columns in the first SELECT clause, which correspond to the aliases "Last Name" and cust_id, respectively.

Option E: ORDER BY "Last Name"This is correct because "Last Name" is a valid alias defined in the first SELECT clause.

The other options fail for the following reasons:

Option B: "CUST_NO" is not recognized in the ORDER BY clause because it's not an alias used in the first SELECT statement.

Option C: Incorrect because 2, cust_id mixes positional reference with a column name that doesn't apply to both SELECT statements consistently.

Option D: ORDER BY CUST_NO fails because CUST_NO is not an alias in the first SELECT clause.

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: