Using SQL

After we've performed the steps depicted in Figure 24.3, we're all dressed up and ready to go. Ready to go where? So far, all we have in place are the necessary connections between the database and the form upon which we want to display the data. What we need to do now is learn how to pass commands to the database that return the data we're interested in. That's where SQL enters the picture.

This section presents a brief crash course on SQL. We'll cover only the most common SQL commands. A complete review of all the SQL features available is beyond the scope of this book. Indeed, entire books are available that do nothing else but teach you how to use SQL. Still, we can learn enough in a very short period of time to cover most of our needs. With that in mind, let's begin our crash course in SQL.

SELECT

SELECT is the fundamental keyword used in most SQL queries. The SELECT query has the following general form:

 SELECT  ListOfFieldNames  FROM  TableName  

The words SELECT and FROM are SQL keywords and, therefore, have special meaning in SQL. ListOfFieldNames is a list of the field names from the table that you want to use. TableName is the database table on which you want to use the SELECT query.

For example, suppose that you want a list of customer ID numbers and how much each bought for all of your company sales. The SELECT query could be written:

 SELECT [Customer ID],[Order Amount] FROM Orders 

First, because the designers of the Xtreme.mdb database have field names with blank spaces in them, we must surround such field names with square brackets. If the designers had written the field names as CustomerID and OrderAmount , you could write the same SELECT query as

 SELECT CustomerID,OrderAmount FROM Orders 

You can add as many field names as you want to the SELECT query. Simply separate each field name with a comma. Obviously, it is an error to add a field name to the SELECT query if that field does not exist in the table to which you are applying the SELECT query. In our example, that means we can use only the field names you see listed in the Orders table in Figure 24.1. Any field names not found in the Orders table will produce an error.

Programmer's Tip

Most programmers use capital letters for the SQL keywords. This helps to make the SQL statement easier to read.

Suppose that you want to examine the values for every field in the Orders table. You could, of course, add all the field names to the comma-delimited list of field names and the SELECT query would work just fine. However, SQL provides a shorthand method for such queries. If you write the SELECT query as

 SELECT * FROM Orders 

all the field values would be returned for each record in the Orders table. Stated another way, this statement returns the entire contents of the Orders database table.

You might be asking, "What do you mean, 'returns the entire contents'?" Refer back to Figure 24.3. Now pretend that you could connect your keyboard to the line drawn between the Data Adapter object and the Dataset object. This might look like Figure 24.4. (I've omitted the Connection object and the Database shown in Figure 24.3 for the sake of brevity.)

Figure 24.4. Issuing an SQL SELECT query to the database.

Now suppose that you type in a SELECT query in at the keyboard and press the Enter key. Your SELECT query travels down the keyboard wire, hangs a right turn and enters the Data Adapter object. The Data Adapter object checks over the SELECT query, likes what it sees, and passes it along to the Connection object (refer to Figure 24.3). The Connection object also looks at your SELECT query, says "Cool!" and passes it along to the DBMS (probably part of a DLL) that's buried within the code that actually manages the database. (It's the responsibility of the DBMS to process all valid SQL statements for the database.)

When the DBMS finishes gathering the data your SELECT query requested , it bundles up the data and sends it back to the Connection object. The Connection object passes the data along to the Data Adapter object. The Data Adapter object then takes the data and shoves it into the Dataset object. At this point, your SQL SELECT request has been fulfilled. It's up to the code you write in the Visual Basic .NET form to move the data from the Dataset object and display it on the form (more on that in Chapter 25).

So far, so good. Our SELECT query actually returns the entire contents of the Orders table. We would get every record for every sale the company ever had. Although we might actually want this on certain (rare) occasions, more often than not, this would be information overload. There's just too much information to be useful. What we need in most cases is a way to make the SELECT query more selective. That is, we need a way to create the SELECT query such that it can filter the data to better suit our needs.

We can narrow the focus of the SELECT query by using predicates with the SELECT query. SQL predicates are simply SQL keywords that operate with the SELECT keyword in a specific manner. You'll often hear these called predicate clauses because they're like a clause in a sentence . Let's see how these predicates might be used.

The WHERE Predicate

The WHERE predicate is used in conjunction with a SELECT query and has the general form

 SELECT  FieldList  FROM  TableName  WHERE  SearchCriteria  

The WHERE predicate specifies a search criteria that we want to apply to the SELECT query. The search criterion is normally used to filter, or limit, the number of records that are returned from the database query.

