The ALTER TABLE statement in SQL is used to add, remove, or modify columns in an existing table. The ALTER TABLE statement is also used to add and remove various constraints on existing tables.
ALTER TABLE ADD Column Statement in SQL
ADD is used to add columns to the existing table. Sometimes we may require to add additional information, in that case, we do not require to create the whole database again, ADD comes to our rescue.
ALTER TABLE ADD Column Statement Syntax:
ALTER TABLE table_name ADD (Columnname_1 datatype,
Columnname_2 datatype, …Columnname_n datatype);
The following SQL adds an “Email” column to the “Students” table:
ALTER TABLE ADD Column Statement Example:
ALTER TABLE Students
ADD Email varchar(255);
ALTER TABLE DROP Column Statement
DROP COLUMN is used to drop columns in a table. Deleting the unwanted columns from the table.
ALTER TABLE DROP Column Statement Syntax:
ALTER TABLE table_name
DROP COLUMN column_name;
The following SQL drop an “Email” column to the “Students” table:
ALTER TABLE DROP Column Statement Example:
ALTER TABLE Students
DROP COLUMN Email;
ALTER TABLE MODIFY Column Statement in SQL
It is used to modify the existing columns in a table. Multiple columns can also be modified at once. *Syntax may vary slightly in different databases.
ALTER TABLE MODIFY Column Statement Syntax:
ALTER TABLE table_name
MODIFY column_name column_type;
ALTER TABLE MODIFY Column Statement Syntax(SQL Server):
ALTER TABLE table_name
ALTER COLUMN column_name column_type;
ALTER TABLE MODIFY Column Statement Example:
ALTER TABLE table_name
MODIFY COLUMN column_name datatype;
SQL ALTER TABLE Queries
Suppose there is a student database:
ROLL_NO |
NAME |
1 |
Ram |
2 |
Abhi |
3 |
Rahul |
4 |
Tanu |
To ADD 2 columns AGE and COURSE to table Student.
Query:
ALTER TABLE Student ADD
(AGE number(3),COURSE varchar(40));
Output:
ROLL_NO |
NAME |
AGE |
COURSE |
1 |
Ram |
|
|
2 |
Abhi |
|
|
3 |
Rahul |
|
|
4 |
Tanu |
|
|
MODIFY column COURSE in table Student.
Query:
ALTER TABLE Student
MODIFY COURSE varchar(20);
After running the above query the maximum size of the Course Column is reduced to 20 from 40.
DROP column COURSE in table Student.
Query:
ALTER TABLE Student
DROP COLUMN COURSE;
Output:
ROLL_NO |
NAME |
AGE |
1 |
Ram |
|
2 |
Abhi |
|
3 |
Rahul |
|
4 |
Tanu |
|
If you like GeeksforGeeks and would like to contribute, you can also write an article using write.geeksforgeeks.org or mail your article to review-team@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks. Please write comments if you find anything incorrect, or if you want to share more information about the topic discussed above.
Unlock the Power of Placement Preparation!
Feeling lost in OS, DBMS, CN, SQL, and DSA chaos? Our
Complete Interview Preparation Course is the ultimate guide to conquer placements. Trusted by over 100,000+ geeks, this course is your roadmap to interview triumph.
Ready to dive in? Explore our Free Demo Content and join our
Complete Interview Preparation course.
Last Updated :
05 Apr, 2023
Like Article
Save Article