SQL ALTER TABLE: A Comprehensive Guide

SQL ALTER TABLE

The ALTER TABLE statement in SQL is a powerful command that allows you to modify an existing table without the need to recreate it. With this statement, you can add, modify, or delete columns and even update the constraints applied to a table. Additionally, it enables you to rename columns or the table itself. Let’s dive deeper into the various functionalities of the ALTER TABLE statement.

Complete Advance AI topics:- CLICK HERE
Complete Python Course with Advance topics:-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 data. The ALTER TABLE statement with the ADD keyword makes this process seamless.

Syntax for Adding a Single Column

ALTER TABLE table_name ADD column_name column_definition;

This syntax is used to add 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 you to add multiple columns in a single query.

Examples

Example 1: Adding a Single Column

Consider a table named Cars:

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:

To add two columns, Emp_ContactNo and Emp_EmailID, use this query:

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

Modifying Columns Using ALTER TABLE

The MODIFY keyword is used to alter 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

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 tool for managing and modifying the structure of existing tables in SQL. Whether you need to add new columns, update their definitions, remove unnecessary ones, 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