ALTER TABLE Statement 🔧

Mentor's Note: Requirements change. You built a "Users" table, but now the client wants to store "Phone Numbers". You don't need to delete the table and start over; you just Renovate it! 💡

---

🌟 The Scenario: The Home Renovation 🏠

• Add Column: Building a new balcony.
• Drop Column: Removing an old fireplace.
• Modify Column: Expanding a small window into a large glass door.

---

💻 1. ADD Column (Expansion) ➕

Add a new field to your table.

-- Syntax
ALTER TABLE table_name ADD column_name datatype;

-- Example: Add Email to Students
ALTER TABLE students ADD email VARCHAR(100);

💻 2. DROP Column (Cleanup) ✂️

Remove a field you no longer need. Warning: Deletes all data in that column!

-- Syntax
ALTER TABLE table_name DROP COLUMN column_name;

-- Example: Remove Age (Calculate it from DOB instead)
ALTER TABLE students DROP COLUMN age;

💻 3. MODIFY / ALTER Column (Renovation) 🛠️

Change the data type or size of a column.

Oracle / MySQL

-- Expand name from 50 to 100 chars
ALTER TABLE students MODIFY first_name VARCHAR(100);

SQL Server

ALTER TABLE students ALTER COLUMN first_name VARCHAR(100);

PostgreSQL

ALTER TABLE students ALTER COLUMN first_name TYPE VARCHAR(100);

---

💡 Important Rules

1. Not Null to Null: You can usually easily relax a rule.
2. Null to Not Null: You can only do this if the column currently has NO null values.
3. Reducing Size: You cannot shrink a VARCHAR(100) to VARCHAR(10) if you already have a 50-character name stored.

---

📈 Learning Path

• ← Back: Create Table
• Next: DROP TABLE →