SQL - Create Table in the Database

The CREATE statements are used to create the database structures like table, view, sequence, function, procedure, package, trigger, etc. We will go and explore all of these database structures in the later part of the tutorials.

The CREATE TABLE statement is used to create a new table in the database. The following is the syntax to create a new table in the database.

Syntax:

CREATE TABLE table_name(
    column_name1 data_type [NULL|NOT NULL],
    column_name2 data_type [NULL|NOT NULL],
    ...
);

In the above CREATE TABLE syntax, table_name is the name of the table you want to give, column_name1 is the name of the first column, column_name2 would be the name of the second column, and so on. The data_type is the type of data a column is going to be stored e.g string, integer, datetime, etc. The data types vary from database to database, for example, string datatype in SQL Server is varchar or nvarchar, whereas it is varchar2 in Oracle. So, data types are depend on the database you are using.

Use NULL or NOT NULL constraint to specify whether a column allows NULL values or not. By default, all the columns allow NULL values unless specified NOT NULL. The NULL columns are optional, whereas NOT NULL columns are mandatory while inserting or updating data.

Create Table in SQL Server, MySQL, PostgreSQL, SQLite

The following command will be used to create the Employee table in SQL Server, MySQL, PostgreSQL, SQLite database:

CREATE TABLE Employee(
    EmpId integer, 
    FirstName varchar(20), 
    LastName varchar(20), 
    Email varchar(25), 
    PhoneNo varchar(25), 
    Salary integer
);

Above, Employee is the name of the table, and EmpId, FirstName, LastName, Email, PhoneNo, HireDate, and Salary are the columns. varchar is the string data type with size mentioned in the parenthesis e.g. varchar(20) specifies that the column will store a string upto 20 characters long.

Most of the time, all the tables in the database will have at least one column as a primary key. The following defines a table with a primary key.

The EmpId integer PRIMARY KEY, creates the EmpId column and also defines it as a primary key at the same time.

CREATE TABLE Employee (
    EmpId integer PRIMARY KEY, 
    FirstName varchar(20), 
    LastName varchar(20), 
    Email varchar(25), 
    PhoneNo varchar(25), 
    Salary integer
);

Create Table in Oracle

The following SQL script creates the Employee table in Oracle Database with a primary key:

CREATE TABLE Employee(
    EmpId number PRIMARY KEY,
    FirstName varchar2(20),
    LastName varchar2(20),
    Email varchar2(25),
    PhoneNo varchar2(25),
    Salary number(8)
);

Note that, in Oracle, the string data type is called varchar2, and integer type is called number.

At this stage, the Employee table is empty and does not contain any data. Also, note that we created a table with EmpId column as a Primary Key, meaning EmpId will be Unique and NOT NULL field in all the rows in the Employee table.

The following is the illustration of the Employee table in the database.

Create New Table from Existing Table

The CREATE TABLE AS command is used to create a new table from an existing table with the structure and data, as shown below: The Following queries will work in Oracle, MYSQL, SQLite, and PostgreSQL.

CREATE TABLE Employee_Backup AS SELECT * FROM Employee;

To create a copy of the Employee table, with selected columns and data, use CREATE TABLE AS command, as shown below:

CREATE TABLE TempEmployee as (SELECT EmpId, FirstName, LastName FROM Employee);

To create the copy of the Employee table, with just Structure and NO DATA, use the CREATE TABLE AS command, as shown below:

CREATE TABLE Consultant AS SELECT * FROM Employee WHERE 1=2;

Above, the where clause where 1=2 will not return any records and so the above command only creates a table without data.

Create a Copy of Table in MS SQL Server

The SELECT * INTO command creates a copy of an existing table with structure and data.

SELECT * INTO Employee_backup FROM Employee

Table Design Guidelines

Before creating any table in the database, there is a need to design the tables that what kind of information will be stored in a table. What are columns needed, and what will be the datatypes of columns? What constraints will be applicable on different columns? What constraints should be applied on a table? What will be the relationship between the tables?

Here are some guidelines to consider while designing tables:

• Use abbreviated but meaningful names while defining table and column names.
• The maximum length allowed for a column name is 30 characters, and they should begin with the alphabet.
• Define appropriate Data Types for each column of the table.
• Try to keep only required columns in a table. The less important columns can be kept into some other detail table instead of storing them in the main table.
• Always try to put a column which can contain NULL values. That way, it will save storage space.