How to Create Table in SQL

CREATE TABLE syntax is among the first statements which users learn in order to work with databases which support SQL. These include SQL Server, MySQL, and Oracle.

To store data in SQL databases, first we should create tables. In a database, each table should have a unique name. There are one or more columns in each table.

Moreover, a data type should be associated with each column to tell the database what kind of data is going to be stored. These types of data include strings or characters, numbers and so on.

CREATE TABLE syntax

When working with SQL databases, we could create a new table by using the CREATE TABLE syntax.

It could be used as following:

CREATE TABLE t_name (
    col_1 type,
    col_2 type,
    col_3 type,
….
);

In the above syntax, t_name denotes the name you like for your database table.

As you might know, each table in a database consists of one or more columns. Each of these table columns has their own names and datatypes defined when creating the table.

CREATE TABLE example

To help you better understand this SQL statement, here is an example which shows how to create a table called “Students” including six columns: StudentID, FirstName, LastName, Phone, City, and Address:

CREATE TABLE Students (
StudentID int,
FirstName varchar(50),
LastName varchar(50),
Phone varchar(15),
City varchar(250),
Address varchar(250)
);

Here’s how this example works:

  • The StudentID column will hold an integer. That’s why we defined it as “int” type.
  • And since the FirstName, LastName, Phone, City and Address columns will hold characters we defined them as “varchar” type above.
  • Please note that the number in parentheses after varchar shows the maximum length – which is 50 characters for two of these fields here.

At first, when tables are made they are empty. So they should be filled with data somehow. One of the main methods to do that is using INSERT INTO syntax of SQL.

CREATE TABLE AS SELECT syntax

Sometimes we just want to create a whole or partial copy of an existing database table. In these situations we could use CREATE TABLE syntax along with AS SELECT to do the task.

Here’s how the SQL query looks like:

CREATE TABLE new_t_name AS
SELECT col_1, col_2,…
FROM existing_t_name
WHERE ….;

Please note that when using the above statement for creating a new table from and old one, we could select specific columns or all columns from our old database table.

And the same column definitions are used for the new table.

In addition, old table values will be used to fill the newly created table.

Run the query using phpMyAdmin

In addition to the standard way of making a table using SQL Server queries, you could try other methods to do the job depending on your database.

For example, if you are working with MySQL, you could take advantage of the open source and free MySQL administration tool called phpMyAdmin, which could be found on most linux-based web hosting services.

A wide range of queries and operations on MySQL are supported by phpMyAdmin including creating a table.

Working with this tool requires a separate tutorial if you’re not familiar with it. But in case you have worked with phpMyAdmin before, you could take the simple following steps to easily create tables inside your SQL database.

  • Log in to phpMyAdmin
  • Choose the database from the left column
  • Now choose a name for your new table and define the number of fields (columns) using the fields below “Create new table on database”.
  • Click Go.
  • Now you could add, edit and manage different details and values for each columns inside your table.

You could also run the queries directly to create a table in MySQL. To do that just enter the queries using the SQL tab from the interface.