Add Column in the Table

In this section, we shall learn how to add a column in the table in Structured Query Language.

The ALTER command in SQL allows the database users to add one or more columns in the SQL table. It allows the database users to modify the structure of the existing table in the database.

The syntax for adding a Single Column from the table is given below:

ALTER TABLETable_Name ADD Column_Name datatype;

The syntax for deleting Multiple Columns from the table is given below:

ALTER TABLETable_Name ADD Column_Name1 Column1_datatype, Column_Name2 Column2_datatype, ......, Column_NameN Columnn_datatype;

We have to use the ADD keyword in the ALTER command for adding one or more columns in the table.

If you want to add a column in the table, you have to follow the following steps one by one in a given order:

Create a Database.
Create a Table in the database.
View the Table structure before column addition.
Add a single column to the table.
View the Table structure after column addition.

Now, we are going to explain the above steps with an example:

Step 1: Create a Database

In the Structured Query Language, creating a database is the first step for storing the structured tables in the database.

Use the below SQL syntax to create a database:

CREATE DATABASE Database_Name;

Suppose you want to create the Vehicles database. For this, you have to type the following command in Structured Query Language:

CREATE DATABASE Vehicles;

Step 2: Create a Table and Insert the data

Now, you have to use the below SQL syntax for creating the table in your database:

CREATE TABLE table_name
(
column_Name_1 data type (size of the column_1),
column_Name_2 data type (size of the column_2),
column_Name_3 data type (size of the column_3),
...
column_Name_N data type (size of the column_1)
);

Suppose you want to create the Cars table in the Vehicles database. For this, you have to type the following query in your SQL application:

CREATE TABLE Cars
(
Car_Number INT AUTO_INCREMENT PRIMARY KEY,
Model INT,
Cars_Name VARCHAR (20),
Colour VARCHAR (20),
Price INT NOT NULL,
) ;

Step 3: View the Table Structure before Column Addition

After table creation and data insertion, you can view the structure of the Cars table by typing the following query in your SQL application:

DESC Cars
or
DESCRIBE Cars ;

Field	Type	NULL	Key	Default	Extra
Car_Number	INT	NO	T	NULL	auro_increment
Model	INT	Yes	-	NULL	-
Car_Name	Varchar(20)	Yes	-	NULL
Color	Varchar(20)	Yes	-	NULL	-
Price	INT	NO	-	NULL	-

Step 4: Add a Single Column to the table

The following ALTER query in SQL, adds the average column to the above Cars table:

ALTER TABLECars ADD Average INT NOT NULL DEFAULT 0;

Step 5: View the Table Structure after Column Addition

To check the result of the query executed in the 4^th step, you have to type the following command in SQL:

DESC Cars;

Field	Type	NULL	Key	Default	Extra
Car_Number	INT	NO	T	NULL	auro_increment
Model	INT	Yes	-	NULL	-
Car_Name	Varchar(20)	Yes	-	NULL	-
Color	Varchar(20)	Yes	-	NULL	-
Price	INT	NO	-	NULL	-
Average	INT	NO	-	0	-

As we can see in the above output, one column has been successfully added to the Cars table.

Add Multiple Columns to the table

The following ALTER query in SQLadds the multiple columns to the above Cars table:

ALTER TABLECars Add Engine_Number Varchar(50), Car_Number Varchar(45) ;

To check the result of the above query, you have to type the following DESCRIBE or DESC Command in your SQL application:

DESC Cars
or
DESCRIBE Cars ;

Field	Type	NULL	Key	Default	Extra
Car_Number	INT	NO	T	NULL	auro_increment
Model	INT	Yes	-	NULL	-
Car_Name	Varchar(20)	Yes	-	NULL	-
Color	Varchar(20)	Yes	-	NULL	-
Price	INT	NO	-	NULL	-
Average	INT	NO	-	0	-
Engine_Number	Varchar(50)	Yes	-	NULL	-
Plate_Number	Varchar(45)	Yes	-	NULL	-

As we can see, Engine_Number and Car_Number columns have been successfully added to the Cars table.

SQL Tutorial

SQL Database

SQL Table

SQL Select

SQL Clause

SQL Order By

SQL Insert

SQL Update

SQL Delete

SQL Join

SQL Keys

Difference

SQL Injection

SQL String Functions

Miscl

SQL MCQ

PL/SQL Tutorial

Sql Interview Question

SQl Quiz