Alter table add column,add column to table,alter command in sql,alter table modify column,sql change column name,alter table delete column

How to Alter Table Add column ,Drop column ,Rename table Rename Column.

Releated Topic covered under the below index.

1. alter table add column, sql add column, add column to table sql,alter sql,alter command in sql
2. modify a column ,alter table modify column,sql change column name,alter column datatype in sql
3. drop a column,alter table delete column,delete a column in sql
4. rename a column 
5. rename a table 

syntax -

ALTER TABLE syntax is 

ALTER TABLE TABLE_NAME

ADD <column name> <Datatype / Defination>;

Examples - 

ALTER TABLE Employee

ADD Emp_number varchar2(45);

Alter table with default value

ALTER TABLE Employee

ADD Active_In_Company Char(1) DEFAULT 'Y';

In this case any employee insert into employee_master as active employee set as "Y"

Another example of alter table in multiple column add

Add multiple columns in table

Syntax - 

ALTER TABLE TABLE_NAME ADD (

                           <column name> <Datatype / Defination> , 

                           <column name> <Datatype / Defination>        

                           );

ALTER TABLE Employee

ADD (PAN_NO number(10), BANK_ACCT_NO NUMBER(20))  ;

In multiple adding the column define in Braces and seperatated by comma  "," as above.

--------------------------

Modify column in table

Syntax  - 

ALTER TABLE TABLE_NAME

MODIFY column_name Datatype;

Example

In Employee table there is existing column name "address" and the data type is "Varchar2(80)" and we need to more character required in address field i.e. 100 char.

then alter table employee command as below  

ALTER TABLE Employee

MODIFY Address varchar2(100) NOT NULL; 

Alter table modify column with Default condition :-

ALTER TABLE Employee

MODIFY married varchar2(75) DEFAULT 'N' NOT NULL;

Modify Multiple columns in table

Syntax:

ALTER TABLE table_name

MODIFY (column_1 column_type,

          column_2 column_type,

column_n column_type);

Example

ALTER TABLE customers

MODIFY (Employee_name varchar2(100) NOT NULL,

Active_In_Company varchar2(1) DEFAULT 'Y' NOT NULL);

Drop column in table

Syntax

To DROP A COLUMN 

ALTER TABLE syntax is -

ALTER TABLE table_name

DROP COLUMN column_name;

Example

ALTER TABLE Employee

DROP COLUMN city;

 

Rename column in table

Syntax

To RENAME A COLUMN in an existing table, the Oracle ALTER TABLE syntax is:

ALTER TABLE table_name

RENAME COLUMN old_name TO new_name;

ALTER TABLE Employee

RENAME COLUMN Employee_name TO Emp_name;

Rename table

Syntax

To RENAME A TABLE, the Oracle ALTER TABLE syntax is:

ALTER TABLE table_name

RENAME TO new_table_name;

Example

ALTER TABLE Employee

RENAME TO Employee_master;

• Tweet
• Share
• Share
• Share
• Share

About Admin MC3

This is dummy text. It is not meant to be read. Accordingly, it is difficult to figure out when to end it. But then, this is dummy text. It is not meant to be read. Period.

Related Post