After creating a database, you can modify it as follows:
•After creating tables, you can modify the table definitions.
•After creating columns, you can set optional column attributes.
•After adding data to the database, you can modify the data.
You can perform these tasks using the SQL Data Manager. For information about interactive applications, see Zen User’s Guide. For more information about SQL statements, see SQL Engine Reference.
Modifying Tables
You can use an ALTER TABLE statement to modify a table definition after creating the table. ALTER TABLE statements allow you to add or drop columns; add or drop primary and foreign keys; and change the pathname of a table’s data file.
The following example adds a numeric column called Emergency_Phone to the Tuition table in the sample database.
ALTER TABLE Tuition ADD Emergency_Phone NUMERIC(10,0)#
Zen inserts a default value if you insert a row but do not provide a value for that column. Default values ensure that each row contains a valid value for the column.
In the Person table of the sample database, all students live in a state. Setting a default value such as TX for the State column ensures that the most probable value is always entered for that column.
To set a default value for a column, use a DEFAULT statement in the CREATE TABLE statement:
CREATE TABLE MyTable(c1 CHAR(3) DEFAULT ’TX’, ID INTEGER)#
SELECT * FROM MyTable#
Result of SELECT Statement:
"c1", "ID"
"TX", "1234"
1 row fetched from 2 columns
Using UPDATE
You can use an UPDATE statement to change the data in a row that is already in a table. UPDATE statements let you modify specific columns in a row. Also, you can use a WHERE clause in an UPDATE statement to specify which rows for Zen to change. This is referred to as a searched update. Using SQL declared cursors and the Positioned UPDATE statement, you can update the current row of a declared cursor from which you are fetching data.
UPDATE Course SET Credit_Hours = 4 WHERE Course.Name = 'Math'#
This example instructs Zen to find the row that contains the course name Math and change the Credit Hours column value to 4.
As shown in the previous example, you can use a constant to update a column by placing it on the right hand side of a SET clause in an UPDATE statement.