Introducing SELECT

10/27/2000

One of the most important functions of any database application is finding the data that's in the database. We're going to spend the next few columns exploring the SQL SELECT command, the workhorse of most database applications. Hopefully you've got a database up and running so you can try this yourself as we work through the command.

Simple SELECT

We'll be using the simple database we started with last week as a starting point for our experiments with the SELECT command. We'll be SELECTing data from our tiny music database table (which we'll name MusicCollection for ease of reference):

MusicCollection

ID

Title

Artist

Year

1

Pet Sounds

The Beach Boys

1966

2

Security

Peter Gabriel

1990

3

The Way it Is

Bruce Hornsby

1986

4

Joshua Judges Ruth

Lyle Lovett

1992

As was mentioned in the original article, SQL is very English-like. Commands typically consist of a verb, an object, and possibly a set of clauses that modify the object. So to find all of the musical artists in the database, we could say something like "Choose all the values from the Artist field of the database." The SQL translation of this sentence is

SELECT Artist FROM MusicCollection;

The is the most basic version of the SELECT command. It returns an entire column of data from the database. In this case the results would look something like the following.

Artist

--------------------

The Beach Boys

Peter Gabriel

Bruce Hornsby

Lyle Lovett

So what else can you do with SELECT? Plenty! You can retrieve multiple columns.

SELECT Artist,Title FROM MusicCollection;

which would return

Artist

Title

--------------------

--------------------

The Beach Boys

Pet Sounds

Peter Gabriel

Security

Bruce Hornsby

The Way it Is

Lyle Lovett

Joshua Judges Ruth

You can also use a shortcut command to return ALL the columns of a database, basically displaying the entire thing. This is accomplished by using an asterisk (*) instead of any column names.

SELECT * FROM MusicCollection;

which would return

ID

Artist

Title

Year

-----

--------------------

--------------------

-----

1

Pet Sounds

The Beach Boys

1966

2

Security

Peter Gabriel

1990

3

The Way it Is

Bruce Hornsby

1986

4

Joshua Judges Ruth

Lyle Lovett

1992

You can even get really fancy and assign a field a new temporary name, or an alias. This technique is normally used when there are fields in two or more tables with the same name, which is a topic for a later column -- but just for fun (for the sake of completeness), we could do something like the following:

SELECT Title AS AlbumName FROM MusicCollection;

AlbumName

--------------------

Pet Sounds

Security

The Way it Is

Joshua Judges Ruth

So here's what we know so far about the SELECT statement:

SELECT returns one or more columns from a data table chosen by field name.

The FROM clause identifies the database table to use as a source of data.

The asterisk (*) is a shortcut for returning all field names.

Column names can be aliased.

And to whet your appetite for future columns, we can use the SELECT statement to display data from more than one table. But that's a story for another column. With the space that's left this time, we've got another important topic to cover.