how to add and delete columns in an existing table?

We have a 'users' table and need to add a new 'middle_name' column right after 'first_name'. How would you write the SQL for this, and how do we ensure existing users get a default value of NULL?

Imagine you need to drop a 'phone_number' column from our production database because of a privacy policy change. What checks would you perform on the application code before running that ALTER TABLE statement to prevent runtime errors?

We need to add a non-nullable 'status' column with a default value of 'pending' to a 10-million-row 'orders' table. If we run a standard ALTER TABLE ADD COLUMN, what are the risks regarding table locking and application downtime, and how would you mitigate them?

You try to drop a 'legacy_id' column from a table, but MySQL throws an error. What are the most likely reasons this drop operation is failing, and how would you investigate and resolve it?

We have a 500GB 'transactions' table running on MySQL 8.0 that handles high write volume. We need to drop an unused column and add a new JSON metadata column. Walk me through how you would execute this schema change safely without causing replication lag or blocking production traffic.

MySQL 8.0 introduced ALGORITHM=INSTANT for adding columns. Under what conditions does this fail or fall back to INPLACE or COPY, and how would you design your migration pipeline to guarantee zero-downtime schema changes?

We are migrating a monolithic database to a sharded MySQL architecture with hundreds of physical instances. How would you design a zero-downtime schema migration engine or process that allows product teams to safely add and drop columns across all shards without coordinated deployments?

When deprecating a feature, we often need to drop columns. However, rolling back a deployment might require the old code to run again. How do you design your database schema evolution and deployment pipeline to support safe rollbacks when columns are being dropped?