← QCE Digital Solutions Unit 2

SQL ALTER TABLE

An SQL command that changes the structure of an existing table, such as adding a column with ADD, removing one with DROP COLUMN, or adding a FOREIGN KEY, without deleting the table.

Part of: QCE Digital Solutions Unit 2 — Data and Programming Techniques — Module 80

Understanding SQL ALTER TABLE in QCE Digital Solutions Unit 2

What it does: ADD FOREIGN KEY on an existing table names the column and uses REFERENCES to point to the primary key of the other table. ALTER TABLE changes the structure of an existing table. UPDATE changes the data in rows, not the structure, and UPDATE TABLE is not valid SQL. The command is always ALTER TABLE followed by the table name. The ADD clause needs a column name and a data type, such as rating INT. Without them the statement is incomplete. DROP COLUMN permanently removes the column and its data, so check before you run it. Correct syntax is ALTER TABLE students ADD email VARCHAR(50); because SQL needs the word TABLE to know what is being altered.

Common mistakes: ADD needs a data type after the new column name. It should be ADD email VARCHAR(50);. The command is ALTER TABLE, so the TABLE keyword is missing. It should be ALTER TABLE products.

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 186–228) and word search (Modules 144–228).

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 VARCHAR(50);UPDATE TABLE students ADD email VARCHAR(50);

Matches

ALTER students ADD email VARCHAR(50);

↓

The TABLE keyword is missing. The command must start with ALTER TABLE before the table name.

Applying

Meanings

Term

ALTER TABLE

Meaning

An SQL command that changes the structure of an existing table, such as adding a column with ADD, removing one with DROP COLUMN, or adding a FOREIGN KEY, without deleting the table.

Practice

ALTER TABLE students ADD email VARCHAR(50);

Testing

Gaps

TABLE students ADD email VARCHAR(50);

Check Answer

Spots

1ALTER TABLE students

2ADD email;

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 VARCHAR(50); ✓

B. UPDATE TABLE students ADD email VARCHAR(50);

ALTER TABLE changes the structure of an existing table. UPDATE changes the data in rows, not the structure, and UPDATE TABLE is not valid SQL.

2. Which statement removes the house column from the students table?

A. DELETE COLUMN house FROM students;

B. ALTER TABLE students DROP COLUMN house; ✓

Use ALTER TABLE with DROP COLUMN to remove a column. DELETE removes rows, not columns.

3. True or false: ALTER TABLE ... ADD deletes the existing rows in the table.

A. False: the table and its rows stay, and the new column is added ✓

B. True: the table is emptied and rebuilt

ALTER TABLE changes the structure only. Existing rows are kept, and the new column is NULL for them unless a default is set.

4. What value do existing rows have in a newly added column (with no default)?

A. NULL ✓

B. 0

Existing rows have no value for the new column, so it is NULL until you update it.

5. Which command would you use to add a FOREIGN KEY to the existing orders table so studentId links to students?

A. UPDATE orders SET FOREIGN KEY (studentId) REFERENCES students(studentId);

B. ALTER TABLE orders ADD FOREIGN KEY (studentId) REFERENCES students(studentId); ✓

A foreign key is part of the table structure, so it is added with ALTER TABLE ... ADD FOREIGN KEY. UPDATE ... SET changes row values only.

Match the Pairs

ALTER TABLE students ADD email VARCHAR(50); → Adds a new column called email to the existing students table without deleting the table or its rows.

ALTER TABLE students DROP COLUMN house; → Removes the house column, and all the values stored in it, from the students table.

Link an existing orders table to the students table by adding a foreign key on studentId. → ALTER TABLE orders ADD FOREIGN KEY (studentId) REFERENCES students(studentId);

Change the structure of the products table by adding a whole number column called discount. → ALTER TABLE products ADD discount INT;

ALTER students ADD email VARCHAR(50); → The TABLE keyword is missing. The command must start with ALTER TABLE before the table name.

ALTER TABLE students DROP house; → DROP on its own is not enough here. Removing a column needs DROP COLUMN followed by the column name.

Key Term

ALTER TABLE

An SQL command that changes the structure of an existing table, such as adding a column with ADD, removing one with DROP COLUMN, or adding a FOREIGN KEY, without deleting the table.

Fill in the Blanks

1. ALTER TABLE students ADD email VARCHAR(50);

Hint: Type the keyword that starts a command to change an existing table's structure.

2. ALTER TABLE students ADD email VARCHAR(50);

Hint: Type the keyword that puts a new column into the table.

3. ALTER TABLE products DROP COLUMN stock;

Hint: Type the keyword that removes a column from the table.

4. ALTER TABLE orders ADD FOREIGN KEY (productId) REFERENCES products(productId);

Hint: Type the keyword that names the table and key the foreign key points to.

5. ALTER TABLE results ADD FOREIGN KEY (subjectId) REFERENCES subjects(subjectId);

Hint: Type the two-word constraint that links this column to another table's primary key.

Spot the Error

Question 1

A ALTER TABLE students

B ADD email;

C SELECT firstName, email

D FROM students;

Line B — ADD needs a data type after the new column name. It should be ADD email VARCHAR(50);

Question 2

A ALTER products

B ADD discount DECIMAL(4,2);

C SELECT name, discount

D FROM products;

Line A — The command is ALTER TABLE, so the TABLE keyword is missing. It should be ALTER TABLE products.

Question 3

A ALTER TABLE students

B ADD nickname VARCHAR(20);

C ALTER TABLE students

D DELETE COLUMN nickname;

Line D — ALTER TABLE removes a column with DROP COLUMN, not DELETE COLUMN. It should be DROP COLUMN nickname;

Question 4

A UPDATE TABLE students

B ADD FOREIGN KEY (houseId)

C REFERENCES houses(houseId);

Line A — UPDATE changes rows of data. To change a table's structure you use ALTER TABLE students.

Question 5

A ALTER TABLE students

B ADD FOREIGN KEY (houseId)

C REFERENCE houses(houseId);

Line C — The keyword is REFERENCES, with an S. It should be REFERENCES houses(houseId);

Student with a class code? Join your class →