Oracle PL 1z0-071 Exam Questions and Answers PDF

For the sorting of output in compound queries (INTERSECT, UNION, etc.):

D. The first column in the first SELECT of the compound query: By default, Oracle does not automatically sort the results of SET operations unless an ORDER BY clause is explicitly stated. However, if an ORDER BY is implied or specified without explicit columns, the default sorting would logically involve the first column specified in the first SELECT statement of the compound query.

A self-join is used to join a table to itself, and here's why the selected answers are correct:

Option B: It can be an outer join.A self-join can indeed be either an inner or an outer join, allowing for more flexibility in how records are matched and returned, especially useful in hierarchical or sequential data scenarios.

Option E: The query must use two different aliases for the table.When performing a self-join, aliases are necessary to distinguish between the different instances of the same table in the query.

Option F: The ON clause can be used.In SQL, the ON clause specifies the conditions that match rows in a self-join, offering a clear and structured way to define how the join works.

Other options are not universally true:

Option A: It must be an inner join. Incorrect because, as explained, outer joins are also possible.

Option C: The ON clause must be used. Incorrect because the WHERE clause might also be used to specify the join condition.

Option D: It must be an equijoin. Incorrect because non-equijoins (like non-equality comparisons) can also be used in self-joins.

A. Only the second statement will display departments with no employees. This is true because the second statement uses a LEFT JOIN to include all departments from the departments table, even those without matching entries in the employees table. When there are no employees in a department, the AVG(salary) will be NULL, and the department will still be displayed1.

C. Both statements will execute successfully if you add e.avg_sal to the select list. This is correct. Both statements calculate e.avg_sal as an average salary, either through a subquery or a join operation. Adding e.avg_sal to the select list will display the average salary alongside the departments. However, it’s important to note that the first statement will not display departments with no employees because it does not use a join that would allow for NULL values from the employees table2.

References:

Understanding SQL JOINs - Stack Overflow1.

Oracle Documentation on JOIN Operations2.

Note: The other options are incorrect because:

B. The first statement will not display departments with no employees since it does not use a join that includes departments without matching employee records.

D. As explained, the first statement will not display departments with no employees.

E. There is a typo in the option; it should be E.AVG_SAL. Even if corrected, the first statement alone would not execute successfully because it does not include a join to bring in the avg_sal value.

F. The second statement will display departments with no employees, but the first statement will not, so this option is incorrect.

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: