Alter Command In SQL | Truncate Table In SQL

We offer you a brighter future with FREE online courses - Start Now!!

SQL provides us with the flexibility to modify and delete a table after it’s creation. We have data that needs to be put in the table and, at the same time, data that is of no use after a particular time.

To keep our data updated, we need to take various measures, and table altering is one of those. Alter and Drop commands are applicable for the same.

Demo Database

Let us view the database that we will use here, using the SELECT query.

SELECT * FROM tableName ;


Select * from dataflair_employee ;

SQL View Database

We have 5 columns, namely:

  1. name_emp
  2. post_emp
  3. email
  4. age
  5. salary

What is an Alter Command in SQL?

Alter command is applicable to add, modify, or update columns in an existing table. It is also applicable to drop or delete a table. The basic syntax of the alter command is as follows:


operation columnName datatype;

We can perform the following tasks using the Alter Command:

1. ADD Column in SQL

Technology is evolving rapidly!
Stay updated with DataFlair on WhatsApp!!

Used to add a new column to the existing table.

ADD columnName datatype ;

Example 1: Let us add a gender column in the table.

ALTER TABLE dataflair_employee
ADD gender varchar(10);

rename column in SQL

2. MODIFY Column in SQL

Used to modify the already existing columns in a database. We can modify one or multiple columns at once.


MODIFY columnName columnType;

Example 1: Let us modify the size of our name column to 100 in varchar.

ALTER TABLE dataflair_employee
MODIFY name_emp varchar(100);

modify column in SQL

We can see the name_emp column has changed in size from varchar(50) to varchar(100).

3. RENAME Column in SQL

Rename is put with Alter to change the name of a column. This could be applicable due to various reasons, for example:

  • To make the column name meaningful.
  • Simplification of names for analysis.

Let us rename our table and also the columns of our table.

Renaming the Table in SQL


RENAME TO newTableName 

Example 1: Let us rename our table from DataFlair_Employee to DataFlair_Info.


ALTER TABLE dataflair_employee
RENAME TO DataFlair_Info ;


rename table in SQL

Renaming The Column in SQL


ALTER TABLE table_name
CHANGE COLUMN old_name TO new_name;

Example 1: Let us rename our name_emp column to employeeName.

ALTER TABLE dataflair_info
CHANGE COLUMN `name_emp` `employeeName` VARCHAR(100) ;



change column in SQL

4. DROP Column in SQL

We use drop to remove the columns which are no longer required in the database. We should be very careful while we drop a column as data we delete once can’t be retrieved.

DROP COLUMN columnName ;

Example 1: Let us DROP the gender column from our DataFlair_info database.

ALTER TABLE dataflair_info
DROP COLUMN gender ;

drop column in SQL

We can see the gender column is no longer available in our database.

What is the Truncate Command in SQL?

We use the Truncate command to delete the data stored in the table. The working of truncate command is similar to the working of Delete command without a where clause.



Difference between Truncate and Drop command in SQL?

Executing the truncate command deletes all the data stored in the table or database. Whereas the Drop command is applicable to destroy the whole object it could be a table, a database, or a schema.

Also, syntax differences exist. Syntax of both are as follows:

SQL Truncate Syntax:


Syntax of Drop: 

DROP TABLE tableName ;

Example 1: Let us drop the table DataFlair_Info.

TRUNCATE TABLE  dataflair_info;

truncate table in SQL

Example 2: Let us drop our table.

DROP TABLE dataflair_info ;

drop table in SQL

Some of the Restrictions on Alter table are as follows:

  • By using an alter table, we can’t change the name of the table.
  • We can’t modify the names of the columns.
  • We can’t alter a column already containing data.


Thus, we have seen the alter and the truncate command in SQL. We use the alter command for multiple functionalities i.e., to add columns, modify columns, and many more.

With these commands, we can easily modify our database. We need to be very attentive while truncating the data as once a database point strikeout, we can’t recover it back.

You give me 15 seconds I promise you best tutorials
Please share your happy experience on Google

follow dataflair on YouTube

3 Responses

  1. Kavya says:

    Can you share the detailed pdf of SQL with some real scenarios

  2. Veersh says:

    Can we use trancuate for perticular column?

Leave a Reply

Your email address will not be published. Required fields are marked *