SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL Data Types SQL OPERATORS SQL COMMANDS SQL Queries

SQL Database

SQL Create Database SQL DROP Database SQL SELECT Database

SQL Table

SQL TABLE SQL CREATE TABLE SQL COPY TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL DROP TABLE SQL UPDATE TABLE SQL INSERT TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT WHERE Clause SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT OR Operator SQL SELECT LIKE Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL WHERE Clause SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT Statement SQL INSERT INTO Statement SQL INSERT INTO Values SQL INSERT INTO SELECT SQL Insert multiple rows

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL CROSS Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Cast Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS Difference between Delete, Drop and Truncate in SQL

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL Aggregate Operators SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter WEB SQL SQL Auto Increment Save Point in SQL space() function in SQL SQL Aggregate Functions SQL Topological Sorting SQL Injection SQL Cloning Tables SQL Aliases SQL Handling Duplicate Update Query in SQL Grant Command in SQL SQL SET Keyword SQL Order BY LIMIT SQL Order BY RANDOM

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL How index works in SQL How to calculate age from Date of Birth in SQL How to Rename Column name in SQL What are single row and multiple row subqueries?

SQL Alter Table
2022-03-17 21:16:29

SQL Alter Table

In Structured Query Language, if you want to add columns in an existing table, then modify the table, or delete columns from the table. All these operations are allowed only while using the ALTER TABLE statement. The ALTER TABLE statement also permits users to add or drop the SQL constraints on the existing tables in the SQL.

The ALTER TABLE statement also permits the user to rename the existing table.

ALTER TABLE ADD COLUMN statement in SQL

This is used when you need to add columns to the existing table. In such situation, rather than creating a new table, you can add a column in an existing table using ADD keyword.

Syntax of ALTER TABLE ADD COLUMN statement in SQL

ALTER TABLE Table_Name ADD Column_Name Data_Type;   

The above syntax is used to add a column in an existing table, and allows the users to add only a single field in the table.

To add more than one column in an existing table, use the following syntax:

ALTER TABLE Table_Name ADD (Column_Name1 Data_Type, Column_Name2 Data_Type);

Let's understand the ALTER TABEL ADD COLUMN in SQL with examples.

Example of ALTER TABLE ADD COLUMN in SQL

We have taken multiple SQL examples, which will help you understand adding a single column and multiple columns in the existing table using ALTER TABLE ADD COLUMN statement.

Consider the following tables along with the given records.

Table 1: Mobile_Plan:

Package_IdData_IdTalktime_Id
121
232
313

Table 2: Data_Plan:

Data_IdData_LimitData_Price
1230
2570
3455

Table 3: Talktime_Plan:

Talktime_IdTalktime _LimitTalktime _Price
1100150
270105
36090

Table 4: Customer (Empty Table)

Example 1: Write a query to add a new column Extra_Data_Price in the Data_Plan Table.

ALTER TABLE Data_Plan ADD Extra_Data_Price int;

We have used the ALTER TABLE ADD statement in the above query to add a new Extra_Data_Price column in the Data_Plan table. We have added a single column in the existing table.

We will now use the DESC keyword and the table name to cross-check whether the Extra_Data_Price column was added or not successfully in the Data_Plan table.

DESC Data_Plan;   
FieldTypeNullKeyDefaultExtra
Data_Idint(11)NOPRINULL
Data_Limitint(11)YESNULL
Data_Priceint(11)YESNULL
Extra_Data_PriceInt(11)YES

Example 2: Write a query to add multiple columns CustomerId, Customer_Name, and Phone_Number in the Customer table, which is empty, having no columns.

ALTER TABLE Customer ADD (CustomerId int, Customer_Name varchar(20), Phone_Number varchar(10));

We have used the ALTER TABLE ADD statement in the above query to add multiple new columns CustomerId, Customer_Name, and Phone_Number in the Customer table. We have added multiple columns in the existing table.

We will now use the DESC keyword and the table name to cross-check whether the CustomerId, Customer_Name, and Phone_Number columns were added successfully or not in the Customer table.

