ALTER TABLE operation performs on exists table, through ALTER TABLE command we can ADD COLUMN, UPDATE COLUMN, and DROP COLUMN.
ALTER TABLE is also used to add INDEX, drop INDEX, ADD CONSTRAINT and DROP CONSTRAINT like (ADD UNIQUE/DROP UNIQUE/ADD FOREIGN KEY/DROP FOREIGN KEY), etc.
Add COLUMN INTO TABLE
ALTER TABLE tableName ADD columnName datatype
If you want to add multiple columns into the table
ALTER TABLE tableName ADD (columnName1 datatype, columnName2 datatype, columnName3 datatype……columnNameN datatype)
Suppose We have an employees table
+----+------------+-----------+----------------+------------+
| id | first_name | last_name | email | address |
+----+------------+-----------+----------------+------------+
| 1 | John | Tailor | john@abc.com | California |
| 2 | Rom | Tailor | rom@abc.com | California |
| 3 | Andrew | Symonds | andrew@abc.com | Sydney |
| 4 | Miacle | clerk | miacle@abc.com | sydney |
| 5 | Sachin | Tendulkar | sachin@abc.com | Mumbai |
+----+------------+-----------+----------------+------------+
Now Add column mobile_number into employees table
ALTER TABLE employees ADD mobile_number varchar(10)
Now, check employees table
+----+------------+-----------+----------------+------------+---------------+
| id | first_name | last_name | email | address | mobile_number |
+----+------------+-----------+----------------+------------+---------------+
| 1 | John | Tailor | john@abc.com | California | NULL |
| 2 | Rom | Tailor | rom@abc.com | California | NULL |
| 3 | Andrew | Symonds | andrew@abc.com | Sydney | NULL |
| 4 | Miacle | clerk | miacle@abc.com | sydney | NULL |
| 5 | Sachin | Tendulkar | sachin@abc.com | Mumbai | NULL |
+----+------------+-----------+----------------+------------+---------------+
DROP COLUMN FROM TABLE
ALTER TABLE tableName DROP COLUMN columnName
Example:-
Now, we are going to drop mobile_number column from the employees table
ALTER TABLE employees DROP COLUMN mobile_number
Now, check employees table
+----+------------+-----------+----------------+------------+
| id | first_name | last_name | email | address |
+----+------------+-----------+----------------+------------+
| 1 | John | Tailor | john@abc.com | California |
| 2 | Rom | Tailor | rom@abc.com | California |
| 3 | Andrew | Symonds | andrew@abc.com | Sydney |
| 4 | Miacle | clerk | miacle@abc.com | sydney |
| 5 | Sachin | Tendulkar | sachin@abc.com | Mumbai |
+----+------------+-----------+----------------+------------+
SQL Alter Table – Interview Questions
Q 1: What is ALTER TABLE used for?
Ans: It modifies the structure of an existing table.
Q 2: Can we add columns using ALTER TABLE?
Ans: Yes, new columns can be added.
Q 3: Can we remove columns?
Ans: Yes, using the DROP clause.
Q 4: Does ALTER TABLE affect data?
Ans: Structure changes may affect data but do not delete rows.
Q 5: Can constraints be added later?
Ans: Yes, constraints can be added or removed.
SQL Alter Table – Objective Questions (MCQs)
Q1. Which SQL command is used to modify an existing table?
Q2. Which operation can be performed using ALTER TABLE?
Q3. Which keyword is used to add a new column?
Q4. ALTER TABLE is categorized under:
Q5. Which command removes a column from a table?