
SQL is a language that ties in closely with the Microsoft Jet Engine and Data Access Object (DAO). By indicating relations between tables and other queries of a database, through SQL, records are temporarily created and passed back to the recordset object of the database object in Visual Basic.
This appendix serves as a quick reference and introduction to SQL. Using this reference, you should be able to create your own SQL statements in Visual Basic to better exploit the power of the Microsoft Jet Engine and its components.
SQL statements are broken into two distinct classifications, both of which are discussed in detail in this appendix. The first classification, the Data Definition Language (DDL), is used to create, modify, or remove the actual definitions in a particular database. The second classification, the Data Manipulation Language (DML), is used to create, modify, remove, or gather information that resides in the structure of the database. In other words, you would use DDL to create tables, fields, and indexes, and you would use DML to populate, alter, and retrieve the information that resides in the tables and field.
Table A.1 lists the seven available SQL commands that are the basis of any SQL statement. These commands indicate what kind of query the SQL statement actually is. Table A.1 also indicates the classification of each command listed, either definition (DDL) or manipulation (DML). A SQL command is used together with other components of a SQL statement to create either an action query or a selection query. Action queries are those that begin with a SQL command other than SELECT. As you might guess, a query beginning with SELECT is a selection query.
| COMMAND | Classification | Description |
| CREATE | Definition | Create a table, a field, or an index. |
| ALTER | Definition | Modify a table by adding a field or changing a field definition. |
| DROP | Definition | Drop a table or an index. |
| SELECT | Manipulation | Query a database with given parameters. |
| INSERT | Manipulation | Insert multiple records with one operation. |
| UPDATE | Manipulation | Change information throughout a range with given parameters. |
| DELETE | Manipulation | Remove records from the table. |
When using queries, you will use various clauses that are implemented in the SQL statement. Table A.2 lists and describes the available clauses for SQL used by the Microsoft Jet Engine.
| CLAUSE | Descriptions |
| FROM | Specifies the table from which data is queried |
| WHERE | Specifies the condition(s) for the query |
| GROUP BY | Specifies the group(s) for the selected information |
| HAVING | Specifies the condition(s) for each group in the query |
| ORDER BY | Specifies the order of the query |
The first of these clauses, FROM, is used to indicate the table or query used to gather the information for the SQL statement. More than one table or query can be listed in the statement, using the FROM clause. When doing so, you are creating at least one join in your query. Joins are discussed later in this appendix.
The second clause, WHERE, lists the condition or conditions that must be met for a record to be included in the query results. Each condition is evaluated using conditional operators. Multiple conditions are listed using logical operators. Table A.3 lists available conditional and logical operators.
| OPERATOR | TYPE | Condition Is Met When |
| AND | Logical | Both expressions are true. |
| OR | Logical | Either expression is true. |
| NOT | Logical | The expression is false. |
| < | Comparison | The first expression is less than the second expression. |
| <= | Comparison | The first expression is less than or equal to the second expression. |
| > | Comparison | The first expression is greater than the second expression. |
| >= | Comparison | The first expression is greater than or equal to the second expression. |
| = | Comparison | The first expression is equal to the second expression. |
| <> | Comparison | The first expression is not equal to the second expression. |
| BETWEEN | Comparison | The value belongs to a specified set of values. |
| LIKE | Comparison | The value matches the pattern specified. |
| IN | Comparison | The record belongs to a particular group in a database. |
The third SQL clause, GROUP BY, is used to group the query's result set. GROUP BY uses at least one field name from a table or query listed in the statement's FROM clause to evaluate the records and group like values. You can also use an aggregate function in a SQL statement to create summaries of the groups in the recordset. The list of available aggregate functions is shown in Table A.4.
| AVG | Returns the average value of a specified field |
| COUNT | Returns the number of records in a query |
| SUM | Returns the sum of the values in a specified field |
| MAX | Returns the largest value in a specified field |
MIN Returns the smallest value in a specified field
The fourth SQL clause, HAVING, specifies conditions (of the same syntax used for the WHERE clause) the groups must meet to be included in the resulting recordset.
The last SQL clause, ORDER BY, uses field names to order the result set in a specified manner. More information on all the SQL commands and clauses is presented in detail throughout this appendix.
DDL statements are used to create, alter, or remove tables, fields, and indexes from a database. The three SQL commands used to do so are CREATE, DROP, and ALTER. All three are action queries.
Action queries in Visual Basic can be initiated in one of two ways. The first method of invoking an action query is by using a QueryDef object. The second is by using the Execute method of the database object, as shown here:
db.Execute sSQLStatement
This assumes that db is a database object variable successfully set to a valid database, and sSQLStatement is a valid string variable containing a valid action SQL statement.
The CREATE command is used to create tables and indexes in a specified database. To create a table in a given database, use the CREATE TABLE statement with the syntax shown here:
CREATE TABLE table (fld1 type [(sz)] [NOT NULL] [idx1] [, fld2 type [(sz)][NOT NULL] [idx2] [, ...]] [, CONSTRAINT MFidx [, ...]])
In the preceding syntax, the table name succeeds the actual CREATE TABLE statement and is followed by a comma-delimited list of field definitions that are used to create the specified table. At least one field must be listed in parentheses as shown. Any valid database type can be used to indicate the type of field to be created, and a size (sz) can be specified for text and binary fields.
The following example creates a new table in the db database with three fields:
db.Execute "CREATE TABLE Customers (CustNum INTEGER, " _& "CustName TEXT (25), Address TEXT (30))"
The new table created is called Customers. It contains an integer field named CustNum and two text fields named CustName and Address.
Using the NOT NULL optional parameter, you can specify that the field can never have a NULL value. If a NULL value is assigned to a field created with this option, a runtime error will occur. The following example is a more bulletproof table definition:
db.Execute "CREATE TABLE Customers (CustNum INTEGER NOT NULL, " _& "CustName TEXT NOT NULL (25), Address TEXT (30))"
This version of the CREATE TABLE action query specifies that the Number and CustName field values cannot consist of any NULL values. The Address field, however, can.
You can also use the CONSTRAINT clause in a CREATE TABLE statement to create an index either on an individual field or on multiple fields. The following example creates an index in a new table:
db.Execute "CREATE TABLE Customers (" _ & "CustNum INTEGER CONSTRAINT CustNumIndex PRIMARY, " _& "CustName TEXT (25), Address TEXT (30))"
This example creates a table with three fields as shown before, but it also adds an index named CustNumIndex for the CustNum field.
To create an index on multiple fields, you can also use the CONSTRAINT clause as shown in this example:
db.Execute "CREATE TABLE Customers (CustNum INTEGER, " _ & "CustName TEXT (25), Address TEXT (30), " _& "CONSTRAINT CustIndex UNIQUE (CustNum, CustName))"
Notice that the new index created by the CONSTRAINT clause, named CustIndex, is composed of both the CustNum and the CustName fields described in the SQL statement. The difference between creating an individual field index and creating a multiple field index with the CREATE TABLE statement is that, with the individual fields, you indicate the CONSTRAINT clause after the given field, without a comma. Creating indexes of multiple fields with the CONSTRAINT clause, you specify the index after the fields in the index are created, separated by a comma, listing the individual fields, as shown in the last example. More information on the CONSTRAINT clause can be found in the "Using the CONSTRAINT Clause" section, later in this appendix.
You do not necessarily have to create indexes in the CREATE TABLE statement--you can also explicitly create indexes using the CREATE INDEX statement as shown in this example:
db.Execute "CREATE UNIQUE INDEX CustIndex ON Customers (CustNum, CustName)"
This statement leads to the same results as derived by the last CREATE TABLE statement.
The syntax for the CREATE INDEX statement is as follows:
CREATE [UNIQUE] INDEX idx ON table (fld1 [ASC|DESC] [, fld2 [ASC|DESC], ...])[WITH {PRIMARY | DISALLOW NULL | IGNORE NULL}]
When using the CREATE INDEX statement, specifying the UNIQUE option as shown in the preceding example, you are telling the Jet Engine that no two combinations, for the values of the fields listed, are to be allowed, thus creating a unique index on the fields. By default, indexes are created in ascending order (ASC keyword), but you can specify to list the order of the values of an index in descending order by using the DESC keyword after the corresponding field name.
The last portion of the CREATE INDEX statement allows you to specify, in more detail, how an index is to be handled. You do this with the WITH clause of the CREATE INDEX statement. To create a primary key on the table indicated, use the WITH PRIMARY statement. This statement creates a unique index that is now the primary key. Only one primary key can exist per table; attempting to create an additional one will result in a runtime error.
You can also prohibit the use of NULL values in an index, with the WITH DISALLOW NULL statement, or you can allow NULL values with the WITH IGNORE NULL statement.
The second command belonging to the SQL Data Definition Language is ALTER. You can use the ALTER TABLE statement to complete four distinct tasks:
You can add or delete only one field or index with each ALTER TABLE statement. The syntax for ALTER TABLE is shown here:
ALTER TABLE table {ADD {COLUMN fld type[(size)] [NOT NULL] [CONSTRAINT idx]| CONSTRAINT MFidx} | DROP {COLUMN fld | CONSTRAINT indexname}}
The following example creates a new field in an existing table named Customers:
db.Execute "ALTER TABLE Customers ADD COLUMN PhoneNum TEXT (12)"
Just as you can add a field to a table, you can delete one, as shown in this example:
db.Execute "ALTER TABLE Customers DROP PhoneNum"
You can also specify new indexes just as you did with CREATE TABLE, except that you can add the index after the table is already created, as shown here:
db.Execute "ALTER TABLE Customers " _
& "ADD CONSTRAINT NameAndPhoneIndex (CustName, PhoneNum)"
Now you can delete that index with this final example:
db.Execute "ALTER TABLE Customers DROP CONSTRAINT NameAndPhoneIndex"
So far you have seen the CONSTRAINT clause in both the CREATE and the ALTER SQL commands. Now take a closer look at this clause and discover how powerful it really is.
In its simplest form, a constraint is an index. Not only does the CONSTRAINT clause allow you to create or remove indexes as shown in the CREATE and ALTER command sections, but it also allows you to create primary and foreign keys to define relations and enforce referential integrity.
As shown earlier, by example, there are two versions of the CONSTRAINT statement, one for individual field indexes and one for multiple field indexes. The syntax for a single field index is shown here:
CONSTRAINT name {PRIMARY KEY | UNIQUE | NOT NULL | REFERENCES foreigntable[(foreignfield1, foreignfield2)]}
The syntax for a multiple field index CONSTRAINT is this:
CONSTRAINT NAME
{PRIMARY KEY (primary1[, primary2 [, ...]]) |
UNIQUE (unique1[, unique2 [, ...]]) |
NOT NULL (notnull1[, notnull2 [, ...]]) |
FOREIGN KEY (ref1[, ref2 [, ...]])
REFERENCES foreigntable
[(foreignfield1 [, foreignfield2 [, ...]])]}
The name of the index to be created is specified directly after the CONSTRAINT keyword. In either version, you can create three types of indexes:
You can create a foreign key to create a relationship between multiple fields as in the following example:
db.Execute "ALTER TABLE Customers " _ & "ADD CONSTRAINT NewIndex " _& "FOREIGN KEY (CustNum) REFERENCES Orders (CustNum)"
This example creates a relation between the Customers table and the Orders table of the db database object. A new index is created, NewIndex, that links the two tables based on the CustNum field name.
The DROP command comes in two flavors: DROP TABLE and DROP INDEX. As you might easily guess, the DROP TABLE statement removes an existing table from a database as shown here:
db.Execute "DROP TABLE Customers"
The DROP INDEX statement works similarly to the DROP TABLE statement, except that it removes an index from a given table as shown in this example:
db.Execute "DROP INDEX CustNumIndex ON Customers"
For reference, the syntax of the DROP command is as follows:
DROP {TABLE table | INDEX idx ON table}
DML commands create a mix of action and selection queries. The commands SELECT, INSERT, UPDATE, and DELETE are used to quickly and efficiently, through the use of the Jet Engine performance, manipulate the data residing in specified tables.
The SELECT command differs from all other SQL commands in that it is a part of a selection query rather than an action query. When you're using a selection query in a Visual Basic application, it is most commonly set in a recordset object of a given database. Assuming that rs is a valid recordset object and db is a database object already set to a valid database file, you can use the following statement to gather all the records in the Customers table of the db object:
Set rs = db.OpenRecordset("SELECT * FROM Customers")
This example uses the most generic and basic form of a SELECT statement. This section explains the various components of the SELECT statement and describes how the programmer can use each component to effectively sort and filter records according to his or her needs.
The syntax for the SELECT statement is as follows:
SELECT [predicate] { * | table.* | [table.]field1 [AS alias1] [, [table.]field2 [AS alias2] [, ...]]} FROM tableexpression [, ...] [IN externaldatabase] [WHERE... ] [GROUP BY... ] [HAVING... ][ORDER BY... ]
It is quite understandable to be overwhelmed by this declaration, but not all the clauses included in this syntax are necessarily used together--although they could be.
Take the following SELECT statement, for example:
SELECT DISTINCT CUSTNUM, CUSTNAME
FROM Customers WHERE (CustNum >= 100) AND (CustNum <= 120)ORDER BY CustName;
This statement retrieves only the fields CustNum and CustName from the Customers table. The WHERE clause in this statement shows two conditions. The first condition says that the CustNum field values must be greater than or equal to 100. The second condition states that the CustNum field values must be less than or equal to 120. So far, you can see that the SELECT statement shown previously will return the CustNum and CustName for all records with a customer number between 100 and 120. The last part of this statement says to order the records returned by the query by the CustName field--in other words, in alphabetical order. You can also request the records to be returned in reverse alphabetical order by placing the DESC keyword after the ORDER BY CustName clause.
You can use multiple conditions in a SELECT statement as shown in this statement. For a complete list of operators for conditions, both logical and comparison, see Table A.4 at the beginning of this section.
It is important to note that the DISTINCT keyword following the SELECT command is called a predicate. This keyword indicates that only distinct (unique) combinations of fields will be returned. In this example, if you had three records in the Customers table with the same customer number and name, but with different addresses, only one record would be returned. If you were to add the Address field to the list of fields in the SELECT statement, you would receive three records for the same customer number and name because the combination (number, name, and address) is unique for each record returned.
The DISTINCT keyword is not the only predicate available in a SELECT statement. Table A.5 lists the four available predicates for a SELECT statement.
| PREDICATE | Description |
| ALL | Returns all records, even duplicates |
| DISTINCT | Returns only unique records, based on fields specified in the statement |
| DISTINCTROW | Returns only unique records, based on all fields in the specified table(s), even those not listed in the SELECT statement |
| TOP | Returns the first n records or the top p percentage of records of the selected recordset |
The default predicate used in SELECT statements not indicating a predicate is ALL. ALL returns all records that meet the conditions of the selection query.
The TOP predicate is used in conjunction with the ORDER BY clause of a SELECT statement. It can be used in one of two ways:
Following is an example of the TOP predicate:
SELECT TOP 10 PERCENT OrderNum, OrderPrice FROM OrdersORDER BY OrderPrice;
This statement would return the top 10% of records in the Orders table, based on the OrderPrice field. If you were to order the recordset returned by this statement by the OrderNum field, you would get the top 10% of records based on the OrderNum, which would probably not make a whole lot of sense.
It is worth pointing out that if there are identical values for the ORDER BY field that is specified, there is a chance that more than the desired number of records will be returned. Assume, for instance, that I wanted to get the top 10 parts based on quantity ordered. If there were 9 distinct quantity amounts that topped the list and 2 others that followed, with the same value, I would have 11 records in all when I only wanted 10. The same is true for the TOP p PERCENT format. If I wanted to retrieve the top 2% of part prices with my selection query, I would use the following statement:
SELECT TOP 2 PERCENT PartName FROM OrdersORDER BY PartPrice;
This example returns the names of the items priced in the top 2%. If I have numerous items all with the same price, they are considered one item, so I can very easily retrieve more records than I expected. It is also important to understand what you are asking for when using the TOP predicate. For instance, it might be easy to think that you are getting the top 10 customers based on moneys spent, but you must specifically state that you want the selection to be made on a field such as TotalMoneySpent as compared to TotalOrderPrice.
You can also group records that are returned from a query by using the GROUP BY clause in a SELECT statement, as in the following example:
SELECT PartName, Count(PartName) FROM Orders WHERE (PartPrice > 10)GROUP BY PartName;
This SELECT statement gathers and groups all the records from the Orders table whose PartPrice field values are greater than 10. These records are then grouped by the PartNum field only, and a count of the number of each type of part sold is returned. In other words, the resulting recordset contains the distinct names of parts that cost over $10 and a count of the number of orders for each part.
The function COUNT, in this example, is called an aggregate function. Other aggregate functions include AVG, SUM, MAX, and MIN. Table A.4 is a list of aggregate functions and their descriptions.
Another example of using an aggregate function is shown here:
SELECT PartName, SUM(PartPrice) FROM Orders WHERE (PartPrice > 10) GROUP BY PartNameHAVING (SUM(PartPrice) > 1000) AND (PartName LIKE "WIDGET*");
This example introduces the HAVING clause. This clause acts very similarly to the WHERE clause, except that it tests the condition specified after the grouping has occurred, unlike the WHERE clause, which tests conditions of records to be included in the groups. The preceding SELECT statement gathers distinct part names and a sum of the total prices sold from the Orders table. The only condition for records to be included in this grouping is that the price of each part be greater than 10.
After the groups have been created by the Jet Engine, only the groups with a total PartPrice summary of more than 1,000 are included. In addition to this condition, you can see the use of the LIKE keyword. You can use the LIKE keyword anywhere you can specify a condition. LIKE uses pattern matching to check whether a field should be included in a resulting recordset. In this example, only groups with a PartName field value beginning with the characters WIDGET will be included in the recordset.
Joins are a very commonly used function of a SELECT statement. Joins are used to create temporary relationships between tables when evaluating a selection query. Following is the syntax for using joins in a SELECT statement:
SELECT ...FROM table1 [LEFT | RIGHT] JOIN table2 ON (table1.fld1 CompOp table2.fld2)
CompOp is a comparison operator (refer to Table A.4). A join effects the FROM clause of a SELECT statement as shown in the preceding syntax declaration.
Left and right joins mirror each other in definition. A left join (called a left outer join) includes all records from table1, even if no related records are found in table2. A right join (called a right outer join) includes all records from table2, even if no related records are found in table1. Take the following statement, for example:
SELECT Customers.*, Orders.*FROM Customers LEFT JOIN Orders ON (Customers.CustNum = Orders.CustNum);
This example returns all fields from the Customers table and only the fields from the Orders table that have matching CustNum fields. In other words, the resulting recordset from this statement would include all the customers and information on orders for those customers that did order.
To create a join that returns only records that are included in both tables (inner join), you do not have to use the JOIN clause at all, as shown in this example:
SELECT Customers.*, Orders.* FROM Customers, OrdersWHERE (Customers.CustNum = Orders.CustNum);
This statement would return a shorter recordset than the earlier statement, given that there are customers who did not place orders. In this SELECT statement, only the customers who placed orders qualify to be returned in the recordset.
Although it is unnecessary to do so, there is a SQL clause to specify inner joins, as shown in this example:
SELECT Customers.*, Orders.*FROM Customers INNER JOIN Orders ON (Customers.CustNum = Orders.CustNum);
This statement is the equivalent to the preceding one, and in many cases it is actually easier to read and comprehend at a glance.
You might also care to specify more than one condition in any of your joins, as shown in this example:
SELECT Customers.*, Orders.* FROM Customers INNER JOIN Orders ON ((Customers.CustNum = Orders.CustNum)AND (Customers.Flag = Orders.Flag));
If you really want to go overboard, and many times you might find that you need to, you can nest joins within each other as shown in the following example:
SELECT Customers.*, Orders.*, Tax.* FROM Customers INNER JOIN (Orders INNER JOIN Tax ON (Orders.State = Tax.State))ON (Customers.CustNum = Orders.CustNum));
This SELECT statement returns all the records in which there is a customer number from the Customers table and Orders table and a state in the Orders table and the Tax table.
You can nest either a LEFT JOIN or a RIGHT JOIN inside an INNER JOIN; however, you cannot nest an INNER JOIN inside either a LEFT JOIN or a RIGHT JOIN.
The INSERT command is used in the INSERT INTO statement to create an append query, a type of action query. You can use this command to add single or multiple rows to a table. This is the syntax for adding a single row using the INSERT INTO statement:
INSERT INTO table [(fld1[, fld2[, ...]])]VALUES (val1[, val2[, ...])
Using this syntax, you can add single rows to a table. You must specify each field and its value that you want to add. If you leave out a field and its corresponding value, a NULL value is automatically inserted into the field. The new records are appended to the end of the table. Following is an example of how to add a row onto a specific table:
INSERT INTO Customers (CustNum, CustName)VALUES (1, "Kimberly");
If other fields are in the Customers table, they are assigned a NULL value.
The syntax for adding multiple rows differs slightly:
INSERT INTO table2 [(fld1[, fld2[, ...]])] SELECT [table1.]fld1[, fld2[, ...]FROM ...
This syntax takes a specified selection of records from table1 and inserts them into table2. The number of fields for both tables must be the same and in the correct order. The following example demonstrates the INSERT INTO statement:
INSERT INTO Delivered (CustNum, DelPart, PriceCharged) SELECT CustNum, PartName, PartPrice FROM OrdersWHERE (Delivered = True);
This example inserts all the delivered orders from the Orders table into the corresponding fields of the Delivered table.
The INSERT INTO statement appends records at the end of an existing table, but you can also use a similar statement and selection of records to create a new table, as in this example:
SELECT CustNum, PartName, PartPrice INTO DeliveredBackup FROM OrdersWHERE (Delivered = True);
This example creates a new table called DeliveredBackup, with the identical fields and properties of the fields listed in the SELECT clause from the Orders table. The new table would include all the records with the Delivered field value of True.
The UPDATE command is used to set information in a current table to a new value. The syntax for the UPDATE command is as follows:
UPDATE table SET valueWHERE criteria
The value of the SET clause in the UPDATE statement is an assignment expression that will alter the current value of the selected records of the table specified. Here is an example of the UPDATE statement:
UPDATE OrdersSET OrderTotal = (PartPrice * Quantity);
If no WHERE clause is specified, as in this example, the UPDATE query makes the necessary changes to all the records in the specified table. In this example, the OrderTotal field is calculated based on the PartPrice and Quantity field values for the corresponding record.
With a single SQL UPDATE query, it's possible to update multiple fields for each record, as shown in this next example:
UPDATE Orders SET OrderTotal = (PartPrice * Quantity), Discount = (PartPrice * .05) * QuantityWHERE (OrderDate < #1/1/1998#);
This example gives a new value to both the OrderTotal field and the Discount field for all records whose OrderDate field value is before January 1, 1998. Notice the use of the number signs (#) before and after the date criteria in the WHERE clause. This is the correct notation for specifying dates in a condition.
Notice that you cannot reverse UPDATE queries and must take care to ensure that the correct records are selected for the update. To check which fields are going to be updated before the execution of the UPDATE statement, create a selection query with the same criteria as the UPDATE query.
The DELETE command is used to perform bulk deletions of records within a specified table in one operation. You can specify conditions to select the records from a table to delete. When you use the DELETE command, entire records are deleted, not individual fields. The syntax for the DELETE command is shown here:
DELETE table.* FROM tableWHERE ...
Following is an example of a DELETE statement:
DELETE * FROM CustomersWHERE (LastOrderDate <= #6/20/73#);
This example deletes all the records in the Customers table in which the last order date was June 20, 1973, or earlier (You don't need their business anyway!).
You can use the DELETE command to delete all the data in a particular table while maintaining the actual structure and definition of the table, as shown in this example:
DELETE * FROM Orders;
This command could obviously be very dangerous; therefore, use this form of the DELETE command with extreme caution.
SQL is a convenient, effective, and easy way to organize data for your applications. By using the commands described in this appendix, you can select, filter, order, and group records in any way you need. The SQL is much more involved than is described in this brief reference, but by knowing just these basic commands and functions, you can create just about any desired recordset from your raw data.
© Copyright, Macmillan Computer Publishing. All rights reserved.