SQL-How to Create Table in Oracle?

A table in a database is the most basic unit. It stores rows of data. It can be seen as a collection of rows and columns. The columns are the fields under which we wish to save data. Rows are the records that are created by assigning values to the columns. A table usually stores data for a single entity.  A table can store data about customers, employees, clients, events, students or any other entity. In this tutorial we will learn how to create table in Oracle.

How to Create Table in Oracle- Syntax of the Command

CREATE TABLE table-name
(
column-name1 Datatype (length) [DEFAULT Value/Expression][ Column Constraint],
column-name2 Datatype (length) [DEFAULT Value/Expression][ Column Constraint],
column-name3 Datatype (length) [DEFAULT Value/Expression][ Column Constraint],
……
column-nameN Datatype (length) [DEFAULT Value/Expression][ Column Constraint]
[Table Level Constraint]
);

All the words written in uppercase are the keywords.  Oracle is NOT CASE SENSITIVE for the keywords.

Datatype– it is the kind of data you wish to store in a column like string, numeric, date etc. The basic and very commonly used data types are

DataType Type of values to be stored
VARCHAR2(number of characters) String
DATE Date and Time
NUMBER(number of digits, digits after decimal) Numeric data
CHAR(length) Character data

Table-name and Column-names are the names you wish to give to the table and its columns. The names must follow these rules

  • Names can begin only with a alphabet and can have a length up to 30 characters
  • Alphabets, numeric (0-9), _(underscore), $ and # are allowed
  • Names of same objects must be unique. No two tables or two columns of a table can have same names

DEFAULT is the value that you want the column to store while inserting a new row if the INSERT query does not provide  a value for a specific column. For example if in the CustomerTbl table if you want that when no city name is given in the INSERT query, the city column must store value ‘New York’. It can be done by setting the default value of column city as ‘New York’ The default value must be of data type that of the column with which it is defined.

How to Create Table in Oracle- Examples

In the following example we have create three table

SalesTbl- for Sales Person Details

CREATE TABLE SalesTbl
(
SPID number(6),
SPNAME varchar2(30),
SPCITY varchar2(30) DEFAULT 'New York',
SPCOMM number(2,2)
);

CustomerTbl- for Customer Details

 CREATE TABLE CustomerTbl
(
CustID number(4),
CustName varchar2(50),
CustCity varchar2(30),
CustPhone varchar2(15),
CustCreditScore number(4)
);

OrdersTbl- for Order details

 CREATE TABLE OrdersTbl
(OrderID number(4),
CustID number(4),
SPID number(6),
SaleAMT number(8,2),
OrderDate date,
DeliDate   date
);

 

In this tutorial we discussed about how to create tables in oracle. You can copy these CREATE commands and run in Oracle Live SQL by logging in with your Oracle Account details.

The next discussion is about how to define constraints on table to preserve data consistency.