For example, suppose that you're a customer with the company and your Customer ID number is 524. The SELECT query

 SELECT * FROM Orders WHERE [Customer ID] = 524 

would return a dataset that contains records describing every purchase you ever made with the company.

If your last name is Thomas, you might rewrite the SELECT query as

 SELECT * FROM Orders WHERE [Contact Last Name] = 'Thomas' 

This seems like it should work, but it won't because the field named Contact Last Name doesn't exist in the Orders table. Your last name only appears in the Customer table.

Let's try another query:

 SELECT * FROM Customer WHERE [Contact Last Name] = 'Thomas' 

This works fine because we switched from the Orders table to the Customer table. The result would be a dataset that contains a list of all customers who have the last name of Thomas. However, we no longer get the orders information because we aren't searching the Orders table. However, we could use your Customer ID from this query in another query on the Orders table to return your sales records.

Compound WHERE Predicates with AND and OR

We can apply additional data filters by using compound search criteria to augment the WHERE predicate. For example:

 SELECT * FROM Employee WHERE _         Salary > 50000 AND _        Position = 'Sales Representative' 

Notice the SQL keyword AND in the SELECT query. The AND keyword has the same interpretation that it did when we studied relational operators in Chapter 10, "Relational and Logical Operators." The SELECT query would return all employee records in which the employee's salary exceeds $50,000 and the position is Sales Representative.

Keep in mind that SELECT queries have the potential to return zero records in response to a search. As a general rule, an AND clause returns a smaller number of records from the database than if the AND clause were not used.

You probably guessed that we can also use an OR clause with the WHERE predicate:

 SELECT * FROM [Orders Detail] WHERE _      [Unit Price] > 50 OR _     Quantity > 3 

This would return more records than if either of the two WHERE predicates were omitted. For example, the statement

 SELECT * FROM [Orders Detail] WHERE _       [Unit Price] > 50 

might return 1000 records, whereas

 SELECT * FROM [Orders Detail] WHERE _       Quantity > 3 

might return 200 records. However, 1,200 records would be returned by using the OR clause. Therefore, OR clauses usually expand the record count when compared to a single WHERE clause.

Compound WHERE Clauses with LIKE

Sometimes you'd like to perform a search using a partial match. For example, perhaps you'd like a list of all employees who live in the Indianapolis metro area. You could use

 SELECT * FROM [Employee Addresses] WHERE _      [Postal Code] LIKE '462*' 

which would return all employee who live in a ZIP Code that starts with 462. This means the records of anyone who lives in a ZIP Code between 46200 and 46299 will be included in the dataset.

Programmer's Tip

You'll notice that some WHERE criteria surround the parameter with single quotation marks (for example, '462*' ), whereas others simply present a value (for example, 50 ). As a general rule, when you're searching for a string literal, you need to surround the parameter with single quotation marks. If the parameter is a numeric data type, you don't need to use the quotation marks.

The ORDER BY Clause

Many databases organize the records within a table according to the primary key for that table. This is usually done to improve the search performance of the database. However, there are times when you want to control the order in which the data are presented. In those cases, you would use the ORDER BY clause. The general syntax is

 SELECT  FieldNameList  FROM  TableName  [WHERE  SearchCriteria  ] _      ORDER BY  FieldNames  [ASC  DESC] 

where the terms in brackets are optional. For example, if you want to present a list of all employees in alphabetical order, you might use

 SELECT * FROM Employee ORDER BY [Last Name] 

A variation would be

 SELECT * FROM Employee ORDER BY [Last Name],[First Name] 

which would present the same list, but also sort on the first name. In other words, if there are two employees named Smith, the one named John might appear before the one named Fred simply because the primary key causes them to appear that way. If you add First Name to the ORDER BY clause, the dataset would have them in alphabetical order across both the first and last names.

The SELECT query

 SELECT * FROM Employee ORDER BY [Last Name] DESC 

would return the records in reverse alphabetical order. The default order is in ascending ( ASC ) ordering.

You can also use a WHERE predicate, if you want:

 SELECT * FROM Customer WHERE Country = 'Italy' _    ORDER BY [Last Name], [First Name] 

This would produce an alphabetical list of all customers who live in Italy.

There are also SQL commands for inserting new records into a table, deleting records from a table, and for editing existing table records. We'll cover these in Chapter 25. For the moment, however, let's experiment with the basic SELECT query to see how it actually behaves.