How to create table in Oracle

In this Oracle tutorial, we will learn about how to create table in oracle database 19c. Also, we will cover these topics.

• Oracle Create Table
• Oracle Create Table Syntax 19c
• Oracle create table from another table
• Oracle Create Table with Sequence Primary Key
• Oracle create a tablespace
• Oracle create a table with another schema
• Oracle create a table with primary key
• Oracle create a temp table
• Oracle create a partitioned table
• Oracle create a temporary table with stored procedure
• Oracle create a duplicate table
• Oracle create a temporary tablespace
• Oracle Create Table with Foreign Key
• Oracle Create Table with Constraint
• Oracle Create Table Default Timestamp
• Oracle Create Table Default Value Sysdate
• Oracle Create Table Auto_Increment Primary Key
• Oracle Create Table with Insert Data
• Oracle Create Table Backup
• Oracle Create Table comment
• Oracle Create Table if not exists
• Oracle Create Table as Select without Data
• Oracle Create Table Enable
• Oracle Create Table Float

Note: Ideally we should create a table on the Pluggable database (PDBs) But to keep things simple we have demonstrated it on a common user (c##).

Oracle Create Table

In this section, you will learn how to create a table in the oracle database.

• There are two ways of creating table in oracle database:
  • Using SQL developer tool
  • Using Command-line

Create Oracle Table Using SQL developer tool

In this section, we will learn how to create an oracle table using the SQL developer tool.

• SQL developer is a GUI tool for creating databases and tables in oracle database.
• Learn to install SQL developer tool on windows 10 operating system.
• Click on the plus icon on the top left side as marked in the picture below.
• Enter any name to represent this connection and provide the username and password of the user to which you want to connect.
• Provide the SID as orcl, Incase you have created a differenc sid then provide that.
• Test the connection if successful then click on connect button.

• Once connection is established successfully you will be able to see an interface like below.
• On the left side expand the connected database and right click on the Tables option.
• Click on the New Table option.

• Create table wizard will appear with the schema name as the username provided by you in first step.
• Provide the table name, in our case it is LIFE_EXPECTANCY.
• Start createing the table by filling in the information as demonstrated in the below picture.
• Use ‘+’ icon on the right to add new columns for the table and incase of mistake use ‘x’ icon to delete the column from oracle database.
• Click on the ok button after providing all the necessary information. Table will be created successfully.

• Once the table is created successfully, expand the Tables (filtered) option and double click on the table name.

So in this way, we can create an oracle table using the SQL developer tool. Moving forward we will learn how to create an oracle table using the command line in the oracle database.

Read Oracle Database vs MySQL Database

Create Oracle Table Using Command-line

• Using CREATE TABLE statement in oracle sqlplus command-line we can create a table in the database. The basic syntax to CREATE TABLE the statement is given below:

CREATE TABLE schema_name.table_name (
 column_name1 data_type column_constraint,
 column_name2 data_type column_constraint,
 ......
);

In this syntax:

• Use Create Table keyword followed by schema and table name.
• Second, list all columns names with paranthesis. In case you want to create multiple columns you need to separate them by commas(,). A column definition is by its column name by data types like varchar,float,integers, etc and column constraints such as check, primary key,not null.
• Third, try to apply table constraints if applicable primary key, foreign key, check.
• Please note that you must have CREATE TABLE system privilege to create a new table under a new schema.
• You can use CREATE ANY TABLE system privilege to create a new table under another user’s schema.
• The owner of the new table must quote a tablespace that contains a new table or UNLIMITED TABLESPACE system privilege.

Oracle CREATE TABLE statement example:

The following examples show how to create a new TABLE named life_expectancy under OT schema:

CREATE TABLE c##ot.life_expectancy(
countries_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
countries varchar(100) not null,
Year date not null,
Average_age_of_life float not null,
primary key(countries_id)
);

• In this table life_expectancy table are four columns:
  • countries_id,
  • countries,
  • year
  • Average_age_of_life.
• The countries_id is identity column that identifies the column in the table.
• The data type of the column is number and GENERATED BY DEFAULT AS IDENTITY a clause is used to generate a new identity every time a new row is generated or inserted in the table.
• The countries columns contain datatype varchar2 that has maximum length of 100.
• It means that you cannot give the name of the countries column which contains a length of more than 50.
• The not null column constraint prevents the countries column to have null values.
• The year columns contain date constraints like "YYYY-MM-DD" or vice versa or just put YEAR value.
• Average_age_of_life columns contain the same characteristics as countries column but there is a small difference which uses FLOAT constraints.
• The primary key the clause specifies the countries_id column as the primary key column which is used for identifying unique rows in the column.

Read Number Datatype in Oracle Database

Oracle Create Table Syntax 19c

In this section, will learn about oracle create table syntax for oracle versions 19c & 12c.

• Oracle Create Table syntax is common in most of the databases like PostgreSQL, MySQL, SQL, etc. and it works same with all the versions of Oracle database.
• Oracle database version 19c, 12x, 11g, etc have the same format of creating a table.
• So the sysntax discussed in this section can be followed on all the version of oracle database.
• The syntax is divided into three parts:
  • Create Table Keyword
  • Table name
  • column names and their data types
  • Constraints (optional)

CREATE TABLE <schema_name>.<table_name>(
col_1 [data type] [constraints],
col_2 [data type] [constraints],
col_3 [data type] [constraints],
col_n [data type] [constraints]);

Read: Oracle Database Datatype

Oracle Create a Table from Another Table

In this section, we will learn about oracle creating a table from another table in the oracle database.

• Using Create Table <table name> As statement in oracle database we can create a table from another table.
• We can either use the entire columns from the other table or can select few column(s) in oracle database.
• Suppose there is a dog table that holds various attributes of dogs like breed, color of eye & hair, type of food they like, etc.
• So these columns or attributes can be used for other animals as well like for cat as cats also have breed, eye color, food types, etc.
• In that case using As Select statement in oracle database you can create new table with all or selective columns.
• In our example, we will demonstrate the three ways of creating a table from another table in oracle:
  • creating a table from another table with all the columns
  • creating a table from another table with selective columns
  • creating a table from multiple other tables
• For demonstration purposes we have created the dog table below and using this table we will create a cat table in further sub-sections using As Select in the oracle database.

Script:

-- CREATE SEQUENCE
CREATE SEQUENCE DOG_SEQ START WITH 1;

-- CREATE TABLE
CREATE TABLE DOG(
    SERIAL INTEGER DEFAULT DOG_SEQ.nextval PRIMARY KEY,
    BREED VARCHAR2(50),
    MAXAGE INTEGER,
    EYECOLOR VARCHAR2(20),
    HAIRTYPE VARCHAR2(200),
    "HUMANFRIENDLY(T/F)" CHAR(1),
    "TRAINABLE(T/F)" CHAR(1),
    "FOOD(VEG/NVEG/OMNI)" VARCHAR2(50)
);

