SQL ALTER TABLE

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

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;

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

A False

B True

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)

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

A ALTER TABLE products DROP COLUMN stock;

B ALTER TABLE products DELETE stock;

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;

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

Left column		Right column
1ALTER TABLE students ADD email TEXT;		AAdds a new column called email to the existing students table without deleting any rows.
2ALTER TABLE products DROP COLUMN stock;		BALTER TABLE subjects ADD room TEXT;
3ALTER TABLE changes the structure of a table that already exists, such as giving it an extra column.		CCOLUMN is in the wrong place. It should be DROP COLUMN stock, with COLUMN before the column name.
4After a column is added, rows that were already in the table have an empty (NULL) value in that new column.		DRemoves the stock column, and all the data in it, from the products table.
5ALTER students ADD email TEXT;		EThe TABLE keyword is missing. It must be ALTER TABLE students ADD email TEXT;
6ALTER TABLE products DROP stock COLUMN;		FALTER TABLE students ADD age INTEGER; SELECT firstName, age FROM students;

Definitions

ALTER TABLE

Fill in the Blanks

Complete each line of code by filling in the missing parts.

_____ TABLE students ADD email TEXT;

ALTER TABLE students ___ email TEXT;

ALTER TABLE students ___________ house;

ALTER TABLE ________ ADD yearGroup INTEGER;

ALTER TABLE students ADD email TEXT; SELECT * FROM students WHERE email IS ____;

Spot the Error

Shade in the letter of the line that contains an error.

Question 1

A ALTER students

B ADD email TEXT;

C SELECT * FROM students;

Question 2

A ALTER TABLE products

B INSERT COLUMN discount REAL;

C SELECT name, discount

D FROM products;

Question 3

A ALTER TABLE students

B DELETE COLUMN house;

C SELECT * FROM students;

Question 4

A ALTER TABLE products

B ADD COLUMN colour TEXT;

C SELECT name FROM products

D WHERE colour = NULL;

Question 5

A ALTER TABLE students

B ADD COLUMN grade;

C SELECT firstName, grade

D FROM students;

Answer Key

SQL ALTER TABLE

Section A: Quick Questions

A — ALTER TABLE students ADD email TEXT;

B — True

B — NULL (empty)

A — ALTER TABLE products DROP COLUMN stock;

A — ALTER TABLE results ADD grade INTEGER;

Section B: 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.

Section C: Definitions

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.

Section D: Fill in the Blanks

ALTER TABLE students ADD email TEXT;

ALTER TABLE students DROP COLUMN house;

ALTER TABLE students ADD yearGroup INTEGER;

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

Section E: Spot the Error

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.

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;

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;

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;

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