Oracle Column

Introduction to Oracle Column

A column in Oracle can be defined as a set of data values which is only of a particular type and is homogenous containing values which can be integer, character, string, images, BLOB and many more and each value in the column represents one value for each row of the oracle database and a table in the oracle database or any other relational database (where data is stored in tabular form) is formed by the columns and rows.

Examples to Implement Oracle Column

Let us now understand a little better with the help of an example:

Example #1: Add a Column in Oracle

In the first point, we will discuss how to add a column in Oracle. ALTER statement is a DDL statement as we are going to change the structure of the table by adding a column.

Code:

ALTER TABLE customers
ADD frequency NUMBER(3);

Output:

Explanation: In this query frequency column of data type NUMBER is being added to the existing table customers. Let us now execute the query in SQL developer and check the result. As we can see in the screenshot the table customers have been successfully altered.

Example #2: Adding Multiple Columns in a Table

In this case, we are going to add more than one column in a table. For this example, we are going to add two columns to the table customers. Let us look at the query for the same.

Code:

ALTER TABLE customers
ADD (frequency NUMBER(3), DOB date);

Output:

Explanation: In the query above we are adding the columns frequency and DOB to table customers. Let us execute the query in SQL developer and check the result. As we can see in the screenshot the two columns have been successfully added.

Example #3: Alter Statement to Add Constraint to a Column

In the previous point, we discussed how to add single or multiple columns in a table. So, just as we can add a new column to an existing table, similarly we can also use ALTER statement to modify an existing column in a table and add a new constraint to that specific column. Since the column is already present. So, we will modify the column and ADD a new constraint to it.

Let us go through an example to understand it better. In this example, we are going to add a constraint UNIQUE to one of the existing columns in the table customers already present in the database. Let us prepare the query for the same.

Code:

ALTER TABLE customers
MODIFY customer_name UNIQUE;

Output:

Explanation: The column is customer_name and we are adding the NOT NULL constraint which will not allow the column to accept duplicate values. As we can see in the screenshot the table has been successfully altered.

Example #4: Rename a Column

RENAME A COLUMN: Just like we can add a new column or modify an existing column in an Oracle database. We can also RENAME the existing columns. We need to use the ALTER statement in order to achieve that milestone. In this example, we will RENAME an existing column to a different or new name. We will try to change the name of the column ‘PLACE’ present in the table ‘CUSTOMERS’ to a different new name. Let us try to prepare the query for the same.

Code:

ALTER TABLE customers
RENAME COLUMN place TO city;

Output:

Explanation: In the above query the column ‘place’ is being renamed to ’city’. Let us execute the query in SQL developer and check the result. As we can see in the screenshot the column has been successfully renamed.

Example #5: Drop a Column

Just like we can add, modify, and rename an existing column. We can also DROP an existing column from the database. In this example, we are going to drop the column frequency present in the table customers. Let us prepare the query for the same.

Code:

ALTER TABLE customers
DROP COLUMN frequency;

Output:

Explanation: In the above screenshot, we can see that the column has been successfully dropped and the table has been altered.

Conclusion

In this article we discussed the definition of the column in a database and then we moved on to the various types of operations we can perform on columns. In order to have a better understanding, we went through the syntaxes and examples for each operation.

Recommended Articles

This is a guide to Oracle Column. Here we discuss an introduction to Oracle Column with respective query examples for better understanding. You can also go through our other related articles to learn more –

1. Oracle CTE
2. Oracle REPLACE()
3. Oracle Temporary Table
4. Oracle EXTRACT()