-- INSERT DATA
INSERT INTO DOG 
(BREED, MAXAGE, EYECOLOR, HAIRTYPE, "HUMANFRIENDLY(T/F)", "TRAINABLE(T/F)", "FOOD(VEG/NVEG/OMNI)")
VALUES('AKITA', 15, 'DARK BROWN', 'short topcoat and dense undercoat','T', 'T', 'OMNI: RICE, FISH, SEA PLANT');

INSERT INTO DOG 
(BREED, MAXAGE, EYECOLOR, HAIRTYPE, "HUMANFRIENDLY(T/F)", "TRAINABLE(T/F)", "FOOD(VEG/NVEG/OMNI)")
VALUES('BULLDOG', 10, 'DARK BLACK/BROWN', 'straight, short, fine textured, smooth and glossy','T', 'T', 'omni: banana, lamb, fish, beef');

INSERT INTO DOG 
(BREED, MAXAGE, EYECOLOR, HAIRTYPE, "HUMANFRIENDLY(T/F)", "TRAINABLE(T/F)", "FOOD(VEG/NVEG/OMNI)")
VALUES('BULLDOG', 10, 'DARK BLACK/BROWN', 'straight, short, fine textured, smooth and glossy','T', 'T', 'omni: banana, lamb, fish, beef');

-- VIEW DATA
SELECT * FROM DOG;

-- DESCRIBE TABLE
DESC DOG;

Description of the dog table created after running the above script

Read: Oracle Create Index

Create a Table from Another Table with all the Columns

In this section, we will learn how to create a table from another table with all the columns in the oracle database.

• Using Oracle create table with As Select statement we can copy or duplicate the table columns and rows in oracle database 19c.
• Mention the name of schema with table name if you copying it from other table of different user.

Syntax:

In the below code, we have used the ‘As Select’ statement to create a table from another table. To fetch all the columns and data we have used ‘*‘ with a select statement.

Create Table <table_name> As
(Select * from <table_name>);

Example:

Here is an example to duplicate a table in the oracle database using As Select statement.

• In this example, we will create cat table using dog table mentioned above.
• Alternative name for this method is oracle clone table because we create exact replica of ther table.

Script:

-- CREATE TABLE
CREATE TABLE CAT AS 
    (SELECT * FROM SQLSERVERGUIDES.DOG);

-- DESCRIBE TABLE 
DESC CAT;

-- VIEW DATA
SELECT * FROM cat;

Here is the output of the above script, the description of the table is exactly the same as the dog table above. Even all the dog data will also be cloned here inside the cat table.

Read Connect to Oracle Database using Python

Create a Table from Another Table with Selective Columns

In this section, we will learn how to create a table from another table with selective columns in the oracle database.

• Using As Select statement in oracle database we can create a table from another table with selective columns in oracle database.
• In the select statament provide the name of columns that you want to clone.
• Column name you are providing in the new table must be present in the table from where you are creating this new table.
• Please provide the schema name followed by table name incase tables are from different users.

Syntax:

In this syntax, (col1, col2 .. coln ) are the column names that we want to clone from another table.

Create Table <schema>.<new_table_name> As
(Select col1, col2, .. coln from <schema>.<other_table_name>

Example:

In this example, we have created a cat table using a few columns from the dog table mentioned above.

-- CREATE TABLE
CREATE TABLE CAT AS 
(SELECT dog.Serial, dog.breed, dog.maxage FROM SQLSERVERGUIDES.DOG);

-- DESCRIBE TABLE 
DESC CAT;

--VIEW DATA
SELECT * FROM CAT;

Output:

In this output, a cat table is created with the selective columns in the oracle database. Data of those columns are also copied from the source table.

Create a table from multiple other tables

In this section, we will learn how to create a table from multiple other tables in the oracle database.

• Using Oracle create table As Select statament we can create a table from multiple other tables.
• Two or more table name needs to pass in the select statement to fetch information from the tables.
• You can also fetch selective columns from the multiple tables. All you need to do is instead of using ‘*’ use the names of columns.
• We have created a new table ‘mammals’ . Using this table and dog table we will demonstrate this section.
• Here is the script to create a table mammals in oracle database.

-- CREATE TABLE
CREATE TABLE MAMMALS(
    ANIMAL_NAME VARCHAR2(200),
    BREED VARCHAR2(50),
    SOUND VARCHAR2(50),
    ORIGIN_COUNTRY VARCHAR2(50)
);

-- INSERT DATA
INSERT INTO MAMMALS
VALUES('DOG', 'AKITA', 'BARK', 'JAPAN');

INSERT INTO MAMMALS
VALUES('Puma concolor', 'Felinae', 'ROAR', 'UNITED STATES OF AMERICA');

-- VIEW DATA
SELECT * FROM MAMMALS;

Syntax:

• This syntax shows two methods, in the first method we are creating a table from multiple tables and we are copying all the columns and data of those tables.
• In the second method, we are copying only selective columns from multiple tables.
• Please note that you need to specify the schema name followed by table name.

# All columns
Create Table <Table_name> As
(Select * from table_name1, table_name2 .. table_name_n);

# few Columns
Create Table <Table_name> As
(Select <table_name1.col1>, <table_name2.col1> from <table_name1> <table_name2> .. <table_name_n>);

Example:

In this script, using oracle create table as select statement we have created a new table with the name cat which has all the columns and data of dog and mammal table.

-- CREATE TABLE
CREATE TABLE CAT AS 
(SELECT * FROM SQLSERVERGUIDES.DOG, SQLSERVERGUIDES.MAMMALS);

-- DESCRIBE TABLE 
DESC CAT;

--VIEW DATA
SELECT * FROM CAT;

Read How to create a database in Oracle 19c

Oracle Create Table with Sequence Primary Key

In this section, we will learn about the oracle create table with sequence primary key.

• Sequence in oracle database is used to auto-generate the value in the given range and type.
• We can create sequence primary key in two ways in oracle database:
  • Oracle create sequence primary key using command-line
  • Oracle create sequence primary key using SQL developer tool (GUI).

Oracle creates sequence primary key using SQL developer tool (GUI).

Right-click on the Table -> Trigger -> Create (PK from Sequence)… -> Provide the trigger name in the dialog box -> Select the available sequence name -> Select column on which you want to apply sequence primary key -> Click on Apply.

Once you have followed the above picture’s steps then you will see the below wizard. Provide trigger name, select sequence name from the drop-down, select the column name on which you want to apply this setting. Click on apply button.

Oracle create sequence primary key using command-line

In this section, we will learn how to create a sequence primary key using a command-line oracle database.

CREATE SEQUENCE emp_id
MINVALUE 1
MAXVALUE 999999999999999
START WITH 1
INCREMENT BY 1
NOCACHE; 

• Parameter Description:
  • MINVALUE: minimum value that for the sequence
  • MAXVALUE: Maximum till where Sequence can go.
  • START WITH: Starting point of Sequence.
  • INCREMENT BY: increment as per the provided value
  • NOCACHE : If you want to speed up the accessing speed then set cache <value>

Oracle Create a Tablespace

A tablespace is a storage location where the actual data datatype underlying database objects can be kept. In layman’s language, where tables get stored. It links physical storage layers like(files on disks) and logical storage layers like (tables, indexes).

The following illustration shows how to create a tablespace named tb1 with the size of 1Mb:

CREATE TABLESPACE tb1
DATAFILE 'tb1_data.dbf'
size 1m;

This statement shows:

• Firstly, specify the tablespace name after CREATE TABLESPACE keyword. In this above example, the tablespace name is tb1.
• Secondly, specifty the datafile name after the keyword DATAFILE clause. In this case, the datafile name is tb1_data.dbf as datafile full path.
• Third, specify the size of the data file under the tablespace keyword as SIZE clause.In this example, 1m stands for 1mb file which is quite really small version file size.

Once the database files are created, you can find the information by querying data from the tb1_data_files view:

SELECT TABLESPACE_NAME,FILENAME,BYTES /1024/1024 MB FROM dba_data_files;

Here are all the tablespaces created in the database:

The CREATE TABLESPACE is quite complex with many options.

Read How to Fetch Data from Oracle Database in Python

Oracle Create a Table with Another Schema

The only way to allow a non-DBA user is to create a table with another schema is to give the user the CREATE ANY TABLE system privilege.

The following command shows how to create a table from one user to another user:

# login as sys user
sqlplus / as sysdba;

# username: c##smithj password: smith
CREATE USER c##smithj identifed by smith;

# grant permission
grant create session,create table to c##smithj;
alter user c##smithj quota unlimited on users;

# username: c##romej  password: sql
CREATE USER c##romej identifed by sql;

# grant permission
grant session,create table to c##romej;
alter user c##romej quota unlimited on users;

# table name: life_expectancy
Create table c##smithj.life_expectancy(
   country_id number generated by default as identity,
   country varchar(100) not null,
   Year date not null,
   Average_age_of_life float not null,
   primary key(country_id));

