As we've seen in the In Depth section of this chapter, you can use Structured Query Language (SQL) in data adapters to configure what those data adapters do. Although SQL is beyond the scope of this book, and Visual Basic itself is able to write SQL for you in many cases, it won't hurt to see an overview of basic SQL here.
In this case, I'll suppose that we're working with a database that contains a table named Customers that holds customer IDs, addresses, and so on, and a table named Orders, that holds customer orders. This database is based on the Northwind example database that comes with SQL Server 7.0, but you don't need to have that example database to follow along in this topic. In this topic, I'll just work through some basic SQL to get us started; the foundation of SQL is the SELECT statement, and I'm going to start with that.
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.
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)
The logical comparisons, such as < and> are familiar, of course, but what about BETWEEN, IN, and LIKE? They're coming up next.
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.
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:
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.
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
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.
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:
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.
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
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
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":
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")
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"
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.
As mentioned in the In Depth section of this chapter, to get the actual documents that define SQL, as standardized by the International Organization for Standardization (ISO), go to www.iso.org/iso/en/prods-services/catalogue/intstandards/CatalogueListPage.CatalogueList?ICS1=35&ICS2=60, which (as of this writing) lists the ISO's catalogue for SQL documents. Note that they're not free, however.