SQL add/drop/update column operation

The statement ALTER TABLE is mainly used to delete, add, or modify the columns into an existing table. It is also used to add many constraints on the current table.

ADD COLUMN is used to add the particular table to the existing one. We can add additional information without creating the whole database again.

SQL add column is used to add column operation into the existing SQL table. We can drop the table and recreate it according to our need. In the production environment, it is destructive as it pertains to the data.

Syntax of ADD COLUMN

The add column operation is used before the table with the help of transact SQL command.

ALTER TABLE table_name  

ADD column_name column_definition;

According to the syntax,

• Specify the table where we want to add the new column firstly.
• Then, we specify the column definition from the ADD Column

Syntax of column definition:

Column_name date_type constraints;  

If we want to add multiple columns to the existing table using any single statement, we can use the below syntax:

ALTER TABLE table_name (Name of  the table)  

ADD [COLUMN] column_definition, (for adding column)  

ADD [COLUMN] column_definition,  

 ...;

Many database support ALTER TABLE ADD COLUMN statement.

To add any one column to a table using SQL, we can specify that if we want to change the table structure by the ALTER TABLE command, which is followed by the ADD command in RDBMS.

Syntax:

The syntax for ALTER TABLE Add Column is,

ALTER TABLE "table_name"  

ADD "column_name" "Data Type"; 

Examples:

Look at the below example. Assuming our starting point is the Student table created in the CREATE TABLE section:

Table Student

Column Name	Data Type
First_Name	char(30)
Last_Name	char(25)
Birth_Date	datetime
Address	char(50)
City	char(40)
Country	char(20)

Example 1: Add one column to the table

If we want to add any column named “Gender.” Then, we write:

MySQL:

ALTER TABLE Student ADD Gender char(1);  

The result is shown below:

Table Student

Column Name	Data Type
First_Name	char(30)
Last_Name	char(25)
Birth_Date	datetime
Address	char(50)
City	char(40)
Country	char(20)
Gender	char(1)

Note: The new column named “Gender” becomes the last column in the Student table.

Example 2: Add multiple columns to the table

It is possible to add multiple columns.

For example, if we want to add a column called “Telephone” and another column called “Email,” we should type the following:

MySQL:

ALTER TABLE Student ADD (Telephone char(15), Email char(20) );  

Now the table becomes:

Table Student

Column Name	Data Type
First_Name	char(30)
Last_Name	char(25)
Birth_Date	datetime
Address	char(50)
City	char(40)
Country	char(20)
Gender	char(1)
Telephone	char(15)
Email	char(20)

Drop-Table command

The drop column is used to drop the column in the table. It is used to delete the unnecessary columns from the table.

Syntax:

ALTER TABLE table_name   /name of table  

DROP COLUMN column_name; 

ALTER TABLE- MODIFY

Modify function is used to modify the existing columns in a simple table. Multiple columns can be changed at once.

Syntax:

ALTER TABLE table_name  

MODIFY column_name column_type;

TABLE EMPLOYEE

ROLL_NO	Name OF Employee
1	Jessy
2	Trump
3	Stephan
4	Hawkins

QUERY:

• To ADD 2 columns COURSE and AGE to table Student.

ALTER TABLE EMPLOYEE ADD (AGE number(3),COURSE varchar(20));  

OUTPUT:

ROLL_NO	Name of employee	AGE	COURSE
1	Jessy
2	Trump
3	Stephan
4	Hawkins

• To “MODIFY” column AGE in table Employee

ALTER TABLE EMPLOYEE MODIFY COURSE varchar(15);  

After running the above query maximum size, of Course, Column is reduced to 15 from 20.

• To DROP column in the table Employee.

ALTER TABLE EMPLOYEE DROP COLUMN AGE;  

OUTPUT:

ROLL_NO NAME	COURSE
1	Ram
2	Abhi
3	Rahul
4	Tanu