DESC Customer; 
FieldTypeNullKeyDefaultExtra
CustomerIdint(11)YESNULL
Customer_Namevarchar(20)YESNULL
Phone_Numbervarchar(10)YESNULL

Suppose you want to add constraints like Primary Key, Foreign Key to an existing table. We will take an example of adding Primary Key constraints and Foreign Key constraints to the existing table. We can also add those constraints using ALTER TABLE ADD statement.

Example 3: Write a query to add Primary Key constraint on the column name CustomerId in the Customer Table.

ALTER TABLE Customer ADD Primary Key(CustomerId);

In the above query, we have used ALTER TABLE ADD statement to add the Primary Key constraint on the column name CustomerId in the Customer Table. We have used the Primary Key keyword with ALTER TABLE ADD statement to add a constraint on the CustomerId.

We will now use the DESC keyword and the table name to cross-check whether the CustomerId column key is changed to PRI in the Customer table.

DESC Customer; 
FieldTypeNullKeyDefaultExtra
CustomerIdint(11)YESPRINULL
Customer_Namevarchar(20)YESNULL
Phone_Numbervarchar(10)YESNULL

Example 4: Write a query to add Foreign Key constraint on the column name Package_Id in the Customer Table.

ALTER TABLE Customer ADD (Package_Id int, FOREIGN KEY(Package_Id) references Mobile_Plan(Package_ID));

In the above query, we have first added the Package_Id column in the Customer table, and then we have used Foreign Key on the same column Package_Id in the same query using ALTER TABLE ADD statement.

We will now use the DESC keyword and the table name to cross-check whether the Package_Id column was added or not successful in the Customer table and key change to MUL.

DESC Customer;   
FieldTypeNullKeyDefaultExtra
CustomerIdint(11)YESPRINULL
Customer_Namevarchar(20)YESNULL
Phone_Numbervarchar(10)YESNULL
Package_IdInt(11)YESMULNULL

ALTER TABLE MODIFY COLUMN statement in SQL

Suppose you want to update the column name or definition like a data type. In that case, we will use ALTER TABLE MODIFY statement to update the column data type of the already existing table. We will use MODIFY keyword just after the Table_Name in the statement.

Syntax of ALTER TABLE MODIFY COLUMN statement in SQL

ALTER TABLE Table_Name MODIFY Column_Name Data_Type;   

The above syntax is used to modify columns in an existing table allows the user to modify only a single field.

To modify more than one column in an existing table-use below syntax:

ALTER TABLE Table_Name MODIFY (Column_Name1 Data_Type, Column_Name2 Data_Type);

Let's understand the ALTER TABEL MODIFY in SQL with examples.

Example of ALTER TABLE MODIFY column in SQL

We have taken multiple SQL examples to help you understand modifying single columns and multiple columns in the existing table using ALTER TABLE MODIFY statement.

Consider the following tables along with the given records.

Table 1: Mobile_Plan:

Package_IdData_IdTalktime_Id
121
232
313

Table 2: Data_Plan:

Data_IdData_LimitData_Price
1230
2570
3455

Table 3: Talktime_Plan:

Talktime_IdTalktime _LimitTalktime _Price
1100150
270105
36090

Table 4: Customer

CustomerIdCustomer_NamePhone_NumberPackage_Id
101Bhavesh98465220211
102Mahesh77985982723
103Anita78652220211

Example 1: Write a query to update the size of the column Customer Name from the Customer Table.

ALTER TABLE Customer MODIFY Customer_Name varchar(30);

In the above query, we have modified the column Customer_Name size from 20 to 30. We have used ALTER TABLE statement with MODIFY keyword just after the table name to modify the column.

We will now use the DESC keyword and the table name to cross-check whether the column Customer_Name data type is modified in the Customer table.

DESC Customer; 
FieldTypeNullKeyDefaultExtra
CustomerIdint(11)YESPRINULL
Customer_Namevarchar(30)YESNULL
Phone_Numbervarchar(10)YESNULL
Package_IdInt(11)YESMULNULL
SQL ALTER TABLE

