SQL and MySQL course Β· Module 4: Creating tables (DDL)
ALTER TABLE and DROP TABLE - rebuilding a shelf
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. Which statement will you use to add a new column to the existing ksiazki table?
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):
Tasks after this lesson
- QuestionAdding a new column
- QuestionChanging a column's type
- Coding taskAdd a jezyk column
- QuestionDropping a column
- Coding taskDrop an unnecessary column
- Put in orderAn ALTER TABLE statement with ADD COLUMN
- Coding taskChange the type of the imie column
- QuestionRemoving the whole table
- Coding taskDrop the whole shelf
- Click in orderDropping the recenzje table
- Put in orderA table's lifecycle