Alter Query:




The ALTER command or statement is used to alter that is add, delete, modify etc. the table. The ALTER TABLE command is used to make alteration or changes in a structure of the table.

Through this statement the user can add columns to a table and can modify the column, drop a column or rename a column or table.




To add column to existing table:

 

SYNTAX

We can add a column to already existing table by using the alter command. The syntax for this query is given below:

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

EXAMPLE

Consider the following example to add a column to an already existing table:

Alter table STUDENT add (department char);

This query will add the column department of character type to the table named student.

 

To add multiple columns to existing table

We can also add a number of columns to already existing table by using the alter command.

 

SYNTAX

The syntax for this is as follows:

Alter table table-name

add (column-name-1 datatype,

Column-name-2 datatype,

Column-name-n datatype);

EXAMPLE

Consider the following example to add multiple columns to an already existing table:

Alter table STUDENT add (department char, roll-no int, date-of-birth date);

This query will add the columns department of character type, roll number of integer type and date of birth of date type to the table named student.

 

To add column with default value

We can also add a new column to already existing table with some default values by using the alter command. The syntax for this is as follows:

 

SYNTAX

Alter table table-name add (column-name datatype default data);

EXAMPLE

Consider the following example to add a column with a default value to an already existing table:

Alter table STUDENT add (date-of-birth date default ‘10-18-16’);

This query will add the column date of birth of date type with a default value to the table named student.




To modify an existing table

We can also modify the data type of an existing column by using the alter command. The syntax for this is as follows:

 

SYNTAX

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

EXAMPLE

Consider the following example to modify an existing column:

Alter table STUDENT add (department char (30));

This query will modify the already existing column named department.

style=”text-align: justify;”>To rename a column

We can also rename an existing column by using the alter command. The syntax for this is given below:

 

SYNTAX

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

EXAMPLE

Consider the following example to rename an existing column:

Alter table STUDENT rename department to DEPT;

This query will rename the already existing column named department.

 

To drop a column

The alter command can also be used to drop various columns from a table. The syntax for this is given below:




SYNTAX

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

EXAMPLE
Consider the following example to drop a column:

Alter table STUDENT drop (department);

This query will drop the column named department.