Over the years, the Oracle database has continued to revolutionize the way databases are managed and interacted with. In this article, we will discuss how to add a new column to an existing Oracle table.
Adding a new column to an existing Oracle table can be achieved by using the ALTER TABLE command. This command allows the structure of an existing table to be modified. The syntax of this command looks like this:
ALTER TABLE table_name
ADD column_name datatype;
Let’s consider an example where we want to add a new column “salary” into the “employees” table. First, we need to check if the table exists. To do this, we can run the following SQL query:
SELECT * FROM employees;
If the query returns the list of columns, it means that the table exists and we can proceed. Then, we can use the ALTER TABLE command to add the “salary” column:
ALTER TABLE employees
ADD salary NUMBER( 10 );
In the above query, we specified the data type of the new column as “number”, which is a numeric data type that can store values of up to 10-digits. If we want to add a column of different data type, we can use the “datatype” keyword followed by the specific data type.
Once the column is created, we can add the data for that column. This can be done by using theUPDATE statement as follows:
UPDATE employees
SET salary = 2000
WHERE empid = 1;
In the above query, we are setting the salary of the employee whose Employee ID is 1 to 2000$. The UPDATE command can be used to update the data in any column.
Finally, we can verify if the column was added successfully or not by querying the employees table:
SELECT * FROM employees;
If the above statement returns the list of columns with the “salary” column included, it means the column has been successfully added to the table.
In conclusion, Oracle provides flexibility to add a new column to an existing table by using the ALTER TABLE command. This command allows us to modify the existing table structure without having to create a new one. This feature makes Oracle a powerful database management system.