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:

SELECT [TOP n [PERCENT]]
  * | [DISTINCT] field1 [,field2]...
  FROM TableName
  WHERE criteria
  ORDER BY FieldName1 [ASC|DESC] [,FieldName2 [ASC|DESC] ]...

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,

SELECT * FROM MyTable

selects all records from MyTable and includes all (*) of the fields that make up a record. In short, it retrieves the entire table. In contrast, the statement,

SELECT LastName, FirstName FROM MyTable

selects all records from the table, but returns only the fields named LastName and FirstName. 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.

SELECT DISTINCT ItemType FROM MyTable

The TOP Predicate

In order to limit the number of records returned you can preface the selection with the keyword TOP, specifying the first n number of records or the first n PERCENT of records in the table.

SELECT TOP 10 * FROM MyTable

The WHERE Clause

A common way to restrict the number of records returned from a selection is to supply a criterion against which records can be matched. Only the matching records are returned. The SELECT statement has an optional WHERE clause to supply the condition for selecting.

The keyword WHERE is followed by one or more selection criteria. A typical way to use 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".

SELECT * FROM Customers WHERE State='GA'

Here, the database management system delivers only those records that have a matching state code.

Note in this example that the criterion value is enclosed 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#).

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

SELECT * FROM Customers WHERE State='GA' OR State='KY'

The LIKE operator locates records where a field contains a given string value. In its most general form, this comparison tests for a string anywhere in a field.

SELECT * FROM Products WHERE Description LIKE '%micro%'

In this example, the string "micro" is located anywhere in the Description field. This criterion retrieves records where the field contains the actual word "micro", as well as records containing "Microsoft", "microcomputer", "microphone", "micrometer", "micron", or any other text combination containing this string. The search string must be enclosed in "%" characters inside single quotes.

Variations on the LIKE comparison check for the string at the beginning of a field (WHERE Description LIKE 'micro%') or at the end of a field (WHERE Description LIKE '%micro'). The "%" sign is a wildcard character meaning any following or preceding text.

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 can arrange a set of names in order by last name, first name, and middle initial by using a SELECT statement that resembles the following:

SELECT * FROM Customers ORDER By LastName, FirstName, MI

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 does not need to be coded.

SELECT * FROM Customers ORDER By LastName DESC, FirstName ASC, MI

Including TOP n along with ORDER BY returns records that fall at the top or the bottom of a range of values. When ordered with DESC, the top of the range is retrieved; when ordered with ASC, the bottom of the range is retrieved.

SELECT TOP 10 ItemName, ItemPrice FROM Products ORDER BY ItemPrice ASC

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

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.

Composing SQL Strings

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

SQLString = "SELECT * FROM Customers WHERE State='GA' ORDER BY LastName DESC"
CommandObject = New OleDbCommand(SQLString, Connection)

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

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

SQLString  = "SELECT * FROM Customers "
SQLString &= "WHERE State='GA' OR State='KY' "
SQLString &= "ORDER BY LastName DESC, FirstName, MI"

An alternative is to concatenate portions of the SELECT statement by using line continuations.

SQLString = "SELECT * FROM Customers " _
          & "WHERE State='GA' OR State='KY' " _
          & "ORDER BY LastName DESC, FirstName, MiddleInitial"

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 the following,

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

causes 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. For example, a script might define a variable, StateCode, to hold a state code, and use whatever value is stored in the variable as the search criterion. In this case, a script must compose an appropriate SELECT statement by concatenating fixed portions of the SELECT statement with the variable value.

Dim StateCode As String
...
SQLString = "SELECT * FROM Customers WHERE State = '" & StateCode & "'"

Notice that apostrophes are included in the literal text strings surrounding the variable so that the StateCode value is treated as a string. When retrieving numeric data, apostrophes are not required.

Dim TheAge As Integer
...
SQLString = "SELECT * FROM Customers WHERE Age = " & TheAge

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

Dim TheDate As Date
...
SQLString = "SELECT * FROM Orders WHERE OrderDate = #" & TheDate & "#"

Sometimes it can get a bit tedious in composing SQL statements when there are several variables to integrate. In these cases, you should consider breaking the statement into line-continued portions so you can better visualize the statement composition.

Dim TheState As String
Dim TheAge As Integer
Dim TheDate As Date
...
SQLString = "SELECT * FROM Customers WHERE " & _
  "State   = '" & TheState & "' AND " & _
  "Age     >  " & TheAge   & " AND " & _
  "ResDate = #" & TheDate  & "# " & _
  "ORDER BY Age ASC"

Notice that spaces are used to align fields and values for ease of reading since spaces are generally ignored in SQL unless they have meaning as a data value. Focus on the variables and the literal portions inside the double quotes to see what values are strung together to produce the statement.

In summary, then, SQL SELECT statements for the three data types have the following general constructions.

SQLString = 

"SELECT * FROM Table WHERE StringField = '" & StringVariable & "'"
"SELECT * FROM Table WHERE NumericField = " & NumericVariable
"SELECT * FROM Table WHERE DateField = #" & DateVariable & "#"

When composing statements that include strings with possible apostrophes, you should use the Visual Basic Replace() statement to convert single apostophes to double apostrophes.

Dim TheName As String
...
SQLString = "SELECT * FROM Customers WHERE " & _
            "Name = '" & Replace(TheName, "'", "''") & "'"

If variable TheName contains the string "O'Reilly", then the resulting SQL statement is

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