ALTER and DROP TABLE

The full text of this chapter, to read or revisit. To practise, do the exercises.

▶ Do the exercises

Exercise 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?

Options
• NULL: no default was given
• An empty text
• Their name followed by @league.org
• The rows are deleted and must be inserted again

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.

Items to match
• ALTER TABLE gyms ADD COLUMN founded INTEGER
• ALTER TABLE gyms RENAME TO arenas
• DELETE FROM gyms
• DROP TABLE gyms
Definitions
• Adds a column; existing rows get NULL in it
• Renames the table, data untouched
• Removes every row, keeps the table
• Removes the table itself

Exercise 7 — Empty or gone?

After DROP TABLE gyms;, what does SELECT COUNT(*) FROM gyms; return?

Options
• An error: no such table
• 0
• NULL
• The number of rows the table had

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.

Starter code
-- Write your statements below, and finish with a SELECT to check the result
Back to the course