Overview

In this Oracle SQL Tutorial, we will learn CREATE TABLE statement. This statement allows you to create a table in the database schema. In this post, we will create the most common type of table which is a relational table in Oracle database.

Syntax

The CREATE TABLE statement only creates the table structure. We need to specify the table name and the columns for the table. Table columns can have different data types like numbers(NUMBER), strings(VARCHAR2), dates(DATE), etc.

Simple Syntax:

CREATE TABLE table_name (
column_list );

Each column definition is separated by a comma. Columns can have constraints that we can specify during the table creation. We will talk about database constraints in upcoming posts.

Once the table is created, we can insert data using the INSERT statement.

Example : dept table

In this example, we will create a department table with the name dept.

CREATE TABLE dept (
DEPTNO NUMBER(4) CONSTRAINT PK_DEPT PRIMARY KEY,
DNAME VARCHAR2(20),
LOC VARCHAR2(20));

 

In this example, the table dept has three columns. We can use the DESC or describe command to know the structure.

 

 

SQL> desc table_name;

deptno -> department number. The deptno column is the Primary key for the table. Primary key column values are unique and uniquely identify the rows in the table.

dname -> department name column

loc -> department location column

Example: emp table

Let’s create another table with more number of columns. The table name is emp. We will reference the primary key of the dept table in this emp table as Foreign key. Foreign key is a primary key of one table that is references in another table. This common column would be used in joining tables together.

CREATE TABLE emp (
empno NUMBER(4) CONSTRAINT pk_emp PRIMARY KEY,
ename VARCHAR2(20),
job VARCHAR2(10),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(4) CONSTRAINT fk_deptno REFERENCES dept);

 

Notice that, we have used UPPERCASE only for keywords and data types. Table, column and constraint names are in lowercase. This will increase the readability of the SQL statement. As a good programming practice we will follow the same standard in SQL statement. Good indentation and breaking the SQL statement into multiple lines also add readability of the code.

If the table is created successfully, SQL Plus displays a ‘Table created’ success message.

—

Oracle Database Tutorials on this website:

https://www.testingdocs.com/oracle-database-tutorials-for-beginners/

More information about Oracle Database:

https://www.oracle.com/database/