SQL CREATE Table

SQL CREATE Table command is used to create a table in SQL.

In order to create a table using SQL, you need a table name, column name and it’s datatypes.

SQL CREATE Table Syntax

Let’s check the basic syntax of the SQL CREATE TABLE.

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

CREATE TABLE followed by table name instructs the database to create a table in the table with the specified table name. Then we specify the name of the column names required for the table along with the datatypes. The datatype can vary from one database to another. For example, to specify integer value number is supported in ORACLE whereas int is used in MySQL server.

Lastly, we define PRIMARY KEY on one column or on a combination of columns to maintain the uniqueness of the value in that column. 

SQL CREATE Table Example

In the following example, we are creating STUDENTS tables containing four columns. The column ROLLNO which is a primary key and NAME and AGE is created with NOT NULL Constrains showing that these fields cannot be null while creating records.

mysql> CREATE TABLE STUDENTS ( 
ROLLNO INT NOT NULL, 
NAME VARCHAR (20) NOT NULL, 
AGE INT NOT NULL, 
ADDRESS VARCHAR (100), 
PRIMARY KEY (ROLLNO) 
);

You can verify if the table is created successfully by looking at the message displayed by MySQL server otherwise you can use DESC commands as follow.

mysql> desc STUDENTS;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| ROLLNO  | int         | NO   | PRI | NULL    |       |
| NAME    | varchar(20) | NO   |     | NULL    |       |
| AGE     | int         | NO   |     | NULL    |       |
| ADDRESS | varchar(100)    | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

Now you have created STUDENTS table in the database and this can be used to store the information related to students.

SQL CREATE TABLE Example in MySQL

Below is the example of creating a table in MySQL database.

CREATE TABLE STUDENTS ( 
RollNo INT NOT NULL, 
FirstName VARCHAR (255) NOT NULL, 
LastName VARCHAR (255) NOT NULL, 
AGE INT NOT NULL, 
ADDRESS VARCHAR (255), 
PRIMARY KEY (RollNo) 
); 

SQL CREATE TABLE Example in ORACLE

Below is the example of creating a table in ORACLE database.

CREATE TABLE STUDENTS ( 
RollNo NUMBER (10) NOT NULL, 
FirstName VARCHAR (255) NOT NULL, 
LastName VARCHAR (255) NOT NULL, 
AGE NUMBER (2) NOT NULL, 
ADDRESS VARCHAR (255), 
PRIMARY KEY (RollNo) 
);

SQL CREATE TABLE Example in SQL Server

Below is the example of creating a table in SQL Server database. It is the same as MySQL.

CREATE TABLE STUDENTS ( 
RollNo INT NOT NULL, 
FirstName VARCHAR (255) NOT NULL, 
LastName VARCHAR (255) NOT NULL, 
AGE INT NOT NULL, 
ADDRESS VARCHAR (255), 
PRIMARY KEY (RollNo) 
);