# connect to c##romej user
conn c##romej/sql;

# create a table & grant all permission
create table romej_table(a char);
grant all on romej_table to c##smithj;

# connect to c##smithj user
conn c##smithj/smith

# create table from another table
create table country_life_expectancy as select * from c##romej.sqlplus_table;

Read Connect Excel to Oracle Database

Oracle Create a Table with Primary Key

The primary key is the column that uniquely identifies each row in the table.

• There are certain rules of primary key in oracle database:
  • Primary key in oracle database can’t contain a null value or an empty string.
  • Primary key in oracle database must be unique within entire table.
  • Primary key in oracle database can’t be changed over time.
• According to these rules, there are certain recommendations to follow primary key:
• First, primary key should be meaningless. If the primary key will be meaningful then you probably need it. For example, If you created phone number as primary key then this piece of information you may need later and you want to change it or perform other opertations on it. so this should be avoided.
• Primary key should be something that you solely use only for identifying a record.
• Second, the primary keys should be compact. The primary keys are typically numeric and Oracle posses number faster than any other data type

The following CREATE TABLE statement creates the USA_healthcare table:

CREATE TABLE USA_HEALTHCARE(
patient_id number generated by default as identity, 
health_Camp_id number not null,
outcome float not null,
primary key(patient_id));

• The USA_healthcare the table has four columns:
  • patient’s number (patient_id),
  • health camper id as (health_camp_id),
  • outcome as (outcome)
• This table is defined by the patient_id column as the primary key in oracle database.
• Primary key column can’t be empty so there is no need for ‘Not Null’ constraint.

Read How to Check Oracle Database Version

Oracle Create a Temp Table

• Oracle create temp table is defined as an object where data is stored on a disk and after a particular session or transaction, that data is deleted.
• These tables are generally embedded in the SQL program in Oracle sessions.
• There are two types of temporary tables:
  • global temporary table
  • private temporary table.
• To create a global temporary table, we need to add a global keyword.
• Here is the syntax of the global temporary table in oracle database:

CREATE GLOBAL TEMPORARY TABLE TABLE_NAME(
column1_datatype [constraint],
column2_datatype [constraint],
...,
);

• Parameters Description:
  • table_name refers to temporary table that users is creating.
  • column1, column2 refers to name of column.
  • constraints refers to column constraints which we will add or may not.

• Here is an example of a global keyword in oracle database. The query contains ON COMMIT DELETE which means, the table will be deleted after every transaction.

CREATE GLOBAL TEMPORARY TABLE temp_table(
id number primary key,
description varchar2(20)),
on commit delete rows;

• To preserve the data in a temperory table for enitire session use ON COMMIT PRESERVE ROWS. Here is the example for the same.

CREATE GLOBAL TEMPORARY TABLE temp_table(
id number primary key,
description varchar2(20)),
on commit preserve rows;

Read How to Get List all Tables in Oracle Database

Oracle Create a Partitioned Table

• To handle Very Large Databases (VLDB) of enterprises Oracle provides a new feature called partition which is used to create a partition in a table i.e: Suppose you have a table containing millions of records like 1991,1992,1993,1994 and 1995. But you are worried about one year then you will give queries in the following :

SELECT PRODUCT,SUM(AMT) from Sales where year=1992 
group by product;

• While using this query under the search table, then this will make a performance issue where you use partition this table according to year, then the performance is improved and oracle will scan a single partition, not the whole table.

CREATE TABLE under_armour(
year number (4),
product varchar2(10),
product_amount number(10,2))
partitioned by range (year)
partitioned p1 values less than (1992) tablespace u1,
partitioned p2 values less than (1993) tablespace u2;

• There are mainly two ways using which we can perform partitioning of a table in oracle database:
  • Range Partitioning
  • Hash Partitioning
  • List Partitioning
  • Compsite Partitioning

1. Range Partitioning

Range partitioning in an oracle database allows objects to be partitioned by a specific range of partitioned keys. In this case, you can create partition by month, quarter, or year using a column by DATE field.

2. Hash Partitioning

Hash Partitioning in an oracle database is a method of separating rows and spreading them in sub-tables within the database. For this spreading out, hash keys are used effectively and efficiently. The following example shows how to create a partition table.

CREATE TABLE Life_expectancy(
part_no number,
decription varchar(60))
Partition by Hash(part_no)
Partitions 4
Store In (tab1, tab2, tab3, tab4);

3. List Partitioning

