ALTER and DROP TABLE
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — Adding a column
Tables change. ALTER TABLE … ADD COLUMN adds a column to an existing table; the rows already there get NULL in it:
ALTER TABLE trainers ADD COLUMN email TEXT;
UPDATE trainers SET email = 'alex@league.org' WHERE name = 'Alex';
SELECT name, email FROM trainers;With a DEFAULT, the existing rows get that value instead of NULL:
ALTER TABLE trainers ADD COLUMN is_active INTEGER NOT NULL DEFAULT 1;
SELECT name, is_active FROM trainers;NOT NULL needs a DEFAULT here
You cannot add a NOT NULL column without a DEFAULT to a table that already has rows: the database would have nothing to put in them.
Exercise 2 — Renaming
ALTER TABLE also renames: a column with RENAME COLUMN … TO …, the whole table with RENAME TO:
ALTER TABLE trainers RENAME COLUMN hometown TO city;
ALTER TABLE trainers RENAME TO league_trainers;
SELECT name, city FROM league_trainers;The data does not move; only the names change. Every query that used the old names must be updated — which is why renaming is rare on a live database.
Look at a table's definition
SELECT name, type FROM pragma_table_info('league_trainers'); lists the columns of a table with their types — handy after an ALTER.
Exercise 3 — The new column
After ALTER TABLE trainers ADD COLUMN email TEXT;, what do the existing trainers contain in email?
Hint : New column, nothing known yet.
Exercise 4 — A nickname column
Complete the script so that it adds a nickname column to codemon, fills it for Aquachu and shows Alex's Codémons with their nickname.
Hint : Add the column, then select it.
Exercise 5 — Removing: DROP COLUMN, DROP TABLE
ALTER TABLE … DROP COLUMN removes a column and everything it contained:
ALTER TABLE codemon DROP COLUMN capture_date;
SELECT * FROM codemon LIMIT 3;DROP TABLE removes a whole table — rows, columns, constraints, everything (unlike DELETE FROM, which empties the table but keeps it). Watch codemon vanish from the catalog:
DROP TABLE codemon;
SELECT name FROM sqlite_master
WHERE type = 'table' AND name NOT LIKE 'sqlite_%'
ORDER BY name;No undo
DROP TABLE and DROP COLUMN are immediate and permanent. Before running one, make sure a backup exists. DROP TABLE IF EXISTS gyms avoids an error when the table is already gone.
Exercise 6 — What does it do?
Match each statement with its effect on the gyms table.
ALTER TABLE gyms ADD COLUMN founded INTEGERALTER TABLE gyms RENAME TO arenasDELETE FROM gymsDROP TABLE gymsExercise 7 — Empty or gone?
After DROP TABLE gyms;, what does SELECT COUNT(*) FROM gyms; return?
Hint : DROP removes the structure, not just the rows.
Exercise 8 — Reshaping trainers
Professor Pi reorganizes the trainers table.
Add a region text column, set it to 'North' for the Azure City trainers and 'South' for the others, rename the badges column to badge_count, then show name, region and badge_count, sorted by name.
-- Write your statements below, and finish with a SELECT to check the result