It is possible to modify the structure of a table (the relation schema) even if rows have already been inserted into the table.

Adding the Column to the Table: - A column can be added using the alter table command. Following is the syntax for adding the column to the table.

Syntax: - alter table <table name> add(<column> data type [<default value>] [<column constraint>]);

If more than one column should be added at one time, respective add clauses need to be separated by colons.

A table constraint can be added to a table using

alter table <table name> add (<table constraint>);

(Note that a column constraint is a table constraint, too. not null and primary key constraints can only be added to a table if none of the specified columns contains a null value.)

Example: - Suppose you want to add a new column Contact_Number to the Student table in the previous example.

You can add the column using the following statement: -

alter table Student add(Contact_Number Number(10));

Modifying the Column of the Table: - A column can be modified using the alter table command. Following is the command for modifying the column attributes: -

Syntax: - alter table <table name> modify(<column> [<data type>] [<default value>] [<column constraint>]);

Example: - If you want to change the size of TeacherName in the Teacher table. You can do it with the following statement:-

alter table Teacher modify(TeacherName varchar2(35));

Deleting a Table: - A table and its rows can be deleted by issuing the drop table command. Following is the syntax for deleting a table.

Syntax: - drop table table name [cascade constraints];

Example: - If you want to delete the Marks table. You can delete it using the following statement:-

drop table Marks;

Updating the Table: - For modifying attribute values of (some) tuples in a table, we use the update statement. The following is the syntax for Update:

Syntax: - update <table name> set <column i> = <expression i>, . . . , <column j> = <expression j> [where <condition>];

An expression consists of either a constant (new value), an arithmetic or string operation, or an SQL query. Note that the new value to assign to column i must be the matching data type. An update statement without a where clause results in changing respective attributes of all tuples in the specified table. Typically, however, only a (small) portion of the table requires an update.