Skip to main content

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
 intnvarchar(20)numeric(18)intnvarchar(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
 intnvarchar(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
 intnvarchar(20)nvarcharnumeric(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
 intnvarchar(30)nvarcharnumeric(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
 intnvarchar(30)numeric(18,0)nvarchar(10)




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

Comments