SQL ALTER TABLE Statement

ALTER Table

We will take a demo table: Customers 

We have 5 columns: customer_id, first_name, last_name, age, country. 

Using Alter Command, we will modify the table and demonstrate every use case of the Alter command. 

Let us now see: 

ALTER TABLE ADD Column statement in SQL

You can use the ALTER TABLE command to add a new column to an existing table. This is useful when you need to store new types of data that were not originally included in the table.

ALTER TABLE ADD Column Statement Syntax:

ALTER TABLE mytable
ADD COLUMN newcolumn VARCHAR(50);

Example of ALTER TABLE ADD Column statement in SQL

Let us add a new column: Address

ALTER TABLE Customers
ADD COLUMN Address VARCHAR(50);

Output

To see the result, we have to run a Select query. 

Select * from Customers;

ALTER Column statement in SQL

You can use the ALTER COLUMN command to modify an existing column in a table. This can be used to change the data type of a column or to add constraints to a column.

ALTER Column statement in SQL Syntax

SQL Server / MS Access:

ALTER TABLE table_name
ALTER COLUMN column_name datatype;

My SQL / Oracle (prior version 10G):

ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

Oracle 10G and later:

ALTER TABLE table_name
MODIFY column_name datatype;

Example

Here's an example of how to change the data type of a column named "age" in "customers" from INT to VARCHAR:

ALTER TABLE Customers MODIFY age VARCHAR(3);

In this example, the data type of the "age" column is changed from INT to VARCHAR, and the new data type has a length of 3.

Note that changing the data type of a column may cause data loss if the new data type cannot store the existing data in the column. For example, if you change the data type of a column from INT to VARCHAR, any values that are not numeric may be truncated or lost. So it's important to back up your data before making any changes to a table.

ALTER TABLE DROP Column Statement

If you no longer need a column in a table, you can utilize the ALTER TABLE command to remove it.

ALTER TABLE DROP Column Statement Syntax:

ALTER TABLE mytable
DROP COLUMN oldcolumn;

Example of ALTER TABLE DROP Column Statement

ALTER TABLE Customers
DROP COLUMN Address;

Output

ALTER TABLE MODIFY Column Statement in SQL

If you want to modify the name of a table, you can utilize the ALTER TABLE command to rename it.

ALTER TABLE MODIFY Column Statement Syntax:

ALTER TABLE mytable
RENAME TO newtable;

Example of ALTER TABLE MODIFY Column Statement

ALTER TABLE Customers
RENAME TO CustomerTable;

Output

ALTER TABLE RENAME Column statement in SQL

The ALTER TABLE RENAME Column statement in SQL is used to modify an existing table by renaming one of its columns.

Syntax of ALTER TABLE RENAME Column statement in SQL

ALTER TABLE mytable
RENAME COLUMN old_column_name TO new_column_name;

Examples of ALTER TABLE RENAME Column statement in SQL

ALTER TABLE students
RENAME COLUMN id_no TO roll_no;

Learn about pwd command in linux here.

Frequently Asked Questions 

What is the ALTER command in SQL used for?

The ALTER command is used to alter the structure of a table, such as adding or deleting columns, changing the data type of a column, or modifying table constraints.

What are the different types of ALTER commands?

There are several types of ALTER commands, including ADD, DROP, MODIFY, RENAME, and ALTER COLUMN.

How do you add a new column to a table using the ALTER command?

To add a column to an existing table, you can use the ADD command followed by the new column name and data type. For example:

ALTER TABLE tableName ADD columnName data_type;

What is a relational database?

A relational database is a type of database that organizes data into one or more tables with a unique key for each row. The tables are related to each other through foreign key relationships.

Conclusion

In this article, we discussed the ALTER command in SQL. We learnt how to manipulate the table's structure and update the existing data.

We believe this article on the ALTER command in SQL was helpful. You can refer to other similar articles as well - 

• SQL Introduction
• SQL Syntax
• Data Types in SQL
• Operators in SQL
• SQL Expressions

Refer to our guided paths on Coding Ninjas Studio to learn more about DSA, Competitive Programming, JavaScript, System Design, etc. Enrol in our courses and refer to the mock test and problems available. Take a look at the interview experiences and interview bundle for placement preparations.

You can start practicing SQL Problems here.

Happy Learning Ninja!