In my previous article we have already seen how to create a simple table (Customer). Now we will see how to make different changes to the table, and alter it structure. We shall also see how to drop the table from the database permanently.
We shall use the same Customer table, and make alternation in its structure:
Customer Table
Alter a Table
As a DBA, we always come across tables which need some modifications. Some time table created by us also needs to be altered because of change in the specifications.
To change the structure of the table, ALTER command is used. We can make following changes using ALTER statement:
- Add a Column
To add a new ‘Sex’ column to the Customer table, execute the following statement:
ALTER TABLE Customer ADD Sex VARCHAR(10);
The Customer table will look like this:
Customer Table
- Alter Datatype of a Column
To alter the ‘Phone’ columns Datatype from VARCHAR to INT, execute the following statement:
ALTER TABLE Customer ALTER COLUMN Phone INT;
The Customer table will look like this:
Customer Table
- Drop a column
To delete the ‘Sex’ column from the Customer table, execute the following statement:
ALTER TABLE Customer DROP COLUMN Sex;
The Customer table will look like this:
Customer Table
Drop the Table
Many times we across a situation when have need to delete the table from the database permanently. We shall delete the Customer table by executing the following statement:
DROP TABLE Customer;
The Customer table will be deleted from the database.
Bhavesh Patel's Blog 