in your result set. The shorter notation for
this command is set feed on.
The last line displayed in Listing 2’s result
set is
6 rows selected.
the number of rows returned in your result
set, you can turn this setting off with the
set feed offcommand.
Your environment settings will apply to all
of your current session’s subsequent query
execution results.
This line appears because the SQL*Plus
feedback setting was turned on (in Listing 1).
If you do not want to see this final count of
ORDERING THE UNKNOWN
Recall that a null value is one that is not
known. Listing 6, for example, lists all
Code Listing 6: Query that displays all employees with their manager values
SQL> select employee_id, first_name, last_name, manager
2 from employee
3 ORDER BY manager, last_name;
EMPLOYEE_ID FIRST_NAME LAST_NAME MANAGER
———————————————— ————————————————————————————— ————————————————————————————— ——————————
6567 Roger Friedli 28
6568 Betsy James 28
7895 Matthew Michaels 28
1234 Donald Newton 28
28 Emily Eckhardt
37 Frances Newton
employees from the EMPLOYEE table
with their manager values. Two of the six
returned records have null values in the
manager column.
When an ORDER BY clause sorts results
in ascending order, any null values are
displayed last by default. Conversely, if an
ORDER BY clause specifies descending order
for a column containing null values, as in
Listing 7, the null values are displayed first by
default. By using the NULLS FIRST or NULLS
LAS T option in the ORDER BY clause, you can
override the defaults and explicitly specify
how you want null values to be sorted. The
example in Listing 8 uses the NULLS FIRS T
option to override the default display-nulls-last behavior of an ORDER BY clause.
6 rows selected.
Code Listing 7: Query that orders a column containing null values in descending order
SQL> select employee_id, first_name, last_name, manager
2 from employee
3 ORDER BY manager DESC, last_name;
EMPLOYEE_ID FIRST_NAME LAST_NAME MANAGER
———————————————— ————————————————————————————— ————————————————————————————— ——————————
28 Emily Eckhardt
37 Frances Newton
6567 Roger Friedli 28
6568 Betsy James 28
7895 Matthew Michaels 28
1234 Donald Newton 28
6 rows selected.
SORTING WITH DIS TINCTION
When including an ORDER BY clause in a SQL
SELEC T statement, you will usually choose
to sort by a column or an expression that’s
in the statement’s SELEC T list. However, you
can also order by columns or expressions that
are not in the SELEC T list. Listing 9 displays a
list of employees ordered by the most recent
to the least recent date of hire, within which
the employees are sorted alphabetically by
last name. Although the sort occurs and displays correctly, only the employees’ first and
last names appear in the output, because
hire_date is not in the SELEC T list.
If you include the DISTINCT keyword in
the SELECT list, only columns or expressions
in the SELECT list may be included in the
ORDER BY clause. As Listing 10 shows, an
error will occur if a query using the DISTINCT
keyword tries to order by a column not
included in the SELECT list.
Code Listing 8: Query that orders a column containing null values with the NULLS FIRST option
SQL> select employee_id, first_name, last_name, manager
2 from employee
3 ORDER BY manager NULLS FIRST, last_name;
EMPLOYEE_ID FIRST_NAME LAST_NAME MANAGER
———————————————— ————————————————————————————— ————————————————————————————— ——————————
28 Emily Eckhardt
37 Frances Newton
6567 Roger Friedli 28
6568 Betsy James 28
7895 Matthew Michaels 28
1234 Donald Newton 28
6 rows selected.
THE ERRORS OF OUR WAYS
You will inevitably make mistakes while
learning to write SQL statements. Being able
to interpret the Oracle Database error messages you receive is key to your understanding
of SQL. Some error messages make it easy to
understand what you’ve done wrong, whereas
others are not so straightforward. The best
approach is to try to resolve one error message
at a time (a process called debugging).
Oracle Database tells you on which line of
a query an error has occurred. Listing 10, for
example, displays the following error message:
