SQL from Zero › Module 2: DDL: building the structure
Changing and removing tables
Add or rename columns with ALTER TABLE, and remove tables with DROP TABLE.
Lesson 12 of 33 · about 12 minutes
Video: Renovating the shopThe narrated video for this lesson is coming soon. The lesson below covers the same ideas.
The lesson
Shops get renovated. A new shelf goes up, a label gets changed, an old display is taken down. Tables change too. Your practice database has authors, customers, books and an old old_promotions table.
Add a column to an existing table:
ALTER TABLE customers ADD COLUMN phone TEXT;
Existing rows get an empty value (NULL) in the new column, or the DEFAULT if you give one.
Rename a column or rename a table:
ALTER TABLE books RENAME COLUMN stock TO copies_in_stock;
ALTER TABLE customers RENAME TO shoppers;
Remove a table completely with DROP TABLE:
DROP TABLE old_promotions;
⚠️ DROP TABLE deletes the table and every row in it, permanently. There's no undo button in most databases. At work, people back up first and double-check the table name. Here in practice, the Reset button brings everything back.
Key ideas
- ALTER TABLE ... ADD COLUMN adds a column to an existing table.
- ALTER TABLE ... RENAME COLUMN ... TO ... renames a column; RENAME TO renames the table.
- DROP TABLE removes a table and all its data permanently, so use it with care.
Practise
Loading practice…