Oracle SQL Create Table Example

Oracle SQL Create Table Example | In Oracle SQL, the CREATE command is used to create database objects like table, view, synonym, index, and e.t.c. To create a new table in the Oracle database we must use the CREATE TABLE statement. Tables contain columns and constraints, rules to which data must conform.

Create Table Query in Oracle

Create table syntax in Oracle:-

CREATE TABLE tablename
(
   columnname1 datatype(size),
   columnname2 datatype(size),
   …
);

The create table syntax start with CREATE TABLE followed by table name then specify the column name, its datatype with size (if applicable). For more than one column they should be separated with comma(,). Each valid Oracle SQL statement should end with a semicolon(;).

Oracle Create Table Example

Let us create a table STUDENT with column names SNO and NAME with NUMBER and VARCHAR2 datatypes respectively.

CREATE TABLE student
(
  sno NUMBER(10),
  name VARCHAR2(10)
);

To view the structure of the table we are using the DESC command. It is a SQL *Plus tool-specific command i.e. only works in the SQL *Plus tool. The DESC command gives information about the table like column names, its data type, and whether the column has the NOT NULL constraint or not.

DESC student;

Output:-

Name        Null?    Type
----------- -------- ------------
SNO                  NUMBER(10)
NAME                 VARCHAR2(10)

To display the table data we must use the SELECT query, it will display all column names and their data.

SELECT * FROM student;

Output:-

no rows selected

Since we only created the table that’s why it is empty. To insert data into detail we must use the INSERT statement which is discussed in detail in the INSERT statement. One simple example is given here.

INSERT INTO student VALUES(1010, 'OLIVIA');
INSERT INTO student VALUES(1011, 'EMMA');
SELECT * FROM student;

Output:-

       SNO NAME
---------- ----------
      1010 OLIVIA
      1011 EMMA

Create a Table in Another Schema

In Order to create a table in the Oracle database, you must have CREATE TABLE system privilege in your schema, and CREATE ANY TABLE system privilege to create a new table in another user’s schema.

Syntax to create table in another user’s schema:-

CREATE TABLE schema_name.tablename
(
  columnname1 datatype(size),
  columnname2 datatype(size),
  ...
);

Table with Constraints

We can also apply constraints on the columns of the table, there are five constraints in Oracle that are discussed in detail in the Oracle constraints topic. Tables contain columns and constraints, rules to which data must conform.

Create table syntax with a constraint:-

CREATE TABLE tablename
(
  columnname1 datatype(size) column_constraint,
  columnname2 datatype(size) column_constraint,
  ...
  table_constraint
);

Important Points

Point to remember while creating a table in the Oracle database:-

  • A table can have a minimum of one column.
  • The maximum number of columns allowed in an Oracle table:- prior to Oracle 7 it can have a max of 254 columns per table but Oracle 8i, 9i, 10g, 11g, 12c, 18c, 19c can have a maximum of 1000 columns per table.

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Leave a Comment

Your email address will not be published. Required fields are marked *