Using CREATE TABLE Statement in Oracle

This article is written about how to use the Oracle CREATE TABLE statement with syntax, examples, and exercise exercises.

Description

The Oracle CREATE TABLE declaration permits you to create and outline a table.

Syntax

The syntax for the CREATE TABLE declaration in Oracle/PLSQL is:

CREATE TABLE table_name
( 
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...
  column_n datatype [ NULL | NOT NULL ]
);

Parameters or Arguments

table_name The identify of the desk that you desire to create. column1, column2, … column_n The columns that you want to create in the table. Each column have to have a datatype. The column both be described as “null” or “not null” and if this value is left blank, the database assumes “null” as the default.

Example

Let’s look at an Oracle CREATE TABLE example.

CREATE TABLE customers
( customer_id number(10) NOT NULL,
  customer_name varchar2(50) NOT NULL,
  city varchar2(50)
);

This Oracle CREATE TABLE example creates a desk called customers which has 3 columns.

The first column is called customer_id which is created as a number datatype (maximum 10 digits in length) and can no longer comprise null values. The 2nd column is known as customer_name which is a varchar2 datatype (50 maximum characters in length) and also can now not include null values. The third column is known as town which is a varchar2 datatype however can include null values.

Now the only hassle with this Oracle CREATE TABLE statement is that you have not described a main key for the table. We should modify this CREATE TABLE announcement and define the customer_id as the predominant key as follows:

CREATE TABLE customers
( customer_id number(10) NOT NULL,
  customer_name varchar2(50) NOT NULL,
  city varchar2(50),
  CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);

Learn about primary keys.

Learn about foreign keys.

Practice Exercise #1:

Create an Oracle table called suppliers that stores dealer ID, name, and address information.

Solution for Practice Exercise #1:

The Oracle CREATE TABLE assertion for the suppliers table is:

CREATE TABLE suppliers
( supplier_id number(10) NOT NULL,
  supplier_name varchar2(50) NOT NULL,
  address varchar2(50),
  city varchar2(50),
  state varchar2(25),
  zip_code varchar2(10)
);

Practice Exercise #2:

Create an Oracle table called customers that shops patron ID, name, and address information.

But this time, the patron ID must be the major key for the table.

Solution for Practice Exercise #2:

The Oracle CREATE TABLE statement for the customers table is:

CREATE TABLE customers
( customer_id number(10) NOT NULL,
  customer_name varchar2(50) NOT NULL,
  address varchar2(50),
  city varchar2(50),
  state varchar2(25),
  zip_code varchar2(10),
  CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);

Practice Exercise #3:

Based on the departments desk below, create an Oracle desk known as personnel that stores employee number, worker name, department, and salary information. The important key for the personnel table need to be the employee number. Create a overseas key on the employees desk that references the departments table based on the department_id field.

CREATE TABLE departments
( department_id number(10) NOT NULL,
  department_name varchar2(50) NOT NULL,
  CONSTRAINT departments_pk PRIMARY KEY (department_id)
);

Solution for Practice Exercise #3:

The Oracle CREATE TABLE statement for the personnel table is:

CREATE TABLE employees
( employee_number number(10) NOT NULL,
  employee_name varchar2(50) NOT NULL,
  department_id number(10),
  salary number(6),
  CONSTRAINT employees_pk PRIMARY KEY (employee_number),
  CONSTRAINT fk_departments
    FOREIGN KEY (department_id)
    REFERENCES departments(department_id)
);