How to add new columns to an existing table in SQL

Overview

Tables are the building blocks in any Database. Each table consists of two components: tuples and attributes represent rows and columns respectively. We know that the name of attributes, their data types, and their key values are declared at the time of creation. We create attributes in the table as per our need, but the probability is very high to append new attributes in the future. There are two ways to add new attributes to the table:

Create a new table from scratch. This method can add a new attribute to a new table. First, you have to copy and paste the data from the previous table to the new one and then insert the values of the new attribute. This technique is neither productive nor memory-efficient.
Use the ALTER command to add a new attribute to an existing table. This command can do this task vigorously and memory-efficiently.

`ALTER` command

The ALTER command is frequently used to delete, modify and add columns in an existing table. The ALTER command can also be used to add and drop constraints on a table.

Syntax

ALTER TABLE <Table_Name> ADD  <Attr1> datatype, <Attr2> datatype...;

Outcome

The following illustration shows the initial schema of the table Student.

-- Create a table
CREATE TABLE Student
(
    Roll_No int primary key,
    Name_Student varchar(15)
);
-- Insert values
INSERT INTO Student VALUES(1, "Micheal");
INSERT INTO Student VALUES(2, "Marcos");
-- Print Data
SELECT Roll_No AS ID, Name_Student AS Name FROM Student;
SELECT "";
-- Add Another columns in the same table
ALTER TABLE Student ADD Age int;
SELECT Roll_No AS ID, Name_Student AS Name, Age FROM Student;
SELECT "";
-- Update values
UPDATE Student SET Age=19 WHERE Roll_No=1;
UPDATE Student SET Age=17 WHERE Roll_No=2;
SELECT Roll_No AS ID, Name_Student AS Name, Age FROM Student;

Appending new attribute in an existing table

Explanation

In lines 2-6, we create a table named Student using the CREATE command.
In lines 8-9, we insert values in the table Student by using the INSERT command.
In line 11, we display the data of the table Student.
In line 15, we add a new attribute Age using the ALTER command.
In line 16, we display the table Student's data whose schema is updated. By default, all states of the newly added attribute are NULL. We can set default values by using the DEFAULT keyword just after the attribute’s datatype.
In lines 20-21, we update the values of the Age attribute by using the UPDATE command.
In line 21, we display the final states of the table Student.

Learn in-demand tech skills in half the time

PRODUCTS

Mock Interview

New

Courses

Skill Paths

Projects

Assessments

Roll_No	Name_Student
1	Micheal
2	Marcos

Roll_No	Name_Student	Age
1	Micheal	NULL
2	Marcos	NULL