SQL Tutorial SQL Advanced SQL Database SQL References

SQL CREATE TABLE Keyword



The SQL CREATE TABLE keyword is used to create a new table. Creating a table involves providing a name to the table and defining name and data type (e.g. varchar, integer, date, etc.) of each column.

Syntax

The syntax of using CREATE TABLE keyword is given below:

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

Example: Create a table

The below mentioned SQL code creates a table called Employee which contains five columns: EmpID, Name, City, Age and Salary.

CREATE TABLE Employee (
  EmpID INT              NOT NULL,
  Name VARCHAR(255)      NOT NULL,
  City VARCHAR(100),
  Age INT,
  Salary DECIMAL(18,2),
  PRIMARY KEY(EmpID)
);

This will create a empty table named Employee containing five columns as shown below:

EmpIDNameCityAgeSalary

When the table is created successfully it displays a message. Along with this, it can also be checked using DESC command as follows:

DESC Employee;

FieldTypeNullKeyDefaultExtra
EmpIDint(11)NoPRI
Namevarchar(255)No
Cityvarchar(100)YesNULL
Ageint(11)YesNULL
Salarydecimal(18,2)YesNULL

This indicates that the Employee is now available in the database which can be used to store information related to employee.

Example: Create a table using another table

A new table can be created using existing table in the database. Consider a database table called Employee with the following records.

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

In the example below, a new table called New_Employee_table is created using this table.

CREATE TABLE New_Employee_table AS
  SELECT  EmpID, Name, City, Age
  FROM Employee;

--see the result
SELECT * FROM New_Employee_table;

This result of the following code will be:

EmpIDNameCityAge
1JohnLondon25
2MarryNew York24
3JoParis27
4KimAmsterdam30
5RameshNew Delhi28
6HuangBeijing28

❮ SQL Keywords

5