SQL, or Structured Query language, is a nearly universal language used for database management. SQL is a declarative language, as-opposed to a procedural language such as Visual Basic. In a declarative language, you specify what you want, not how to do it. You do not need to tell SQL how to access a database; you only need to tell it what you want from the database. In a procedural language, you must tell the language to some degree how to accomplish a given task.
Procedural versus Declarative is the primary dividing line between a third generation language, such as Visual Basic, and a fourth-generation language, such as SQL. Whether SQL is a fourth-generation language is a tricky question. It was designed in the 1970s by Dr. E. F. Codd at IBM. At that time, languages were not even classified by generations.
We’ll start by examining the structure of SQL statements and the keywords used in them, and then we’ll look at numerous examples of SQL statements and develop a tool for retrieving data from databases with SQL statements.
You can use the SQLExec application, discussed later in this chapter, to experiment with SQL statements. The SQLExec application lets you select a database, execute SQL statements, and view the results (which is a RecordSet) on a grid.
The Format of SQL Statements
There are SQL statements for all types of operations you can perform on a database. You can use SQL statements to create a new database and to create and add tables and indices to it. You can use other SQL statements to update a database. SQL is a complete database manipulation language, but the most common use of SQL is to retrieve data from databases. Retrieving data from a database is called querying the database, and the SQL statements for querying databases are called Select statements, because they begin with the SELECT verb.
The general format of a Select SQL statement is:
SELECT (field list) FROM.(table list) WHERE(expression)
(field list)
This part of the statement is a list of fields to be included in the query, separated by commas, If the name of an element (field or table) contains a spate, you must enclose the name in square brackets ([]). For example, to include the Customer Name field in a query, you would enter [Customer Name].
Prefix the full name of each field with the name of the table to which it belongs, This notation ensures that field names are unique, even if multiple tables have fields with the same name. For example, the full name of the Total field in the Customer Orders table is [Customer Orders] Total. If the field name Total doesn’t appear in any other table, you can omit the table’s name and refer to it as Total.
The output of a Select query contains the rows that match the criteria, plus a row of headers. The headers are the names of the fields by default. When you display the results of a query on a data-bound Grid control, the names of the fields are displayed as headers. To change the headers, use the AS keyword after the field name. This addition can be handy when a field name th~t is appropriate in the context of a table would be unclear from the context of a query consisting of several tables or would clash with the field name in another table involved in the query. For example, if you have a Total field in the Customer Orders table, You can display the header Order Total for this field with the following syntax;
[Customer Orders]. [Total] AS [Order Total]
To select all the fields in a table, you can use the asterisk (.) instead of entering all the field names. For example, the following statement selects all the fields in the Orders table: SELECT * FROM Orders.
(table list)
This part of the statement is a list of all the tables on which the query is based. To retrieve fields from multiple tables, separate the field names with a comma. If your SQL statement is based on more than one table, it’s a good idea to prefix the field names with the name of the table to which they belong.
When you select fields from multiple tables, you must tell the SQL engine how to combine the tables. If you specify only the names of the tables, the result of the query will contain all possible combinations of the fields in each table. To combine fields-from multiple tables, you must create a so-called join, which is a fundamental concept in SQL Select statements that we will look at shortly.
(expression)
]This part of the statement is a logical expression that is used to filter the data and report back a subset of the RecordSet. You can use most Visual Basic built-in functions and operators to form this expression as well as the following SQL-specific operators
field_name BETWEEN value1 AND value2 Only rows in which the field_name is between value1 and value2 are returned. See the entry #date# for an example..
field_name IN(value1, value2) Only rows in which field_name is one of the values listed in parentheses are returned. You can specify any number of list elements inside the parentheses. The following statement retrieves customer records from certain cities:
SELECT Customers.CompanyName, Customers.ContactTitle,Customers.Clty, Customers.Country FROM Customers WHERE UCase(customers.city) IN(‘BERLIN”, ‘LONDON’, “BERN’, “PARIS’) ORDER BY Customers.Country
#date# This operator specifies dates within an expression. Dates are always specified using the U.S. system of month/ day /year. The following SQL statement retrieves all the orders placed in 1994 from the NWIND database:
SELECT Orders.ShipName, Orders.OrderDate , Orders.CustomerID FROM Orders WHERE OrderDate BETWEEN #1/1/94# AND #12/31/94#
LIKE This operator is one of the more powerful and complex of the SQL operators, and you can use it to select rows with a pattern string. To build the expression, use the special characters in Table 17.11.
ORDER BY (field list) This operator orders the rows of the Recordset according to the values of the specified fields. The following SQL statement creates a RecordSet of the names of all the customers in the Customers table of the NWIND database, sorted by country. Customers in the same country appear in the order of their city:
The table name need not appear in front of the field names in this statement.) The results of this statement are-shown below;
SQL Joins
Joins specify how you connect multiple tables in a query, and-there are three types of joins:
- Left Outer
- Right Outer
- Inner
A join operation combines all the rows of one table with the rows of another table. joins are usually.followed by a condition, which determines which records in either. side of the join will appear in the RecordSet.
Left Outer This join displays all the records in the left table and only those records of the table on the right that match certain user-supplied criteria. This join has the following syntax;
In a Left Outer join, all records in the primary table are matched according to specified criteria with records from a secondary table. Records from the left table (the one whose name appears to the left of the Left Join keyword) are included even if they do not match any records in the secondary table.
Right Outer This join is similar to the Left Outer join, except that-all the records in the table on the right are displayed and only the matching records from the left table are displayed. This join has the following syntax: .
In a Right Outer join, all records in the primary table are matched according to specified criteria with records from a secondary table. Records from the primary table are included even if they do not match any records in the,first table .
Outer joins return enormous RecordSets, and you should avoid using them. Inner joins, on the other hand, are common, and most SQL statements are built with them.
Inner This join returns the rows of both tables involved in the operation that. match according to specified criteria. The Inner join has the following syntax:
Because Inner joins are so useful, we’ll look at a couple of examples in detail. In their simplest format, Inner joins are similar to WHERE clauses. The following SQL statement.combines records.from the Titles and Publishers tables of the BIBLIO database if their PubID fields match. It returns a RecordSet with all the titles and their publishers:
You can retrieve the same Recordset using an Inner join, as follows:
The results of this statement are shown below:
Let’s look at a more complicated join operation. This time we are going to retrieve all titles along with their authors and publishers from the BIBLIO database. The following statement is quite complicated and represents a real-world situation:
The results of this statement are shown below:
In the previous statement, brackets indicate the field names that contain spaces. Also, all field names are prefixed with the corresponding table’s name, even though most field names are unique, to make the statement easier to read and understand,
The field list specifies the following fields:
- Titles.Title The book’s title
- Titles.ISBN The book’s ISBN
- Authors.Author The book’s author
- Titles.[Year Published] The book’s publication year
- Publishers.[Company Name] The book’s publisher
As you may recall from our discussion of the BIBLIO database, four tables are involved, and our query requires data from all tables. Actually, no data from the Title Author table are displayed in the query’s results, but this table links titles and authors (via the book’s ISBN), and we must use it in the SQL statement.
The fields won’t come directly from any single table. Instead, they come from a series of inner joins. The first Inner join is as follows:
[title author] INNERJOIN Titles ON[title author].ISBN =_ Titles.ISBN
This expression extracts (and matches) each title with its author’s ID if the ISBNs on both tables match. In essence, you get an author ID for each title. Let’s call this expression, which represents a RecordSet, Title AuthorIDs. This RecordSet is placed in parentheses and joined with another RecordSet: .
(Title-AuthorIDs INNERJOIN Authors ON[title author].Au_ID = Authors.Au_ID)
This RecordSet joins the Author IDs of the previous RecordSet with actual author names. So far, we have created a RecordSet with author names and titles. Let’s call it Titles Author. The last join operation joins the RecordSet that has author names and titles with the Publishers table as follows:
(Title-Author) INNERJOIN Publishers ONTitles.PubID = Publishers.PubID
This time, the publishers’ IDs must match. The final RecordSet contains titles, authors, and publishers, and our data Will come from it. The last keyword in this SQL statement, ORDER BY, determines the order in which the orders will be displayed.
Using SQL Statements
You now know the basic SQL statements, and you can extract data from a database with SQL statements using the SQLExec application (described in the section “VB6 at Work: The SQLExec Project,” later in this chapter). But how can you use SQL statements in your application?
SQL statements are basically RecordSet definitions. The RecordSet property of the Data control need not be a table’s name. You can be more specific by identifying certain rows of the table or combining data from more than one table. The RecordSet that the SQL statement selects from the database is assigned to the Data control,’ and the bound fields see the fields of this RecordSet. Let’s look at a simple example.
The Data1 application, presented earlier in this chapter, displays a few fields from the Customers table in the NWIND database. The Data control’s Record- Source property was set to the name of the Customers table, which you selected from a drop-down list. The list next to the name of the RecordSource property in the-Properties window is a combo box, and you ean also enter data in it.
Suppose you want the Data1 application to display customers from Germany only. To select part of a table, you supply the SQL statement that will create the corresponding RecordSet in the RecordSource property’s field. Follow these steps:
- Open the Datal application and select the Data control.
- In the Properties window, locate the RecordSource property and enter the following SQL statement.
SELECT • FROM Customers WHERE Country – ‘Germany’ - Now run the application and use the Data control’s buttons to navigate through the customers of the NWIND database whose country is Germany.
Because SQL statements are so common in databases, you can incorporate them in the design-of the database itself. If you expand the list of the RecordSource property for a Data control connected to the NWIND database, you will see that the list contains more than table names. It contains the names of SQL queries that are stored in the database itself. The Category Sales for 1995 item, for example, is not a table name. It’s the name of a query that returns sales for 1995, grouped according 10 product category. If you set the RecordSource property to this item, the resulting RecordSet’s fields will appear in the list next to the DataField property of the data-bound controls. For the Categories Sales for 1995 query, the field names that can be displayed in data-bound controls are CategoryName and CategorySales.
