sql alter

SQL Advance

homeBack

Next

SQL ALTER

  • The ALTER TABLE Statement

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

    SQL ALTER TABLE Syntax

    To add a 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 (notice that some database systems don’t allow deleting a column):

    ALTER TABLE table_name
    DROP COLUMN column_name

    To change the data type of a column in a table, use the following syntax:

    My SQL / SQL Server / MS Access:

    ALTER TABLE table_name
    ALTER COLUMN column_name datatype

    Oracle:

    ALTER TABLE table_name
    MODIFY column_name datatype

  • SQL ALTER TABLE Example

    The “emp” table:

    S.no Name1 Name2 City
    2458 Peter Jhon Hyderabad
    4857 Faran Khan New York
    5874 Raj Kiran London
    8578 Sameer Ali Mumbai

    Now we want to add a column named “DOB” in the “emp” table.

    We use the following SQL statement:

    ALTER TABLE emp
    ADD DOB date

    Notice that the new column, “DOB”, is of type date and is going to hold a date. The data type specifies what type of data the column can hold. For a complete reference of all the data types available in MS Access, MySQL, and SQL Server,

    The “emp” table look like:

    S.no Name1 Name2 City DOB
    2458 Peter Jhon Hyderabad
    4857 Faran Khan New York
    5874 Raj Kiran London
    8578 Sameer Ali Mumbai

  • Change Data Type Example

    Now we want to change the data type of the column named “DOB” in the “emp” table.

    We use the following SQL statement:

    ALTER TABLE emp
    ALTER COLUMN DOB year

    Notice that the “DOB” column is now of type year and is going to hold a year in a two-digit or four-digit format.


  • DROP COLUMN Example

    Next, we want to delete the column named “DOB” in the “emp” table.

    We use the following SQL statement:

    ALTER TABLE emp
    DROP COLUMN DOB

    The “emp” table:

    S.no Name1 Name2 City
    2458 Peter Jhon Hyderabad
    4857 Faran Khan New York
    5874 Raj Kiran London
    8578 Sameer Ali Mumbai

Advertisements

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