Tuesday, 9 February 2010

Modifying Table and Computed Column ( Interbase Tutorial # 6 )

You can modify your table using ALTER TABLE statement. With this statement you can add a columns into your table or you can delete a column as well. The syntax of this statement is :

For adding a column :

    ALTER TABLE TableName ADD columnname datatype

For dropping a column :

    ALTER TABLE TableName DROP columnname

Example 1 : in ‘employee’ table, we have four columns : id, name, age and phonenumber. We’re going to delete age column and add a column named bithdate. 
First we delete age column using this statement :

    Alter table employee drop age

Age column has been dropped. Now we’re adding birthdate column using this statement :

    Alter table employee add birthdate date

We use date data type for birtdate column. Now we have four column in ‘employee’ table : id, name, phonenumber, birthdate.

To see the changes we've made in 'employee' table, choose Metadata - Extract Table. And the output is :

/* Extract Table EMPLOYEE */

/* Table: EMPLOYEE, Owner: NEW_USER */
CREATE TABLE EMPLOYEE (ID INTEGER NOT NULL,
  NAME VARCHAR(30) NOT NULL,
  PHONENUMBER VARCHAR(12) NOT NULL,
  BIRTHDATE DATE);

Computed column

We can also have a column that has a value based on the values of other columns in the table. The syntax to create a computed column is :

   ColumnName COMPUTED BY ( arithmetic operation ) 

Example 2 : in ‘employee’ table we’re going to add three columns : salary, bonus, totalsalary. And the totalsalary column’s value = salary column’s value + bonus column’s value.

The statement will be like this :

    Alter table employee add salary integer

    Alter table employee add bonus integer

    Alter table employee add totalsalary computed by (salary+bonus)


You have to execute the statements above one by one in the InterBase Interactive SQL window. Now we have ‘employee’ table which has seven columns : id, name, phonenumber, birthdate, salary, bonus and totalsalary.
To see what we have in mydata.gdb database choose Metadata – Extract Database. And the output is :

/* Extract Database D:\mydata\MYDATA.GDB */
CREATE DATABASE "D:\mydata\MYDATA.GDB" PAGE_SIZE 1024 
;

/* Domain definitions */
CREATE DOMAIN STRING30 AS VARCHAR(30);
CREATE DOMAIN STRING12 AS VARCHAR(12);
CREATE DOMAIN DOMAIN_ID AS INTEGER
  check(value>0);
CREATE DOMAIN DOMAIN_AGE AS INTEGER

  check(value>18 and value<55);

Go to Previous Tutorial or Next Tutorial

0 comments:

Post a Comment

These links are part of a pay per click advertising program called Infolinks. Infolinks is an In Text advertising service; they take my text and create links within it. If you hover your mouse over these double underlined links, you will see a small dialog box containing an advertisement related to the text. You can choose to move the mouse away and go on with your browsing, or to click on the box and visit the relevant ad. Click here to learn more about Infolinks Double Underline Link Ads.