ALTER TABLE operations in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Adding a new column with default value

Consider a table employees with columns id (int) and name (varchar). You run this command:

ALTER TABLE employees ADD COLUMN status VARCHAR(10) DEFAULT 'active';

What will be the value of status for existing rows after this command?

A NULL for all existing rows

B 'active' for all existing rows

C Empty string ('') for all existing rows

D The command will fail because existing rows have no value

💡 Hint

Think about what happens when you add a column with a default value to existing rows.

When you add a new column with a default value, MySQL sets that default value for all existing rows automatically.

📝 Syntax

intermediate

Correct syntax to rename a column

You want to rename the column birthdate to dob in a MySQL table users. Which ALTER TABLE command is correct?

A ALTER TABLE users CHANGE birthdate dob DATE;

B ALTER TABLE users RENAME COLUMN birthdate TO dob;

C ALTER TABLE users MODIFY COLUMN birthdate dob DATE;

D ALTER TABLE users ALTER COLUMN birthdate RENAME TO dob;

💡 Hint

MySQL uses CHANGE to rename a column and requires the new column type.

In MySQL, CHANGE is used to rename a column and you must specify the new column name and its data type.

❓ optimization

advanced

Improving performance when adding a column

You want to add a new nullable column last_login of type DATETIME to a large table accounts without locking the table for a long time. Which approach is best?

A ALTER TABLE accounts ADD COLUMN last_login DATETIME NULL;

B Drop indexes before adding the column, then recreate indexes.

C Add the column with NOT NULL and a default value to avoid NULLs.

D Create a new table with the extra column, copy data, then rename tables.

💡 Hint

Think about minimizing table locks and downtime for large tables.

For large tables, creating a new table with the new structure and copying data in chunks reduces locking time compared to ALTER TABLE which locks the table.

🔧 Debug

advanced

Error when dropping a column

You run this command:

ALTER TABLE orders DROP COLUMN order_date;

But you get an error: ERROR 1091 (42000): Can't DROP 'order_date'; check that column/key exists. What is the most likely cause?

A The column <code>order_date</code> does not exist in the table.

B You cannot drop columns in MySQL using ALTER TABLE.

C The column <code>order_date</code> is part of a foreign key constraint.

D You need to use DROP COLUMN IF EXISTS syntax.

💡 Hint

Check if the column name is correct and exists in the table.

The error means MySQL cannot find the column to drop. The most common cause is a typo or the column does not exist.

🧠 Conceptual

expert

Impact of changing column datatype with ALTER TABLE

You have a column price defined as INT in a table products. You run:

ALTER TABLE products MODIFY price DECIMAL(10,2);

What happens to the existing data in price after this command?

A The data remains as integers and the datatype change is ignored.

B The command fails because you cannot change from INT to DECIMAL.

C All integer values are converted to decimal with two decimal places (e.g., 10 becomes 10.00).

D All values are truncated to zero because of incompatible types.

💡 Hint

Think about how MySQL converts data when changing column types.

MySQL converts existing integer values to decimal format preserving the numeric value, adding decimal places as specified.

Practice

1. What does the ALTER TABLE statement do in MySQL?

easy

A. It changes the structure of an existing table without deleting data.

B. It deletes all data from a table permanently.

C. It creates a new table with specified columns.

D. It backs up the database automatically.

Solution

1. Step 1: Understand the purpose of ALTER TABLE

   The ALTER TABLE command is used to change the structure of an existing table, such as adding or removing columns, without deleting the data inside.

2. Step 2: Compare with other options

   Options A, B, and D describe different commands: creating tables, deleting data, or backing up, which are not the purpose of ALTER TABLE.

3. Final Answer:

   It changes the structure of an existing table without deleting data. -> Option A

4. Quick Check:

   ALTER TABLE modifies table structure [OK]

Hint: ALTER TABLE changes structure, not data content [OK]

Common Mistakes:

• Confusing ALTER TABLE with DELETE or DROP commands
• Thinking ALTER TABLE creates new tables
• Assuming ALTER TABLE backs up data

2. Which of the following is the correct syntax to add a new column named age of type INT to a table called users?

easy

A. ALTER TABLE users ADD age;

B. ALTER users ADD age INT;

C. ALTER TABLE users ADD COLUMN age INT;

D. ALTER TABLE ADD COLUMN age INT TO users;

