Appendix A - SQL Statements (not included in filePro Lite)

ALTER TABLE Statement

Modifies the design of a table after it has been created with the CREATE statement.

Syntax

ALTER TABLE table ADD COLUMN field type [( size )]
ALTER COLUMN field type [( size )] |
ALTER TABLE  DROP COLUMN field

The ALTER TABLE statement has these parts:

Remarks

Using the ALTER TABLE statement you can alter an existing table in several ways. You can:

·    Use ADD COLUMN to add a new field to the table. You specify the field name, data type, and (for Text and Binary fields) an optional size. For example, the following statement adds a 20-character Text field called Remarks to the mytest1 table:

ALTER TABLE mytest1 ADD COLUMN Remarks TEXT(20)

·    Use ALTER COLUMN to change the data type of an existing field. You specify the field name, the new data type, and an optional size for Text and Binary fields. For example, the following statement changes the data type of a field in the mytest1 table called " Customer " (originally defined as Integer) to a 10-character Text field:

ALTER TABLE mytest1 ALTER COLUMN Customer TEXT(10)

·    Use DROP COLUMN to delete a field. You specify only the name of the field. For example, the following statement drops the column named " Remarks " from the table called " mytest1 " . Note that you can only delete one field at a time.

·    ALTER TABLE mytest1 DROP COLUMN Remarks

CREATE TABLE Statement (not included in filePro Lite)

Description: Creates a new table.

Syntax

CREATE TABLE table ( field1 type [( size )] [, field2 type [( size )] [, ...]] )

Parts - The CREATE TABLE statement has these parts:

Remarks

Use the CREATE TABLE statement to define a new table and its fields and field constraints. If NOT NULL is specified for a field, then new records are required to have valid data in that field.

You can also use the CREATE INDEX statement to create a primary key or additional indexes on existing tables.

DELETE Statement  (not included in filePro Lite)

Creates a DELETE query that removes records from one or more of the tables listed in the FROM clause that satisfy the WHERE clause.

Syntax

DELETE [ table .*]
    FROM table
    WHERE criteria

Parts - The DELETE statement has these parts:

Remarks

DELETE is especially useful when you want to delete many records.

To delete an entire table from the database, you can use the DROP statement. Keep in mind If you use DROP that the entire table structure is lost. In contrast, when you use DELETE, only the data is deleted. The table structure and all of the table properties, such as field attributes and indexes, remain intact.

A delete query deletes entire records, not just data in specific fields. If you want to delete values in a specific field, see Update Query to change the values to NULL.

Notes

After you remove records using a delete query, you cannot undo the operation. If you want to know which records were deleted, first examine the results of a SELECT query that uses the same criteria, and then run the delete query.

Maintain backup copies of your data at all times. If you delete the wrong records, you can retrieve them from your backup copies.

DROP Statement (not included in filePro Lite)

Deletes an existing table, procedure, or view from a database, or deletes an existing index from a table.

Syntax

DROP {TABLE table | INDEX index ON table | PROCEDURE procedure | VIEW view }

Parts - The DROP statement has these parts:

Remarks

You must close the table before you can delete it or remove an index from it.

You can also use ALTER TABLE to delete an index from a table.

You can use CREATE TABLE to create a table and CREATE INDEX or ALTER TABLE to create an index. To modify a table, use ALTER TABLE.

INSERT INTO Statement  (not included in filePro Lite)

Adds a record or multiple records to a table. This is referred to as an append query.

Syntax

Multiple-record append query:

