← QCE Digital Solutions Unit 4

SQL ALTER TABLE

Changes the structure of an existing table, such as adding a column with ADD or removing one with DROP COLUMN. Existing rows keep their data and get empty (NULL) values in a new column.

Part of: QCE Digital Solutions Unit 4 — Prototype Digital Data Exchanges — Module 62

Understanding SQL ALTER TABLE in QCE Digital Solutions Unit 4

What it does: This statement changes the existing subjects table by adding a room column, rather than creating a new table. The SELECT shows age as NULL for every existing student, because ADD does not fill in values for old rows. Use DROP COLUMN to remove a column. DELETE is used to remove rows, not columns. The order is ALTER TABLE, then the table name, then ADD, then the new column name and its data type. Dropping a column removes the whole column, including every value stored in it. Check you no longer need it first.

Common mistakes: The statement must start with ALTER TABLE followed by the table name. Without the TABLE keyword the database cannot tell what is being altered. It should be ALTER TABLE students. INSERT is used to add rows, not columns. To add a new column to an existing table you use ADD (or ADD COLUMN), so the line should be ADD COLUMN discount REAL;.

Class: _____________________ · Name: _____________________ · Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add SQL ALTER TABLE to a class →

This module's key terms are also in a printable crossword (Modules 334–401) and word search (Modules 127–401).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning

Duo

Which statement adds a new column called email to the students table?

ALTER TABLE students ADD email TEXT;UPDATE TABLE students ADD email TEXT;

Matches

ALTER students ADD email TEXT;

↓

The TABLE keyword is missing. It must be ALTER TABLE students ADD email TEXT;

Applying

Meanings

Term

ALTER TABLE

Meaning

Changes the structure of an existing table, such as adding a column with ADD or removing one with DROP COLUMN. Existing rows keep their data and get empty (NULL) values in a new column.

Practice

ALTER TABLE students

Testing

Gaps

TABLE students ADD email TEXT;

Check Answer

Spots

1ALTER students

2ADD email TEXT;

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy

Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games

Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print

Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

1. Which statement adds a new column called email to the students table?

A. ALTER TABLE students ADD email TEXT; ✓

B. UPDATE TABLE students ADD email TEXT;

ALTER TABLE changes the structure of an existing table. UPDATE changes the data in rows, not the table's structure.

2. True or false: ALTER TABLE can change the structure of a table that already exists.

A. False

B. True ✓

ALTER TABLE is used on an existing table, for example to add or remove a column. CREATE TABLE is what makes a brand new table.

3. A table has 10 rows. You run ALTER TABLE students ADD house2 TEXT; What value do the 10 existing rows get in the new column?

A. The old row's first column value

B. NULL (empty) ✓

Existing rows keep their data. The new column is filled with NULL (empty) until you use UPDATE to give it values.

4. Which statement removes the column stock from the products table?

A. ALTER TABLE products DROP COLUMN stock; ✓

B. ALTER TABLE products DELETE stock;

Use DROP COLUMN to remove a column. DELETE is used to remove rows, not columns.

5. Which is the correct syntax for adding a column called grade that stores whole numbers?

A. ALTER TABLE results ADD grade INTEGER; ✓

B. ALTER results TABLE ADD grade INTEGER;

The order is ALTER TABLE, then the table name, then ADD, then the new column name and its data type.

Match the Pairs

ALTER TABLE students ADD email TEXT; → Adds a new column called email to the existing students table without deleting any rows.

ALTER TABLE products DROP COLUMN stock; → Removes the stock column, and all the data in it, from the products table.

ALTER TABLE changes the structure of a table that already exists, such as giving it an extra column. → ALTER TABLE subjects ADD room TEXT;

After a column is added, rows that were already in the table have an empty (NULL) value in that new column. → ALTER TABLE students ADD age INTEGER; SELECT firstName, age FROM students;

ALTER students ADD email TEXT; → The TABLE keyword is missing. It must be ALTER TABLE students ADD email TEXT;

ALTER TABLE products DROP stock COLUMN; → COLUMN is in the wrong place. It should be DROP COLUMN stock, with COLUMN before the column name.

Key Term

ALTER TABLE

Changes the structure of an existing table, such as adding a column with ADD or removing one with DROP COLUMN. Existing rows keep their data and get empty (NULL) values in a new column.

Fill in the Blanks

1. ALTER TABLE students ADD email TEXT;

Hint: Type the keyword that starts a statement to change the structure of an existing table.

2. ALTER TABLE students ADD email TEXT;

Hint: Type the keyword that adds a new column to the table.

3. ALTER TABLE students DROP COLUMN house;

Hint: Type the two-word clause that removes a column from the table.

4. ALTER TABLE students ADD yearGroup INTEGER;

Hint: Type the name of the existing table that holds the student records being changed.

5. ALTER TABLE students ADD email TEXT; SELECT * FROM students WHERE email IS NULL;

Hint: Type the empty-value marker that existing rows get in a newly added column.

Spot the Error

Question 1

A ALTER students

B ADD email TEXT;

C SELECT * FROM students;

Line A — The statement must start with ALTER TABLE followed by the table name. Without the TABLE keyword the database cannot tell what is being altered. It should be ALTER TABLE students.

Question 2

A ALTER TABLE products

B INSERT COLUMN discount REAL;

C SELECT name, discount

D FROM products;

Line B — INSERT is used to add rows, not columns. To add a new column to an existing table you use ADD (or ADD COLUMN), so the line should be ADD COLUMN discount REAL;

Question 3

A ALTER TABLE students

B DELETE COLUMN house;

C SELECT * FROM students;

Line B — DELETE removes rows, not columns. To remove a column from a table's structure you use DROP COLUMN, so the line should be DROP COLUMN house;

Question 4

A ALTER TABLE products

B ADD COLUMN colour TEXT;

C SELECT name FROM products

D WHERE colour = NULL;

Line D — Existing rows get NULL in a new column, and NULL can't be compared with =. You must test for it with IS NULL, so the line should be WHERE colour IS NULL;

Question 5

A ALTER TABLE students

B ADD COLUMN grade;

C SELECT firstName, grade

D FROM students;

Line B — A new column needs a data type so the database knows what it can store. The line should be ADD COLUMN grade INTEGER;

Student with a class code? Join your class →