SQL ALTER TABLE

The ALTER TABLE statement in Structured Query Language allows you to add, modify, and delete columns of an existing table. This statement also allows database users to add and remove various SQL constraints on the existing tables.

Any user can also change the name of the table using this statement.

ALTER TABLE ADD Column statement in SQL

In many situations, you may require to add the columns in the existing table. Instead of creating a whole table or database again you can easily add single and multiple columns using the ADD keyword.

Syntax of ALTER TABLE ADD Column statement in SQL

ALTER TABLE table_name ADD column_name column-definition;  

The above syntax only allows you to add a single column to the existing table. If you want to add more than one column to the table in a single SQL statement, then use the following syntax:

ALTER TABLE table_name     

ADD (column_Name1 column-definition,    

column_Name2 column-definition,    

.....    

column_NameN column-definition);    

Examples of ALTER TABLE ADD Column statement in SQL

Here, we have taken the following two different SQL examples, which will help you how to add the single and multiple columns in the existing table using ALTER TABLE statement:

Example 1: Let’s take an example of a table named Cars:

Car Name	Car Color	Car Cost
Hyundai Creta	White	10,85,000
Hyundai Venue	White	9,50,000
Hyundai i20	Red	9,00,000
Kia Sonet	White	10,00,000
Kia Seltos	Black	8,00,000
Swift Dezire	Red	7,95,000

Table: Cars

• Suppose, you want to add the new column Car_Model in the above table. For this, you have to type the following query in the SQL:

ALTER TABLE Cars ADD Car_Model Varchar(20);  

2. This statement will add the Car_Model column to the Cars table.

Example 2: Let’s take an example of a table named Employee:

Emp_Id	Emp_Name	Emp_Salary	Emp_City
201	Abhay	25000	Goa
202	Ankit	45000	Delhi
203	Bheem	30000	Goa
204	Ram	29000	Goa
205	Sumit	40000	Delhi

Table: Employee

• Suppose, you want to add two columns, Emp_ContactNo. and Emp_EmailID, in the above Employee table. For this, you have to type the following query in the SQL:

