What is the ALTER Statement in SQL ?
Answer : The SQL ALTER statement is used to add, delete, or modify columns in an existing table. The ALTER TABLE statement is also used to add and drop various constraints on an existing table.
Using ALTER, we can:
- Add a new column
- Modify a column
- Rename a column
- Drop a column
1. Add a Column
Suppose we have an Employees table:
| Employee_ID | Employee_Name | Salary |
|---|---|---|
| INT | VARCHAR(100) | DECIMAL(10,2) |
To add a Department column:
ALTER TABLE Employees
ADD Department VARCHAR(30);
After adding the column:
| Employee_ID | Employee_Name | Salary | Department |
|---|---|---|---|
| INT | VARCHAR(100) | DECIMAL(10,2) | VARCHAR(30) |
2. Drop a Column
To remove the Salary column:
ALTER TABLE Employees
DROP COLUMN Salary;3. Rename a Column
The syntax varies by database. For example, in PostgreSQL/MySQL:
ALTER TABLE Employees
RENAME COLUMN Employee_Name TO Name;If we want to change the Salary column from INT to DECIMAL(10,2):
ALTER TABLE Employees
MODIFY Salary DECIMAL(10,2);
