Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

sql

How to add new columns to an existing table in SQL

Educative Team

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:

  1. 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.
  2. 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.

Student

Roll_No

Name_Student

1

Micheal

2

Marcos

The following illustration shows the schema of the Student table after appending the Age attribute using the ALTER command.

Student

Roll_No

Name_Student

Age

1

Micheal

NULL

2

Marcos

NULL

Example

-- 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.

RELATED TAGS

sql
Copyright ©2022 Educative, Inc. All rights reserved
RELATED COURSES

View all Courses

Keep Exploring