Knowing how to create an SQL table properly is perhaps one of the most essential skills a budding database designer should have.

If you’re new to database management or simply need a refresher on SQL tables, this tutorial is just the one for you.

Getting Started With Your SQL Table

Before we create a table, make sure that you have a schema set up in an SQL server. For this example, we will be using a MySQL server along with MySQL Workbench to create a table.

The first thing to do is to set up a connection.

To do this, open MySQL Workbench, and click on the + icon to add a connection.

This opens up a dialog box where you can control the properties of the new connection. Add a new Connection Name and click OK.

Clicking on the connection takes you to the editor where you can input queries to create and manipulate schemas.

creating a new connection in mysql

To test out our code for creating a table, let’s create a new schema.

        CREATE schema mySchema;

USE mySchema

This creates an SQL schema that stores tables and their relationships. Now, onto the table.

Create an SQL Table

In SQL, a table can be created using the CREATE keyword. While creating the table, you need to specify its column names, column data types, and the primary key column.

The general syntax for doing so is:

        CREATE TABLE table_name(

    column1 datatype

    column2 datatype,

    column3 datatype,

    .....

    columnN datatype,

    PRIMARY KEY( columnName )

 );

Let’s use this syntax to create a table that stores employee data in a company.

        use mySchema;

CREATE TABLE employee(

    empID int not null,

    empName varchar(25) not null,

    emailID varchar(25) not null,

    PRIMARY KEY (empID)

  );

Note the not null key phrase here. This ensures that whenever a new employee is added, none of the fields can be left empty while adding their information.

Now, let’s test whether our table was successfully created and stored in the schema. One way to do so is to add some values to the table, and output them into the ‘Result Grid’ panel.

Adding Values in an SQL Table

To add values to the table, use the following command and arguments:

        INSERT INTO employee

    VALUES (1, ‘John Matthews’, ‘john_matthews@muo.com’);

Displaying Values From an SQL Table

To display values from the employee table, we can make use of the SELECT command.

To do so, use the following:

        SELECT * from employee;

The * here is a wildcard operator which selects everything by default. In this case, all rows of the employee table are selected to be displayed.

If everything goes smoothly, this is what you should see:

SQL Message

Related: Learn SQL's GROUP BY Command

Exploring SQL Further

There’s a lot more to databases than simply building table-upon-table. You can play around with some handy features such as queries and subqueries or, if you’re feeling adventurous, even try your hand at writing a procedure or trigger.

At the end of the day, however, the effectiveness of your SQL program comes down to how well you build and structure your tables. So make sure to keep this guide bookmarked until you know how to build SQL tables like the back of your hand!