SQL ALTER TABLE Statements


 

The ALTER TABLE statement is use to add,delete or modify columns in an existing table.

The general syntax for SQL ALTER TABLE Commands:

 

To add new column in a Table, Use the following syntax:

ALTER TABLE table_name ADD column_name datatype

 

 

To delete a column in a Table, Use the following syntax:

ALTER TABLE table_name DROP COLUMN column_name

 

 

To change the datatype of column in a table ,use the following table

 

Oracle 10g ansd Oracle 11g:

ALTER TABLE table_name MODIFY column_name datatype

 

 

SQL Server/MS Access:

ALTER TABLE table_name ALTER COLUMN column_name datatype

 

 

MySQL /Oracle:

ALTER TABLE table_name MODIFY COLUMN column_name datatype

 

 

 

SQL ALTER TABLE Examples

Look at the table “STUD” table

image1

Change the Column name:

The Example table contains a column “SEM”, and we change the column name to “SEMESTER”.

ALTER TABLE STUD RENAME COLUMN SEM TO SEMESTER;

 

 

Image2

Add new Column:

The Example table “STUD” add a new column “DOB”(Date Of Birth) which have type DATE.

ALTER TABLE STUD ADD DOB DATE;

 

Image3

 

 

 

 

 

 

Change Datatype of a Column:

The Example table “STUD” add a new column “DOB”(Date Of Birth) which have type DATE.

ALTER TABLE STUD MODIFY FNAME VARCHAR2(20);

 

 

Here change the Datatype of the Column “FNAME” from Type CHAR2(15) to VARCHAR2(20)

 

Change Name of the Table:

The Example table “STUD” changed to “STUDENTS”

ALTER TABLE STUD RENAME TO STUDENTS;

 

 

Here change the TABLE NAME from “STUD” to “STUDENTS”

Remove a Column:

Remove the column “DOB” from the table “STUDENTS”.

ALTER TABLE STUDENTS DROP COLUMN DOB;

 

 

IMAGE4

Advertisements

One thought on “SQL ALTER TABLE Statements

  1. Any business today aims to be on the first page of major search engines for higher visibility and higher sales. If you have a great product that somehow never seems to get the attention that it deserves, it may be that by creating a landing page and a campaign to drive traffic to that page is all you require, rather than a full website build and optimisation of every page on the website. There are people who successfully create SEO plans complete with effective strategy, and they are able to get the results they want without hiring an SEO company.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s