Altering the structure of a table

DESCRIBE

To go back and view the structure of a table we can use the DESCRIBE command followed by the name of the table we wish to see. For example:

DESCRIBE student;

ALTER TABLE

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

To add a field column to a table, use the following syntax:

ALTER TABLE table_name
ADD field_name datatype

To delete a field column in a table, use the following syntax:

ALTER TABLE table_name
DROP COLUMN field_name

There are some slight changes to how MySQL, Oracle, SQL Server, and Microsoft Access handle the changing of the data types.To change the data type of a field column in SQL Server and MS Access use the following syntax:

ALTER TABLE table_name
ALTER COLUMN field_name datatype

If you are using MySQL or a version of Oracle prior to 10G use the following syntax:

ALTER TABLE table_name
MODIFY COLUMN field_name datatype

If you are using a newer version of Oracle use the following syntax:

ALTER TABLE table_name
MODIFY field_name datatype

Lesson Activities

  1. Use the DESCRIBE command to view the structure of the student table.
    Click to see code
    DESCRIBE student;
  2. Remove the date_enrolled field from the student table.
    Click to see code
    ALTER TABLE student
    DROP COLUMN date_enrolled;
  3. Change the student table to include a new field called date_enrolled with a data type of TIMESTAMP. It should DEFAULT to the time the record is created.
    Click to see code
    ALTER TABLE student
    ADD date_enrolled TIMESTAMP DEFAULT NOW();
  4. Add the field year using the YEAR data type to the program table.
    Click to see code
    ALTER TABLE program
    ADD year YEAR;
  5. Update the program table so that all fields have a year of 2015.
    Click to see code
    UPDATE program
    SET year ="2015";

Questions