Solution

1. Step 1: Recall correct ALTER TABLE syntax for adding columns

   The correct syntax is: ALTER TABLE table_name ADD COLUMN column_name datatype; The keyword COLUMN is optional but recommended.

2. Step 2: Check each option

   ALTER TABLE users ADD COLUMN age INT; matches the correct syntax. ALTER users ADD age INT; misses TABLE keyword. ALTER TABLE users ADD age; misses datatype. ALTER TABLE ADD COLUMN age INT TO users; has wrong order.

3. Final Answer:

   ALTER TABLE users ADD COLUMN age INT; -> Option C

4. Quick Check:

   ADD COLUMN syntax = ALTER TABLE users ADD COLUMN age INT; [OK]

Hint: Use ALTER TABLE table ADD COLUMN col datatype [OK]

Common Mistakes:

• Omitting TABLE keyword
• Forgetting datatype for new column
• Incorrect order of keywords

3. Given the table employees with columns id INT, name VARCHAR(50), what will be the result of this query?

ALTER TABLE employees DROP COLUMN name;

medium

A. The table employees will be deleted.

B. The data in the name column will be set to NULL but column remains.

C. A syntax error will occur because DROP COLUMN is invalid.

D. The column name will be removed from the table.

Solution

1. Step 1: Understand DROP COLUMN in ALTER TABLE

   Using DROP COLUMN removes the specified column and all its data from the table structure permanently.

2. Step 2: Analyze the query effect

   The query removes the 'name' column from 'employees'. It does not delete the table or cause syntax errors.

3. Final Answer:

   The column name will be removed from the table. -> Option D

4. Quick Check:

   DROP COLUMN removes column and data [OK]

Hint: DROP COLUMN deletes column, not table [OK]

Common Mistakes:

• Thinking DROP COLUMN deletes the whole table
• Assuming DROP COLUMN sets data to NULL only
• Believing DROP COLUMN causes syntax error

4. You run this command:

ALTER TABLE orders MODIFY COLUMN order_date VARCHAR(20);

But get an error. What is the likely cause?

medium

A. The MODIFY COLUMN syntax is incorrect in MySQL.

B. The column order_date does not exist in the table.

C. VARCHAR(20) is not a valid data type.

D. You cannot change a column's data type with ALTER TABLE.

Solution

1. Step 1: Check MODIFY COLUMN syntax validity

   MySQL supports MODIFY COLUMN to change a column's data type, so syntax is correct.

2. Step 2: Identify possible causes of error

   If the column does not exist, MySQL throws an error. VARCHAR(20) is valid, so the likely cause is the column missing.

3. Final Answer:

   The column order_date does not exist in the table. -> Option B

4. Quick Check:

   Nonexistent column causes MODIFY error [OK]

Hint: Check column exists before MODIFY COLUMN [OK]

Common Mistakes:

• Assuming MODIFY COLUMN syntax is wrong
• Thinking data type change is not allowed
• Ignoring column existence

5. You want to rename the column username to user_name in the accounts table and change its type to VARCHAR(100). Which command is correct?

hard

A. ALTER TABLE accounts CHANGE username user_name VARCHAR(100);

B. ALTER TABLE accounts RENAME COLUMN username TO user_name VARCHAR(100);

C. ALTER TABLE accounts MODIFY username TO user_name VARCHAR(100);

D. ALTER TABLE accounts RENAME username user_name VARCHAR(100);

Solution

1. Step 1: Understand renaming columns in MySQL

   MySQL uses CHANGE to rename a column and optionally change its type: CHANGE old_name new_name datatype.

2. Step 2: Evaluate each option

   ALTER TABLE accounts CHANGE username user_name VARCHAR(100); uses correct CHANGE syntax. ALTER TABLE accounts RENAME COLUMN username TO user_name VARCHAR(100); uses invalid RENAME COLUMN syntax for MySQL. Options C and D use incorrect keywords.

3. Final Answer:

   ALTER TABLE accounts CHANGE username user_name VARCHAR(100); -> Option A

4. Quick Check:

   CHANGE renames and modifies column [OK]

Hint: Use CHANGE old_col new_col datatype to rename and modify [OK]

Common Mistakes:

• Using RENAME COLUMN which is not supported in MySQL
• Confusing MODIFY with renaming
• Omitting datatype when using CHANGE