Tables are used to store data in the database. Tables are uniquely named within a database and schema. Each table contains one or more columns. And each column has an associated data type that defines the kind of data it can store e.g., numbers, strings, or temporal data.

The SQL CREATE TABLE Statement

SQL provides the CREATE TABLE statement to create a new table in a given database. An SQL query to create a table must define the structure of a table. The structure consists of the name of a table and names of columns in the table with each column's data type. Note that each table must be uniquely named in a database.

Syntax

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

CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( one or more columns )
);

Here are the key points-

• 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.
• The column parameters (e.g. column1, column2, column3, etc.) specify the names of the columns of the table.
• The datatype parameter specifies the type of data the column can hold (e.g. integer, varchar, string, etc.).
• PRIMARY KEY constraint uniquely identifies each record in a table. Primary keys must contain UNIQUE values, and cannot contain NULL values.

Example: Creating Table in SQL

CREATE TABLE Employee(
   E_ID          INT NOT NULL,
   E_NAME        VARCHAR (20) NOT NULL,
   E_AGE         INT NOT NULL,
   E_ADDRESS     CHAR (25),
   E_SALARY      DECIMAL (18, 2),
   PRIMARY KEY (E_ID)
);

Here are the key points-

• The following code block is an example, which creates a Employee table with column name E_ID, E_NAME, E_AGE, E_ADDRESS and, E_SALARY and E_ID as a primary key.
• NOT NULL are the constraints showing that these fields cannot be NULL while creating records in this table.

Verification

Once your table is created, you can check if it has been created successfully or not. You can use SQL DESC table_name command to list down the description of the table as follows:

DESC Employee;

This will display the structure of the table created: column names, their respective data types, constraints (if any) etc.

Now, you have Employee table available in your database which you can use to store the required information related to Employees

SQL CREATE TABLE IF NOT EXISTS

Consider a situation where you will try to create a table which already exists, in such situation MySQL will throw the following error.

ERROR 1050 (42S01): Table 'Employee' already exists

So to avoid such error we can use SQL command CREATE TABLE IF NOT EXISTS to create a table.

Syntax

Following is the basic syntax of a CREATE TABLE IF NOT EXISTS statement -

CREATE TABLE IF NOT EXISTS table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( one or more columns )
);

Example: Creating Table if Not Exists

The following SQL command will create the Employee table only when there is no table exists with the same name otherwise it will exit without any error.

CREATE TABLE IF NOT EXISTS Employee(
   E_ID          INT NOT NULL,
   E_NAME        VARCHAR (20) NOT NULL,
   E_AGE         INT NOT NULL,
   E_SALARY      DECIMAL (18, 2),
   PRIMARY KEY (E_ID)
);

Creating a Table from an Existing Table

Instead of creating a new table every time, one can also copy an existing table and its contents including its structure, into a new table. This can be done using a combination of the CREATE TABLE statement and the SELECT statement. Since its structure is copied, the new table will have the same column definitions as the original table. Further more, the new table would be populated using the existing values from the old table.

Syntax

The basic syntax for creating a table from another table is as follows -

CREATE TABLE NEW_TABLE_NAME AS
SELECT [column1, column2...columnN]
FROM EXISTING_TABLE_NAME
WHERE Condition;

Here, column1, column2... are the fields of the existing table and the same would be used to create fields of the new table.

Example: Creating Table from an Existing Table

Following is an example, which would create a table SALARY using the Employee and having the fields Employee ID and Employee SALARY -

CREATE TABLE SALARY AS
SELECT ID, SALARY
FROM Employee;

This will create a new table SALARY which will have the following structure -