ALTER TABLE IN SQL

ALTER TABLE statement is used to add, delete, or modify columns and add or drop constraints on existing table.

ALTER TABLE- ADD COLUMN

Syntax-

ALTER TABLE <table_name>

ADD <column_name> <datatype>;

Example-

Following SQL will add DOB column to existing msstudentsnew table-

ALTER TABLE msstudentsnew

ADD DOB date;

ALTER TABLE – ALTER COLUMN

To change the data type/properties of a column in a table.

Syntax-

ALTER TABLE <table_name>

ALTER COLUMN <column_name> <datatype>;

Example-

To change the data type from date type to year type of the column named “DOB” in the ” msstudentsnew ” table

ALTER TABLE msstudentsnew

ALTER COLUMN DOB year;

ALTER TABLE- DROP COLUMN

To delete a column in a table.

Syntax-

ALTER TABLE <table_name>

DROP COLUMN <column_name>;

Example-

ALTER TABLE msstudentsnew

DROP COLUMN DOB;

ALTER TABLE USES SUMMARY-

ALTER TABLE	Description	Syntax	Example
ADD COLUMN	To ADD COLUMN in existing Table.	ALTER TABLE <table_name> ADD <column_name> <datatype>;	ALTER TABLE msstudentsnew ADD DOB date;
ALTER COLUMN	To change column properties	ALTER TABLE <table_name> ALTER COLUMN <column_name> <datatype>;	ALTER TABLE msstudentsnew ALTER COLUMN DOB year;
DROP COLUMN	To delete a column in a table	ALTER TABLE <table_name> DROP COLUMN <column_name>;	ALTER TABLE msstudentsnew DROP COLUMN DOB;