Database management systems provide SQL, the Structured Query Language, for working with information in a database. When using SQL, you are relying on the database management system to perform the work. Rather than coding a server script to access tables or to maintain the data in the databases, this work is farmed out to the DBMS. The script simply issues an SQL request to the DBMS, which independently carries out the task. This method promotes the notion of a three-tier, client/server processing system where data access and database processing is localized to the database server.

The SELECT Statement

The most commonly used of the SQL statements is the SELECT statement. As the name implies, this statement is used to select records from a database table. The selection can encompass the entire table with all of its fields, or it can be restricted to certain fields in certain records that match given search criteria. Optionally, the selected records can be ordered, or sorted, on particular fields. The group of selected records itself becomes a recordset that can be processed in the same fashion as used for an entire table.

The general format for the SELECT statement is shown below:

The keyword SELECT is followed by one of two specifications identifying the fields of data to be selected from a table. An asterisk (*) denotes that all fields are to be selected for each record. Otherwise, you can provide a list of field names, separated by commas, and only those data fields will be selected. The FROM clause identifies the table from which these records and fields are to be selected.

For example, the statement

selects all records from MyTable and includes all (*) of the fields that make up a record. The resulting recordset is identical to the one returned when opening a full table. In contrast, the statement

selects all records from the table, but only provides the fields named LastName and FirstName from among all the fields in the records. In this case the resulting recordset contains as many rows as there are records in the table, but only two columns.

The DISTINCT Keyword

Some table fields are likely to contain non-unique data values. That is, the same value may appear in more than one record. In order to retrieve only the unique values from these fields, precede the field name with the keyword DISTINCT. For instance, the following statement retrieves a single column of data containing only the unique values in field ItemType:

The WHERE Clause

In both of the above instances, all records are retrieved from the table. Only the fields that comprise a record differ. There may be cases, however, where you do not want or need to retrieve each and every record in a table. You might wish to select only those records that meet certain critera. For these purposes the SELECT statement provides an optional WHERE clause.

The keyword WHERE is followed by one or more selection criteria. A common way of using this feature is to check for equality, that is, to look for a matching value in one of the record's fields. For example, if you are processing a set of customer records based on the state in which they are located, you might wish to select only those records where the State field contains the value "GA". So, you would issue the SQL statement

and the database management system would deliver only those records that had a matching state code. You can, in fact, use any of the common conditional operators,

to formulate your selection criterion. Plus, you can combine tests using the logical operators AND, OR, and NOT to expand or contract your selection:

Note in these examples that the selection criterion values are enclose in single quotes (apostrophes). Any time you are matching against a database text field, the criterion value must be enclosed in single quotes (WHERE State = 'GA'). If you are testing against a numeric field, the data value is not enclosed in quotes (WHERE Number > 10). If you are testing against a date/time field, the criterion value is surrounded by # symbols (WHERE TheDate > #1/1/01#).

The ORDER BY Clause

A SELECT statement can also include the ORDER BY clause in order to arrange, or sort, the set of records retrieved from a table.

The ORDER BY clause identifies the names of fields on which to sort the records. If more than one field name is supplied, then sorting takes place in the order in which the names appear, separated by commas. The first field becomes the major sort field, the second field becomes the intermediate sort field, and the third field becomes the minor sort field. Thus, you could arrange a set of names in order by last name, first name, and middle inititial by using a SELECT statement that resembles the following:

You can also specify whether ordering is to take place in ascending or descending sequence by coding ASC or DESC following the field name. The default order is ascending (ASC), which doesn't need to be coded.

The WHERE and ORDER BY clauses are optional in a SELECT statement and either can appear. If both are included, however, the WHERE clause must precede the ORDER BY clause.

The TOP n Predicate

Preceding the selected fields with TOP n returns n number of records that fall at the top or the bottom of a range specified by an ORDER BY clause. When ordered with DESC, the top of the range is retrieved; when ordered with ASC, the bottom of the range is retrieved.

This statement selects the 10 lowest-priced products from the table. You can also use the PERCENT reserved word to return a certain percentage of records that fall at the top or the bottom of a range specified by an ORDER BY clause.

Composing SQL Strings

Sometimes SELECT statements can become quite complex with selected fields, selection criteria, and ordering clauses. Therefore, it is often convenient to first compose the statement within a script variable, and then issue the statement through the variable name.

Here, the string of characters comprising the SELECT statement are assigned to variable SQLString. Then, this variable is used in issuing the SQL statement.

If the SELECT statement is particular long or complex, you can piece it together a bit at a time by concatenating to the string:

or by using line continuation:

Here, subsequent clauses are concatenated to create the SQL string (making sure that necessary spaces are included to separate the clauses).

Apostrophes in Text Fields

It is often the case that text values contain apostrophes, e.g., names (O'Reilly), possessives (Bill's), contractions (it's), and the like. However, an SQL statement such as

will cause an error because it is invalid to code an apostrophe within a value which itself is enclosed in apostrophes. The way around the problem is to code double apostrophes ('') in place of any single apostrophe within the value:

SELECT * FROM Customers WHERE LastName = 'O''Reilly'

Integrating Variable Data

It is normally the case that SQL statements are composed by integrating script-generated data values rather than by using string or numeric constants. In this case, a script must compose an appropriate SELECT statement by concatenating fixed portions of the SELECT statement with the variable data:

SQLString = "SELECT * FROM Customers WHERE State = '$TheState'"

If variable TheState has the value "GA" stored, then SQLString ends up storing the SQL statement:

SQLString = "SELECT * FROM Customers WHERE State = 'GA'"

Notice that apostrophes are included in the literal text strings surrounding the variable so that TheState value "GA" is treated as a string ('GA') in the SELECT statement.

When retrieving numeric data, apostrophes are not required:

SQLString = "SELECT * FROM Customers WHERE Age = $TheAge"

The value of TheAge variable is appended to the end of the literal string, producing a SELECT statement that resembles the following:

SQLString = "SELECT * FROM Customers WHERE Age = 30"

When composing statements that include a date, the special symbol "#" must surround criteria for a table field defined as Date/Time:

SQLString = "SELECT * FROM Orders WHERE OrderDate = #$TheDate#"

The resulting SQLString contains a SELECT statement resembling the following:

SQLString = "SELECT * FROM Orders WHERE OrderDate = #07/15/04#"

As a general rule, then, SQL SELECT statements for the three data types have the following general constructions: