EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

Table in Oracle

By Priya PedamkarPriya Pedamkar

Secondary Sidebar
Oracle Tutorial
  • Advanced
    • Timestamp to Date in Oracle
    • Oracle Golden Gate
    • Oracle Virtual Machine
    • Oracle Describe Table
    • Oracle Clauses
    • Oracle Having Clause
    • Oracle?Primavera
    • Oracle FOREIGN Key
    • PIVOT in Oracle
    • Oracle Alter Table
    • Oracle Queries
    • Oracle Views
    • Oracle Window Functions
    • Oracle String Functions
    • Oracle Date Functions
    • Oracle Analytic Functions
    • Oracle Aggregate Functions
    • Select in Oracle
    • INSERT in Oracle
    • DISTINCT in Oracle
    • Function in Oracle
    • Oracle GROUP_CONCAT
    • Oracle INSTR()
    • Oracle CONVERT
    • Oracle LENGTH()
    • Oracle EXISTS
    • Oracle REPLACE()
    • Oracle MERGE
    • Oracle LEAD()
    • Oracle EXTRACT()
    • Oracle LISTAGG()
    • Oracle SYS_CONTEXT()
    • Oracle COALESCE
    • Oracle NVL()
    • Oracle SYSDATE()
    • Oracle?Date Format
    • Oracle SYS_GUID()
    • Oracle WILDCARDS
    • Oracle Synonyms
    • Oracle Subquery
    • BETWEEN in Oracle
    • FETCH in Oracle
    • Oracle Index
    • Oracle Function-based Index
    • Oracle UNIQUE Index
    • Oracle Bitmap Index
    • Oracle Column
    • Oracle Triggers
    • Oracle Procedures
    • Sample Database for Oracle
    • Oracle LIKE Operator
    • ORDER BY in Oracle
    • Oracle ORDER BY DESC
    • GROUP BY in Oracle
    • Oracle GROUP BY HAVING
    • Oracle Aliases
    • Table in Oracle
    • Oracle Temporary Table
    • Oracle? Table Partition
    • Oracle rename table
    • Oracle CTE
    • Cursor in Oracle
    • Oracle LOCK TABLE
    • Oracle Tablespace
    • Oracle CARDINALITY
    • Oracle REGEXP
    • Oracle REGEXP_REPLACE
    • Oracle to_date
    • JSON in Oracle
    • Oracle COMMIT
    • Oracle GRANT
    • Oracle MD5
    • Oracle ROLLBACK
    • Oracle Users
    • Oracle TIMESTAMP
    • IF THEN ELSE in Oracle
    • Oracle While Loop
    • Oracle Clone Database
    • Oracle Backup Database
    • Oracle? XML
    • Oracle XMLAGG
    • Oracle XMLTABLE
    • Oracle Performance Tuning
    • Oracle B Tree Index
    • Oracle fusion
    • Oracle ebs
    • Oracle GRC
    • Oracle ERP
    • Oracle ASM
    • Oracle Cloud
    • Oracle HCM Cloud
    • Oracle Integration Cloud
    • Oracle Jinitiator
    • Oracle pathfinder
    • Oracle VirtualBox
    • Oracle Weblogic Server
    • Oracle decode
    • Oracle Exadata
    • Oracle ZFS
    • Oracle? utilities
    • JDBC Driver for Oracle
    • Oracle? DBA Versions
    • Oracle DBA Salary
  • Basic
    • Oracle Marketing Cloud
    • What is Oracle?
    • Career in Oracle
    • How to Install Oracle
    • Oracle Versions
    • What Is Oracle Database
    • Oracle Data Warehousing
    • Oracle Warehouse Builder
    • Career In Oracle Database Administrator
    • Career In Oracle DBA
    • What is Oracle RAC
    • Oracle DBA
    • Oracle? Vanderbilt
    • What is RMAN Oracle
    • Oracle Database Administration
    • Oracle Operators
    • Oracle Constraints
    • Oracle number
    • Oracle Data Types
    • Oracle UNIQUE Constraint
    • Oracle Check Constraint
  • Joins
    • Joins in Oracle
    • Inner Join in Oracle
    • Oracle Cross Join
    • Left Join in Oracle
    • OUTER Join in Oracle
    • Oracle Full Outer Join
    • Natural Join in Oracle
    • Oracle Self Join
    • Oracle hash join
    • Oracle? Update with Join
  • Oracle SET Operators
    • UNION in Oracle
    • Oracle UNION ALL
    • INTERSECT in Oracle
    • MINUS in Oracle
  • Interview Questions
    • Oracle Interview Questions
    • Oracle Apps Interview Questions
    • Oracle Apps Technical Interview Questions
    • Oracle Database Interview Questions
    • Oracle Forms Interview Questions
    • Oracle PL/SQL Interview Questions
    • Oracle RAC Interview Questions
    • Oracle SOA Interview Questions

Related Courses

Oracle Course Training

Oracle DBA Certification Course

MongoDB Certification Training

Home Data Science Data Science Tutorials Oracle Tutorial Table in Oracle

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (85,938 ratings)
  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.

table in Oracle

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.

Add data

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.

table in Oracle

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.

table in Oracle

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.

DELETE FROM

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
Popular Course in this category
Oracle Training (14 Courses, 8+ Projects)
  14 Online Courses |  8 Hands-on Projects |  120+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

Oracle DBA Database Management System Training (2 Courses)4.9
All in One Financial Analyst Bundle- 250+ Courses, 40+ Projects4.8
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

Special Offer - Oracle Training (14 Courses, 8+ Projects) Learn More