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.