>> ALTER to MODIFY dataTypes, RENAME columnName / tableName in SQL

ALTER in SQL:

ALTER command is used to modify the data type of a column.

ALTER command is used to rename the name of the column.

ALTER command is used to rename the name of the table.

Before we start ALTER, Let's create a table EMPDATA as below;

CREATE  TABLE  EMPDATA

(

NAME   varchar(20),

DEPT   varchar(20),

SALARY   decimal(10,2),

STATE  varchar(20)

) ;

After running above query , we see a message : Table EMPDATA created.

Let's add data into the table.

INSERT INTO  EMPDATA ( NAME , DEPT , SALARY, STATE )

VALUES  ( 'DAVID' , 'SALES' , 2000.00, 'TX' );

After running above query , we see a message : 1 row inserted.

Let's check the table:

SELECT * FROM EMPDATA ;

NAME	DEPT	SALARY	STATE
DAVID	SALES	2000	TX

ALTER (To modify the data type of a column):

Now, let's change the DATATYPE of a column.

Syntax:

ALTER  TABLE   TABLE_NAME

MODIFY  column_name  new_datatype ;

Let's change the dataType of the column STATE from varchar(20) to char(10);

Query :

ALTER TABLE EMPDATA

MODIFY STATE char(10);

After running above query , we see a message : Table EMPDATA altered.

ALTER (To rename the column).

Syntax:

ALTER  TABLE  TABLE_NAME

RENAME COLUMN  old_COLUMN_NAME TO new_COLUMN_NAME ;

Let's change the name of the column STATE to REGION.

Query:

ALTER TABLE EMPDATA

RENAME COLUMN  STATE  TO  REGION ;

After running above query , we see a message : Table EMPDATA altered.

Let's check the table:

SELECT * FROM EMPDATA ;

NAME	DEPT	SALARY	REGION
DAVID	SALES	2000	TX

ALTER (To change the name of the table).

Syntax:

ALTER TABLE  TABLE_NAME

RENAME TO new_ TABLE_NAME;

Let's rename the table name from EMPDATA to EMPRECORD.

Query:

ALTER TABLE  EMPDATA

RENAME TO  EMPRECORD;

After running above query , we see a message : Table EMPDATA altered.

Let's check the table:

SELECT * FROM EMPDATA ;

This query will return this message :" table or view does not exist" , because the name of the table has already been changed to new name.

Let's check the table with new name.

SELECT * FROM  EMPRECORD;

NAME	DEPT	SALARY	REGION
DAVID	SALES	2000	TX

NEXT

Go To Main SQL Page



.