Learn SQL:Use Of Alter Statement/Command, With Example and Important Interview Questions.

Learn SQL: How to use Alter Query, to Add/Remove Columns.

 

ALTER COMMAND:

 
This command is used to change, modify, delete and also to rename the existing columns of the table.

The ALTER Command allows you to change almost everything in the table without having to reinsert the data. But be careful, if we change a column of one data type to a different one, we have risk of losing the data.

SYNTAX FOR ADDING COLUMN:

 ALTER TABLE table_name

ADD COLUMN col_name datatype;

If you write whole statement in one line then it will give an error.
Because firstly we have to apply a command for alter the table which reaches to the database that someone want to make changes in the table and then on next line write what you want to change.

Example:

In above example we add only one column if you want to add multiple columns then syntax is:

ALTER TABLE table_name

ADD ( col_1 datatype, col_2 datatype,.......col_n datatype );

 
SYNTAX FOR MODIFYING THE COLUMN:

MODIFY Command is used to change the datatype of columns.

For Example: Suppose you define CHAR datatype for address and now you have to enter some integer values in it. For this, you need VARCHAR. 


SYNTAX of MODIFY Command is: 

ALTER TABLE emp

MODIFY COLUMN col_name new_datatype; 

SYNTAX FOR DELETING THE COLUMN:

For deleting any column we use DROP command as:

ALTER TABLE emp

DROP COLUMN col_name;

 
SYNTAX FOR RENAMING THE TABLE OR COLUMN NAME:

We use RENAME command for changing the name of the table.

Syntax of RENAME is:

• ALTER TABLE table_name

RENAME TO new table_name; 

(This syntax is for changing the table name) 

• ALTER TABLE table_name

RENAME old col_name TO new col_name;

(This syntax is for changing the column name)

 
EXAMPLE:

Previous                                                                                 Next