Skip to main content.

Web Based Programming Tutorials

Homepage | Forum - Join the forum to discuss anything related to programming! | Programming Resources

Web Database Developer's Guide with Visual Basic 5

World Wide Web Database Developer's Guide with Visual Basic 5




-7-

SQL

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.

An Overview of Basic SQL Statements

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


These commands seem pretty simple, but they can be qualified to perform a number of complex functions, as you can see in the following examples.

SELECT

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


So let us begin with a very simple SELECT statement. We will build on this statement as we move from one example to the next, adding complexity through the use of WHERE and ORDER BY clauses. A Simple SELECT Statement In the following example, you simply query several columns in the authors table:

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'

NOTE: If you have ever created a query in Access using the Query By Example (QBE) grid and then viewed the SQL that it generated, you have noticed that a semicolon appears at the end of the statement. This is Access's way of terminating the SQL statement, although it is not necessary in order for the SQL statement to be executed. SQL Server has no such terminator and will treat a semicolon attached to the end of the statement as a syntactical error.

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.


In addition to a variety of comparison operators, the columns included in the WHERE clause can be checked using the AND and OR logical operators. If the AND operator is used, the conditions on both sides of the AND must be met for a row to be returned. The OR operator requires that at least one of the conditions must be met. Adding the ORDER BY Clause Now let's take your simple query one step further and sort the results by author last name (au_lname). To do this, you add an ORDER BY clause.

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

NOTE: Microsoft SQL Server offers several sort orders, with the default being dictionary order and case-insensitive. The sort order is defined during SQL Server installation and cannot be overridden, so you might want to consult your DBA to be sure how your results will be sorted. The default sort order for Access is ascending (A-Z, 0-9).

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.


Using the JOIN operator, the SQL statement in the previous example would be written as follows:

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).


The following statement illustrates the use of the count function on the entire titles table with no grouping:

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.


NOTE: You can use an alias to return a different name for a column or assign a name to a column that returns an expression (and, therefore, has no name). There are three ways to assign an alias to a column. (All three methods can be used in SQL Server. Only the first method can be used in Access.
SELECT count(title) AS titles FROM titles
SELECT count(title) `titles' FROM titles
SELECT `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.


NOTE: At this point, you are familiar with the use of table names prefacing column names in order to avoid ambiguity in multitable queries. You also might have begun to notice that this can result in lengthy SQL statements. Using a table alias, you can shorten your SQL statement and make it easier to both read and type. Like the column alias shown earlier, the table alias is simply the use of a new name to represent the actual table name. They are often used to shorten the SQL statement and to enhance the readability of the WHERE clause, so the table names are usually replaced by an alias of a single character. Let's look at the preceding SQL example, replacing table names with aliases
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.

INSERT

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.


Note that either a value list or a SELECT statement (not both) is used to provide the data to be added. Use of the INSERT Statement with a Value List This example shows the use of the value list to add a row to the title table, providing values for each column in the table. Note that with a value list, you can insert only a single row into 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


NOTE: The INSERT INTO..SELECT statement can be interpreted as a two-part query. That is, the SELECT portion of the statement actually is performed by itself as "step one" of the statement. The resulting set of rows is then given to the INSERT INTO portion of the statement, which is "step two" of the statement. For this reason, there is no ambiguity between the identical column lists in these two distinct portions of the SQL statement. Therefore, it is not necessary to preface the column names with the table names in order to distinguish those in the destination table from those in the source table.

UPDATE

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


Setting Columns to a Fixed Value with the UPDATE Statement In some cases, you want to update columns in a table with a fixed value for every row in the table. For instance, assume that you want to update the processing date to January 1, 1997 for each row in the newtitles table (used in the revious example) that currently has a processing date of NULL:

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'

DELETE

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


Using DELETE to Delete All Rows From a Table To delete all rows from a table, you need only specify the name of the table from which to delete those rows. The following example shows how to delete all rows from the title table:

DELETE FROM titles

NOTE: SQL Server offers another method, called the TRUNCATE TABLE statement, for removing all rows from a table. It acts like a DELETE statement that has no WHERE clause, with a couple of important exceptions that should be noted. The DELETE statement deletes rows from a table one row at a time, logging each deletion as a transaction, and it can therefore be rolled back. The TRUNCATE TABLE statement, on the other hand, removes entries from a table pages at a time, and does not log individual row deletions. Rows removed from a table with a TRUNCATE TABLE statement cannot be recovered. The other important note is that delete triggers associated with a table will not be fired when that table is truncated. So why use a TRUNCATE TABLE statement? Speed. The TRUNCATE TABLE statement performs the task of removing all rows from a table much faster than a DELETE statement does. Just keep in mind that TRUNCATE TABLE always removes all the rows from a table and these rows are not recoverable.


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' )

CREATE TABLE

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


Using CREATE TABLE to Create the authors Table To create a new table using the CREATE TABLE statement, you simply specify the table to create and the columns that will configure the table. The following statement creates the authors table as it is currently structured in the pubs database on SQL Server:

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

SELECT..INTO

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


Using SELECT..INTO to Create a New authortitles Table Suppose that you want to store the results of a SELECT statement in a table for use later in your application or in a report. The SELECT..INTO statement allows you to do just that. You simply need to add the INTO clause to a standard SELECT statement. In this example, you use the SELECT statement from an earlier example, which displayed author names and the titles of books that the authors wrote, to create a new table called authortitles:

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.


Summary

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.