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|
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 );
The next discussion is about how to define constraints on table to preserve data consistency.