ALTER TABLE Employee ADD ( Emp_ContactNo. Number(13), Emp_EmailID varchar(50) ;  

This statement will add Emp_ContactNo. and Emp_EmailID columns to the Employee table.

ALTER TABLE MODIFY Column statement in SQL

The MODIFY keyword is used for changing the column definition of the existing table.

Syntax of ALTER TABLE MODIFY Column statement in SQL

ALTER TABLE table_name MODIFY column_name column-definition;  

This syntax only allows you to modify a single column of the existing table. If you want to modify more than one column of the table in a single SQL statement, then use the following syntax:

ALTER TABLE table_name     

MODIFY (column_Name1 column-definition,    

column_Name2 column-definition,    

.....    

column_NameN column-definition);   

Examples of ALTER TABLE MODIFY Column statement in SQL

Here, we have taken the following two different SQL examples, which will help you how to modify single and multiple columns of the existing table using ALTER TABLE statement:

Example 1: Let’s take an example of a table named Cars:

Car Name	Car Color	Car Cost
Hyundai Creta	White	10,85,000
Hyundai Venue	White	9,50,000
Hyundai i20	Red	9,00,000
Kia Sonet	White	10,00,000
Kia Seltos	Black	8,00,000
Swift Dezire	Red	7,95,000

Table: Cars

• Suppose, you want to modify the datatype of the Car_Color column of the above table. For this, you have to type the following query in the SQL:

ALTER TABLE Cars ADD Car_Color Varchar(50);  

Example 2: Let’s take an example of a table named Employee:

Emp_Id	Emp_Name	Emp_Salary	Emp_City
201	Abhay	25000	Goa
202	Ankit	45000	Delhi
203	Bheem	30000	Goa
204	Ram	29000	Goa
205	Sumit	40000	Delhi

Table: Employee

• Suppose, you want to modify the datatypes of two columns Emp_ContactNo. and Emp_EmailID of the above Employee table. For this, you have to type the following query in the SQL:

ALTER TABLE Employee ADD ( Emp_ContactNo. Int, Emp_EmailID varchar(80) ;  

ALTER TABLE DROP Column statement in SQL

In many situations, you may require to delete the columns from the existing table. Instead of deleting the whole table or database you can use DROP keyword for deleting the columns.

Syntax of ALTER TABLE DROP Column statement in SQL

ALTER TABLE table_name DROP Column column_name ;  

Examples of ALTER TABLE DROP Column statement in SQL

Here, we have taken the following two different SQL examples, which will help you how to delete a column from the existing table using ALTER TABLE statement:

Example 1: Let’s take an example of a table named Cars:

Car Name	Car Color	Car Cost
Hyundai Creta	White	10,85,000
Hyundai Venue	White	9,50,000
Hyundai i20	Red	9,00,000
Kia Sonet	White	10,00,000
Kia Seltos	Black	8,00,000
Swift Dezire	Red	7,95,000

Table: Cars

• Suppose, you want to delete the Car_Color column from the above table. For this, you have to type the following query in the SQL:

ALTER TABLE Cars DROP COLUMN Car_Color ;  

• Let’s check using the following statement that the Car_Color column is deleted from the table or not:

SELECT * FROM Cars;  

Car Name	Car Cost
Hyundai Creta	10,85,000
Hyundai Venue	9,50,000
Hyundai i20	9,00,000
Kia Sonet	10,00,000
Kia Seltos	8,00,000
Swift Dezire	7,95,000

Table: Cars

Example 2: Let’s take an example of a table named Employee:

Emp_Id	Emp_Name	Emp_Salary	Emp_City
201	Abhay	25000	Goa
202	Ankit	45000	Delhi
203	Bheem	30000	Goa
204	Ram	29000	Goa
205	Sumit	40000	Delhi

Table: Employee

• Suppose, you want to delete the Emp_Salary and Emp_City column from the above Employee table. For this, you have to type the following two different queries in the SQL:

ALTER TABLE Cars DROP COLUMN Emp_Salary ;  

ALTER TABLE Cars DROP COLUMN Emp_City ;

ALTER TABLE RENAME Column statement in SQL

The RENAME keyword is used for changing the name of columns or fields of the existing table.

Syntax of ALTER TABLE RENAME Column statement in SQL

ALTER TABLE table_name RENAME COLUMN old_name to new_name;    

Examples of ALTER TABLE RENAME Column statement in SQL

Here, we have taken the following two different SQL examples, which will help you how to change the name of a column of the existing table using ALTER TABLE statement:

Example 1: Let’s take an example of a table named Cars:

Car Name	Car Color	Car Cost
Hyundai Creta	White	10,85,000
Hyundai Venue	White	9,50,000
Hyundai i20	Red	9,00,000
Kia Sonet	White	10,00,000
Kia Seltos	Black	8,00,000
Swift Dezire	Red	7,95,000

Table: Cars

• Suppose, you want to change the name of the Car_Color column of the above Cars table. For this, you have to type the following query in the SQL:

ALTER TABLE Cars RENAME COLUMN Car_Color to Colors;    

This statement will change the name of a column of the Cars table. To see the changes, you have to type the following query:

SELECT * FROM Cars;  

Car Name	Car Color	Car Cost
Hyundai Creta	White	10,85,000
Hyundai Venue	White	9,50,000
Hyundai i20	Red	9,00,000
Kia Sonet	White	10,00,000
Kia Seltos	Black	8,00,000
Swift Dezire	Red	7,95,000

Table: Cars

Example 2: Let’s take an example of a table named Employee:

Emp_Id	Emp_Name	Emp_Salary	Emp_City
201	Abhay	25000	Goa
202	Ankit	45000	Delhi
203	Bheem	30000	Goa
204	Ram	29000	Goa
205	Sumit	40000	Delhi

Table: Employee

• Suppose, you want to change the name of the Emp_City column of the above Employee table. For this, you have to type the following query in the SQL:

ALTER TABLE Employee RENAME COLUMN Emp_City to Emp_Address;  

This statement will change the name of a column of the Employee table. To see the changes, you have to type the following query:

SELECT * FROM Employee;  

Emp_Id	Emp_Name	Emp_Salary	Emp_Address
201	Abhay	25000	Goa
202	Ankit	45000	Delhi
203	Bheem	30000	Goa
204	Ram	29000	Goa
205	Sumit	40000	Delhi