SQL ALTER TABLE
The ALTER TABLE statement in SQL is used to modify the structure of an existing table. Common operations include adding, modifying, or dropping columns.
Syntax
Add a column:
ALTER TABLE table_name ADD column_name datatype;
Modify a column:
ALTER TABLE table_name MODIFY column_name new_datatype;
Drop a column:
ALTER TABLE table_name DROP COLUMN column_name;
Example
Let us add a new column, Salary, to the Staff table:
Code Example
-- Add a New Column
ALTER TABLE Staff ADD Salary DECIMAL(10, 2);
-- Verify the Modified Table
DESCRIBE Staff;
Output
-- DESCRIBE Staff:
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| ID | INT | NO | NULL | ||
| Name | VARCHAR(50) | YES | NULL | ||
| Age | INT | YES | NULL | ||
| Department | VARCHAR(50) | YES | NULL | ||
| Salary | DECIMAL(10,2) | YES | NULL |
Explanation
- The ALTER TABLE command adds a new column Salary to the Staff table.
- The DESCRIBE command shows the modified structure of the table, including the new column.