SQL CREATE TABLE Statement

-- create a table Companies with name, id, address, email, and phone number
CREATE TABLE Companies (
  id int,
  name varchar(50),
  address text,
  email varchar(50),
  phone varchar(10)
);

CREATE TABLE Syntax

The syntax of the SQL CREATE TABLE statement is:

CREATE TABLE table_name(
column1 data type, column2 data type, ...
);

Here,

• table_name is the name of the table
• column1, column2, ... are the names of the columns in the table
• data type is the column's data type (can be an integer, string, date, etc.)

Example: CREATE SQL Table

-- create a table Students with name, id, address, grades, and phone number
CREATE TABLE Students(
  id int,
  name varchar(50),
  address text,
  grades  varchar(50),
  phone varchar(10)
);

Here, int, varchar(50), and text are data types that tell what data could be stored in that field. Some commonly used data types are as follows:

Note: We must provide data types for each column while creating a table. Learn more about SQL Data Types.

CREATE TABLE IF NOT EXISTS

You will get an error if you create a table with the same name as an existing table. To fix this issue, we can add the optional IF NOT EXISTS command while creating a table.

For example,

-- create a Companies table if it does not exist
CREATE TABLE IF NOT EXISTS Companies (
  id int,
  name varchar(50),
  address text,
  email varchar(50),
  phone varchar(10)
);

Here, the SQL command will only create the Companies table if there is no table with the same name.

CREATE TABLE AS

We can also create a table using records from any other existing table using the CREATE TABLE AS command. For example,

-- create a table by copying those records
-- from Costomers table with country name USA
CREATE TABLE USACustomers
AS (
  SELECT *
  FROM Customers
  WHERE country = 'USA'
);

Here, the SQL command creates a table named USACustomers and copies the records of the nested query into the new table.

Recommended Readings

• SQL Views
• SQL CREATE DATABASE