For an instructor lead, in-depth look at learning SQL click below.
Introduction
In SQL, tables are mutable which means they can be altered after creation. We usually find ourselves in situations where we need to add more data to a table, which ideally should fit in a new column. This post aims to educate you on how to add a new column to an already existing table in your database using SQL.
SQL ALTER TABLE Statement
One of the Data Manipulation Language (DML) commands in SQL is ALTER TABLE – the command that you would use to add a column in SQL. The basic syntax is:
|
1 2 3 4 |
ALTER TABLE table_name ADD column_name datatype; |
Example:
Suppose we have a table called Employees which has the following columns: Emp_ID, Name, and Position. Let’s say we need to add a new column “Age”. The SQL statement would be:
|
1 2 3 4 |
ALTER TABLE Employees ADD Age INT; |
The command begins with ALTER TABLE followed by the table name. ADD is the keyword telling the database system to add a new column. Age is the name of the new column and INT is the data type specifying the kind of data that can be stored in the column.
Adding a column with a default value
In some situations, you might want to add a new column with a default value. SQL allows us to do this in one command. Continuing with our example, we shall add a column “Date_of_joining” with a default value.
|
1 2 3 4 |
ALTER TABLE Employees ADD Date_of_joining DATE DEFAULT GETDATE(); |
The keyword DEFAULT is used to set a default value for the new column. GETDATE() is a SQL function that returns the current date. Therefore, any new records will automatically get the current date as the joining date, unless specified otherwise during insertion.
Conclusion
SQL provides powerful and flexible tools for managing and manipulating your data. This post showed you how to add a new column to an exiting database. Remember to always plan ahead and design your database schema appropriately, but also know that SQL has got you covered when you need to make changes.