SQL ALTER TABLE

Tip: Choose Save as PDF in the print dialog to download. Use A4 paper size.

SQL ALTER TABLE

Name:
Date:
Class:
A

Quick Questions

Shade in the A or B circle for each question.

1. Which statement adds a new column called email to an existing students table?
A ALTER TABLE students ADD email VARCHAR(50);
B CREATE TABLE students ADD email VARCHAR(50);
2. True or false: ALTER TABLE can change the structure of a table without deleting the table.
A True
B False
3. A table has 10 rows. You run ALTER TABLE students ADD house VARCHAR(10); What value does the new house column hold for the 10 existing rows?
A The word 'house' in every row
B NULL (empty) in every row
4. Which statement removes the column nickname from the students table?
A ALTER TABLE students DROP COLUMN nickname;
B DROP TABLE students COLUMN nickname;
5. After ALTER TABLE students DROP COLUMN nickname; what happens to the data stored in that column?
A It is kept and moved to a new column
B It is deleted along with the column
B

Match the Pairs

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

Left columnRight column
1ALTER TABLE students ADD email VARCHAR(50); AAdds a new column called email to the students table. Existing rows are kept and get NULL in the new column.
2ALTER TABLE students DROP COLUMN house; BThe new column has no name and no data type. ADD must be followed by a column name and its data type.
3Change the data type or size of an existing column, such as making the name column hold up to 100 characters CRemoves the house column from the students table, along with all the data stored in that column.
4Add a new column to an existing table so that rows already stored have NULL in it until they are updated DALTER TABLE products MODIFY name VARCHAR(100);
5ALTER students ADD email VARCHAR(50); EALTER TABLE orders ADD status VARCHAR(20);
6ALTER TABLE students ADD COLUMN; FThe TABLE keyword is missing. The command must be ALTER TABLE followed by the table name.
C

Definitions

ALTER TABLE
D

Fill in the Blanks

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

1.
_____ TABLE students ADD email VARCHAR(50);
2.
ALTER TABLE students ___ email VARCHAR(50);
3.
ALTER TABLE students ____ COLUMN house;
4.
ALTER TABLE products ______ price DECIMAL(6,2);
5.
ALTER TABLE students ADD email VARCHAR(50); SELECT * FROM students WHERE email IS ____;
E

Spot the Error

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

Question 1
A ALTER TABLE Student
B ADD COLUMN VARCHAR(50) Email;
C SELECT * FROM Student;
Question 2
A SELECT * FROM Student;
B CHANGE TABLE Student
C DROP COLUMN Phone;
Question 3
A ALTER Book TABLE
B MODIFY Title VARCHAR(100);
C SELECT * FROM Book;
Question 4
A ALTER TABLE Student
B ADD YearLevel INT;
C UPDATE Student
D SET YearLevel == 7;
Question 5
A ALTER TABLE Product;
B DROP COLUMN Weight;
C SELECT * FROM Product;

Answer Key

SQL ALTER TABLE
Section A: Quick Questions
Q1
A — ALTER TABLE students ADD email VARCHAR(50);
Q2
A — True
Q3
B — NULL (empty) in every row
Q4
A — ALTER TABLE students DROP COLUMN nickname;
Q5
B — It is deleted along with the column
Section B: Match the Pairs
Q1
ALTER TABLE students ADD email VARCHAR(50); → Adds a new column called email to the students table. Existing rows are kept and get NULL in the new column.
Q2
ALTER TABLE students DROP COLUMN house; → Removes the house column from the students table, along with all the data stored in that column.
Q3
Change the data type or size of an existing column, such as making the name column hold up to 100 characters → ALTER TABLE products MODIFY name VARCHAR(100);
Q4
Add a new column to an existing table so that rows already stored have NULL in it until they are updated → ALTER TABLE orders ADD status VARCHAR(20);
Q5
ALTER students ADD email VARCHAR(50); → The TABLE keyword is missing. The command must be ALTER TABLE followed by the table name.
Q6
ALTER TABLE students ADD COLUMN; → The new column has no name and no data type. ADD must be followed by a column name and its data type.
Section C: Definitions
Q1
ALTER TABLE — An SQL command that changes the structure of an existing table, such as adding, modifying or dropping a column. Existing rows are kept, and a new column is empty (NULL) for them.
Section D: Fill in the Blanks
Q1
ALTER TABLE students ADD email VARCHAR(50);
Q2
ALTER TABLE students ADD email VARCHAR(50);
Q3
ALTER TABLE students DROP COLUMN house;
Q4
ALTER TABLE products MODIFY price DECIMAL(6,2);
Q5
ALTER TABLE students ADD email VARCHAR(50); SELECT * FROM students WHERE email IS NULL;
Section E: Spot the Error
Q1
B — The column name must come before its data type. It should be ADD COLUMN Email VARCHAR(50);
Q2
B — SQL has no CHANGE TABLE command. To change the structure of an existing table you write ALTER TABLE Student.
Q3
A — The keywords are in the wrong order. The command is ALTER TABLE followed by the table name: ALTER TABLE Book.
Q4
D — SQL uses a single = to assign a value in SET. The new column is NULL for existing rows until UPDATE fills it, so it should be SET YearLevel = 7;
Q5
A — The semicolon ends the statement too early, leaving DROP COLUMN on its own. The first line should be ALTER TABLE Product with no semicolon.