SQL: create command

create is a DDL SQL command used to create a table or a database in relational database management system.


Creating a Database

To create a database in RDBMS, create command is used. Following is the syntax,

CREATE DATABASE <DB_NAME>;

Example for creating Database

CREATE DATABASE Test;

The above command will create a database named Test, which will be an empty schema without any table.

To create tables in this newly created database, we can again use the create command.


Creating a Table

create command can also be used to create tables. Now when we create a table, we have to specify the details of the columns of the tables too. We can specify the names and datatypes of various columns in the create command itself.

Following is the syntax,

CREATE TABLE <TABLE_NAME>
(
    column_name1 datatype1,
    column_name2 datatype2,
    column_name3 datatype3,
    column_name4 datatype4
);

create table command will tell the database system to create a new table with the given table name and column information.


Example for creating Table

CREATE TABLE Student(
    student_id INT, 
    name VARCHAR(100), 
    age INT);

The above command will create a new table with name Student in the current database with 3 columns, namely student_id, name and age. Where the column student_id will only store integer, name will hold upto 100 characters and age will again store only integer value.

If you are currently not logged into your database in which you want to create the table then you can also add the database name along with table name, using a dot operator .

For example, if we have a database with name Test and we want to create a table Student in it, then we can do so using the following query:

CREATE TABLE Test.Student(
    student_id INT, 
    name VARCHAR(100), 
    age INT);

Most commonly used datatypes for Table columns

Here we have listed some of the most commonly used datatypes used for columns in tables.

DatatypeUse
INTused for columns which will store integer values.
FLOATused for columns which will store float values.
DOUBLEused for columns which will store float values.
VARCHARused for columns which will be used to store characters and integers, basically a string.
CHARused for columns which will store char values(single character).
DATEused for columns which will store date values.
TEXTused for columns which will store text which is generally long in length. For example, if you create a table for storing profile information of a social networking website, then for about me section you can have a column of type TEXT.