Datasheet

42 Chapter 1

Introducing SQL
LAST_NAME COMMISSION_PCT
------------------------- --------------
Rogers
Raphaely
Rajs
Russell .4
SQL>
Why Do You Limit and Sort Rows?
The power of an RDBMS and SQL lies in getting exactly what you want from the data-
base. The sample tables you considered under the
HR schema are small, so even if you
get all the information from the table, you can still find the specific data you’re seeking.
But what if you have a huge transaction table with millions of rows?
You know how easy it is to look through a catalog in the library to find a particular book or
to search through an alphabetical listing to find your name. When querying a large table,
make sure you know what you want.
The
WHERE clause lets you query for exactly what you’re looking for. The ORDER BY clause
lets you sort rows. The following steps can be used as an approach to query data from
single table:
1. Know the columns of the table. You can issue the DESCRIBE command to get the
column names and datatype. Understand which column has what information.
2. Pick the column names you are interested in including in the query. Use these columns
in the
SELECT clause.
3. Identify the column or columns where you can limit the rows, or the columns that
can show you only the rows of interest. Use these columns in the
WHERE clause of the
query, and supply the values as well as the appropriate operator.
4. If the query returns more than a few rows, you may be interested in having them
sorted in a particular order. Specify the column names and the sorting order in the
ORDER BY clause of the query.
Let’s consider a table named
PURCHASE_ORDERS. First, use the DESCRIBE command to list
the columns:
SQL> DESCRIBE purchase_orders
Name Null? Type
--------------------- -------- --------------
ORDER# NOT NULL NUMBER (16)
ORDER_DT NOT NULL DATE
95127c01.indd 42 2/18/09 6:37:10 AM