ALTER TABLE statement - Add,Update,Delete or Modify Columns in Oracle Database

In Oracle database, ALTER TABLE is used to

> Add Column

> Modify Column

> Delete Column

> Rename Column

Add Column :

Syntax :

ALTER TABLE table_name  
 ADD column_name column-definition;   

Example :

Consider that already existing table student(we created this table in our previous post). Now, add a new column student_city into the table student.

ALTER TABLE student  
ADD student_city varchar2(50);  

Output:

Table altered.

Here a new column "student_city" is created in student table.

Add multiple Columns

Syntax:

ALTER TABLE table_name  
 ADD (column_1 column-definition,  
 column_2 column-definition, 
 ...  
 column_n column_definition); 

Example:

ALTER TABLE student  
ADD (student_type varchar2(50),  
     student_address varchar2(50)); 

Now, two columns student_type and student_address will be added in the table student.

MODIFY (modify column) :

Syntax:

ALTER TABLE table_name  
MODIFY column_name column_type;  

Example:

ALTER TABLE student  
MODIFY student_address varchar2(100) not null;  

Now the column student_address in the student table is modified to varchar2 (100) and forced the column to not allow null values.

RENAME COLUMN (rename column )

syntax:

ALTER TABLE table_name  
 RENAME COLUMN old_name to new_name;  

Example:

ALTER TABLE student  
 RENAME COLUMN student_name to sname;  

This will rename the column student_name into sname.

Rename Table (RENAME)

Syntax:

ALTER TABLE table_name  
RENAME TO new_table_name;   

Example:

ALTER TABLE student  
RENAME TO students;  

This will rename the student table into "students" table.

DROP COLUMN (to delete column):

Syntax:

ALTER TABLE table_name  
DROP COLUMN column_name;  

Example:

ALTER TABLE students  
 DROP COLUMN student_address;  

This will drop the student_address column from the table students.

we discussed ALTER statement.

If you have any query ,Feel free to comment down below.

Thanks for reading.