SQL ALTER TABLE: A Comprehensive Guide

SQL ALTER TABLE

The ALTER TABLE statement in SQL is used to modify the structure of an existing table without recreating it. It can be used to add, modify, or remove columns and update the structure of a table. It also allows you to rename columns or the table itself. Let’s explore the different functionalities of the ALTER TABLE statement.

Complete Advance AI Topics: Click Here
SQL Tutorial: Click Here

Adding Columns Using ALTER TABLE

In real-world scenarios, you may need to add new columns to an existing table to store additional information. The ALTER TABLE statement with the ADD keyword is used for this purpose.

Syntax for Adding a Single Column

ALTER TABLE table_name ADD column_name column_definition;

This syntax adds a single column to an existing table.

Syntax for Adding Multiple Columns

ALTER TABLE table_name
ADD (
    column_name1 column_definition,
    column_name2 column_definition,
    ...
    column_nameN column_definition
);

This syntax allows multiple columns to be added in a single query.

Examples

Example 1: Adding a Single Column

Consider 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

To add a new column Car_Model, use the following query:

ALTER TABLE Cars ADD Car_Model VARCHAR(20);

This adds the Car_Model column to the Cars table.

Example 2: Adding Multiple Columns

Consider 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

To add the two columns Emp_ContactNo and Emp_EmailID, use the following query:

ALTER TABLE Employee ADD (
    Emp_ContactNo NUMBER(13),
    Emp_EmailID VARCHAR(50)
);

Modifying Columns Using ALTER TABLE

The MODIFY keyword is used to change the definition of an existing column, such as changing its data type.

Syntax for Modifying a Single Column

ALTER TABLE table_name MODIFY column_name column_definition;

Syntax for Modifying Multiple Columns

ALTER TABLE table_name
MODIFY (
    column_name1 column_definition,
    column_name2 column_definition,
    ...
    column_nameN column_definition
);

Examples

Example 1: Modifying a Single Column

To change the data type of the Car_Color column in the Cars table:

ALTER TABLE Cars MODIFY Car_Color VARCHAR(50);

Example 2: Modifying Multiple Columns

To update the data types of Emp_ContactNo and Emp_EmailID in the Employee table:

ALTER TABLE Employee MODIFY (
    Emp_ContactNo INT,
    Emp_EmailID VARCHAR(80)
);

Deleting Columns Using ALTER TABLE

When a column is no longer needed, it can be removed using the DROP keyword.

Syntax for Deleting a Column

ALTER TABLE table_name DROP COLUMN column_name;

Examples

Example 1: Deleting a Single Column

To delete the Car_Color column from the Cars table:

ALTER TABLE Cars DROP COLUMN Car_Color;

This removes the Car_Color column from the table.

Example 2: Deleting Multiple Columns

To delete the Emp_Salary and Emp_City columns from the Employee table, use two queries:

ALTER TABLE Employee DROP COLUMN Emp_Salary;
ALTER TABLE Employee DROP COLUMN Emp_City;

Renaming Columns Using ALTER TABLE

The RENAME COLUMN clause is used to rename a column in an existing table.

Syntax for Renaming a Column

ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;

Examples

YT:- DecodeIT

Example 1: Renaming a Column in the Cars Table

To rename the Car_Color column to Colors:

ALTER TABLE Cars RENAME COLUMN Car_Color TO Colors;

Example 2: Renaming a Column in the Employee Table

To rename the Emp_City column to Emp_Address:

ALTER TABLE Employee RENAME COLUMN Emp_City TO Emp_Address;

Summary

The ALTER TABLE statement is a versatile command for managing and modifying the structure of existing tables in SQL. Whether you need to add new columns, update their definitions, remove unnecessary columns, or rename them for clarity, the ALTER TABLE statement simplifies these tasks. Mastering this command is essential for efficient database management.

Download New Real Time Projects :- Click here

sql alter table add column
sql update
sql alter table add multiple columns
sql alter table
sql alter table add column
oracle sql alter table add column
oracle sql alter table
sql alter table drop column
sql alter table column size
sql alter table add multiple columns
sql alter table alter column
sql alter table name