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.

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:

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

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,

Examples:

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

Table Student

Example 1: Add one column to the table

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

MySQL:

The result is shown below:

Table Student

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:

Now the table becomes:

Table Student

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- MODIFY

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

Syntax:

TABLE EMPLOYEE

QUERY:

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

OUTPUT:

• To "MODIFY" column AGE in table Employee

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

• To DROP column in the table Employee.

OUTPUT: