ALTER TABLE Statement in SQL

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. 

Syntax

  

ALTER TABLE table_name ADD Column_Name datatype

For delete a column in a table:- 

Syntax

  
ALTER TABLE table_name DROP COLUMN column_name

Change Data Type of Clumn in SQL:- Syntax

  
ALTER TABLE table_name ALTER COLUMN column_name datatype

Now, we creating a table named is tblStudentRecord and insert some record as following:- 

  
CREATE TABLE tblStudentRecord
(
Name varchar(100), Rollno int primary key, Branch varchar(30),Email varchar(100),Address varchar(200)
)

------>>Now add some record in tblStudentRecord<<--------

INSERT INTO tblStudentRecord VALUES('Santosh kumar singh',1001,'Information Technology','sa@gmail.com','A-3033')
INSERT INTO tblStudentRecord VALUES('Er. Reena Tanti',1002,'ECE','err@gmail.com','T-387, Gaya')
INSERT INTO tblStudentRecord VALUES('Chandan kumar',1003,'CSE','chn@gmail.com','Gaya')
INSERT INTO tblStudentRecord VALUES('Nand kishor kumar',1004,'BCA','nk@gmail.com','A-3033,Darbhanga')
INSERT INTO tblStudentRecord VALUES('Abhinav kumar',1005,'MCA','ab@gmail.com','H-837')

Now, we want to add new column in tblStudentRecord table. For this we write the following code:-

  
ALTER TABLE tblStudentRecord ADD Gender varchar(20)

Now, we want to change the data type of column Gender --->> varchar(20) To nvarchar(50) for this we write the following code:- 

  
ALTER TABLE tblStudentRecord ALTER COLUMN Gender nvarchar(50)

DROP Column :- Now, we want to drop column Gender from tblStudentRecord table. For this we write the following code:- 

  

ALTER TABLE tblStudentRecord DROP COLUMN Gender

Gender column is removed from tblStudentRecord table.