Database Application Developer and DBA
SQL 101 BY MELANIE CAFFREY
An Order of Sorts
Part 5 in a series on the basics of the relational
database and SQL
ORACLE DATABASE
Part 4 in this SQL 101 series, “Why WHERE Matters” (Oracle Magazine, March/April
2012), introduced readers to the WHERE
clause of a SQL SELEC T statement (a query)
and the importance of filtering your data.
The WHERE clause and the SELECT list tell
the database which rows you want your
SELECT statement to retrieve. Now that you
know how to narrow the scope of the data a
query fetches, you’re ready to learn how to
sort (or order) the data. This article focuses
on the SQL ORDER BY clause and how it
behaves in conjunction with certain options
and keywords to tell the database how you
want retrieved rows to be sorted.
To try out the examples in this and subsequent articles in the series, you need access
to an Oracle Database instance. If necessary,
download and install an Oracle Database
edition for your operating system from
bit.ly/fherki. I recommend installing Oracle
Database, Express Edition.
If you install the Oracle Database software, choose the installation option that
enables you to create and configure a database. A new database, including sample
user accounts and their associated schemas,
will be created for you. SQL_101 is the user
account you’ll use for the examples in this
series; it’s also the schema in which you’ll
create database tables and other objects.
When the installation process prompts you
to specify schema passwords, enter and
confirm passwords for SYS and SYSTEM and
make a note of them.
LINDY GROENING
Whether you installed the database
software from scratch or have access to an
existing Oracle Database instance, download
and unzip the SQL script available at bit.ly/
yrEVIT and execute the script to create the
example tables for the SQL_101 schema.
(View the script in a text editor to get instructions on how to execute the script and information on what it does.)
The SQL queries in this article are executed against tables in the SQL_101 schema
with Oracle’s SQL*Plus tool. In addition to
discussing the ORDER BY clause, this article
provides a closer look at SQL*Plus.
MAKING ORDER OU T OF DISARRAY
Oracle Database table data isn’t stored in
any specific order, regardless of the order
in which it was inserted into the database.
To retrieve rows in either ascending or
descending order by column, you must
tell the database that you want to do so.
For example, you might want to list all
employees in the order they were hired,
display all employees in order of highest to
lowest annual salary, or list the last names of
all employees in the accounting department
in alphabetical order. You retrieve sorted
data by adding an ORDER BY clause to your
SELECT statement. ORDER BY is always the
last clause in a query.
Code Listing 1: Simple query for listing all rows in the EMPLOYEE table
SQL> set linesize 32000
SQL> set feedback on
SQL> select first_name, last_name, hire_date, salary
2 from employee;
FIRST_NAME LAST_NAME HIRE_DATE SALARY
——————————————————————————————— ——————————————————————————————— ————————————————————————————— ————————
Roger Friedli 16-MAY-07 60000
Betsy James 16-MAY-07 60000
Emily Eckhardt 07-JUL-04 100000
Frances Newton 14-SEP-05 75000
Donald Newton 24-SEP-06 80000
Matthew Michaels 16-MAY-07 70000
6 rows selected.
Code Listing 2: Query that lists all rows in ascending alphabetical order by last name
SQL> select first_name, last_name, hire_date, salary
2 from employee
3 ORDER BY last_name;
FIRST_NAME LAST_NAME HIRE_DATE SALARY
——————————————————————————————— ——————————————————————————————— ————————————————————————————— ————————
Emily Eckhardt 07-JUL-04 100000
Roger Friedli 16-MAY-07 60000
Betsy James 16-MAY-07 60000
Matthew Michaels 16-MAY-07 70000
Frances Newton 14-SEP-05 75000
Donald Newton 24-SEP-06 80000
