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

B UPDATE TABLE students ADD email VARCHAR(50);

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

A DELETE COLUMN house FROM students;

B ALTER TABLE students DROP COLUMN house;

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

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

A NULL

B 0

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);

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 VARCHAR(50);		ADROP on its own is not enough here. Removing a column needs DROP COLUMN followed by the column name.
2ALTER TABLE students DROP COLUMN house;		BThe TABLE keyword is missing. The command must start with ALTER TABLE before the table name.
3Link an existing orders table to the students table by adding a foreign key on studentId.		CALTER TABLE products ADD discount INT;
4Change the structure of the products table by adding a whole number column called discount.		DRemoves the house column, and all the values stored in it, from the students table.
5ALTER students ADD email VARCHAR(50);		EALTER TABLE orders ADD FOREIGN KEY (studentId) REFERENCES students(studentId);
6ALTER TABLE students DROP house;		FAdds a new column called email to the existing students table without deleting the table or its rows.

Definitions

ALTER TABLE

Fill in the Blanks

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

_____ TABLE students ADD email VARCHAR(50);

ALTER TABLE students ___ email VARCHAR(50);

ALTER TABLE products ____ COLUMN stock;

ALTER TABLE orders ADD FOREIGN KEY (productId) __________ products(productId);

ALTER TABLE results ADD ___________ (subjectId) REFERENCES subjects(subjectId);

Spot the Error

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

Question 1

A ALTER TABLE students

B ADD email;

C SELECT firstName, email

D FROM students;

Question 2

A ALTER products

B ADD discount DECIMAL(4,2);

C SELECT name, discount

D FROM products;

Question 3

A ALTER TABLE students

B ADD nickname VARCHAR(20);

C ALTER TABLE students

D DELETE COLUMN nickname;

Question 4

A UPDATE TABLE students

B ADD FOREIGN KEY (houseId)

C REFERENCES houses(houseId);

Question 5

A ALTER TABLE students

B ADD FOREIGN KEY (houseId)

C REFERENCE houses(houseId);

Answer Key

SQL ALTER TABLE

Section A: Quick Questions

A — ALTER TABLE students ADD email VARCHAR(50);

B — ALTER TABLE students DROP COLUMN house;

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

A — NULL

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

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

Section C: Definitions

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.

Section D: Fill in the Blanks

ALTER TABLE students ADD email VARCHAR(50);

ALTER TABLE products DROP COLUMN stock;

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

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

Section E: Spot the Error

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

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

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

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

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