AWS Global Infrastructure

Databases

Topics Covered
  • Cassandra (14 Blogs)
  • MongoDB Dev and Admin (15 Blogs)
  • MySQL (55 Blogs)
  • SQL Essentials Training and Certification (9 Blogs)
SEE MORE

MI-new-launch

myMock Interview Service for Real Tech Jobs

myMock-widget-banner-bg

How To Use Alter Table Statement In SQL?

Published on Aug 23,2019 184 Views
19 / 37 Blog from Introduction to SQL

MI-new-launch

myMock Interview Service for Real Tech Jobs

myMock-mobile-banner-bg

myMock Interview Service for Real Tech Jobs

  • Mock interview in latest tech domains i.e JAVA, AI, DEVOPS,etc
  • Get interviewed by leading tech experts
  • Real time assessment report and video recording

Have you ever tried to add, delete or modify columns in a table? If yes, then, ALTER TABLE is the command which you have to use. So, in this article on Alter Table, I will discuss how you can use this command to modify the columns in a table. 

SQL-Alter Table -EdurekaThe following topics will be covered in this article:

What is the Alter Table statement?

This statement is used to add, modify, or delete column(s) in an existing table. Also, this statement can be used to add/drop constraints on an existing table. The ALTER TABLE statement can be used in SQL along with the following:

Let’s discuss each one of these ones by one by considering the following table:

If you want to know, how to create a table in SQL, you can refer to my article on CREATE TABLE.

studentID

firstname

lastname

phonenumber

1

Rohan

Rathore

9876543210

2

Sonali

Saxena

9876567864

3

Ajay

Agarwal

9966448811

4

Geeta

Gulati

9765432786

5

Shubham

Sinha

9944888756

Operations:

ALTER TABLE ADD Column

This statement is used to add a column or add multiple columns in an existing table.

Syntax:

#Add Single Column
ALTER TABLE TableName
ADD ColumnName datatype;
#Add Multiple Columns
ALTER TABLE TableName 
ADD ColumnName datatype,
ADD ColumnName datatype,
ADD ColumnName datatype
;

Example:

ALTER TABLE students
ADD dob date;

You will see an output, that the column (dob) is added into the table as below:

studentIDfirstnamelastnamephonenumberdob

1

Rohan

Rathore

9876543210

2

Sonali

Saxena

9876567864

3

Ajay

Agarwal

9966448811

4

Geeta

Gulati

9765432786

5

Shubham

Sinha

9944888756

You can go forward, and insert data into the column, by using the insert query in SQL.

ALTER TABLE DROP Column

This statement is used to drop a column or multiple columns in an existing table.

Syntax:

ALTER TABLE TableName
DROP ColumnName datatype;

Example:

ALTER TABLE students
DROP dob date;

You will see an output, that the column is deleted from the table as below:

studentID

firstname

lastname

phonenumber

1

Rohan

Rathore

9876543210

2

Sonali

Saxena

9876567864

3

Ajay

Agarwal

9966448811

4

Geeta

Gulati

9765432786

5

Shubham

Sinha

9944888756

ALTER TABLE MODIFY Column

This statement is used to modify the data type of a column in an existing table.

Syntax:

#SQL Server 
ALTER TABLE TableName
ALTER COLUMN ColumnName datatype;
#MySQL
ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

Example:

Let’s add back the dob column, and change the data type of that column to year;

To add back the column mention the following query:

ALTER TABLE Persons
ALTER COLUMN dob year;

Now, to change the data type of the column, mention the code below:

ALTER TABLE Persons
ALTER COLUMN dob year;

You will see an output, that the dob column is added back to the table and has the data type ‘year’. Refer below.

studentIDfirstnamelastnamephonenumberdob

1

Rohan

Rathore

9876543210

2

Sonali

Saxena

9876567864

3

Ajay

Agarwal

9966448811

4

Geeta

Gulati

9765432786

5

Shubham

Sinha

9944888756

With this, we come to an end to this article. I hope you understood, how to use the above commands. If you wish to learn more about MySQL and get to know this open-source relational database, then check out our MySQL DBA Certification Training which comes with instructor-led live training and real-life project experience. This training will help you understand MySQL in-depth and help you achieve mastery over the subject.

Got a question for us? Please mention it in the comments section of this article and I will get back to you.

Comments
0 Comments

Browse Categories

webinar REGISTER FOR FREE WEBINAR
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP

Subscribe to our Newsletter, and get personalized recommendations.