How to Create a Table in SQL? Updated 2020

In this article, you will be able to know how to create a table in SQL with simple steps.

It is a very basic SQL concept and also it is a frequently asked interview question as well.

By reading this article you will become a master in SQL table creation. I have created a sample table below here for your better understanding.

What is a table and Why we need to create a table in SQL?

A table is a physical storage area to retrieve the rows and columns from the Database or from an excel file.

It is used to store transaction or master data of any object or thing and whenever it is needed it can be used to retrieve using certain SQL queries. 

The database tables have dependencies with other tables stored in the database which are called relationships. The table dependencies re-specified using primary key and foreign key relationships.

The primary key and foreign key relationships of tables can be specified using cardinality. It tells how many times the value of the primary key of an independent table is used as a foreign key independent table.

Well, there are 3 steps that you need to follow while creating a table in SQL.

  • Name the Table
  • Define the columns
  • Mention data types of the columns.

I have not specified any constraints in the create table statement because it is not necessary for table creation.

The above are the absolute things that you have to do but if you also want to apply some constraints in the table you can also do that too.

There are lots of things that you can do to create a statement. It is one of the most powerful statements in SQL.

Obviously right? The data is going to be in the tables. You know creating tables is the sense of data management system.

This is the syntax of the create table statement.

Table creation in SQL table syntax

First, you have to specify the two keywords and the second one is tables.

These keywords you just have to mention and then give a space after typing the name of the table and then you have to type in the open parenthesis.

After that, you have to specify the name of the columns and data types. Remember there has to be a space between the column name and the data type.

Once you have to do that after specifying all the datatypes and columns you can put in the closing parenthesis and then put in the semicolon to terminate the entire statement.

In this example, I have mentioned the primary key constraint at the last step or last line in the create table statement.

Now I am going to show you how to create a table using SQL editor.

I am going to use the test database for our current scenario. I have specified as use test
In the SQL editor.

Now I am going to create a table in SQL called employee. I am going to specify 4 columns.

  1. ID
  2. Name
  3. DOB
  4. Email

All SQL keywords preferably use upper write in upper case because when you share your code with others it becomes easier to follow other people.

Table creation sample in SQL

After you complete the query press control enter combination.

Now I get the message in the output portation of the SQL editor window that the create table employee statement has been processed correctly and no rows have been affected we haven’t populated it. We just created it.

Table Successfully Created message

In case if you want to know very basic stuff in SQL Server Management Studio. You can check our article on What is SQL Server Management Studio.

Now you can go back and expand the database and table in the structure pane. There you could see the newly created table and its columns.

Check table description in SQL

Otherway you can check it using desc command. We have desc command in My SQL and Oracle. In SQL server I just you to use some alternative way.

Once you execute this you will get all the table columns and its datatypes and you also see the columns can have datatypes or not and you have any constrained applied for that column or not.

Here you can see the Id values integer data type and it cannot hold null values since it is a primary key and it does not have any default value because we have not specified it while creating a table.

Check table columns and syntax in SQL Table creation

Things to keep in mind

  • In a table, if you have one column or two-columns or n number of columns I can have only one primary key.
  • To terminate the SQL statement you should put the semicolon.
  • The number should be int data type and string should be varchar. Also, you must have to give a space between the column name and data type while creating a table in SQL.

I hope you enjoyed this article. In case you have still had any queries with regards to table creation in SQL, you check this article SQL Create Table.

Do you have any queries? or Would you like to share something about Create Table in SQL? Please leave your comments in the comment section.

Leave a comment