SQL Basics - 2

ALTER Command :

The structure of the table can be modified by the alter command. Not only the structure but also columns can be added,deleted,data type modified, renamed with the alter table. Also Indexes, constraints can be added,deleted or modified.

ALTER table works by making a temporary copy of the original table. The alteration is performed on the copy , then the original table is deleted and the new one is renamed. While Alter table is executing , the original table is still readable to other oracle users.

Alter table ACCNTS ADD(user_lname varchar2(40), inst_dt date)

DB2 version : Alter table ACCNTS ADD column lname varchar(40)

Alter table ACCNTS DROP COLUMN inst_dt (in DB2 its not possible to drop a column from a table)

Alter table ACCNTS MODIFY (user_lname varchar2(100)) , this statement can modify both the data type and size

DB2 : Alter table ACCNTS alter column lname SET DATA TYPE varchar(200)

RENAME TABLE:

rename table ACCNTS to ACCOUNTS

TRUNCATE TABLE:

truncate table ACCNTS1

Truncate operations drop and re create the table which is much faster than deleting rows one by one

There is no truncate command in DB2, so the equivalent command in DB2 is

alter table accnts1 activate not logged initially with empty table;

DROP TABLE:

drop table ACCNTS1

SYNONYMS:

A synonym is an alternative name for objects such as tables,views , sequences

create or replace public synonym Chulbul for accnts.

(please check another post for difference between synonym and alias)

we can create synonym of any object from table,views,sequence,stored procs, functions even other synonymns. The synonym can also be dropped similar way

drop public synonym Chulbul