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

C: True. In Oracle Database 12c, character sorts are case-sensitive by default, meaning that it differentiates between uppercase and lowercase letters when sorting data. This behavior aligns with the binary collation rules typically used, unless explicitly overridden by using different collation settings.

D: True. Column aliases can indeed be used in the ORDER BY clause in Oracle SQL. This allows for more readable and maintainable code, as you can define a column alias in the SELECT list and refer to that alias in the ORDER BY clause. This usage promotes clarity, especially when dealing with complex calculations or function applications in the select list.

Non-equi joins are joins where the join condition is based on something other than equality. In Oracle SQL, you can perform non-equi joins using various clauses:

A. The ON clause can be used: True, the ON clause can specify any condition for the join, including non-equijoins. It is not limited to equijoins.

E. The Oracle join syntax can be used: The traditional Oracle join syntax, which uses the WHERE clause to specify the join condition, can be used for all types of joins, including non-equijoins.

References:

Oracle Database SQL Language Reference 12c, especially the sections on join clauses including the ON clause and Oracle's proprietary join syntax.

To return all rows for employees whose salary is greater than the average salary in their department, you would use either a subquery or an analytic function:

Option B:

SELECT ... FROM employees WHERE salary > AVG(salary) OVER (PARTITION BY department_id);

This uses the window function AVG with PARTITION BY to calculate the average salary per department, and it compares each employee’s salary to this average.

Option C:

SELECT ... FROM employees e1 WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e1.department_id = e2.department_id);

This correlated subquery compares each employee's salary to the average salary in their department using a subquery to calculate the average salary for that department.

Options A, D, and E are incorrect because:

Option A: The use of ANY with the subquery does not ensure comparison with the average salary of their respective department.

Option D: This is syntactically incorrect; the subquery alias avg_sal is not accessible outside the subquery.

Option E: The subquery does not correlate with the outer query to ensure that each employee's salary is compared to the average salary of their respective department.

A. True, a single table column can be included in both a unique index and a non-unique index, provided they are different indexes.

D. True, an invisible index is maintained during DML operations, which means it is updated when insert, update, or delete operations are performed on the table. However, it is not used by the optimizer by default for query execution unless explicitly hinted or the session is altered to consider invisible indexes.

E. True, an index can be specified in the CREATE TABLE statement using the INDEX clause to create an index on a specific column at the time of table creation.

B, C, and F are not correct: B. A descending index is simply an index that is sorted in descending order, not a type of function-based index. C. A DROP INDEX