Advanced Visual Basic Techniques
(Publisher: John Wiley & Sons, Inc.)
Author(s): Rod Stephens
ISBN: 0471188816
Publication Date: 06/01/97

Group By Clause The GROUP BY clause combines records with the same values in the specified fields into one record. An SQL statement can use a GROUP BY clause together with aggregate functions to produce summary values. The following statement lists the authors in the Books table and the total length of all books written by each:

SELECT Author, SUM(Length) As [Total Length]
    FROM Books GROUP BY Author

For example, if the author Newton had written 18 books with a combined length of 4763 pages, this query would return a row listing Newton and the total length 4763.

Having Clause After a query has grouped records using a GROUP BY clause, the HAVING clause specifies which output records should be displayed. This is similar to a WHERE clause that selects from among the records produced by the GROUP BY clause.

For example, the following statement produces a list of authors and the total length of their books as before, but it displays results only for authors who have written at least 1000 pages.

SELECT Author, SUM(Length) As [Total Length]
    FROM Books GROUP BY Author HAVING SUM(Length) >= 1000

Order By Clause The previous sections have shown several examples of the ORDER BY clause. This clause determines the order of the results returned by SELECT statements. The optional keyword DESC indicates that the values should be arranged in descending order. The following example produces a list of authors and the total length of their books, for authors who have written at least 1000 pages, arranged with those authors having the largest page count totals first.

SELECT Author, SUM(Length) As [Total Length]
    FROM Books GROUP BY Author HAVING SUM(Length) >= 1000
    ORDER BY SUM(Length) DESC

INSERT

An INSERT statement creates new records in a table. There are two formats for INSERT statements. The first inserts a single record into a table. Its syntax is as follows:

INSERT INTO table_name  [(field_name , ...)] VALUES (value1, ...)

For example, the following statement inserts a single record into the Books table. It specifies only the Title and Author fields so any other fields in the record are given null values.

INSERT INTO Books (Title, Author)
    VALUES (“The Longest Day”, “Bishop”)

If an INSERT statement does not explicitly list the fields initialized, the VALUES clause must specify a value for every field in the table in the proper order. The following statement creates a record similar to the one created by the previous example. Because it omits the field list, this statement must specify a value for the Length field as well as the Title and Author fields. This statement sets the Length field to 375. Rather than giving a specific value for this field, the statement could have specified the value null to leave the field’s value undefined.

INSERT INTO Books
    VALUES (“The Longest Day”, “Bishop”, 375)

The second kind of INSERT statement uses a subquery to select rows from one or more tables and insert them into another table. The syntax for this kind of statement is as follows:

INSERT INTO table_name [(field_name, ...)] subquery

Once again, if the statement omits the field name list it must provide values for all the fields in the table.

The following statement selects the Title and Author fields from the Books table and uses the results to create entries in the Films table.

INSERT INTO Films (Title, Producer)
    SELECT Title, Author FROM Books

UPDATE

An UPDATE statement modifies the field values in existing records. The syntax for an UPDATE statement is as follows:

UPDATE table_name SET field_name = value, ... where_clause

For example, the following statement changes the Author field to “Leibniz” for any records in the Books table that currently have Author value “Newton.”

UPDATE Books SET Author = “Leibniz” WHERE Author = “Newton”

The following statement adds 100 to the Length field for every record in the Books table:

UPDATE Books SET Length = Length + 100

Using an UPDATE statement to make changes to many records is generally much faster than making a program loop through the records to update them one at a time.

Like many other SQL statements, UPDATE statements can be dangerous. A program can irreversibly damage a lot of data with a single UPDATE statement if it is not careful. In particular, if the program omits the WHERE clause, the UPDATE statement will affect every record in the table rather than just a few.

DELETE

The DELETE statement removes records from a table. The syntax for this statement is as follows:

DELETE FROM table_name where_clause

The following example deletes all the records in the Books table where the value of the Author field is Plato:

DELETE FROM Books WHERE Author = “Plato”

Using a DELETE statement to remove many records is generally much faster than making a program loop through the records to delete them one at a time.

Like the UPDATE statement, the DELETE statement can be dangerous if used carelessly. Once records have been deleted, they are gone forever. Also like the UPDATE statement, DELETE is particularly dangerous without a WHERE clause. If a program accidentally omits the WHERE clause, it will delete every record in the table instead of just a few.

One strategy for protecting data is to use the multirecord INSERT statement described previously to copy the records into a temporary table before deleting them. This reduces the size of the main data table but allows the program to recover the records later if it needs them. When it is certain the saved values are no longer necessary, the program can use the DROP command to remove the temporary table.

Another strategy for safe deletion is to first test the DELETE statement’s WHERE clause in a SELECT statement. The program can present a list of the records to be deleted to the user and ask for confirmation. If the user approves the deletion, the program can run the DELETE statement using the tested WHERE clause.

If the WHERE clause selects a large number of rows, the program can use a SELECT statement with the COUNT function to determine how many rows would be deleted. The program can tell the user the number of rows that will be removed and ask for confirmation. For example, the WHERE clause in the following statement should select only a few rows. If the COUNT function returns a value larger than a dozen or so, the user should take a closer look at the data and the WHERE clause before deleting the records.

SELECT COUNT (*) FROM Books WHERE Author=“Xeno”