Example 2: Write a query to update the data type of the column Talktime_Limit and Talktime_Price from the Talktime_Plan Table.

ALTER TABLE Talktime_Plan MODIFY (Talktime_Limit varchar(2), Talktime_Price varchar(2));

We have modified the column Talktime_Limit and Talktime_Price data type from int to varchar in the above query. We have used ALTER TABLE statement with MODIFY keyword just after the table name to modify the column.

We will now use the DESC keyword and the table name to cross-check whether the column Talktime_Limit and Talktime_Price data type are modified or not in the Talktime_Plan table.

DESC Talktime_Plan;
FieldTypeNullKeyDefaultExtra
TalkTime_Idint(11)YESPRINULL
TalkTime_Limitvarchar(2)YESNULL
TalkTime_Pricevarchar(2)YESNULL
SQL ALTER TABLE

ALTER TABLE DROP COLUMN statement in SQL

This is used when you delete the column from the existing table. Then in such a situation, rather than drop the entire table from the database, we can use the DROP keyword to drop the column.

Syntax of ALTER TABLE DROP COLUMN statement in SQL

ALTER TABLE Table_Name DROP Column_Name;   

Let's understand the ALTER TABEL DROP in SQL with examples.

Example of ALTER TABLE DROP column in SQL

We have taken SQL examples to help you understand modifying single columns and multiple columns in the existing table using ALTER TABLE DROP statement.

Consider the following tables along with the given records.

Table 1: Mobile_Plan:

Package_IdData_IdTalktime_Id
121
232
313

Table 2: Data_Plan:

Data_IdData_LimitData_Price
1230
2570
3455

Table 3: Talktime_Plan:

Talktime_IdTalktime _LimitTalktime _Price
1100150
270105
36090

Table 4: Customer

CustomerIdCustomer_NamePhone_NumberPackage_Id
101Bhavesh98465220211
102Mahesh77985982723
103Anita78652220211

Example 1: Write a query to drop the Package_Id from the Customer table.

ALTER TABLE Customer DROP COLUMN Package_Id;

We have dropped the column name Package_Id from the Customer table in the above query. We have used ALTER TABLE DROP statement to drop the column from the table.

We will now cross_check whether the column Package_Id from the Customer table was deleted or not successfully.

SELECT * FROM CUSTOMER;
CustomerIdCustomer_NamePhone_Number
101Bhavesh9846522021
102Mahesh7798598272
103Anita7865222021
SQL ALTER TABLE

ALTER TABLE RENAME statement in SQL

Suppose we want to change the name of the column or table, then we will use RENAME keyword to rename the fields or tables of the existing table.

Syntax of ALTER TABLE RENAME statement in SQL

ALTER TABLE Table_Name RENAME Column_Name;   

Let's understand the ALTER TABEL RENAME in SQL with examples.

Example of ALTER TABLE RENAME column in SQL

We have taken SQL examples to help you understand modifying single columns and multiple columns in the existing table using ALTER TABLE RENAME statement.

Consider the following tables along with the given records.

CustomerIdCustomer_NamePhone_Number
101Bhavesh9846522021
102Mahesh7798598272
103Anita7865222021

Example 1: Write a query to change the name of the Customer table.

ALTER TABLE Customer RENAME TO CUST;

In the above query, we have renamed the table name of the Customer table to the Cust table. We used ALTER TABLE RENAME statement to rename the table name.

We will cross-check whether the table's name is changed or not successfully.

SHOW TABLES;
SQL ALTER TABLE

Example 1: Write a query to change the name of the column CustomerId to cid from the Cust table.

ALTER TABLE Cust RENAME COLUMN CustomerId cid int not null;

In the above query, we have changed the column name CustomerId to Cid. We have used ALTER TABLE RENAME statement to rename the table name.

We will cross-check whether the table's name is changed or not successfully.

SELECT * FROM CUST;
SQL ALTER TABLE