SQL ALTER TABLE - The Coding Shala

Home >> Learn SQL >> SQL Alter Table

SQL ALTER TABLE Command

The SQL 'ALTER TABLE' command is used to add, delete(drop) or modify columns in an existing table. Using SQL 'ALTER TABLE' we can add or drop various constraints on an existing table.
SQL ALTER TABLE - The Coding Shala

SQL ADD Column Syntax

The following SQL syntax is used to add a new column in the existing table - 

 ALTER TABLE table_name  
 ADD column_name datatype;

SQL DROP Column Syntax

The following SQL syntax is used to drop column in the table - 

ALTER TABLE table_name  
 DROP COLUMN column_name;  

SQL MODIFY Column Syntax 

The following SQL syntax is used to modify the existing column in the table. This syntax will only work in MySQL and Oracle databases.

ALTER TABLE table_name  
 MODIFY COLUMN column_name datatype; 

SQL ALTER Table Example

The following 'emp' is used for the examples - 

 Id     Name     Age     Address     Salary  
 1     Akshay     22     Pune        40000  
 2     Mohit      21     Delhi       42000  
 3     Akash      21     Delhi       45000  
 4     Nikhil     24     Mumbai      50000  
 5     Smith      24     Pune        50000  
 6     Akshay     22     Pune        50000  
 7     Nikhil     24     Mumbai      43000  

The following query will add one more column 'Sex' to the existing table - 

SQL >> alter table emp  
        add column Sex char(1);  

 Output >>  
 Id     Name     Age     Address     Salary     Sex  
 1     Akshay     22     Pune        40000     null  
 2     Mohit      21     Delhi       42000     null  
 3     Akash      21     Delhi       45000     null  
 4     Nikhil     24     Mumbai      50000     null  
 5     Smith      24     Pune        50000     null  
 6     Akshay     22     Pune        50000     null  
 7     Nikhil     24     Mumbai      43000     null  

The following query will drop the column 'Sex' - 

 alter table emp drop column sex;  



Other Posts You May Like
Prev<< SQL Delete Query                                                                   NEXT >>SQL Where Clause
Please leave a comment below if you like this post or found some errors, it will help me to improve my content.

Comments

Popular Posts from this Blog

Shell Script to find sum, product and average of given numbers - The Coding Shala

Java Program to Convert Binary to Decimal - The Coding Shala

N-th Tribonacci Number Solution - The Coding Shala

Shell Script to Create a Simple Calculator - The Coding Shala

InterviewBit - Colorful Number Solution - The Coding Shala