Table in Oracle

Introduction to Table in Oracle

Table in Oracle, Table is the basic unit of data storage in a structured database like Oracle. A single schema in a database can contain multiple tables. A table consists of rows and columns in which data is stored. So, it is also called a structured database since the table gives you a structure to store data. A row is a collection of values from different columns for a single record.

How to Create a Table in Oracle?

Now that we have a got knowledge of what exactly a table in oracle is, we will now see how to create a table in the oracle database.

Let us look into the syntax on how to create a table in oracle.

Syntax to Create Table

CREATE TABLE schema.table_name (column1
datatype columnconstraint, column2 datatype
columnconstraint
…………….
tableconstraint);

Parameters

Let us now look into the parameters for the above syntax

1. Schema.table_name: In this, we need to tell the table name and schema name to which the new table will be stored in the database. A database can have multiple schemas.
2. Column datatype: It represents the data type of the new columns.
3. Columnconstraint: It represents any specific rule we would like to mention for the particular column.
4. Tableconstraint: To mention table constraints like primary key, foreign key.

Now let us take an example to understand better. In this example, we will create a table named customer which will have three columns named customer_id, customer_name and place. Out of these three columns, we will have customer_id as the primary key(values should be unique and not null).

Example

Let us look at the query below:

Query

CREATE TABLE customers (customer_id varchar(20) primary key,
customer_name varchar(10), place varchar2(10));

So, now let us execute this query in oracle SQL developer. The output is shown in the screenshot below.

How to Add Data in the Table?

The insert statement is used to add a new row or rows in a table in the Oracle database. We generally use it after we have created a table in the database. One important point to remember is that while inserting records into a table, we must provide a value for every NOT NULL value. Let us look into the syntax of the INSERT statement in oracle.

Syntax to Add Data

INSERT INTO
table_name (column1, column2,……….)
VALUES (expression1, expression2,……);

Parameters

• table_name: It refers to the name of the table in which we want to insert the data
• column2: The column names in the table.
• Expression2: The values/expression to be inserted in the respective columns.

Example

Now let us look at an example below to get a better understanding of the INSERT statement. In our example we are going to insert values into the CUSTOMERS table we had created a while ago. Let us look at the query below.

Query

INSERT INTO customers (customer_id, customer_name, place)
VALUES ('CU001', 'Vipul', 'Mumbai');

If we look at the query we are inserting values to all three columns which were created during the creation of the table.

Let us now run the query in Oracle SQL developer. The screenshot below represents the output of the same.

As you can see the output reads as “1 row inserted”. This means we were successful in inserting the records into the table.

How to Edit Table in Oracle?

We can use ALTER MODIFY command to change the definition of existing columns and we can use the UPDATE statement to change any data of a particular row. We will check the syntax for both ALTER MODIFY and UPDATE statements.

Syntax for Alter Modify

ALTER TABLE table_name
MODIFY column action;

Parameters

• table_name: It represents the table name that we want to modify.
• column: The name of the column we want to modify
• action: The action that we want to perform on that particular column.

Example

Now let us look at the example to understand better. In this example, we are going to modify the column constraints of the column customer_name in the table customers. We are going to add constraints NOT NULL with the column customer_name. Let us look at the query below for the same.

Query

ALTER TABLE customers
MODIFY customer_name varchar2(10)NOT NULL;

Let us now run the query in SQL developer. The below screenshot shows the output for the same.

As you can see the output says that the table has been altered which means the new constraint has been added to the column.

Syntax for Update

UPDATE table_name
SET column = value
WHERE condition;

Parameters

• table_name= It refers to the name of the table.
• [where condition]: It refers to the condition that has to be satisfied for the update to take place.

Example

So, in this example, we are going to update the value of the ‘place’ column of ‘customers table’ where the customer name is ‘Vipul’. Let us look at the query for the same

Query

UPDATE customers SET PLACE = 'Agra'
WHERE CUSTOMER_NAME ='Vipul';

The below screenshot shows the output of the query when run on SQL developer.

The output shows that the row satisfying the condition has been updated.

How to Delete Table in Oracle?

In the Oracle database, we use the DELETE statement to delete or remove records/records from a table.

Syntax to Delete Table

DELETE FROM
table
WHERE conditions;

Parameters

• table: The name of the table
• conditions: It refers to the conditions which must be met to get the record deleted.

Example

In this example, we will delete a record that has a name as ’Vipul’. Let us look at the query for the same.

Query

DELETE FROM
customers WHERE customer_name='Vipul';

The below screenshot shows the output of the query when run on SQL developer.

The output shows that the row satisfying the condition has been deleted.

Conclusion

In this article, we learned about the concept of Table in the oracle database. We discussed how to create a table and then insert, *edit and delete values in the table with the help of examples.

Recommended Articles

This is a guide to Table in Oracle. Here we discuss the introduction, How to Create a Table in Oracle and How to Add Data in the Table. You can also go through our other related articles to learn more–

1. Oracle Having Clause
2. Oracle Queries
3. Oracle Cross Join
4. PostgreSQL Cross Join