Creating Tables in SQL: Step-by-Step Guide

Table is a collection of data, organized in terms of rows and columns. In DBMS term, table is known as relation and row as tuple. Table is the simple form of data storage. A table is also considered as a convenient representation of relations. A table has a specified number of columns, but can have any number of rows.

The SQL CREATE TABLE Statement

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

Syntax

The following SQL statement is syntax for CREATE TABLE.

  CREATE TABLE table_name(
     column1 datatype,
     column2 datatype,
     column3 datatype,
     .....
     columnN datatype
    );

CREATE TABLE is the keyword telling the database system what you want to do. In this case, you want to create a new table. The unique name or identifier for the table follows the CREATE TABLE statement.

Then in brackets comes the list defining each column in the table and what sort of data type it is. The syntax becomes clearer with the following example.

A copy of an existing table can be created using a combination of the CREATE TABLE statement and the SELECT statement. 

The column parameters specify the names of the columns of the table.

The datatype parameter specifies the type of data the column can hold (e.g. varchar, integer, date, etc.).

Example

This a example SQL statement for the above syntax.

The following code block is an example, which creates a Human table with an ID as NOT NULL are the constraints showing that these fields cannot be NULL while creating records in this table ?

  CREATE TABLE Human(
     ID   INT              NOT NULL,
     FIRST_NAME VARCHAR (20)     NOT NULL,
     LAST_NAME VARCHAR (20)     NOT NULL,
     AGE  INT              NOT NULL,
     ADDRESS  CHAR (25) ,
     SALARY   DECIMAL (18, 2)
  );

After placing the above code it will show you Table created. statement.

Example From SQL PLUS screen

  SQL> CREATE TABLE Human(
    2     ID   INT              NOT NULL,
    3     FIRST_NAME VARCHAR (20)     NOT NULL,
    4     LAST_NAME VARCHAR (20)     NOT NULL,
    5     AGE  INT              NOT NULL,
    6     ADDRESS  CHAR (25) ,
    7     SALARY   DECIMAL (18, 2)
    8  );

  Table created.

  SQL>

You can verify if your table has been created successfully by looking at the message displayed by the SQL server, otherwise you can use the DESC command as follows ?

  SQL> DESC Human;
   Name                                      Null?    Type
   ----------------------------------------- -------- ----------------------------
   ID                                        NOT NULL NUMBER(38)
   FIRST_NAME                                NOT NULL VARCHAR2(20)
   LAST_NAME                                 NOT NULL VARCHAR2(20)
   AGE                                       NOT NULL NUMBER(38)
   ADDRESS                                            CHAR(25)
   SALARY                                             NUMBER(18,2)

  SQL>

Remember: table name should be always unique. Means no two table have same name. table name should be always different

Another Example with primary key

Syntax

The following SQL statement is syntax for CREATE TABLE with primary key.

  CREATE TABLE Human(
     ID   INT              NOT NULL,
     FIRST_NAME VARCHAR (20)     NOT NULL,
     LAST_NAME VARCHAR (20)     NOT NULL,
     AGE  INT              NOT NULL,
     ADDRESS  CHAR (25) ,
     SALARY   DECIMAL (18, 2),
     PRIMARY KEY (ID)
  );

Example

This a example SQL statement for the above syntax.

  SQL> CREATE TABLE Human(
    2     ID   INT              NOT NULL,
    3     FIRST_NAME VARCHAR (20)     NOT NULL,
    4     LAST_NAME VARCHAR (20)     NOT NULL,
    5     AGE  INT              NOT NULL,
    6     ADDRESS  CHAR (25) ,
    7     SALARY   DECIMAL (18, 2),
    8     PRIMARY KEY (ID)
    9  );

  Table created.

  SQL>

You can verify if your table has been created successfully by looking at the message displayed by the SQL server, otherwise you can use the DESC command as follows ?

SQL> DESC Human;
  

Some Another Examples

Example 1: CREATE TABLE Persons

   PERSONID                                           NUMBER(38)
   FIRSTNAME                                          VARCHAR2(255)
   LASTNAME                                           VARCHAR2(255)
   ADDRESS                                            VARCHAR2(255)
   CITY                                               VARCHAR2(255)
  

SQL Syntax

    CREATE TABLE Persons (
        PersonID int,
        FirstName varchar(255),
        LastName varchar(255),
        Address varchar(255),
        City varchar(255)
    );
  

Example 2: CREATE TABLE Client_master_21

   CLIENT_NO                                 NOT NULL VARCHAR2(6)
   NAME                                      NOT NULL VARCHAR2(20)
   ADDRESS1                                           VARCHAR2(30)
   ADDRESS2                                           VARCHAR2(30)
   CITY                                      NOT NULL VARCHAR2(15)
   PINCODE                                   NOT NULL NUMBER(6)
   STATE                                     NOT NULL VARCHAR2(15)
   BAL_DUE                                   NOT NULL NUMBER(10,2)
  

SQL Syntax

    CREATE TABLE Client_master_21(client_no varchar2(6) not null,
    name varchar2(20) not null,
    address1 varchar2(30),
    address2 varchar2(30),
    city varchar2(15) not null,
    pincode number(6) not null,
    state varchar2(15) not null,
    bal_due number(10,2) not null
    );
  

Example 3: CREATE TABLE Product_master_21

   PRODUCT_NO                                NOT NULL VARCHAR2(6)
   DESCRIPTION                               NOT NULL VARCHAR2(15)
   PROFIT_PERCENT                            NOT NULL NUMBER(4,2)
   UNIT_MEASURE                              NOT NULL VARCHAR2(10)
   QTY_ON_HAND                               NOT NULL NUMBER(8)
   RECODE_LVL                                NOT NULL NUMBER(8)
   SELL_PRICE                                NOT NULL NUMBER(8,2)
   COST_PRICE                                NOT NULL NUMBER(8,2)
  

SQL Syntax

    CREATE TABLE Product_master_21( product_no varchar2(6) not null,
    description varchar2(15) not null,
    profit_percent number(4,2) not null,
    unit_measure varchar2(10) not null,
    qty_on_hand number(8) not null,
    recode_lvl number(8) not null,
    sell_price number(8,2) not null,
    cost_price number(8,2) not null
    );
  

Example 4: CREATE TABLE Salesman_master_21

 SALESMAN_NO                                        VARCHAR2(6)
 SALESMAN_NAME                             NOT NULL VARCHAR2(20)
 ADDRESS1                                  NOT NULL VARCHAR2(30)
 ADDRESS2                                  NOT NULL VARCHAR2(30)
 CITY                                      NOT NULL VARCHAR2(20)
 PINCODE                                   NOT NULL NUMBER(6)
 STATE                                     NOT NULL VARCHAR2(15)
 SAL_AMT                                   NOT NULL NUMBER(8,2)
 TGT_TO_GET                                NOT NULL NUMBER(6,2)
 YTD_SALES                                 NOT NULL NUMBER(6,2)
 REMARKS                                   NOT NULL VARCHAR2(10)

SQL Syntax

    CREATE TABLE Salesman_master_21( salesman_no varchar2(6),
    salesman_name varchar2(20) not null,
    address1 varchar2(30) not null,
    address2 varchar2(30) not null,
    city varchar2(20) not null,
    pincode number(6) not null,
    state varchar2(15) not null,
    sal_amt number(8,2) not null,
    tgt_to_get number(6,2) not null,
    ytd_sales number(6,2) not null,
    remarks varchar2(10) not null
    );