DBMS Alter query ~ GNIITHELP

alter command

alter command is used for alteration of table structures. There are various uses of alter command, such as,

• to add a column to existing table
• to rename any existing column
• to change datatype of any column or to modify its size.
• alter is also used to drop a column.

---

To Add Column to existing Table

Using alter command we can add a column to an existing table. Following is the Syntax,

alter table table-name add(column-name datatype);

Here is an Example for this,

alter table Student add(address char); 

The above command will add a new column address to the Student table

---

To Add Multiple Column to existing Table

Using alter command we can even add multiple columns to an existing table. Following is the Syntax,

alter table table-name add(column-name1 datatype1, column-name2 datatype2, column-name3 datatype3);

Here is an Example for this,

alter table Student add(father-name varchar(60), mother-name varchar(60), dob date); 

The above command will add three new columns to the Student table

---

To Add column with Default Value

alter command can add a new column to an existing table with default values. Following is the Syntax,

alter table table-name add(column-name1 datatype1 default data);

Here is an Example for this,

alter table Student add(dob date default '1-Jan-99'); 

The above command will add a new column with default value to the Student table

---

To Modify an existing Column

alter command is used to modify data type of an existing column . Following is the Syntax,

alter table table-name modify(column-name datatype);

Here is an Example for this,

alter table Student modify(address varchar(30)); 

The above command will modify address column of the Student table

---

To Rename a column

Using alter command you can rename an existing column. Following is the Syntax,

alter table table-name rename old-column-name to column-name;

Here is an Example for this,

alter table Student rename address to Location; 

The above command will rename address column to Location.

---

To Drop a Column

alter command is also used to drop columns also. Following is the Syntax,

alter table table-name drop(column-name);

Here is an Example for this,

alter table Student drop(address); 

The above command will drop address column from the Student table