Using the SELECT Statement

You use the SELECT statement to get fields from a table; here's an example where I'm getting all the records in the Customers table, using the wildcard character *:

SELECT * FROM Customers

This returns a dataset that holds all the records in the Customers table. You also can use the SELECT statement to select specific fields from a table, like this, where I'm selecting the CustomerID, Address, and City fields of all the records in the Customers table:

SELECT CustomerID, Address, City FROM Customers

This returns a dataset that holds all the records in the Customers table, and each record will have a CustomerID, Address, and City field.

Using Where Clauses

In SQL, you can use the WHERE clause to specify criteria that you want records to meet. For example, to select all the records in the Customers table where the City field holds "Boston", you can execute this statement:

SELECT * FROM Customers WHERE City = "Boston"

You don't have to use an equals sign here; you can test fields using these operators:

• < (less than)

• <= (less than or equal to)

• > (greater than)

• >= (greater than or equal to)

• BETWEEN

• IN

• LIKE

The logical comparisons, such as < and> are familiar, of course, but what about BETWEEN, IN, and LIKE? They're coming up next.

Using the BETWEEN Clause

You can use the BETWEEN clause to indicate a range of values you will accept. For example, here's how to select all the records from the Customers table where the CustomerID record starts with the letter H (the CustomerID field is alphabetic, not numeric, in this table):

SELECT * FROM Customers WHERE CustomerID BETWEEN
"H*" AND "I*"

Note the use of wildcard characters: "H*" and "I*". Using these wildcards lets you specify that you want all the CustomerID values that start with the letter H.

Using the IN Clause

In SQL, you can use the IN clause to specify a set of values that fields can match, which is very helpful if you know exactly what you're looking for. For example, here's how I get records that have values in the City field that match Boston or York:

SELECT * FROM Customers WHERE City IN ("Boston",
"York")

Using the LIKE Clause

In SQL, the LIKE clause lets you use partial string matching, which you can specify with wildcards. Here's an example, where I'm selecting all the records from the Customers table where the City field matches the wildcard string "Los*":

SELECT * FROM Customers WHERE City LIKE "Los*"

This creates a dataset with records whose City fields match names "" Los*", such as Los Angeles or Los Altos.

Using the DISTINCT Clause

Sometimes, a database may hold duplicate values in the fields of the records of a table; for example, several customers come from the same cities, so they'd have the same value in the City field. You might want to take a look at all the cities represented, without duplicates, and you can use the DISTINCT clause for that, like this:

SELECT DISTINCT City FROM Customers

Using Logical Operations

You also can use logical operations on the clauses in your SQL statements. Here's an example in which I'm specifying two criteria: the City field cannot be either Boston or York, and there must be some value in the Fax field (note that I'm using the NULL keyword to test if there's anything in a field):

SELECT * FROM Customers WHERE City NOT IN ("Boston",
"York") AND Fax IS NOT NULL

You can use these logical operators to connect clauses: AND, OR, and NOT. Using AND means that both clauses must be True, using OR means either one can be True, and using NOT flips the value of a clause from True to False or from False to True.

Using the ORDER BY Clause

You can order the records in the dataset using an SQL statement. For example, here's how I order the records in the Customers table by CustomerID:

SELECT * FROM Customers ORDER BY CustomerID

You can also sort in descending order with the Desc keyword:

SELECT * FROM Customers ORDER BY CustomerID Desc

Using the AS Clause

Here's a handy one. The names of the fields in a dataset are the same as the names they had in the original table. You might want to change those names; for example, labeling a field "Name" might be more descriptive to the user than "au_lname". You can alias a field's name with the AS clause like this, where I'm changing ContactName to just Name for the purposes of the returned dataset:

SELECT ContactName AS Name FROM Customers

Now in the dataset, the ContactName field will be called Name.

Using Built-in Functions

It's also worth noting that SQL comes with a number of built-in functions such as COUNT, SUM, MIN, MAX, and AVG that let you work with the records in a dataset. Here is what these functions can do for you:

• COUNT— gets a count of records.

• SUM— adds values over records.

• MIN— finds the minimum value of a set of records.

• MAX— finds the maximum value of a set of records.

• AVG— finds the average value of a set of records.

Here's how you can use these functions in an SQL statement:

SELECT COUNT(EmployeeID) AS NumberEmployees,
AVG(DateOfBirth) AS AverageDateOfBirth, SUM(DateOfBirth)
AS TotalYears, MIN(DateOfBirth) AS MinDateOfBirth,
MAX(DateOfBirth) AS MaxDateOfBirth FROM Employees

Using the GROUP BY Clause

Here's another useful one—you can group records with the GROUP BY clause like this, where I'm grouping them by city:

SELECT * FROM Customers GROUP BY City

Using the HAVING Clause

You can use the SQL HAVING clause with GROUP BY; this clause is like the WHERE clause, but is used only with GROUP BY. This clause lets you specify additional criteria that records must meet, like this, where I'm specifying only records with cities that begin with "Los":

SELECT * FROM Customers GROUP BY City HAVING City LIKE "Los*"

Using the DELETE Statement

Although we've been using SQL in data adapters, note that not all SQL statements are designed to return datasets. For example, you can use the DELETE statement to delete records like this, where I'm deleting every record from the Customers table that has City values that are not Boston or York:

DELETE * FROM Customers WHERE City NOT IN ("Boston", "York")

Using the UPDATE Statement

You can use the UPDATE statement to update a database. Although Visual Basic will do the updating for us, it's worth noting that you can use SQL to do updates as well. Here's an example where I'm changing the City to Boston in all records where it's York now:

UPDATE Customers SET City = "Boston" WHERE City = "York"

Joining Tables

There's much more you can do with SQL. For example, you can work with relational databases—say you wanted to create a new dataset with customer contact names from the Customers table and the IDs of the items they've ordered from the Orders table. The key that relates these two tables is CustomerID, so you can set up the SQL query like this, making sure that the CustomerID field matches in each record you're joining:

SELECT Customers.ContactName, Orders.OrderID FROM
Customers, Orders WHERE Customers.CustomerID =
Orders.CustomerID

You also can do inner joins, where records must be in both tables, or outer joins, where records can be in either table.

This topic has just given us an introduction to SQL; of course, there's plenty more power available. But now we have all the SQL we'll need here—and more—under our belts.