INSERT INTO target [( field1 [, field2 [, ...]])] [IN externaldatabase ]
    SELECT [ source .] field1 [, field2 [, ...]
    FROM tableexpression

Single-record append query:

INSERT INTO target [( field1 [, field2 [, ...]])]
    VALUES ( value1 [, value2 [, ...])

Parts - The INSERT INTO statement has these parts:

Remarks

You can use the INSERT INTO statement to add a single record to a table using the single-record append query syntax as shown above. In this case, your code specifies the name and value for each field of the record. You must specify each of the fields of the record that a value is to be assigned to and a value for that field. When you do not specify each field, the default value or NULL is inserted for missing columns. Records are added to the end of the table.

You can also use INSERT INTO to append a set of records from another table or query by using the SELECT ... FROM clause as shown above in the multiple-record append query syntax. In this case, the SELECT clause specifies the fields to append to the specified target table.

INSERT INTO is optional but when included, precedes the SELECT statement.

If your destination table contains a PRIMARY key, make sure you append unique, non- Null values to the primary key field or fields; if you do not, the database engine will not append the records.

If you append records to a table with an AutoNumber field and you want to renumber the appended records, do not include the AutoNumber field in your query. Do include the AutoNumber field in the query if you want to retain the original values from the field.

Use the IN clause to append records to a table in another database.

To create a new table, use the SELECT INTO statement instead to create a table.

To find out which records will be appended before you run the append query, first execute and view the results of a SELECT query that uses the same selection criteria.

An append query copies records from one or more tables to another. The tables that contain the records you append are not affected by the append query.

Instead of appending existing records from another table, you can specify the value for each field in a single new record using the VALUES clause. If you omit the field list, the VALUES clause must include a value for every field in the table; otherwise, the INSERT operation will fail. Use an additional INSERT INTO statement with a VALUES clause for each additional record you want to create.

SELECT Statement (not included in filePro Lite)

Return information from the database as a set of records.

Syntax

SELECT [ predicate ] { * | table .* | [ table .] field1 [AS alias1 ] [, [ table .] field2 [AS alias2 ] [, ...]]}
    FROM tableexpression [, ...] [IN externaldatabase ]
    [WHERE... ]
    [GROUP BY... ]
    [HAVING... ]
    [ORDER BY... ]

Parts - The SELECT statement has these parts:

Remarks

To perform this operation, the Microsoft® Jet database engine searches the specified table or tables, extracts the chosen columns, selects rows that meet the criterion, and sorts or groups the resulting rows into the order specified.

SELECT statements do not change data in the database.

SELECT is usually the first word in an SQL Statement.

The minimum for a SELECT statement is:

SELECT fields FROM table

You can use an asterisk (*) to select all fields in a table. The following example selects all of the fields in the mymedia table:

SELECT * FROM mymedia

If a field name is included in more than one table in the FROM clause, precede it with the table name and the . (dot) operator. In the following example, the Title field is in both the mymedia table and the mytest1 table. The SQL statement selects Title and Rated from the mymedia table and remarks from the mytest1 table:

SELECT mymedia.title, mymedia.rated, mytest1.remarks

FROM mymedia , mytest1

WHERE mymedia.title = mytest1.title

When a record set object is created, the table's field name is used as the Field object name in the Recordset object. If you want a different field name or a name is not implied by the expression used to generate the field, use the AS reserved word. The following example uses the title Notes to name the returned Field object in the resulting Recordset object:

SELECT mytest1.Remarks

AS Notes FROM mytest1

SELECT...INTO Statement (not included in filePro Lite)

Creates a new Table from an existing table.

Syntax

SELECT field1[, field2[, ...]] INTO newtable [IN externaldatabase]
    FROM source

Parts - The SELECT...INTO statement has these parts:

Remarks

You can use make-table queries to archive records, make backup copies of your tables, or make copies to export to another database or to use as a basis for reports that display data for a particular time period. For example, you could produce a Monthly Sales by Region report by running the same make-table query each month.

Notes

You may want to define a primary key for the new table. When you create the table, the fields in the new table inherit the data type and field size of each field in the query's FROM source, but no other field or table properties are transferred.

To add data to an existing table, use the INSERT INTO statement instead to create an append query.

To find out which records will be selected before you run the make-table query, first examine the results of a SELECT statement that uses the same selection criteria.

Make sure that you do not use a table name that already exists.

UPDATE Statement  (not included in filePro Lite)

Creates an update query that changes values in fields in a specified table based on specified criteria.

Syntax

UPDATE table
    SET newvalue
    WHERE criteria ;

Parts - The UPDATE statement has these parts:

Remarks

UPDATE is especially useful when you want to change many records. The following example will change the Format column values in mytest2 from " Tape " to " VHS " .

 UPDATE mytest2 SET Format= ’ VHS ’ WHERE Format = ‘ Tape ’

Notes

UPDATE does not generate a result set. Also, after you update records using an update query, you cannot undo the operation. If you want to know which records were updated, first examine the results of a SELECT query that uses the same criteria, and then run the update query.

Maintain backup copies of your data at all times. If you update the wrong records, you can retrieve them from your backup copies.