SQL: ALTER command

alter command is used for altering the table structure, such as,

• to add a column to existing table
• to rename any existing column
• to change datatype of any column or to modify its size.
• to drop a column from the table.

ALTER Command: Add a new Column

Using ALTER command we can add a column to any existing table. Following is the syntax,

ALTER TABLE table_name ADD(
    column_name datatype);

Here is an Example for this,

ALTER TABLE student ADD(
    address VARCHAR(200)
);

The above command will add a new column address to the table student, which will hold data of type varchar which is nothing but string, of length 200.

ALTER Command: Add multiple new Columns

Using ALTER command we can even add multiple new columns to any existing table. Following is the syntax,

ALTER TABLE table_name ADD(
    column_name1 datatype1, 
    column-name2 datatype2, 
    column-name3 datatype3);

Here is an Example for this,

ALTER TABLE student ADD(
    father_name VARCHAR(60), 
    mother_name VARCHAR(60), 
    dob DATE); 

The above command will add three new columns to the student table

ALTER Command: Add Column with default value

ALTER command can add a new column to an existing table with a default value too. The default value is used when no value is inserted in the column. Following is the syntax,

ALTER TABLE table_name ADD(
    column-name1 datatype1 DEFAULT some_value
);

Here is an Example for this,

ALTER TABLE student ADD(
    dob DATE DEFAULT '01-Jan-99'
);

The above command will add a new column with a preset default value to the table student.

ALTER Command: Modify an existing Column

ALTER command can also be used to modify data type of any existing column. Following is the syntax,

ALTER TABLE table_name modify(
    column_name datatype
);

Here is an Example for this,

ALTER TABLE student MODIFY(
    address varchar(300)); 

Remember we added a new column address in the beginning? The above command will modify the address column of the student table, to now hold upto 300 characters.

ALTER Command: Rename a Column

Using ALTER command you can rename an existing column. Following is the syntax,

ALTER TABLE table_name RENAME 
    old_column_name TO new_column_name;

Here is an example for this,

ALTER TABLE student RENAME 
    address TO location; 

The above command will rename address column to location.

ALTER Command: Drop a Column

ALTER command can also be used to drop or remove columns. Following is the syntax,

ALTER TABLE table_name DROP(
    column_name);

Here is an example for this,

ALTER TABLE student DROP(
    address); 

The above command will drop the address column from the table student.