ALTER TABLE
To add, modify, drop or delete columns in a table ALTER TABLE statement is used. Along with all these, it is also used to rename a table. The ALTER TABLE statement allow the users to Add one or more columns, to Modify column definition, to Drop one or more columns, to Rename columns and to Rename a table.

Syntax:

ALTER TABLE table_name action;

ALTER TABLE ADD column
Syntax: To add a column in the existing table.

ALTER TABLE table_name 
ADD column_name data_type constraint;

Parameters:
table_name: It is used to specify the name of the table.
column_definition: It is used to specify the column name, data type, and its constraint.

Syntax: To add multiple columns in the existing table.

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

Example 1: Adding a new column to an already existing table.

ALTER TABLE students  
ADD student_age varchar(20);

Explanation:
A new column “student_age” of data type VARCHAR and a maximum length of 20 will be added in the “students” table.

Example 2: Adding multiple columns to an already existing table.

ALTER TABLE students  
ADD student_age varchar(20);  
ADD student_city varchar(30);

Explanation:
Two new columns “student_age” and “student_city” of data type VARCHAR and a maximum length of 20 and 30 will be added in the “students” table.

ALTER TABLE MODIFY column
Syntax: To modify a single column of a table.

ALTER TABLE table_name 
MODIFY column_name action;

Syntax: To modify multiple columns of a table.

ALTER TABLE table_name
MODIFY (
    column_name_1 action,
    column_name_2 action,
    ...
);

Example 1: Modifying a single column of a table.

ALTER TABLE students
MODIFY student_name varchar2(50) NOT NULL;

Explanation:
Here the “students” is an already existing table whose column student_name is modified to varchar2 (50) and the column is now restricted to not allow NULL values.

Example 2: Modifying multiple columns of a table.

ALTER TABLE students
MODIFY (student_name varchar2(50) NOT NULL;  
student_age varchar2(40));

Explanation:
Here the “students” is an already existing table whose column student_name is modified to varchar2 (50) and the column is now restricted to not allow NULL values. Similarly, the column student_age is modified to varchar2 (40).

ALTER TABLE DROP COLUMN
Syntax:

ALTER TABLE table_name  
DROP COLUMN column_name;  

Example :

ALTER TABLE students  
DROP COLUMN student_age;

Explanation:
Here, “students” is an already existing table, from which the student_age column will be dropped.

ALTER TABLE RENAME COLUMN
Syntax:

ALTER TABLE table_name  
RENAME COLUMN existing_column_name to new_column_name;  

Example :

ALTER TABLE students  
RENAME COLUMN student_age to std_age;

Explanation:
Here, “students” is an already existing table. The column student_age will be renamed as std_age.

ALTER TABLE RENAME TO

Syntax:

ALTER TABLE existing_table_name  
RENAME TO new_name;   

Example :

ALTER TABLE students  
RENAME TO children;

Explanation:
Here, “students” is an already existing table. The table name will be renamed as “children”.