ALTER

ALTER:

This command is used to modify the structure of the  table. Using this command we can perform four different operations. This command contains four subprograms those are

ALTER- a) Modify

             b) ADD

             c) RENAME

             d) DROP

a) Alter- Modify:

Guidelines for Modifying Columns:

This command is used to increase or decrease the size of the data type and also we can use the data type from all data type to new data type.

Syntax for Modifying Single Column:

ALTER TABLE <Table name> MODIFY (<Column name> data type(size)

EX:

SQL> alter table emp modify ename varchar2(12);

SQL> alter table emp modify(empno number(6));

Syntax for modify more than one column:

ALTER TABLE <Table name> MODIFY (COLUMN1 data type(size), COLUMN2 data type(size)…….,COLUMNN data type(size));

EX:

SQL> alter table emp modify(empno number(6),ename varchar2(12));

SQL> alter table emp modify(ename varchar2(12),deptno number(6));

Guidelines for Modifying Columns:    

• We can increase the width or precision of a numeric column.
• We can decrease the width of a column if the column contains only NULL values and if the table has no rows.
• We can change the data type if the column contains NULL's.
• We can converts a CHER column to the VARCHAR2 data type or convert a VARCHAR2 COLUMN to the CHAR data type if the column contains NULL values or if the size is not changed.

Alter - ADD:

This command is used to add a new column to the existing table.

Syntax for add a single column:

alter table<Table name>add column name datatype(size);

EX:

SQL> alter table emp add deptno number(4);

SQL> alter table employee add(email varchar2(25));

Syntax to add more than one column:

alter table<table name> add (column1 datatype(size), column2 datatype(size)…..,columnn datatype(size));

EX:

SQL> alter table emp add( mobileno number(10), address1 varchar2(30));

SQL> alter table employee add(mobileno number(10),address2 varchar2(30));

Note:

When  we add a new column to a table the new column is always added to the end of the table only.

Alter- RENAME:

This command is used to change the column name from old column name to new column name.

Guidlines for RENAME a Columns: 

• A column can renamed in table by using ALTER RENAME.
• The Feature is enabled from oracle 9.2 onwards.
• We can rename only one column at a time.

Syntax:

alter table <table name> rename COLUMN<old_column_name> to <new_column_name).

EX:

SQL>alter table employe rename column sal to salary;

SQL>alter table emp rename column ename to employe_name;

Syntax for change table name:

ALTER TABLE<old table name> RENAME TO <New table name>

EX:

SQL>alter table emp rename to employee;

SQL>alter table employee rename to emp;

Alter – DROP:

This command is used to remove the column from the existing table.

Guidlines to Drop a Column:

• The column may or may not contain data.
• We can drop more than one column at a time.
• The table must have at least one column remaining in it after it is altered.
• Once a column is droaped it cannot be recovered.

Syntax for Drop a Column:

alter table <table_name> drop column <column_name>;

EX:

SQL>alter table emp drop column empno;

SQL>alter table employee drop column email;

Syntax to drop more than one column:

alter table <table name> drop(<column name1>,<column name2>,....);

Ex:

SQL>alter table emp drop(empno,ename,deptno);

SQL>alter table emp employee(

Note:

Here we can’t drop all columns.