ALTER TABLE

The SQL ALTER TABLE statement is used to  add, delete, or modify columns in an existing table.

Syntax:

To add a column in a table, use the following syntax:

ALTER TABLE table_name
ADD column_name datatype

To delete a column in a table, use the following syntax:

ALTER TABLE table_name
DROP COLUMN column_name

Examples:

Here is the EMPLOYEE1 table:

ID	NAME	START_DATE	END_DATE	SALARY	CITY
1	A	01/01/2016 00:00:00	01/06/2016 00:00:00	10,000	CHENNAI
2	B	02/01/2016 00:00:00	07/03/2016 00:00:00	10,000	BOMBAY
3	C	03/01/2016 00:00:00	08/06/2016 00:00:00	10,000	DELHI
4	D	01/01/2016 00:00:00	07/06/2016 00:00:00	10,000	PUNE
5	E	01/01/2016 00:00:00	09/06/2016 00:00:00	10,000	NOIDA
6	F	01/01/2016 00:00:00	08/06/2016 00:00:00	10,000	BHUBANESWAR

ADD COLUMN Example

We want to add a column named "Designation" in the "EMPLOYEE1" table.

We use the following SQL statement:

ALTER TABLE EMPLOYEE1
ADD DESIGNATION VARCHAR(20)

The " EMPLOYEE1" table will now look like this:

ID	NAME	START_DATE	END_DATE	SALARY	CITY	DESIGNATION
1	A	01/01/2016 00:00:00	01/06/2016 00:00:00	10,000	CHENNAI
2	B	02/01/2016 00:00:00	07/03/2016 00:00:00	10,000	BOMBAY
3	C	03/01/2016 00:00:00	08/06/2016 00:00:00	10,000	DELHI
4	D	01/01/2016 00:00:00	07/06/2016 00:00:00	10,000	PUNE
5	E	01/01/2016 00:00:00	09/06/2016 00:00:00	10,000	NOIDA
6	F	01/01/2016 00:00:00	08/06/2016 00:00:00	10,000	BHUBANESWAR

DROP COLUMN Example

we want to delete the column named " DESIGNATION " in the " EMPLOYEE1" table.

We use the following SQL statement:

ALTER TABLE EMPLOYEE1
DROP COLUMN DESIGNATION

The " EMPLOYEE1 " table will now look like this:

ID	NAME	START_DATE	END_DATE	SALARY	CITY
1	A	01/01/2016 00:00:00	01/06/2016 00:00:00	10,000	CHENNAI
2	B	02/01/2016 00:00:00	07/03/2016 00:00:00	10,000	BOMBAY
3	C	03/01/2016 00:00:00	08/06/2016 00:00:00	10,000	DELHI
4	D	01/01/2016 00:00:00	07/06/2016 00:00:00	10,000	PUNE
5	E	01/01/2016 00:00:00	09/06/2016 00:00:00	10,000	NOIDA
6	F	01/01/2016 00:00:00	08/06/2016 00:00:00	10,000	BHUBANESWAR