Dark Mode
Image
Login / Sign Up

SQL Tutorial

SQL Database

SQL Table

SQL Select

SQL Clause

SQL Order By

SQL Insert

SQL Update

SQL Delete

SQL Join

SQL Keys

Difference

SQL Injection

SQL String Functions

Miscl

SQL MCQ

PL/SQL Tutorial

Sql Interview Question

SQl Quiz

SQL ALTER TABLE

The ALTER TABLE statement in Structured Query Language allows you to add, modify, and delete columns of an existing table. This statement also allows database users to add and remove various SQL constraints on the existing tables.

Any user can also change the name of the table using this statement.

ALTER TABLE ADD Column statement in SQL

In many situations, you may require to add the columns in the existing table. Instead of creating a whole table or database again you can easily add single and multiple columns using the ADD keyword.

Syntax of ALTER TABLE ADD Column statement in SQL

ALTER TABLE table_name ADD column_name column-definition;  

The above syntax only allows you to add a single column to the existing table. If you want to add more than one column to the table in a single SQL statement, then use the following syntax:

ALTER TABLE table_name     
ADD (column_Name1 column-definition,    
column_Name2 column-definition,    
.....    
column_NameN column-definition);         

Examples of ALTER TABLE ADD Column statement in SQL

Here, we have taken the following two different SQL examples, which will help you how to add the single and multiple columns in the existing table using ALTER TABLE statement:

Example 1: Let's take an example of a table named Cars:

Car Name Car Color Car Cost
Hyundai Creta White 10,85,000
Hyundai Venue White 9,50,000
Hyundai i20 Red 9,00,000
Kia Sonet White 10,00,000
Kia Seltos Black 8,00,000
Swift Dezire Red 7,95,000

Table: Cars

  • Suppose, you want to add the new column Car_Model in the above table. For this, you have to type the following query in the SQL:
  • ALTER TABLE Cars ADD Car_Model Varchar(20);  

This statement will add the Car_Model column to the Cars table.

Example 2: Let's take an example of a table named Employee:

Emp_Id Emp_Name Emp_Salary Emp_City
201 Abhay 25000 Goa
202 Ankit 45000 Delhi
203 Bheem 30000 Goa
204 Ram 29000 Goa
205 Sumit 40000 Delhi

