Submit Tutorials

Most database management systems provide easier and faster ways of extracting information from their tables. The common denominator of these methods is SQL, the Structured Query Language built into these systems. SQL is a full-service language for maintaining information in a database and worthy of study in its own right. However, we'll pick and choose the features as we need them rather than present the entire structure of the language.

When using SQL methods of working with databases, 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 a 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 useful 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 matching a given 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 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.

Issuing an SQL Request

SQL requests are issued through a Recordset Object's open method. That is, rather than opening a recordset by specifying a table name (RSObj.Open "Accounts",CNObj), a recordset is opened with an SQL statement:

The statement is enclosed in quotes and is followed by the reference to the Connection Object which was previously linked to the database.

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 SQL. Then this variable is used in the recordset Open statement.

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

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