List partitioning in an oracle database enables you to explicit control how rows map to partitions by specifying a list of discrete values for the partitioning key in the description for each partition. It allows unordered and unrelated sets of data to be grouped and organized together very naturally.

The following examples use list partitioning to create a table:

CREATE TABLE CUSTOMERS (
customer_code number(5),
name varchar2(20),
city varchar2(20),
balance number))
Partition by list(city),
Partition north_india values('DELHI', 'CHANDIGARH'),
Partition west_india values('BOMBAY','GOA');

4. Composite Partitioning

Composite partitioning in an oracle database uses the range method to partition table and within each partition, subpartition uses the hash method. When you are creating a composite partition, you specify the following:

• Partition method: range
• Partitioning columns
• Partition description defines partition bounds
• Subpartition method:hash
• Subpartition columns(s)
• No of subpartitions for each partitions

The following methods tell how to create a composite partition. In this example, three range partitions are created, each containing eight subpartitions.

CREATE TABLE PRODUCTS(
Part_no number,
description varchar(25)),
partition by range(Part_no),
Subpartition by hash(description)
Subpartition 8 store in (tab_1, tab_2,tab_3,tab_4)
(Partition p1 value less than (100),
 Partition p2 values less than (MAXVALUE));

Oracle Create a Temporary Table in Stored Procedure

• Oracle has a global temporary table that has objects whose data are stored in disk and are deleted with the end of the session or transaction.
• It is visible to all sessions currently connected to the database. To create a temporary table, you must have to CREATE TEMPORARY TABLE clause used at the end of the query.
• The following example show to create a temporary table in the stored procedure.

CREATE global temporary table my_medicare
(coumn_1 number) on commit delete rows
tablespace temporary_ts;

Oracle Create a Temporary Tablespace

Use of create temporary tablespace to create locally managed temporary tablespace and it is allocation of space in the database that contains schema objects for the duration of the session.
Note:

• Specify in bytes the size of tablespace extents. Use K or M to specify the size in kilobytes and megabytes.
• The temp_tablespace_extent clause let you specify how the tablespace is managed.
• All extents of temporary tablespace are the same size(uniform), so this keyowrd is optional. However, you must specify UNIFORM in order to specify size.

Creating a Temporary Tablespace: Example

CREATE TEMPORARY TABLESPACE temp_demo
tempfile 'temp01.ddf' size 5M AUTOEXTEND ON;

Oracle Create a Duplicate Table

The copy command will be obsoleted in the future part of SQL plus. No new data types will be supported by COPY. Duplicate/ Copy the command is used to copy data from one table to another table. Copy supports the following data types which are given below:

• CHAR
• DATE
• LONG
• NUMBER
• VARCHAR2

The syntax of copy command:

