>> ALTER to ADD / DROP columns in SQL :

ALTER to ADD / DROP columns in SQL:

ALTER command is used to add new column to an existing table  OR remove an existing column from an existing table.

ADD Column:
Syntax:
ALTER  TABLE  table_name
ADD  NEW_COLUMN  NEW_datatype;

Let's take an existing table NEWDEPT as below:

DEPTNO	DEPT_NAME	LOCATION
10	ACCOUNTING	BALTIMORE
20	RESEARCH	DALLAS
30	SALES	CHICAGO
40	OPERATIONS	RESTON

Let's write query to add a new column "MANAGER" in above table.

Query:

ALTER TABLE NEWDEPT

ADD MANAGER VARCHAR(15);

After running this , we see a message : Table NEWDEPT altered

Now, let's see data in the table :

SELECT * FROM NEWDEPT;

DEPTNO	DEPT_NAME	LOCATION	MANAGER
10	ACCOUNTING	BALTIMORE	(null)
20	RESEARCH	DALLAS	(null)
30	SALES	CHICAGO	(null)
40	OPERATIONS	RESTON	(null)

The MANAGER column has been added . Since we donot have any data for that column, the respective rows are showing "null".

Let's add data in the column MANAGER.

Let's add MANAGER = 'DAVID' for  DALLAS    Location.

Query:

UPDATE  NEWDEPT

SET MANAGER = 'DAVID'

where LOCATION= 'DALLAS ' ;

After running this , we see a message : 1 row updated.

Now, let's see data in the table :

SELECT * FROM NEWDEPT;

DEPTNO	DEPT_NAME	LOCATION	MANAGER
10	ACCOUNTING	BALTIMORE	(null)
20	RESEARCH	DALLAS	DAVID
30	SALES	CHICAGO	(null)
40	OPERATIONS	RESTON	(null)

In this way you can add data in other cells also.

We can add more than one columns at the same time.

Syntax;
ALTER  TABLE  table_name
ADD  ( NEW_COLUMN1  NEW_datatype1 , NEW_COLUMN2 NEW_DATATYPE2, ....) ;

Query:
ALTER TABLE NEWDEPT
ADD (SALARY DECIMAL(5,2) , EMPLOYEES int );

After running this , we see a message : Table NEWDEPT altered..

Now, let's see data in the table :

SELECT * FROM NEWDEPT;

DEPTNO	DEPT_NAME	LOCATION	MANAGER	SALARY	EMPLOYEES
10	ACCOUNTING	BALTIMORE	(null)	(null)	(null)
20	RESEARCH	DALLAS	DAVID	(null)	(null)
30	SALES	CHICAGO	(null)	(null)	(null)
40	OPERATIONS	RESTON	(null)	(null)	(null)

DROP column:

We can remove(Drop) existing column from an existing table.

Syntax:

ALTER TABLE "table_name"

DROP COLUMN "column_name";

Let's drop the column MANGER from the NEWDEPT table.

Query:

ALTER TABLE NEWDEPT

DROP COLUMN MANAGER;

After running this , we see a message : Table NEWDEPT altered.

Now, let's see data in the table :

SELECT * FROM NEWDEPT;

DEPTNO	DEPT_NAME	LOCATION	SALARY	EMPLOYEES
10	ACCOUNTING	BALTIMORE	(null)	(null)
20	RESEARCH	DALLAS	(null)	(null)
30	SALES	CHICAGO	(null)	(null)
40	OPERATIONS	RESTON	(null)	(null)

NEXT

Go To Main SQL Page

.