ALTER statement in sql

ALTER statement.

• ALTER statement alter table design.
• It Add New Column.
• it Add the Column constraints Primary key, foreign key etc.
• it modify column name.
• It modify column data type.
• It Drop the column.

Add new column (Iam using MSSQL Server)

                        ALTER TABLE table_name ADD column_name DataType;

Example:

                        CREATE TABLE Employee(id int);

we create table employee with only one column id, now we add extra column using ALTER command.

Employee

id

int

ALTER TABLE Employee ADD name nvarchar(20),Age numeric(18) ; 

id	name	age
int	nvarchar(20)	numeric(18)

ALTER TABLE Employee ADD dept_id int, location nvarchar(10) ; 

id	name	age	dept_id	location
int	nvarchar(20)	numeric(18)	int	nvarchar(10)

Modify Column datatype:

To change the datatype dept_id int to numeric(18,0)

ALTER TABLE Employee ALTER COLUMN id numeric(18,0);in MS SQL Server.

ALTER TABLE Employee MODIFY id numeric(18,0); in Oracle

ALTER TABLE Employee MODIFY COLUMN id numeric(18,0); in ACCESS

id	name	age	dept_id	location
int	nvarchar(20)	numeric(18)	numeric(18,0)	nvarchar(10)

Changing Size of Column:

ALTER TABLE Employee ALTER COLUMN AGE nvarchar;

id	name	age	dept_id	location
int	nvarchar(20)	nvarchar	numeric(18,0)	nvarchar(10)

ALTER TABLE Employee ALTER COLUMN name nvarchar(30);

This statement change the size of column name for nvarchar(20) to nvarchar(30);

id	name	age	dept_id	location
int	nvarchar(30)	nvarchar	numeric(18,0)	nvarchar(10)

DROP column:

                        To delete column in table use drop command in alter statement.

ALTER TABLE Employee DROP COLUMN age;

id	name	dept_id	location
int	nvarchar(30)	numeric(18,0)	nvarchar(10)

Thank You. Like, Share and Subscribe 👍 Followlect.com