# 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:

Part | Description
Table | The name of the table to be altered.
Field | The name of the field to be added to or deleted from table. Or, the name of the field to be altered in table.
Type | The data type of field.
Size | The field size in characters (Text and Binary fields only).

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:

Part | Description
Table | The name of the table to be created.
field1, field2 | The name of field or fields to be created in the new table. You must create at least one field.
Type | The data type of field in the new table.
Size | The field size in characters (Text and Binary fields only).

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:

Part | Description
table | The optional name of the table from which records are deleted.
table | The name of the table from which records are deleted.
criteria | An expression that determines which records to delete.

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:

Part | Description
table | The name of the table to be deleted or the table from which an index is to be deleted.
procedure | The name of the procedure to be deleted.
view | The name of the view to be deleted.
index | The name of the index to be deleted from table.

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:

Part | Description
target | The name of the table or query to append records to.
field1, field2 | Names of the fields to append data to, if following a target argument, or the names of fields to obtain data from, if following a source argument.
externaldatabase | The path to an external database. For a description of the path, see the IN clause.
source | The name of the table or query to copy records from.
tableexpression | The name of the table or tables from which records are inserted. This argument can be a single table name or a compound resulting from an INNER JOING, LEFT JOIN, or RIGHT JOIN operation or a saved query.
value1, value2 | The values to insert into the specific fields of the new record. Each value is inserted into the field that corresponds to the value's position in the list: value1 is inserted into field1 of the new record, value2 into field2, and so on. You must separate values with a comma, and enclose text fields in quotation marks (' ').

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:

Part | Description
Predicate | One of the following predicates: ALL, DISTINCT, DISTINCTROW. You use the predicate to restrict the number of records returned. If none is specified, the default is ALL.
* | Specifies that all fields from the specified table or tables are selected.
Table | The name of the table containing the fields from which records are selected.
field1, field2 | The names of the fields containing the data you want to retrieve. If you include more than one field, they are retrieved in the order listed.
alias1, alias2 | The names to use as column headers instead of the original column names in table.
Tableexpression | The name of the table or tables containing the data you want to retrieve.
Externaldatabase | The name of the database containing the tables in tableexpression if they are not in the current database.

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:

Part | Description
field1, field2 | The name of the fields to be copied into the new table.
Newtable | The name of the table to be created. If newtable is the same as the name of an existing table, a trappable error occurs.
Externaldatabase | The path to an external database. For a description of the path, see the IN clause.
Source | The name of the existing table from which records are selected. This can be single or multiple tables or a query.

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:

Part | Description
Table | The name of the table containing the data you want to modify.
Newvalue | An expression that determines the value to be inserted into a particular field in the updated records.
Criteria | An expression that determines which records will be updated. Only records that satisfy the expression are updated.

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.