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

The ASC and DESC keywords specify that the key values should be arranged in ascending or descending order, respectively. If neither order is specified, ascending is the default.

The PRIMARY keyword indicates that the index should be the primary index for the table. A table can have only one primary index. If the statement tries to create another primary key, the database generates an error.

If the DISALLOW NULL clause is included, the database will not accept records with null values for any of the fields specified in the key.

If the IGNORE NULL clause is included, the database will not include any records in the index if they have null values for the index fields.

The following statement creates a new Books table and then adds an index on the Title and Author fields. Because the index uses the DISALLOW NULL clause, all records must have Title and Author values.

CREATE TABLE Books (
    ID        COUNTER,
    Title     TEXT (30),
    Author    TEXT (30),
    Length    INTEGER
);
CREATE INDEX BooksIndex ON Books (Title, Author)
    WITH DISALLOW NULL;

DROP

The DROP command removes a table or index from the database. The syntax for removing a table is as follows:

DROP TABLE table_name

For example, the following statement removes the Books table from the database:

DROP TABLE Books

The syntax for removing an index from a table is just as simple.

DROP INDEX index_name ON table_name

The following statement removes the BooksIndex from the Books table:

DROP INDEX BooksIndex ON Books

DROP statements are very dangerous: They are immediate and unforgiving. Once a table has been dropped, it and all of the data it contained are gone forever.

ALTER

The ALTER TABLE command changes the definition of a table after it is created. This statement can add or drop columns and constraints. The syntax for adding a column is as follows:

ALTER TABLE table_name  ADD COLUMN
    field_name type[(size)] [CONSTRAINT index_name]

For example, the following statement adds the text field Publisher to the already existing Books table:

ALTER TABLE Books ADD COLUMN Publisher TEXT (30)

The syntax for removing a field is even simpler.

ALTER TABLE table_name DROP COLUMN field_name

The following statement removes the Publisher field from the Books table:

ALTER TABLE Books DROP COLUMN Publisher

Like the DROP statement, ALTER TABLE statements can be dangerous. Any data contained in a dropped column is permanently lost.

The ALTER CONSTRAINT commands follow a similar pattern. The following two statements first add and then remove a uniqueness constraint on the Title and Author fields in the Books table.

ALTER TABLE Books ADD CONSTRAINT BooksTitleIndex

    UNIQUE (Title, Author);
ALTER TABLE Books DROP CONSTRAINT BooksTitleIndex;

SELECT

The SELECT statement is one of the most important and complex SQL commands. A complete discussion of all the possible combinations of clauses and parameters would be quite long and would waste a lot of your time on subtleties that you might never encounter. This section describes only the most commonly used varieties of the SELECT statement.

The basic syntax for the SELECT command is as follows:

SELECT [predicate] select_list from_clause [where_clause] [group_by_clause] [having_clause]
[order_by_clause]

The pieces of the SELECT statement are described in the following sections.

Predicate The predicate specifies which records should be selected. The predicate can take one of the following values:

•  ALL. This value selects all records that meet the criteria specified by the other clauses. This is the default predicate.

•  DISTINCT. This value omits duplicates of the values selected.

•  DISTINCTROW. This value omits records that duplicate all fields in another record rather than just the fields selected. This is useful only under certain circumstances when the statement selects fields from more than one table.

•  TOP. This value returns an indicated number of rows. For example, “TOP 10” indicates that the first 10 rows returned by the query should be presented. Usually a statement using TOP also specifies an ORDER BY clause (described later) to order the results so the returned rows do not seem randomly selected.

This example selects information about the 25 shortest books listed in the Books table:

SELECT TOP 25 * FROM Books ORDER BY Length

The following statement produces an alphabetized list of the authors who have written books with more than 300 pages:

SELECT DISTINCT Author FROM Books WHERE Length > 300 ORDER BY Author

Select List The select list indicates which fields are to be selected. Several of the previous examples include select lists. The select list usually includes field names or the asterisk to indicate all the fields in a table. The following statement displays all of the data in the Books table:

SELECT * FROM Books

If the statement selects fields from more than one table, and if the tables contain a field with the same name, the statement must specify the table name for those fields in the SELECT statement. For example, suppose the Books and Films tables both have a field named Title. The following statement would produce a list of books with titles that are also the titles of films. The words “Books.Title” indicate the Title field in the Books table.

SELECT DISTINCT Books.Title FROM Books, Films
    WHERE Books.Title = Films.Title
    ORDER BY Books.Title

A statement can use an AS clause to change the displayed name of a field when the statement returns results. If the new name contains space characters, the entire name should be enclosed in square brackets. The following command lists the titles and author names in the Books table, but the information from the Title field is renamed “Work.” Below the SQL statement are the first few output lines produced by this command in the Query application.

SELECT Author, Title AS Work FROM Books;

Author     Work
------     ----
Stephens   Visual Basic Algorithms
Stephens   Visual Basic Graphic Programming
    :