ALTER TABLE Command in SQL

For Complete YouTube Video: Click Here

In this class, we will understand ALTER TABLE Command in SQL.

We have already discussed the creation of a table.

ALTER TABLE Command in SQL

Whenever we want to change the structure of a table, then we will use the Data Definition Language Commands or DDL commands.

We use the ALTER TABLE command to add, delete, and modify columns on an existing table or add and drop various constraints on a current table.

This class will use alter table command to add, delete, and modify columns on an existing table.

In the next class, we will cover the alter table command to add and drop various constraints on a current table.

To understand, let us consider the user’s table as shown below.

To the above table, we try to ADD column, DROP column, and MODIFY the column’s datatype.

ADD Column

The syntax to ADD a column is as shown below.

ALTER TABLE table_name ADD column_name datatype;

ALTER TABLE is the keyword used to alter the structure of the table.

ADD is the keyword used to add a column, and we have to provide the name of the column to be added and the datatype of the column.

We add a new column by name pno of int datatype to the above user’s table, as shown below.

ALTER TABLE users ADD pno int;

The image below is the table after adding a column to the user’s table.

DROP Column

The syntax to DROP a column is as shown below.

ALTER TABLE table_name DROP COLUMN column_name;

DROP is the keyword used to drop a column, and we have to provide the name of the column to be dropped.

We drop a column by name id.

ALTER TABLE users DROP COLUMN id;

The image below is the table after dropping a column of the user’s table.

ALTER Column

The syntax to MODIFY a column is as shown below.

ALTER TABLE table_name MODIFY column_name datatype;

MODIFY is the keyword used to modify the data type of a column, and we have to provide the name of the column and the new data type to which it has to be modified.

ALTER TABLE users MODIFY pno varchar(25);

To know whether the column data type has been modified or not we use the desc users; command.

The image below shows the description of the table.