You have learned the importance of creating a good design for your relational database. Now how do you manage the data that is stored there? This chapter covers SQL (Structured Query Language), which is the ANSI-standard relational database language used for managing objects, data, and security. You will learn about basic SQL statements and commands to allow you to access and manipulate your data.
Because the examples in this book involve either Microsoft SQL Server or Microsoft
Access databases, both SQL contexts are covered, with highlights of the fundamental
differences of each product's adaptation of SQL. In every case, you see an example
for querying tables in the pubs database that comes with SQL Server. The
Microsoft SQL Server statements are contrasted with the comparable Microsoft Access
SQL statements only where the syntax differs. Figure 7.1 outlines the structure of
the pubs tables authors, titleauthor, and titles used in the examples.
FIGURE
7.1. Structure of the authors,
titleauthor, and titles tables in the pubs database.
The number of different types of SQL statements you can execute from your application
is tremendous. Using SQL, you can perform any function from a simple table query,
to creating tables and stored procedures, to assigning user rights. In this book,
we want to stay in the realm of retrieving, updating, and reporting on data from
Visual Basic. For this purpose, the most important SQL statements you need to know
are SELECT, INSERT, UPDATE, DELETE, CREATE
TABLE, and SELECT INTO. Brief descriptions of these commands are shown
in Table 7.1.
Table 7.1. Important SQL statements.
| Command | Description |
| SELECT | Retrieves columns and rows from a table or tables |
| INSERT | Adds rows to a table |
| UPDATE | Updates columns in existing rows in a table |
| DELETE | Deletes rows from a table |
| CREATE TABLE | Creates a new table based on the specified table schema |
| SELECT INTO | Creates a new table based on rows and columns output by a SELECT statement |
The SELECT statement is used to retrieve rows of data from your database
into a recordset object. The SELECT statement specifies the columns of data
you want to retrieve, where the columns are stored, what criteria the returned data
must meet, and the sort order of the data. A SELECT statement can further
group rows of data together and assign retrieval criteria at the group level. The
components of the SELECT statement are described in Table 7.2.
Table 7.2. Components of a SQL SELECT Statement.
| Component | Description |
| SELECT | Specifies the columns of data to be retrieved |
| FROM | Specifies the tables from which to retrieve rows |
| WHERE | Specifies criteria that returned data must meet |
| GROUP BY | For aggregate queries, specifies the returned columns by which the data is to be grouped |
| HAVING | For aggregate queries, specifies criteria that the aggregate value returned must meet |
| ORDER BY | Specifies the sort order of the returned rows |
SELECT au_id, au_lname, au_fname, state, zip, contract FROM authors
The results of this simple query (shown in Figure 7.2) reveal that the data in
the selected columns was returned for each row that exists in the authors table.
FIGURE 7.2.The
results of a simple SELECT statement.
Adding the WHERE Clause Using the same basic SELECT statement,
you can narrow down your results by adding a WHERE clause. Suppose that
you only want to know the names of authors located in the state of California. In
the following example, only those records whose state column has a value of `CA'
will be returned, as shown in Figure 7.3.
SELECT au_id, au_lname, au_fname, state, zip, contract FROM authors WHERE state = `CA'
FIGURE 7.3.The results of a simple SELECT statement demonstrating the WHERE clause.
Your WHERE clause can use several columns as criteria for row retrieval. If you additionally wanted only those rows whose contract value was zero, you would use a logical AND, as in the following:
SELECT au_id, au_lname, au_fname, state, zip, contract FROM authors WHERE state = `CA' AND contract = 0
You can also have the query return rows for authors in any state except California. The way to indicate inequality in SQL is to combine a greater-than and a less-than sign (<>).
SELECT au_id, au_lname, au_fname, state, zip, contract FROM authors WHERE state <> `CA'
The WHERE clause can use a number of different comparison operators for
checking field values. Table 7.3 lists and describes these operators.
Table 7.3. WHERE Clause Comparison operators.
| Operator | Description |
| = | Is equal to. |
| > | Is greater than. |
| < | Is less than. |
| >= | Is greater than or equal to. |
| <= | Is less than or equal to. |
| < > | Is not equal to. |
| IN | Is in a specified list of values or in the results of a specified subquery. |
| BETWEEN..AND | Is between two values. |
| LIKE | Contains the same pattern as a specified string. The pattern being compared is a string that contains one or more wildcard characters. You should refer to Access and SQL Server help for a list of these operators. |
SELECT au_id, au_lname, au_fname, state, zip, contract FROM authors WHERE state = `CA' ORDER BY au_lname
The resulting rows are returned in ascending alphabetical order by the author's
last name, as shown in Figure 7.4.
FIGURE 7.4.The
results of a simple SELECT statement demonstrating the ORDER BY
clause.
You can also sort the records in a field in descending order using the keyword DESC. Suppose you want to first sort the records in descending order by contract and then sort in ascending order by author last name. An example is shown in the following SQL statement:
SELECT au_id, au_lname, au_fname, state, zip, contract FROM authors WHERE state = `CA' ORDER BY contract DESC, au_lname
Using the WHERE Clause to Join Tables You have seen several ways to use SQL SELECT statements to look at the data in the authors table by specifying columns to return, assigning retrieval criteria, and sorting the results. But in the real world, will you want to look at data from only a single table at a time? The answer is most likely "No." The data in the authors table has relationships with data in other tables in the pubs database. For instance, what if you want to know what titles were written by these authors? The author table alone won't tell you this. You must search the cross-reference table titleauthor, which links author to title by the columns au_id and title_id. Unfortunately, this isn't enough. Most of us don't recognize authors or titles by IDs or codes. We know them by names. Because the names of the authors are located in the table author, the titles are located in the table title, and the relationships between the two are located in titleauthor, you need a way to tie these three tables together in a single SELECT statement. You can do this using the WHERE clause.
SELECT authors.au_lname, authors.au_fname, titles.title FROM authors, titleauthor, titles WHERE authors.au_id = titleauthor.au_id AND titleauthor.title_id = titles.title_id ORDER BY authors.au_lname, authors.au_fname, titles.title
The resulting list of authors and their titles is shown in Figure 7.5. Note the way you referenced the column names in this statement. When retrieving data from multiple tables in a single SQL statement, you must preface the column names that appear in more than one of the tables in the FROM clause with their associated table names in order to avoid an ambiguity error. Using the JOIN Operator to Join Tables Another way to join tables is by using the JOIN operator. This is the ANSI standard method for joining tables and uses the following syntax:
SELECT column1, column2, column3 FROM table1 JOIN table2 ON join criteria
FIGURE 7.5. The results of a SELECT statement demonstrating the use of the WHERE clause in joining tables.
The JOIN operator indicates how rows should be returned from the joined
tables. The ON clause acts like a WHERE clause, indicating which
fields in the joined table should be compared for equality. Table 7.4 describes the
different JOIN operators:
Table 7.4. Table JOIN Operators.
| JOIN Operator | Description |
| CROSS JOIN | Returns each row from the first table joined with each row from the second table, resulting in a returned number of rows equal to the product of the two tables' rowcounts. |
| INNER JOIN | Returns all rows from each table that meet the WHERE clause search criteria and where there is a match on the joined fields in the ON clause. |
| LEFT [OUTER] JOIN | Returns all rows from the table on the left side of the join that meet the WHERE clause search criteria and only those from the right side of the join where there is a match on the joined fields in the ON clause. |
| RIGHT [OUTER] JOIN | Returns all rows from the table on the right side of the join that meet the WHERE clause search criteria and only those from the left side of the join where there is a match on the joined fields in the ON clause. |
| FULL [OUTER] JOIN | Returns all rows from each table that meet the WHERE clause search criteria and where there is no match on the joined fields in the ON clause. |
SELECT authors.au_lname, authors.au_fname, titles.title
FROM (authors INNER JOIN titleauthor
ON authors.au_id = titleauthor.au_id) INNER JOIN titles
ON titleauthor.title_id = titles.title_id
ORDER BY authors.au_lname, authors.au_fname, titles.title
Access supports the following JOIN operators: INNER JOIN, LEFT JOIN, and RIGHT JOIN. SQL Server 6.5 supports all of the listed joins. Note that versions of SQL Server earlier than 6.5 do not use this syntax for joining tables. Instead, in those versions, the fields from two tables must be joined in the WHERE clause as shown in the previous example. To indicate a LEFT or RIGHT outer join in the WHERE clause, you would use the *= and =* operators, respectively, in place of the = operator. The following two left outer JOIN statements produce the same result and illustrate the two methods for joining tables:
SELECT column1, column2, column3 FROM table1 LEFT JOIN table2 ON table1.column1 = table2.column1 SELECT column1, column2, column3 FROM table1, table2 WHERE table1.column1 *= table2.column1
One use for an outer join would be to list all authors and, if they wrote a book,
list the title_id of that book (through an outer join with titleauthor).
If the author did not write a book, you would still be able to see the author's name
listed, but the title_id would be returned as NULL. Aggregate Functions
in SQL Statements Aggregate functions return summary values for specified columns
or expressions in the form of sum totals, number of records, averages, and so on.
The aggregate function might return a single value for all rows represented by the
query. If a GROUP BY clause has been added to the SQL statement, such summary
values will be calculated at each level of grouping. Table 7.5 lists the aggregate
functions you can use. Note that the StDev and Var functions are
not available in SQL Server.
Table 7.5. Aggregate Functions.
| Aggregate Function | Description |
| Avg | Returns the average of all values in the columns by taking their sum and dividing by the count. |
| Count | Returns the number of non-null values in the specified column or expression. If the expression is an asterisk (such as count(*)), the result will be the number of rows in the query. |
| Max | Returns the maximum value in the specified column or expression. |
| Min | Returns the minimum value in the specified column or expression. |
| StDev, StDevP | Return estimates of the standard deviation for a population or population sample (Access only). |
| Sum | Returns the sum of values in the specified column or expression. |
| Var, VarP | Return estimates of the variance for a population or a population sample (Access only). |
SELECT count(title) `titles' FROM titles
The result is the total number of title records in the titles table, as shown
in Figure 7.6.
FIGURE 7.6. An example of the aggregate count function on the
titles table.
SELECT count(title) AS titles FROM titles SELECT count(title) `titles' FROM titlesSELECT `titles' = count(title) FROM titles
Use of the GROUP BY Clause The GROUP BY statement accepts
a list of columns specifying how the results of a SELECT statement should
be grouped. The SELECT statement returns one row for each set of distinct
values in columns specified in the GROUP BY list. For example,
Figure 7.5 shows a returned list of authors and the books that they wrote. Because
some of the authors wrote more than one book, their names appear in more than one
of the returned rows. By adding a GROUP BY clause we can group
the returned rows by author last name and first name so that only one row will be
returned for each distinct author name.
In the following example, we will apply the GROUP BY clause to the SELECT statement, the results of which are shown in Figure 7.5. Instead of returning the authors' titles, we will use the aggregate count function to determine the number of titles associated with each author. Because we have grouped the returned set of records by author last name and first name, each author will have its own title count, rather than the count being performed on an entire table, as in the previous example of the count function.
SELECT authors.au_lname, authors.au_fname, count(titles.title) `titles' FROM authors, titleauthor, titles WHERE authors.au_id = titleauthor.au_id AND titleauthor.title_id = titles.title_id GROUP BY authors.au_lname, authors.au_fname
With the results shown in Figure 7.7, you can determine how many books were written
by each author.
FIGURE 7.7. An example of a GROUP BY query.
SELECT a.au_lname, a.au_fname, count(c.title) `titles' FROM authors a, titleauthor b, titles c. WHERE a.au_id = b.au_id and b.title_id = c.title_id GROUP BY a.au_lname, a.au_fname
The following SQL statements illustrate two ways to assign a table alias by assigning the simple alias a to the table author. Note that the syntax of the first example can be used in both Access and SQL Server, but only Access will support the second example.
SELECT a.au_lname, a.au_fname FROM authors a SELECT a.au_lname, a.au_fname FROM authors AS a
Use of the HAVING Clause Like the WHERE clause, the HAVING clause is used for specifying criteria for data to be returned in a query. The difference lies in the level at which the criteria is checked. The WHERE clause uses criteria to restrict rows of data returned by a query. The GROUP BY clause then forms the returned rows into groups and calculates any aggregate values. The criteria in the HAVING clause is then used to restrict groups of rows according to the group level data.
The following example shows how the HAVING clause is used to return only the names of authors who have written more than one book:
SELECT a.au_lname, a.au_fname, count(c.title) `titles' FROM authors a, titleauthor b, titles c WHERE a.au_id = b.au_id and b.title_id = c.title_id GROUP BY a.au_lname, a.au_fname HAVING count(c.title) > 1
Figure 7.8 shows that only the authors with more than one book are returned by
the query.
FIGURE 7.8.The
use of the HAVING clause to determine which authors have written more than
one book.
The INSERT statement is used to add rows of data to a table. The INSERT
statement specifies the table to which rows are to be added, the columns in which
the data is to be stored, the source of the data being added, and the data itself.
The components of the INSERT statement are described in Table 7.6.
Table 7.6. Components of a SQL INSERT Statement.
| Component | Description |
| INSERT INTO | Specifies the table to which rows are to be added. |
| column list | Specifies columns in which to add the data. It is necessary to provide a column list only when not all of the table's columns are to have data added. If left blank, the column list can be understood to be every column in the table, in the order in which they appear in the table structure. |
| VALUES (value list) | Specifies the values to be filled in the respective columns in the column list. (For example, the first value in the value list will be assigned to the first column in the column list, and so on.) |
| SELECT | The SELECT statement that will return rows to be added to the table. |
INSERT INTO titles
VALUES
( `SM1234', `The Small Business Tax Guide', `business', `1389', 15.99, 3000,
10, 0, `Tax guide for owners of small businesses', `1/1/1997')
You can also specify which columns to fill in the insert clause:
INSERT INTO titles (title_id, title) VALUES (`SM5678', `The Small Business Marketing Guide')
A reason for not specifying all values would be that you do not know those values and want any possible default values to be added. Note, however, that if there are no default values for the omitted columns and the structure of the table to which you are adding the row requires that an omitted column be filled, you will receive an error.
In the first example shown, you might have noticed that the list of columns in the insert clause was omitted. This is the equivalent of listing all columns in the table in the order in which they appear in the table structure. This saves you some typing time. However, it is usually better to include the column list so that, if fields are reordered or added to the table at a later date, the SQL statement does not need to be altered to reflect the structure change. Use of the INSERT Statement with a SELECT Statement This example shows the use of the SELECT statement to add one or more rows of data to the title table. Let's assume that the newtitles table is a temporary working table that holds new title information. From this table, you want to add all titles whose processing date is NULL.
INSERT INTO titles ( title_id, title, type, pub_id, price, advance, royalty, ytd_sales, notes, pubdate ) SELECT title_id, title, type, pub_id, price, advance, royalty, ytd_sales, notes, pubdate FROM newtitles WHERE procdate = NULL
The UPDATE statement is used to update column values in existing rows
in a table. The UPDATE statement specifies the table to be updated, the
columns to update, the new values to assign those columns, and criteria for the rows
to be updated. Table 7.7 describes the components of an UPDATE statement.
Table 7.7. Components of a SQL UPDATE Statement.
| Component | Description |
| UPDATE | Specifies the table to be updated |
| SET | Specifies columns to update and the new values to assign to those columns |
| FROM | Specifies the tables to include in the UDPATE statement |
| WHERE | Specifies the criteria that determine which rows' columns are to be updated |
UPDATE newtitles SET procdate = `1/1/1997' WHERE procdate = NULL
Setting a Column Value Based on Existing Column Values Suppose you want to increase the price of each book in the title table by 10 percent of the current price. It would be difficult to update the table with fixed values (as in the previous example), because all books need to be updated to different prices. That could take all day. A better way to perform this update is to use the existing price as a base for the updated price. Just as you can assign a fixed value to a column, you can also assign the results of an expression as shown in the following statement:
UPDATE titles SET price = price * 1.10
Without knowing the value of any of the prices in the title table, you can successfully increase their values by 10 percent in one easy UPDATE statement. Setting a Column Based on Values in a Joined Table Now let's imagine that you want to update the publisher associated with all titles written by a specific author. The author information is nowhere to be found in the titles table. You can get that information only by joining the titles table with the titleauthor table. To do this, you add a FROM clause to the UPDATE statement. This FROM clause works the same way as the FROM clause in a SELECT statement. While the UPDATE clause indicates the table to be updated, the FROM clause indicates the source of the data with which to update that table. The following example updates the pub_id column to `1389' for all titles associated with au_id `998-72-3567':
UPDATE titles SET pub_id = `1389' FROM titles a, titleauthor b WHERE a.title_id = b.title_id AND b.au_id = `998-72-3567'
The DELETE statement allows you to remove rows from tables. This statement
specifies the table from which rows are to be deleted and criteria for the rows to
be deleted. Table 7.8 describes the components of a DELETE statement.
Table 7.8. Components of a SQL DELETE Statement.
| Component | Description |
| DELETE FROM | Specifies the table from which to delete rows |
| WHERE | Specifies the criteria that determine which rows are to be deleted |
DELETE FROM titles
But sometimes you don't want to wipe out all the rows in a table. Instead, assume
that you want to delete only a specific title from the titles table. Using DELETE
to Delete Specific Rows from a Table To delete specific rows from a table, you simply
add the familiar WHERE clause. As you might expect, only the rows that meet
the criteria of the WHERE clause will be deleted. Suppose you want to remove
only the title `Silicon Valley Gastronomic Treats', whose title_id
is `MC2222'. Here is how to do it:
DELETE FROM titles WHERE title_id = `MC2222'
This is a little bit more complex than the previous example, but it's still quite easy. Let's try one more. Using DELETE to Delete Rows Based on a Joined Table Imagine that the criteria for the delete are based on a value in another table. For example, what if you want to delete all titles written by the author whose au_id is `427-17-2319'? You must use the joined titleauthor table to determine which titles were written by each author. This is where Access and SQL Server handle the DELETE statement quite differently.
Access uses the following syntax to delete rows from one table based on data stored in one or more other tables:
DELETE titles.* FROM titles, titleauthor WHERE titles.title_id = titleauthor.title_id and titleauthor.au_id = `427-17-2319'
In SQL Server, you would perform the same function using the following syntax:
DELETE FROM titles WHERE title_id IN ( SELECT title_id from titleauthor WHERE au_id = `427-17-2319' )
The CREATE TABLE statement allows you to create a new table in the database.
But the database should already be established by the DBA, you say, so why should
you have to create a new table from Visual Basic? Sometimes you need to access the
data in such a way that a SELECT statement would not effectively produce
the desired results. For example, you might find that the set of data you need must
be accessed and processed one row at a time through a cursor. Another example is
a result set that would require table joins so complex that the query takes a very
long time to run. In these cases, it is useful to have an empty table structured
as you want the result set structured, so that you can fill in phases through either
the use of a cursor or a combination of INSERT and UPDATE statements.
Table 7.9 describes the components of a CREATE TABLE statement.
Table 7.9. Components of a CREATE TABLE statement.
| Component | Description |
| CREATE TABLE | Specifies the table to be created |
| column list | Specifies the columns of the new table and their attributes |
CREATE TABLE authors ( au_id id NOT NULL , au_lname varchar (40) NOT NULL , au_fname varchar (20) NOT NULL , phone char (12) NOT NULL , address varchar (40) NULL , city varchar (20) NULL , state char (2) NULL , zip char (5) NULL , contract bit NOT NULL )
As you might have guessed, although the statement structure is the same, the syntax
used to create a table in an Access database is markedly different from that shown
for SQL Server due to different data types and keywords in the two environments.
Table 7.10 lists the valid SQL Server and Access data types, respectively.
Table 7.10. SQL Server and Access SQL DataTypes.
| Environment | Valid Data Types |
| SQL Server | binary, varbinary, char, varchar, datetime, smalldatetime, decimal, numeric, float, real, int, smallint, tinyint, money, smallmoney, bit, timestamp, text, image |
| Access | binary, bit, byte, counter, datetime, single, double, short, long, longtext, longbinary, text |
The SELECT..INTO statement is another way you can create a new table.
This method differs from the CREATE TABLE method in that the structure of
the table to create is not explicitly stated; rather, it is determined by the results
of a SELECT statement. Table 7.11 lists the components of the SELECT..INTO
statement.
Table 7.11. Components of a SELECT..INTO Statement.
| Component | Description |
| SELECT column list | Specifies the selected columns with which to build the new table |
| INTO | Specifies the name of the new table being created |
| FROM | Specifies the table or tables from which the column is being selected |
| WHERE | Specifies criteria that returned data must meet |
| GROUP BY | For aggregate queries, specifies the returned columns by which the data is to be grouped |
| HAVING | For aggregate queries, specifies criteria that the aggregate value returned must meet |
| ORDER BY | Specifies the sort order of the returned rows |
SELECT a.au_lname, a.au_fname, c.title INTO authortitles FROM authors a, titleauthor b, titles c WHERE a.au_id = b.au_id and b.title_id = c.title_id ORDER BY a.au_lname, a.au_fname, c.title
Now, if you select all rows from the authortitles table, you get the
same results that you got in the SELECT statement, as shown in Figure 7.9.
FIGURE 7.9. The results of using SELECT..INTO to create the
authortitles table.
This chapter attempted to provide you with the basic tools for performing the most common SQL commands. You have learned how to retrieve rows in many different formats using the SELECT statement, as well as how to add, update, and delete rows in tables using the INSERT, UPDATE, and DELETE statements, respectively. You also learned how to create a new table using the CREATE TABLE or SELECT..INTO statements. All of these statements should get you pretty far, but you might find in your continued development that you require more functionality. You should take the time to try out the commands described here until you feel you have a good understanding of how they work. Then you should research and make use of some of the more advanced features described in SQL Server's Transact-SQL help file and the Microsoft Access help file. You should also become familiar with as many of those products' built-in functions as possible. Such functions will give you a much wider scope of options in the types of SQL statements you can create.
