SQL CREATE TABLE

CREATE TABLE command creates a new table in the database in SQL. In this article, we will learn about CREATE TABLE in SQL with examples and syntax.

SQL CREATE TABLE Statement

SQL CREATE TABLE Statement is used to create a new table in a database. Users can define the table structure by specifying the column’s name and data type in the CREATE TABLE command.

This statement also allows to create table with constraints, that define the rules for the table. Users can create tables in SQL and insert data at the time of table creation.

Syntax

To create a table in SQL, use this CREATE TABLE syntax:

CREATE table table_name
(
Column1 datatype (size),
column2 datatype (size),
.
.
columnN datatype(size)
);

Here table_name is name of the table, column is the name of column

SQL CREATE TABLE Example

Let’s look at some examples of CREATE TABLE command in SQL and see how to create table in SQL.

CREATE TABLE EMPLOYEE Example

In this example, we will create table in SQL with primary key, named “EMPLOYEE”.

CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Department VARCHAR(50),
    Salary DECIMAL(10, 2)
);

CREATE TABLE in SQL and Insert Data

In this example, we will create a new table and insert data into it.

Let us create a table to store data of Customers, so the table name is Customer, Columns are Name, Country, age, phone, and so on.

CREATE TABLE Customer(
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(50),
    LastName VARCHAR(50),
    Country VARCHAR(50),
    Age INT CHECK (Age >= 0 AND Age <= 99),
    Phone int(10)
);

Output:

Create Customer Table

To add data to the table, we use INSERT INTO command, the syntax is as shown below:

Query:

INSERT INTO table_name (column1, column2, …) VALUES (value1, value2, …);

Example Query

This query will add data in the table named Subject

INSERT INTO Customer (CustomerID, CustomerName, LastName, Country, Age, Phone)
VALUES (1, 'Shubham', 'Thakur', 'India','23','xxxxxxxxxx'),
       (2, 'Aman ', 'Chopra', 'Australia','21','xxxxxxxxxx'),
       (3, 'Naveen', 'Tulasi', 'Sri lanka','24','xxxxxxxxxx'),
       (4, 'Aditya', 'Arpan', 'Austria','21','xxxxxxxxxx'),
       (5, 'Nishant. Salchichas S.A.', 'Jain', 'Spain','22','xxxxxxxxxx');

Output:

Insert Data

Create Table From Another Table

We can also use CREATE TABLE to create a copy of an existing table. In the new table, it gets the exact column definition all columns or specific columns can be selected.

If an existing table was used to create a new table, by default the new table would be populated with the existing values from the old table.

Syntax:

CREATE TABLE new_table_name AS
    SELECT column1, column2,…
    FROM existing_table_name
    WHERE ….;

Query:

CREATE TABLE SubTable AS
SELECT CustomerID, CustomerName
FROM customer;

Output:

Create Customer Sub Table

Note

We can use * instead of column name to copy whole table to another table.

Important Points About SQL CREATE TABLE Statement

CREATE TABLE statement is used to create new table in a database.
It defines the structure of table including name and datatype of columns.
The DESC table_name; command can be used to display the structure of the created table
We can also add constraint to table like NOT NULL, UNIQUE, CHECK, and DEFAULT.
If you try to create a table that already exists, MySQL will throw an error. To avoid this, you can use the CREATE TABLE IF NOT EXISTS syntax.