Oracle 1Z0-071: Which statements are true about the ORDER BY clause?

Table of Contents

Question
Answer
Explanation
Reference

Question

Which two statements are true about the ORDER BY clause? (Choose two.)

A. In a character sort, the values are case-sensitive.
B. NULLS are not included in the sort operation.
C. Numeric values are displayed in descending order if they have decimal positions.
D. Column aliases can be used in the ORDER BY clause.
E. Only columns that are specified in the SELECT list can be used in the ORDER BY clause.

Answer

A. In a character sort, the values are case-sensitive.
D. Column aliases can be used in the ORDER BY clause.

Explanation

The two statements that are true about the ORDER BY clause are:

A. In a character sort, the values are case-sensitive.
D. Column aliases can be used in the ORDER BY clause.

Let’s explain each statement in detail:

A. In a character sort, the values are case-sensitive:
When using the ORDER BY clause to sort character data, such as strings or text, the default behavior is case-sensitive. This means that uppercase and lowercase letters are treated as distinct values during sorting. For example, ‘Apple’ would come before ‘banana’ in ascending order. If you want to perform a case-insensitive sort, you can use specific functions like UPPER or LOWER to convert the values to a consistent case before sorting.

D. Column aliases can be used in the ORDER BY clause:
The column aliases, which are defined in the SELECT list using the AS keyword or without it, can be used in the ORDER BY clause. This allows you to sort the result set based on the columns’ aliases rather than the original column names. It provides flexibility in specifying the sort order and simplifies the readability of the SQL statement. However, it’s important to note that column aliases cannot be referenced in the WHERE or GROUP BY clauses because they are evaluated after those clauses.

Let’s briefly explain why the other statements are not true:

B. NULLS are not included in the sort operation:
By default, NULL values are included in the sort operation. When sorting a column containing NULL values, they can appear at either end of the sorted result depending on the sort order specified. You can further control the placement of NULL values using the NULLS FIRST or NULLS LAST syntax in the ORDER BY clause.

C. Numeric values are displayed in descending order if they have decimal positions:
The default sort order for numeric values is ascending, regardless of whether they have decimal positions or not. If you want to sort numeric values in descending order, you need to explicitly specify the DESC keyword in the ORDER BY clause for the respective column.

E. Only columns that are specified in the SELECT list can be used in the ORDER BY clause:
This statement is not true. In the ORDER BY clause, you can reference columns that are not included in the SELECT list. This allows you to sort the result set based on additional columns that may not be displayed in the final output. However, it’s important to note that when using column aliases in the ORDER BY clause, they cannot be used if they are not defined in the SELECT list.

Therefore, the correct statements are A. In a character sort, the values are case-sensitive, and D. Column aliases can be used in the ORDER BY clause.

Reference

Oracle Database SQL 1Z0-071 certification exam practice question and answer (Q&A) dump with detail explanation and reference available free, helpful to pass the Oracle Database SQL 1Z0-071 exam and earn Oracle Database SQL 1Z0-071 certification.