SQL - CREATE TABLE Statement

A tutorial for creating a database table with SQL

A database is an organized collection of data or structured information.

The Database incorporates one or more tables. DBMS (DataBase Management System) usually manages the database.

The CREATE TABLE statement is used to create a new table in the database using SQL.

SQL stands for Structured Query language, pronounced as “S-Q-L” also popularly known as “See-Quel”.

Syntax

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

SQL supports several kinds of data

The datatype of a column defines what type of value the column will hold: integer, character, date and time, binary, and so on.

The datatype you more often come across are:

  • CHAR
  • TEXT
  • VARCHAR
  • DATE
  • TIME
  • DATETIME
  • TIMESTAMP

Numeric datatype includes

  • TINYINT
  • INT
  • BIGINT
  • SMALLINT
  • DECIMAL
  • FLOAT

Wow! so many numeric types, but why?

All these hold the numeric type of data but have different ranges and precisions i.e A TINYINT value resides between 0 to 255 while for an INT it would be between -231 to +231.

The more storage space will be needed as the number of size in bytes increases.

Example

CREATE TABLE Users(
  UserId int,
  FirstName varchar(50),
  LastName varchar(50),
  Email  varchar(255)
  Address varchar(255),
  City varchar(255),
  Birthday date,
);

The UserId column is of type int and will hold an integer value.

The FirstName, LastName, Email, Address, and City columns are of type varchar and will hold characters, and the maximum length for these fields are specified with it. For FirstName here it’s 50 characters, whereas Email has 255 characters.

The structure of the empty “Users” table will look like this:

UserIdFirstNameLastNameEmailAddressCityBirthday

Note: Size, precision or ranges varies with the DBMS that you’re using

Hope you like this! If this article was helpful, share it.

Keep helping and happy 😄 coding

Vishnu Damwala
Vishnu Damwala

A web geek, an industry experienced web developer & tutor/instructor residing in India 🇮🇳