SQL CREATE TABLE

-- create a table named Companies with different columns

CREATE TABLE Companies (
  id int,
  name varchar(50),
  address text,
  email varchar(50),
  phone varchar(10)
);

SQL CREATE TABLE Syntax

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

Here,

• table_name is name of the table you want to create
• column is the name of a column in the table
• datatype is the type of data that the column can hold (e.g., integer, varchar, date)

Example: SQL CREATE TABLE

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

Here, we created a table named Students with five columns.

The table we created will not contain any data as we have not inserted anything into the table

Here, int, varchar(50), and text specify types of data that could be stored in the respective columns.

Note: We must provide data types for each column while creating a table. To learn more, visit SQL Data Types.

CREATE TABLE IF NOT EXISTS

If we try to create a table that already exists, we get an error message 'Error: table already exists'.

To fix this issue, we can add the optional IF NOT EXISTS command while creating a table.

Let's look at an 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 checks if a table named Companies exists, and if not, it creates a table with specified columns.

Create Table Using Another Existing Table

In SQL, we can create a new table by duplicating an existing table's structure.

Let's look at an example.

-- create a backup table from the existing table Customers

CREATE TABLE CustomersBackup 
    AS 
    SELECT * 
    FROM Customers;

This SQL command creates the new table named CustomersBackup, duplicating the structure of the Customers table.

Note: You can choose to copy all or specific columns.

More on CREATE TABLE

Create a table with a Primary Key

To create a table with a primary key, we can write the following command.

In MySQL

CREATE TABLE Companies (
  id int,
  name varchar(50),
  address text,
  email varchar(50),
  phone varchar(10),
  PRIMARY KEY (id)
);

In Oracle and SQL Server

CREATE TABLE Companies (
  id int NOT NULL PRIMARY KEY,
  name varchar(50),
  address text,
  email varchar(50),
  phone varchar(10)
);

To learn more, visit SQL PRIMARY KEY.

Define constraints while creating a table

We can also add different types of constraints while creating a table. For example,

CREATE TABLE Companies (
  id int NOT NULL,
  name varchar(50) NOT NULL,
  address text,
  email varchar(50) NOT NULL,
  phone varchar(10)
);

Here, the constraint NOT NULL is added to the columns id, name and email. It simply means, these columns can't be empty (NULL).

To learn more, visit SQL Constraints.

Note: Sometimes these constraints are database specific, meaning that these keywords may vary database to database.

Create a new filtered table from an existing table

We can create a new table by extracting specific rows from an existing table based on certain criteria. We can make use of SELECT and WHERE clauses. For example,

CREATE TABLE USACustomers AS 
  SELECT *
  FROM Customers
  WHERE country = 'USA';

Output

Here, we created a new table USACustomers with all the customer data from the USA.

Also Read

• SQL Views
• SQL CREATE DATABASE
• SQL INSERT INTO