Table: Employee

  • Suppose, you want to add two columns, Emp_ContactNo. and Emp_EmailID, in the above Employee table. For this, you have to type the following query in the SQL:
  • ALTER TABLE Employee ADD ( Emp_ContactNo. Number(13), Emp_EmailID varchar(50) ;  

This statement will add Emp_ContactNo. and Emp_EmailID columns to the Employee table.

ALTER TABLE MODIFY Column statement in SQL

The MODIFY keyword is used for changing the column definition of the existing table.

Syntax of ALTER TABLE MODIFY Column statement in SQL

ALTER TABLE table_name MODIFY column_name column-definition;  

This syntax only allows you to modify a single column of the existing table. If you want to modify more than one column of the table in a single SQL statement, then use the following syntax:

ALTER TABLE table_name     
MODIFY (column_Name1 column-definition,    
column_Name2 column-definition,    
.....    
column_NameN column-definition); 

Examples of ALTER TABLE MODIFY Column statement in SQL

Here, we have taken the following two different SQL examples, which will help you how to modify single and multiple columns of the existing table using ALTER TABLE statement:

Example 1: Let's take an example of a table named Cars:

Car Name Car Color Car Cost
Hyundai Creta White 10,85,000
Hyundai Venue White 9,50,000
Hyundai i20 Red 9,00,000
Kia Sonet White 10,00,000
Kia Seltos Black 8,00,000
Swift Dezire Red 7,95,000

Table: Cars

  • Suppose, you want to modify the datatype of the Car_Color column of the above table. For this, you have to type the following query in the SQL:
  • ALTER TABLE Cars ADD Car_Color Varchar(50);  

Example 2: Let's take an example of a table named Employee:

Emp_Id Emp_Name Emp_Salary Emp_City
201 Abhay 25000 Goa
202 Ankit 45000 Delhi
203 Bheem 30000 Goa
204 Ram 29000 Goa
205 Sumit 40000 Delhi

Table: Employee

  • Suppose, you want to modify the datatypes of two columns Emp_ContactNo. and Emp_EmailID of the above Employee table. For this, you have to type the following query in the SQL:
  • ALTER TABLE Employee ADD ( Emp_ContactNo. Int, Emp_EmailID varchar(80) ;  

ALTER TABLE DROP Column statement in SQL

In many situations, you may require to delete the columns from the existing table. Instead of deleting the whole table or database you can use DROP keyword for deleting the columns.

Syntax of ALTER TABLE DROP Column statement in SQL

ALTER TABLE table_name DROP Column column_name ;  

Examples of ALTER TABLE DROP Column statement in SQL

Here, we have taken the following two different SQL examples, which will help you how to delete a column from the existing table using ALTER TABLE statement:

Example 1: Let's take an example of a table named Cars:

Car Name Car Color Car Cost
Hyundai Creta White 10,85,000
Hyundai Venue White 9,50,000
Hyundai i20 Red 9,00,000
Kia Sonet White 10,00,000
Kia Seltos Black 8,00,000
Swift Dezire Red 7,95,000

Table: Cars

  • Suppose, you want to delete the Car_Color column from the above table. For this, you have to type the following query in the SQL:
  • ALTER TABLE Cars DROP COLUMN Car_Color ;  
  • Let's check using the following statement that the Car_Color column is deleted from the table or not:
  • SELECT * FROM Cars;  
Car Name Car Cost
Hyundai Creta 10,85,000
Hyundai Venue 9,50,000
Hyundai i20 9,00,000
Kia Sonet 10,00,000
Kia Seltos 8,00,000
Swift Dezire 7,95,000

Table: Cars

Example 2: Let's take an example of a table named Employee:

Emp_Id Emp_Name Emp_Salary Emp_City
201 Abhay 25000 Goa
202 Ankit 45000 Delhi
203 Bheem 30000 Goa
204 Ram 29000 Goa
205 Sumit 40000 Delhi

Table: Employee

  • Suppose, you want to delete the Emp_Salary and Emp_City column from the above Employee table. For this, you have to type the following two different queries in the SQL:
  • ALTER TABLE Cars DROP COLUMN Emp_Salary ;
  •   ALTER TABLE Cars DROP COLUMN Emp_City ;  

ALTER TABLE RENAME Column statement in SQL

The RENAME keyword is used for changing the name of columns or fields of the existing table.

Syntax of ALTER TABLE RENAME Column statement in SQL

ALTER TABLE table_name RENAME COLUMN old_name to new_name;    

Examples of ALTER TABLE RENAME Column statement in SQL

Here, we have taken the following two different SQL examples, which will help you how to change the name of a column of the existing table using ALTER TABLE statement:

Example 1: Let's take an example of a table named Cars:

Car Name Car Color Car Cost
Hyundai Creta White 10,85,000
Hyundai Venue White 9,50,000
Hyundai i20 Red 9,00,000
Kia Sonet White 10,00,000
Kia Seltos Black 8,00,000
Swift Dezire Red 7,95,000

Table: Cars

  • Suppose, you want to change the name of the Car_Color column of the above Cars table. For this, you have to type the following query in the SQL:
  • ALTER TABLE Cars RENAME COLUMN Car_Color to Colors;    

This statement will change the name of a column of the Cars table. To see the changes, you have to type the following query:

  1. SELECT * FROM Cars;  
Car Name Car Color Car Cost
Hyundai Creta White 10,85,000
Hyundai Venue White 9,50,000
Hyundai i20 Red 9,00,000
Kia Sonet White 10,00,000
Kia Seltos Black 8,00,000
Swift Dezire Red 7,95,000

Table: Cars

Example 2: Let's take an example of a table named Employee:

Emp_Id Emp_Name Emp_Salary Emp_City
201 Abhay 25000 Goa
202 Ankit 45000 Delhi
203 Bheem 30000 Goa
204 Ram 29000 Goa
205 Sumit 40000 Delhi

Table: Employee

  • Suppose, you want to change the name of the Emp_City column of the above Employee table. For this, you have to type the following query in the SQL:
  •  TABLE Employee RENAME COLUMN Emp_City to Emp_Address;  

This statement will change the name of a column of the Employee table. To see the changes, you have to type the following query:

SELECT * FROM Employee;  
Emp_Id Emp_Name Emp_Salary Emp_Address
201 Abhay 25000 Goa
202 Ankit 45000 Delhi
203 Bheem 30000 Goa
204 Ram 29000 Goa
205 Sumit 40000 Delhi

Table: Employee

Comment / Reply From

Comment

You May Also Like

SQL COUNT DISTINCT

What is Race Condition

Trigger in SQL

How to Use ORDER BY in SQL

How to Use GROUP BY in SQL

SQL Arithmetic Operators

SQL Languages

How to use Auto-Increment in SQL

Popular Post

PHP Tutorial

NET Framework

Python Tutorial

Language

PHP Tutorial

NET Framework

Python Tutorial

Newsletter

Subscribe to our newsletter

Copyright © 2024 - All Rights Reserved.