COPY FROM database TO database action
destination_table (column1_name,column2_name,
USING query

Examples: The following command copies the entire employees table to a table named as westemployees.The tables are located in two different databases. If westemployees table already existed, the SQL plus already replace the table and its contents.

COPY FROM HR@HQ TO JOHN@WEST 
REPLACE WESTEMPLOYEES
USING SELECT * FROM EMPLOYEES;

Oracle Create Table Data types

In this section, we will learn about oracle create table data types. We will understand what are data types and will learn to use data types in an oracle database.

• Data types in oracle determines the number of characters that can be passed as a value in a particular column.
• Every column in oracle database has a data type assigned and each data type occupies some space on the drive.
• Experienced database developers wisely choose the data types so that only that much space is used which is required.
• Each data type has range which can be crosschecked with the requirement of column and accordingly data type can be assinged in the database.
• In our example, we have created an employee table inside the company schema. Here, we have used Integer, varchar2, and float data types.
• Integer stores only numbers without any decimals, Varchar2 stores string values and float stores number(s) with decimals in oracle database.

Create table company.employee(
emp_id INTEGER,
emp_name VARCHAR2(20),
emp_dept VARCHAR2(20),
emp_sal FLOAT
)

Oracle Create Table with Foreign Key

In this section, we will learn about oracle create table with a foreign keys. We will understand what is a foreign key and how to create it in the oracle database.

• If somone from Australia visits United States of America then he/she is considered to be a foreigner in United States of America. Similarly when primary key of one table is used in another table than that key is referred as foreign key in that table.
• In the above picture emp_id & sal_id are the primary keys of their respective tables.
• But emp_id is common in both the tables because emp_id is acting as a foreign key in second table.
• You cannot assign a value to emp_id that is not present in the table one already. for example, you cannot provide salary to employee with id E108. Because this employee doesn’t exist in table one.

Syntax:

Here is the syntax to apply foreign keys on the salary table in the oracle database.

• In this syntax, We have used constraint

ALTER TABLE Salary
ADD Constraint emp_ID FOREIGN KEY(EMP_ID)
REFERENCES Employees(EMP_ID) on delete set null;

Here is the implementation on SQL developer tool.

• Incase, Emp_ID already has E108 in salary table before applying the foreign key constrainst in that case program automatically set that value to null that is why we have added on delete set null.
• Alternatively, we also apply on delete cascade, this will delete the value that is not present in the main table i.e. employe table in this case.
• Oracle won’t allow to save the data if either of them (on delete cascade or on delete set null) is not applied and there is a data that is not present in the main table.

Oracle Create Table with Constraint

In this tutorial, we will learn about oracle create table with constraint(s). Constraints are the limitation or restrictions added to the column.

• Oracle constraints are the rules that we can apply on the type of data in the table in oracle database.
• Few popular constraints are Not Null, Primary Key, foreign Key, Composite Key, etc.
• Constraints are optional to apply and you can apply more than one constraint on a column also constraints can be applied later on using Alter Table statement.
• Here is the syntax of CREATE TABLE with constraints in oracle database.

CREATE TABLE <table_name>(
col_1 data_type <constraint>
col_2 data_type <constraint>
col_n data_type <constraint>);

• In our example, we have created a table Airbnb which has data of Airbnb in New York city, Unites States of America.
• We have used three constraints in our example Primary Key, Not Null and Unique.
  • Primary key will identity each row of that column uniquely also that column can’t be empty.
  • Not Null column can’t remain empty user has to provide some input.
  • Column with unique constaint can’t have duplicate data. To create table with unique constraint add ‘UNIQUE’ keyword after the data type in oracle database.

Script:

In this script, we have created a table with constraints in the oracle database. The table has information about Airbnbs in New York City of the United States of America (USA).

CREATE TABLE Airbnb(
id numeric(20) Primary Key,
PropertyName VARCHAR2(50) UNIQUE,
host_name VARCHAR2(20) NOT NULL,
neighbourhood VARCHAR2(50),
latitude NUMERIC UNIQUE,
longitude NUMERIC UNIQUE,
room_type VARCHAR2(20),
"Price($)" NUMERIC
);

Description of Script:

• Primary key uniquely defines each row of ID column.
• Property name of same company can’t be same so we have applied unique constraint on it.
• Property owner or hostname is required otherwise whom to contact. That is why we have used not null constraint.
• Latitude and longitude defines the geological location of property. Each property must be at different location that is why we have applied unique constaint on the location.

Before we end this section, we would like to discuss a few constraints in detail with syntax and examples.

Oracle Create Table with Unique Constraint

In this section, we will learn about the oracle create table with unique constraints in the oracle database.

• Unique constraint uniquley defines each row in the column. you cannot put duplicate data if the column has unique constraint.
• Unique constraint allows entry of null values in the column and this is the major difference betwen unique and primary key constraints.
• In a table, you can apply unique constraints on maximum 32 columns in oracle database.
• Also, you apply unique key to the columns of same table in oracle database.
• While insert data in oracle database if you getting the below error that means you are entering value that is already present in that column of the table. Here sqlserverguides is the name of schema, this may appear different in your device.

Error report -
ORA-00001: unique constraint (SQLSERVERGUIDES.SYS_C007439) violated

• Here is the syntax of oracle create table with unique constraints.

CREATE TABLE <table_name> (
col_1 data_type UNIQUE );

Example:

In this example, we have created a table with a username column having a unique constraint. This means a user has to fill in a unique name for the username.

-- CREATE TABLE 
CREATE TABLE uname(
    username varchar2(50) UNIQUE
);

-- INSERT DATA
INSERT INTO UNAME
VALUES('JAMES');

INSERT INTO UNAME
VALUES('');

INSERT INTO UNAME
VALUES('james');

INSERT INTO UNAME
VALUES('JAMES');


-- VIEW TABLE
SELECT * FROM UNAME;;

Output:

This is the output of the above script, you can notice that we have inserted the value 4 times in the table but only three values have been stored.

• First value is JAMES all in uppercase, second is null, third value is james all in lowercase, the forth value was same as first value that is why error appeared.

Oracle Create table with Check Constraints

In this section, we will learn about oracle create constraint check in oracle database.

• Check constraint in oracle database allows implementation of condition on a each row of the column(s).
• If you want to restrict user from certain kind of inputs then using oracle check constraint you can apply condition.
• Suppose there is a database for an event whose tickets are available only to certain age groups.

--CREATE TABLE
CREATE TABLE kids_event(
kid_name varchar2(50),
kid_age INTEGER CHECK (kid_age BETWEEN 3 and 9));

--INSERT DATA
INSERT INTO kids_event VALUES('Tony', 4);

-- AGE 3 YEARS
INSERT INTO kids_event VALUES('Jonathan', 3);

-- AGE 9 YEARS
INSERT INTO kids_event VALUES('william', 9);

-- AGE BELOW 3
INSERT INTO kids_event VALUES('Jonny', 2);

-- AGE ABOVE 9
INSERT INTO kids_event VALUES('James', 10);

-- VIEW DATA
Select * from kids_event;

Output:

In this output, two error messages appeared one is when we enter a value less than 3 and the second error appeared when age is greater than 9.

Oracle Create Table Constraint Primary Key

In this section, we will learn about oracle create table constraint primary key constraint in oracle database.

• Primary key uniquely identifies each row in a table. Also, it has unique not null values.
• Primary key column has no other purpose apart from identifiying each row. It is preferred to create a column as primary key which is not in use.
• for example, if you will set a primary key on column that is in use then you may have to alter it’s value later that will hamper the principles of primary key.
• To create a table with primary key constraints in oracle database use the keyword primary key after defining the datat type of that column.
• Please note that primary key can’t be empty and this feature seperates it from unique key constraint.
• Here is the syntax of primary key constraint in oracle database.

CREATE TABLE <table_name>(
serial_no INTEGER Primary Key);

Example:

Here, is an example to demonstrate oracle create table constraint primary key. In this example, we have created a payroll table with primary key constraints added to emp_id. We have inserted data in acceptable and non-acceptable ways for demonstration purposes.

-- CREATE TABLE
CREATE TABLE PAYROLL(
EMP_ID INTEGER PRIMARY KEY,
EMP_NAME VARCHAR2(20),
EMP_SAL NUMERIC(7,2)
);

-- INSERT TABLE
INSERT INTO PAYROLL VALUES (101, 'JAMES', 56000.00);
INSERT INTO PAYROLL VALUES (102, 'THOMAS', 76000.00);

-- INSERT DUPLICATE VALUE 
INSERT INTO PAYROLL VALUES (101, 'JONATHAN', 86000.00);

-- INSERT NULL VALUE
INSERT INTO PAYROLL (EMP_NAME, EMP_SAL) VALUES ('JAMES', 56000.00);

-- VIEW TABLE
SELECT * FROM PAYROLL;

Output:

Here is the output of the above script in the oracle database. In this output, an error is displayed for duplicate and null values.

Oracle Create Table Composite Primary Key

In this section, we will learn about oracle create table composite primary key constraint in oracle database.

• When two or more primary keys are combined together in a table then it is called composite key.
• Composite keys are also called aggregate keys, concatenated keys and composite unique key(s).
• composite key can be called as primary key which is made of multiple columns.
• Composite key can contain maximum 16 columns in an oracle database.
• In the below example, emp_id and dept_id can be used to uniquely identify each row in the table. So the combination of these two primary table will be called as composite key.

create table payroll(
emp_id NUMERIC,
emp_name VARCHAR2(30),
dept_id NUMERIC,
constraint payroll_pk primary key(dept_id,loc_id));

In this last section, we have created a primary key in a different way. So the method is to type the CONSTRAINT keyword followed by naming convention (table_name_pk) then the name of the constraint. for example, in this case, we have written primary key but you can use any constraints and in the end name of the columns in the bracket separated by comma(s).

Oracle Create Table Default Timestamp

In this section, we will learn about oracle create table default timestamp in the oracle database.

• This section is all about automatically generating date and time in oracle database.
• Oracle TIMESTAMP data type offers a feature CURRENT_TIMESTAMP using which we can fetch the system date and time in oracle database.
• In our example, we have created a logs table wherein user can provide the name of the activity and date and time will automatically fetched from system.

-- CREATE TABLE
CREATE TABLE LOGS(
ACTIVITY VARCHAR2(20),
ACT_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- INSERT DATA ONLY IN ACTIVITY COLUMN
INSERT INTO LOGS(ACTIVITY) 
VALUES ('BACKUP');

-- VIEW DATA
SELECT * FROM LOGS;

Oracle Create Table Default Value Sysdate

In this section, we will learn about oracle create table default value sysdate in oracle database.

• Oracle Data data type provides sysdate option using which we can fetch system date and store it in the database.
• Set sysdate as default value for date column in oracle database and each the program will be exeucted it will automatically fetch the date from the database.
• In our example, we have added date column in the Logs table. User will provide the name of the activity rest of the columns will fill automatically.

-- CREATE TABLE
CREATE TABLE LOGS(
ACTIVITY VARCHAR2(20),
ACT_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
ACT_DATE DATE DEFAULT sysdate
);

-- INSERT DATA
INSERT INTO LOGS(ACTIVITY) 
VALUES ('BACKUP');

INSERT INTO LOGS(ACTIVITY) 
VALUES ('maintenance');

INSERT INTO LOGS(ACTIVITY) 
VALUES ('BACKUP');

-- VIEW DATA
SELECT * FROM LOGS;

In this output, the ACT_DATE column has been added which is keeping a record of the current date in the oracle database.

Oracle Create Table Auto_Increment Primary Key

In this section, we will learn about oracle create table auto_increment primary key in the oracle database.

• Auto_increment allows automatic generation of sequence of values so that user don’t have to enter an extra column also it helps in avoiding confusion created while recalling the previous number.
• Each time new record is created a primary is automatically generated with the increment in the previous value. for example, if previous ID was 101 then it will generate 102.
• Using a Trigger function SEQUENCE we can add auto_increment feature in primary key.
• Auto_Increment on Primary key can be applied in two ways:
  • Oracle create table Auto_Increment Primary Key using SQL developer tool (GUI)
  • Oracle create table Auto_Increment Primary Key using Command

Oracle create table Auto_Increment Primary Key using SQL developer tool (GUI)

In this section, we will learn to create a table with auto_increment on the primary key using the oracle SQL developer tool.

• First step in the process is to create a Trigger Sequence in oracle database.
• Select a table -> Click on the Triggers tab -> Click on the Edit icon -> Click on Identity Column -> Select Trigger from the drop down -> Select Sequence Schema -> Provide Sequence name -> Click OK.
• You will need sequence name in further steps so please make a note of it.

• Provide the Trigger Name in the below wizard, select the Sequence Name from the drop down menu, selecy Column Name and click on Apply button.

• If the below prompt appeared that means Trigger feed has been created and now you go ahead and insert data in the table. Primary key column will automatically generate a value once you have clicked the commit button.

Oracle create table Auto_Increment Primary Key using Command

In this section, we will learn to create a table with auto_increment primary key using command or SQL statement.

• Creating a sequence is the first step in the process. Sequence will require a name and increment value.
• Next step is create a table and set default feed_seq.nextval here. feed_seq is the name of the sequence and nextval is the function that will increment the value.
• After that you can provide any data type like Not Null, primary key, etc.
• In the below example, we have demonstrated everything we explained in this section. Please pay attention, while inserting the data we have not speicified the FID value becuase it will be automatically generated.

-- CREATE SEQUENCE
CREATE SEQUENCE feed_seq START WITH 1;

-- CREATE TABLE with SEQUENCE
CREATE TABLE FEEDBACK(
    FID  NUMERIC DEFAULT feed_seq.nextval PRIMARY KEY,
    EMAIL VARCHAR2(50),
    MESSAGE VARCHAR2(50)
);

-- INSERT DATA
INSERT INTO FEEDBACK (EMAIL, MESSAGE) VALUES ('jamesW@aol.com', 'problem solved, thanyou.');
INSERT INTO FEEDBACK (EMAIL, MESSAGE) VALUES ('emaW@aol.com', 'Problem not solved');
INSERT INTO FEEDBACK (EMAIL, MESSAGE) VALUES ('thomasW@aol.com', 'poor service.');

-- VIEW DATA
SELECT * FROM FEEDBACK;

In this output, you can notice that we have not inserted value(s) for FID but still, in the output, it has automatically appeared.

Oracle Create Table with Insert Data

In this section, we will learn about oracle create table with insert data.

• Data can be created used CREATE TABLE statement in oracle database and using INSERT statement we can insert the data inside the table in oracle database.
• In our example, we have created a table: Airbnb which has dataset of all the Airbnbs in New Your City, United States of America. We have used the dataset from kaggle.

Script:

This script is divided into 3 parts -: Create Table, Insert Data, View data. You copy the entire code and paste it into your editor.

-- CREATE TABLE
CREATE TABLE Airbnb(
    pid numeric(20) Primary Key,
    PropertyName VARCHAR2(500) UNIQUE,
    host_name VARCHAR2(100) NOT NULL,
    neighbourhood VARCHAR2(100),
    latitude FLOAT UNIQUE,
    longitude FLOAT UNIQUE,
    room_type VARCHAR2(100),
    "Price($)" FLOAT
);

-- INSERT DATA
INSERT INTO Airbnb 
VALUES(2539,'Clean and quiet apt home by the park','John','Brooklyn',40.64749,-73.97237,'Private room',149);

INSERT INTO Airbnb
VALUES(2595,'Skylit Midtown Castle','Jennifer','Midtown',40.75362,-73.98377,'Entire home/apt',225);

INSERT INTO Airbnb
VALUES(3647, 'THE VILLAGE OF HARLEM, NEW YORK !','Elisabeth','Harlem',40.80902,-73.9419,'Private room',150);

-- VIEW DATA
SELECT * FROM AIRBNB;

Output:

Oracle Create Table Backup

In this section, we will learn how to create table backup in oracle database.

• Backup refers to creating or storing another copy of database so it can be used incase of loss or damage of current working database.
• Backup in oracle is a huge concept that requires seperate blog but for now here will show you how to create table backup in oracle database.
• There are two major ways of taking backup of the table in oracle database:
  • Using As Select statment
  • Using exp on command-line

Oracle Create Table Backup using As Select Statement

In this section, we will learn how to create table backup using As Select statement in oracle database.

• Using As Select statement in oracle database we can create a new table which will act like a back up of the current table.
• We have this table below with the name DOG that we want to backup using As Select statement in oracle database.

-- CREATE SEQUENCE
CREATE SEQUENCE DOG_SEQ START WITH 1;

-- CREATE TABLE
CREATE TABLE DOG(
    SERIAL INTEGER DEFAULT DOG_SEQ.nextval PRIMARY KEY,
    BREED VARCHAR2(50),
    MAXAGE INTEGER,
    EYECOLOR VARCHAR2(20),
    HAIRTYPE VARCHAR2(200),
    "HUMANFRIENDLY(T/F)" CHAR(1),
    "TRAINABLE(T/F)" CHAR(1),
    "FOOD(VEG/NVEG/OMNI)" VARCHAR2(50)
);

-- INSERT DATA
INSERT INTO DOG 
(BREED, MAXAGE, EYECOLOR, HAIRTYPE, "HUMANFRIENDLY(T/F)", "TRAINABLE(T/F)", "FOOD(VEG/NVEG/OMNI)")
VALUES('AKITA', 15, 'DARK BROWN', 'short topcoat and dense undercoat','T', 'T', 'OMNI: RICE, FISH, SEA PLANT');

INSERT INTO DOG 
(BREED, MAXAGE, EYECOLOR, HAIRTYPE, "HUMANFRIENDLY(T/F)", "TRAINABLE(T/F)", "FOOD(VEG/NVEG/OMNI)")
VALUES('BULLDOG', 10, 'DARK BLACK/BROWN', 'straight, short, fine textured, smooth and glossy','T', 'T', 'omni: banana, lamb, fish, beef');

INSERT INTO DOG 
(BREED, MAXAGE, EYECOLOR, HAIRTYPE, "HUMANFRIENDLY(T/F)", "TRAINABLE(T/F)", "FOOD(VEG/NVEG/OMNI)")
VALUES('BULLDOG', 10, 'DARK BLACK/BROWN', 'straight, short, fine textured, smooth and glossy','T', 'T', 'omni: banana, lamb, fish, beef');

-- VIEW DATA
SELECT * FROM DOG;

• In the below script, we have created a new table with the name ‘back_up’ which holds all the information (columns and rows) of dog table in oracle database.
• We have used As Select statement to create a backup table in oracle database.

-- CREATE TABLE
CREATE TABLE BACK_UP AS(
    SELECT * FROM SQLSERVERGUIDES.DOG
);

-- DESCRIBE TABLE
DESC BACK_UP;

-- VIEW TABLE
SELECT * FROM BACK_UP;

Oracle Create Table Backup using exp on command-line

In this section, we will learn how to create table backup using exp on command-line in oracle database.

• Using exp command on command prompt on windows operating system you can create a backup of the table.

exp userid=sqlserverguides/root@pdb1 tables=airbnb file=airbnb.dmp

• If all the information is correct then backup file with extension dmp will be created inside the app data in windows operating system.
• To save file in a specific location , mention the location here: file = C:\custom_location\airbnb.dmp

Oracle Create Table Based on CSV File

In this section, we will learn about the oracle create table based on CSV file in the oracle database.

• Click to Download the sample csv file used in this section to demonstrate oracle create table based on csv file.
• We are using oracle sql developer tool to import data csv data in oracle database.
• Inorder to create table based on csv file in oracle it is mandatory that file has header, header shouldn’t have spaces, underscores are acceptable.
• Please note that steps followed in this section are applicable for Oracle Create Table From Excel aswell.
• Right click on the Table object and select Import data in sql developer tool.

• From the Data import wizard select the csv file by clicking on the browse button. click Next to proceed.

• Provide the table name and adjust the Row Limit as per the rows in the csv file. Since we had rows less than 100 so didn’t changed the default value.

• Remove the column(s) that you don’t want to include in the oracle table. Double click on the column name to move to the left window.
• Any column on the left side will be exlcuded from the table.

• In the column definition window, if any column has red warning sign that means it won’t allow you to proceed further.
• Common errors are if the csv file don’t have header then there must be a value ready to become a header for the table. In that case exit it and had header in the csv file then try again.
• Other problem could be there might be spaces between column names. Double click on the column and the column information will be displayed on the right side. change the name from there and problem will be solved.
• Click on the Next button to proceed.

• Here is the finaly summary of the table. It has all the infomation related to database like database connection name, source file location, etc.
• If you want to make any changes then this is the time. Once you clicked on the finish button. Table will be created.

• If you are not getting this kind of prompt with this message then please check the error message or post it in the comment section. We will share the solution shortly.

• Here is the Oracle Create Table Based on CSV File.

Oracle Create Table comment

In this section, we will learn about the oracle create table with comments. We will learn what are comments and how to put them in the oracle database.

• Comments are the message dropped in the source code that assist other programmer.
• Oracle ignores the comment which means comments do not appear in the output of the oracle script.
• Comments are of two types:
  • Multiline comment(s)
  • single line comment(s)
• Multiline comment can be applied using /* */ anything between them will be treated as comment and won’t appear in the output.
• Single line comment can be performed using -- anything after two consecutive hyphens will be treated as comment in that row or line.
• Here is the quick demonstartion of both type of comments in oracle database.

-- Single line comment
-- SELECT * FROM AIRBNB;

/*
multiple line comment
SELECT * FROM AIRBNB
WHERE PRICE < 100;
*/

• In the below output, you can see that lines have been greyed out. It is because they are comments. first two lines shows singe line comment and remaining shows multiple line comments.

• Other than this, if you want to put the comments inside the exsiting table than it can be done using the following commands.

-- Comment on entire table
COMMENT ON TABLE <table_name> IS '<comment here>';

-- Comment on column of table
COMMENT ON COLUMN <table_name>.<column_name> IS '<comment here>';

Oracle Create Table if not exists

In this section, we will learn about oracle create table if not exist.

• While creating a table if the table already exists then oracle throws an error message:

Error report -
ORA-00955: name is already used by an existing object
00955. 00000 -  "name is already used by an existing object"
*Cause:    
*Action:

• Oracle do not support If Not Exists statment as SQL and MySQL do. This statement checks if the table already exists in the database. If ‘No’ then only creates the table otherwise skips.
• In Oracle, we can add exception to the code where in if the error code ‘ORA-00955 ‘ appeared then program won’t cretae the table in the database.
• Alternatively, we can use drop command before table creation command in oracle database. This way, it will delete the exisiting table if any and then create a new one.
• Incase, there is no table then this program will throw error code: ‘ORA-00942′. By adding exception for this code we can perform oracle create table if not exisits.
• Exception can be added using DDL that uses PL/SQL, so in other words, you have to write exception in PL/SQL.
• Here is an example, to demonstrate oracle create table if not exists. Copy and paste the below code to sqlplus command-line in oracle database.

Source Code:

In this source code, we have created a store procedure that will create a family table. If-Else ladder we have checked and printed message whether a table exists or created.

CREATE OR REPLACE PROCEDURE p_create_family
AS
   l_cnt  NUMBER;
BEGIN
   SELECT COUNT (*)
     INTO l_cnt
     FROM user_tables
    WHERE table_name = 'FAMILY';
  
  IF l_cnt = 0
  THEN
     EXECUTE IMMEDIATE 'CREATE TABLE family
                          (
                             f_id             INTEGER PRIMARY KEY,
                             father_name      VARCHAR (20),
                             mother_name      VARCHAR (20),
                             total_brothers   INTEGER DEFAULT 0 NOT NULL,
                             total_sisters    INTEGER DEFAULT 0 NOT NULL,
                             friends          INTEGER DEFAULT 0 NOT NULL,
                             total_members    INTEGER DEFAULT 0 NOT NULL
                          )';
 
     DBMS_OUTPUT.put_line ('Table created');
  ELSE
     DBMS_OUTPUT.put_line ('Table already exists');
  END IF;
END;
/

Oracle Create Table as Select without Data

In this section, we will learn about oracle create table as select without data.

• Unlike other databases oracle do not support the use of “with no data” keyword.
• Instead of that we can simple pass the condition where 1=0. Using this condition, you can create table as select without data in oracle database.
• Here is the representation of complete code in oracle database.

CREATE TABLE CAT AS 
(SELECT * FROM SQLSERVERGUIDES.DOG where 1=0);

Oracle Create Table Enable

In this section, we will learn about ENABLE option used while creating a table in the oracle database.

• While creating a constraints in oracle table we can specify the functionality status of that constraint.
• By default functionality is set to Enable for all the constraints which means they are activte and user has to follow rules defined by that constraint.
• In some cases, developers have to disable the constraints or a trigger is applied that if the particular condition is satisfied then only constraints will be activated or enabled.
• While creating table using GUI mode, you see checkbox next to constraint option. At that time, in the backend the constraints are applied but are disabled and as soon as you click on the checkbox they are set to enabled.
• This was the easiest example we can think of to demonstrate Oracle create table enable.
• Here is the syntax for oracle create table enable in oracle database.

CREATE TABLE <Table_Name>(
    col_1 [Data_Type] <constraints> [ENABLE/DISABLE]
    col_2 [Data_Type] <constraints> [ENABLE/DISABLE]
    col_3 [Data_Type1] <constraints> [ENABLE/DISABLE]
);

Example of oracle create table enable:

In our example, we have created a table ‘cheesecake_factory’ where we have applied Enabled and Disabled the constraints in the oracle database.

-- CREATE SEQUENCE
CREATE SEQUENCE ccf_seq START WITH 100;

--CREATE TABLE
CREATE TABLE cheesecake_factory(
    store_id INTEGER DEFAULT ccf_seq.nextval PRIMARY KEY,
    ename VARCHAR2(100) NOT NULL ENABLE,
    edept VARCHAR2(100) NOT NULL ENABLE,
    emanager VARCHAR2(100) NOT NULL DISABLE,
    egen char(1)
);

-- INSERT DATA
INSERT INTO cheesecake_factory (ename, edept, emanager, egen)
VALUES ('Liam', 'Production', 'Benjamin', 'M');

INSERT INTO cheesecake_factory (ename, edept, emanager, egen)
VALUES ('Emma', 'Sales', 'Olivia', 'F');

INSERT INTO cheesecake_factory (ename, edept, emanager, egen)
VALUES ('Noah', 'Human Resource', 'Charlotte', 'M');

-- emanager value is missing 
INSERT INTO cheesecake_factory (ename, edept, egen)
VALUES ('William', 'Security', 'M');

-- VIEW DATA
SELECT * FROM cheesecake_factory;

Output:

In this output, we have demonstrated the oracle create table enable statement. By default, constraints are enabled. Emanager column was set to not null but constraints were disabled that is why that column has allowed null value.

Oracle Create Table Float

In this section, we will learn about oracle create table float in oracle database.

• Float data type in oracle database is used to represent the value with decimals.
• It accepts precision as input and automatically picks the scale from the data. In other words, you don’t need to mention scale parameter but while inserting data you can provide values in decimals.
• Using FLOAT keyword in the place of data type you can create float datatype in oracle database.
• Here is the syntax of oracle create table float in oracle database.

CREATE TABLE <table_name>(
    col1 FLOAT [constraints],
    col2 FLOAT [constraints]
);

• In our example, we have used dataset: Best Cities for Startups from Kaggle. As per this dataset San Francisco Bay in United States is the best city for startups.
• There are four columns which has float data type in oracle database (total score, quatity score, quality score, business score).

Script:

• Copy the entire code and paste it into your editor and run it to execute all the queries.
• We have used create table statement to create a table with the name startup_cities.
• Four columns have data type as FLOAT which means user can insert number with decimals.
• Using Insert statement six rows has been inserted in the startup_cities table.
• In the end, using select statement we have displayed all the data of startup_cities table.

-- CREATE TABLE
CREATE TABLE STARTUP_CITIES(
    RANK INTEGER, 
    city VARCHAR2(100), 
    country VARCHAR2(100), 
    total_score FLOAT, 
    quatity_score FLOAT, 
    quality_score FLOAT, 
    business_score FLOAT
);

-- INSERT DATA
INSERT INTO STARTUP_CITIES 
VALUES(1, 'San Francisco Bay', 'United States of America', 328.966, 29.14, 296.02, 3.8);

INSERT INTO STARTUP_CITIES
VALUES(2, 'New York', 'United States of America',110.777, 11.43, 95.55, 3.8);

INSERT INTO STARTUP_CITIES
VALUES(3, 'Beijing', 'China', 66.049, 5.01, 58.61, 2.43);

INSERT INTO STARTUP_CITIES
VALUES(4, 'Los Angeles Area', 'United States', 58.441, 11.23, 43.41, 3.8);

INSERT INTO STARTUP_CITIES
VALUES(5, 'London', 'United Kingdom', 56.913, 15.77, 37.44, 3.7);

INSERT INTO STARTUP_CITIES
VALUES(6, 'Boston Area', 'United States of America', 49.835, 5.5, 40.53, 3.8);

COMMIT;

-- VIEW DATA
SELECT * FROM STARTUP_CITIES;

Output:

• In this output, the top six cities and their countries for startups have been displayed.
• United States of America is leading with four cities (Sans Franscisco, New York, Los Angeles, Boston) followed by china and United Kingdom.
• Total Score is the sum of quality, business and quatity columns. Using Float data type the scores are displayed in decimals.

In this tutorial, we have learned about oracle creating table statements in the oracle database 19c. Also, we have covered these topics.

• Oracle Create Table
• Oracle Create Table Syntax 19c
• Oracle create table from another table
• Oracle Create Table with Sequence Primary Key
• Oracle create a tablespace
• Oracle create a table with another schema
• Oracle create a table with primary key
• Oracle create a temp table
• Oracle create a partitioned table
• Oracle create a temporary table with stored procedure
• Oracle create a duplicate table
• Oracle create a temporary tablespace
• Oracle Create Table with Foreign Key
• Oracle Create Table with Constraint
• Oracle Create Table Default Timestamp
• Oracle Create Table Default Value Sysdate
• Oracle Create Table Auto_Increment Primary Key
• Oracle Create Table with Insert Data
• Oracle Create Table Backup
• Oracle Create Table comment
• Oracle Create Table if not exists
• Oracle Create Table as Select without Data
• Oracle Create Table Enable
• Oracle Create Table Float