SQL and MySQL course Β· Module 4: Creating tables (DDL)

ALTER TABLE and DROP TABLE - rebuilding a shelf

2 min read Β·
In this lesson5

Shelves are not carved in stone. Over time they must be rebuilt: add a field, change a size, remove an unnecessary one. ALTER TABLE serves this purpose. And when an entire shelf becomes useless - DROP TABLE.

ALTER TABLE - adding a column

ALTER TABLE ksiazki ADD COLUMN jezyk VARCHAR(20);

To the existing shelf ksiazki we added a new field jezyk. There is no need to build the table from scratch!

ALTER TABLE - changing a column's type

ALTER TABLE autorzy MODIFY COLUMN imie VARCHAR(120);

The MODIFY COLUMN clause changes the type of an existing column - here we widen imie to 120 characters.

ALTER TABLE - dropping a column

ALTER TABLE ksiazki DROP COLUMN jezyk;

DROP COLUMN removes the field together with its data. Careful - this cannot be undone!

ALTER TABLE - renaming the table

ALTER TABLE recenzje RENAME TO opinie;

The RENAME TO clause changes the name of the whole shelf without touching its content.

DROP TABLE - removing the whole shelf

DROP TABLE recenzje;

This command permanently removes the entire table together with all the scrolls. Use it with the utmost care - the shelf and everything on it disappear. The Keeper of the Catalog always thinks twice before giving such an order.

Spotted a mistake in this lesson?

Check yourself

Answer the questions from this lesson. Pick an answer to see right away whether it is correct.

  1. 1. Which statement will you use to add a new column to the existing ksiazki table?

  2. 2. Which ALTER TABLE clause will you use to change the type of an existing column, e.g. widen imie to VARCHAR(120)?

These are 2 of 4 questions for this lesson. Solve the rest in the game.

Hands-on tasks in the game

  • Code editor

    Write an ALTER TABLE statement that adds a new column jezyk of type VARCHAR(20) to the ksiazki table.

  • Code editor

    Write an ALTER TABLE statement that removes the jezyk column from the ksiazki table.

  • Horizontal ordering

    Arrange the elements of a column-adding statement on one line, from left to right:

  • Code editor

    Write an ALTER TABLE statement that changes the type of the imie column in the autorzy table to VARCHAR(120) (the MODIFY COLUMN clause).

  • Code editor

    Write a statement that permanently removes the entire recenzje_tymczasowe table.

  • Click in order

    Click the elements in the order in which we build a table-dropping statement (one line):

  • Vertical ordering

    Arrange the DDL statements in the natural order of a table's lifecycle (from creation to removal):

  1. QuestionAdding a new column
  2. QuestionChanging a column's type
  3. Coding taskAdd a jezyk column
  4. QuestionDropping a column
  5. Coding taskDrop an unnecessary column
  6. Put in orderAn ALTER TABLE statement with ADD COLUMN
  7. Coding taskChange the type of the imie column
  8. QuestionRemoving the whole table
  9. Coding taskDrop the whole shelf
  10. Click in orderDropping the recenzje table
  11. Put in orderA table's lifecycle

Useful articles