How to add column in table in SQL ?

Introduction

• To add a column in already created table, one needs to use the ALTER command along with the ADD clause.
• If in the query, it is not specified where the new column is to be added, then by default it will be added as a last column.
• One can also add a new column at the first or even after a specific column of an already created table.
• You can add one column or more than one columns at a time using a single SQL query.

(A). Adding a new column at the last of an existing table

Syntax:

1

2

3

ALTER TABLE tablename ADD (ColumnName datatype);

where,

1. Tablename is the name of an already existing table to which you have to add a new column.
2. Column_name is the name of the column which is to be added to an already existing table.

Example:

First, we will create a database with name “studentdb”. Then in that database we will create a table “student” and insert records into the table. We will consider the same database and also the same table for subsequent examples.

Now, we will add a new column ‘City’ to an existing table.

mysql> USE studentdb;

Database changed

mysql> SELECT *FROM student;

+ — — — — -+ — — — — — -+ — — — — — -+ — — — — — — -+

| Stud_ID | Stud_Name | Course_ID | Course_Name |

+ — — — — -+ — — — — — -+ — — — — — -+ — — — — — — -+

| 1 | Prajakta | 101 | DBMS |

| 2 | Shweta | 102 | CN |

| 3 | Nikita | 103 | OS |

| 4 | Ankita | 104 | C |

+ — — — — -+ — — — — — -+ — — — — — -+ — — — — — — -+

4 rows in set (0.00 sec)

mysql> ALTER TABLE student ADD (City VARCHAR(20));

Query OK, 4 rows affected (0.29 sec)

Records: 4 Duplicates: 0 Warnings: 0

mysql> SELECT *FROM student;

+ — — — — -+ — — — — — -+ — — — — — -+ — — — — — — -+ — — — +

| Stud_ID | Stud_Name | Course_ID | Course_Name | City |

+ — — — — -+ — — — — — -+ — — — — — -+ — — — — — — -+ — — — +

| 1 | Prajakta | 101 | DBMS | NULL |

| 2 | Shweta | 102 | CN | NULL |

| 3 | Nikita | 103 | OS | NULL |

| 4 | Ankita | 104 | C | NULL |

+ — — — — -+ — — — — — -+ — — — — — -+ — — — — — — -+ — — — +

4 rows in set (0.00 sec)

How to add column in table in SQL ? - Tutorial And Example