SQL ADD COLUMN

In this SQL article, you will learn about the ADD COLUMN command in detail with syntax and examples.

What is ADD COLUMN Statement?

In many SQL situations, you may require to add the new columns or fields in the existing table. So, SQL provides the ADD keyword with ALTER TABLE command to overcome this situation.

The ALTER TABLE and CREATE TABLE are the two different statements in Structured Query Language that allows you to add columns. But the difference between the both statements is that CREATE statement adds columns at the time of table creation, whereas ALTER statement adds one or more columns later in the existing table.

Syntax of ADD Column statement

ALTER TABLETable_Name ADD Column_Name Definition_of_New_Column;

This ALTER TABLE syntax allows us to add a single new field to the existing table.

If you want to add more than one new field to the table in a single SQL query, you have to use the following syntax:

ALTER TABLE table_name
ADD (column_Name_1 column-definition,
column_Name_2 column-definition,
.....,
column_Name_N column-definition);

Examples of ADD Column statement in SQL

Here, we took the following two different examples of Structured Query Language, which will help us to add single and multiple fields in the existing table:

Example 1:

Firstly, we create the Teacher_Details table and then insert the dummy records into the Teacher_Details table.

CREATE TABLE Teacher_Details
(
Teacher_ID INT PRIMARY KEY,
First_Name VARCHAR (80),
Course VARCHAR (30) NOT NULL,
Teacher_Address VARCHAR (30),
Teacher_Age INT
) ;

The following INSERT query inserts the record of teachers into the above table:

INSERT INTO Teacher_Details (Teacher_ID, First_Name, Course, Teacher_Address, Teacher_Age) VALUES( 2001, Arun, MBA, Goa, 26),
( 1002, Anita, MCA, Ghaziabad, 25),
( 1003, Vishal, MBA, Gorakhpur, 24),
( 1004, Shobhit, BCA, Dehradun, 26),
( 1005, Rohit, MCA, Lucknow, 28),
( 1006, Yogesh, MBA, Jaipur, 28) ;

The following query displays the record of the Teacher_Details table:

SELECT * FROM Teacher_Details;

Teacher_ID	First_Name	Course	Teacher_Address	Teacher_Age
2001	Arun	MBA	Goa	26
1002	Anita	MCA	Ghaziabad	25
1003	Vishal	MBA	Gorakhpur	24
1004	shobhit	BCA	Dehradun	26
1005	Rohit	MCA	Lucknow	28
1006	Yogesh	MBA	Jaipur	28

The following query adds the new Teacher_MailID column into the above Teacher_Details table:

ALTER TABLE Cars Teacher_DetailsTeacher_MailID Varchar(100);

Example 2:

Let's take another example which adds multiple columns into the table. Let's start by creating another Customer table:

CREATE TABLE Customer
(
Cust_Id Int,
First_Name Varchar (20),
Gender Varchar (10),
City Varchar (20),
) ;

The following query inserts the multiple records into the Customer table:

INSERT INTO Customer (Cust_ID, First_Name, Gender, City) VALUES (501, Jones, Male, Goa),
(502, Arun, Male, Mumbai),
(503, Somya, Female, Shimla),
(504, Ajay, Male, Delhi),
(505, Ram, Male, Dehradun),
(506, Anaya, Female, Haridwar);

Cust_ID	First_Name	Gender	City
501	Jones	Male	Goa
502	Arun	Male	Mumbai
503	Somya	Female	Shimla
504	Ajay	Male	Delhi
505	Ram	Male	Dehradun
506	Anaya	Female	Haridwar

Table: Customer

The following query adds two new columns in the Customer table:

ALTER TABLECustomer ADD ( customer_ContactNo. INT, Customer_EmailID varchar(80);

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

SQL ADD COLUMN

What is ADD COLUMN Statement?

Syntax of ADD Column statement

Examples of ADD Column statement in SQL

Comment / Reply From

You May Also Like

Popular Post

Language

Newsletter