The Internet Encyclopedia (Volume 3)

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IML

SQL ̇OLE WL040/Bidgolio-Vol I WL040-Sample.cls June 20, 2003 13:16 Char Count= 0

SINGLETABLEACCESS AND THERESULTSET 357

The criteria specified can be very specific, such as re-

turning those rows that have this exact value for an at-

tribute (e.g., returning all movies with a rating of “PG”).

The criteria can be less specific (e.g., returning all movies

that begin with an “A”). Examples follow:

To return all movie titles with a PG rating (exact

match):

select MOVIE_TITLE

from MOVIE

where MOVIE_RATING = "PG";

The result set would be

MOVIETITLE

THE BEST MAN IN GRASS CREEK

CROCODILE DUNDEE IN L.A.

SPY KIDS

To return all movie titles and rating which begin with

the letter “A” (inexact match):

select MOVIE_TITLE, MOVIE_RATING

from MOVIE

where MOVIE_TITLE like 'A%';

The result set would be

MOVIETITLE MOVIERATING

A KNIGHT’S TALE PG-13

AMORES PERROS R

Note: from this point forward, unless difficult

to determine, the results sets will be left as an

exercise to the reader.

To return all movie titles with a PG or PG-13 rating (ex-

act match), we can use either exact or inexact matching

techniques. If a new movie rating was created, say PG-15,

and we only wanted PG or PG-13 movies, the inexact tech-

nique would no longer work. The following queries will

return the same result set:

select MOVIE_TITLE select MOVIE_TITLE

from MOVIE from MOVIE

where MOVIE_RATING in ( where MOVIE_RATING

"PG," "PG-13") like 'PG%';

select MOVIE_TITLE Other queries using

from MOVIE string searching

where MOVIE_RATING ='PG'or techniques are also

MOVIE_RATING ='PG-13'; possible.

If we want to get a range of values, we can use the

betweenkeyword. For example, we want all movie tickets

priced between $5.00–$7.00. The query would look like

this:

select *

from TICKET_PRICE

where PRICE between 5.00 and 7.00;

The following comparison operators are specified for

use in awhereclause:<,>,=,!=,<>. IN, BETWEEN,

and LIKE are used on string data only.

Computations

Perhaps we want to enlarge the result set or perform a

computation, such as computing the total for a shopping

cart application or performing what-if scenarios relating

to the price of a movie ticket. We can perform opera-

tions on the attributes listed in the select statement. Note:

we cannot perform these operations when we use the

“select *” notation. We can either perform a static compu-

tation (e.g., what would our pricing structure look like if

we raised ticket prices by 15%?) or a dynamic computa-

tion (e.g., a calculation involving two or more attributes).

An example query would be:

select TICKET_TYPE, PRICE, PRICE * 1.15

from TICKET_PRICE;

Arranging the Result Set

SQL provides us with a mechanism for sorting the rows of

a result set. This mechanism is theorder byclause. When

using theorder byclause, the query developer specifies

which attribute(s) to sort the data on and the direction

asc (ascending) or desc (descending). If more than one

attribute is specified, then the outer attribute specifies

the major ordering and the next attribute(s) indicates the

ordering within the primary ordering.

Note: theorder byclause is the last clause of a select

statement. For example, the following query using the

order byclause generates the result set that follows:

select MOVIE_RATING, MOVIE_TITLE

from MOVIE where MOVIE_TITLE like 'A%'

order by MOVIE_RATING desc, MOVIE_TITLE asc;

MOVIERATING MOVIETITLE

R ALONG CAME A SPIDER

R AMORES PERROS

PG-13 A KNIGHT’S TALE

Segregating the Result Set

Often we want to work on a partitioned result set (i.e.,

for each movie rating in our result set, we want to count

the number of movies that have the rating). Or we want

to get the most (or least) expensive book in our inven-

tory. To support these types of queries, SQL has de-

fined a number of aggregate functions. Example aggre-

gate functions include COUNT(), MIN(), MAX(), SUM(),

and AVG(). All functions take in column expressions (ei-

ther column names or column computations). The count

function can also take in a “*.” In order, these functions

perform the following computations—counting the num-

ber of rows, determining the minimum value, determin-

ing the maximum value, totaling the value, and deter-

mining the average value. In general, these functions are

most useful when we partition the result set (e.g., total