How to Create Your First Table in SQL

Creating a database table with SQL is one of the core skills you’ll need to work with data. And it’s easy to learn, so let’s get started!

Imagine you’re analyzing data and want to store your results in a database table. Sure, you've done this a million times in Excel. But you’re not sure how to create a table with SQL.

Or maybe you’ve noticed that data engineering is in high demand and you want to start learning its core concepts. If either of these scenarios sound like you, keep reading. In this article, I’ll teach you how to create a database table. You will also learn how to insert fresh data into new tables; an empty table isn’t worth much! We’ll also peak at some of the learning paths available for data engineering and other SQL jobs.

What Is a Database Table?

Like an Excel spreadsheet, a database table organizes and stores information using rows and columns. Inside the table, each data item is defined with one row (record) and each attribute of the item is stored within a column (cell). Here’s a sample table named customer:

Customer table

This table contains three rows. Each row represents one person (Marc, Helen, and Billy). There are five columns in this table (id, name, gender, age and income) and each column represents one customer attribute.

A relational database can have many tables; each table will contain data that’s related in some way. For example, a bank could have:

• The customer table shown above.
• A loan table that stores information about customer loans.
• A balance table with data about customers’ account balances.

All three tables are related and can be in the same database. You can create other tables as well, depending on your needs. Keep in mind that if you want to create a table in SQL, you first need to know where you want to create the table, i.e. in which database.

Connecting to the Database

If you are creating a table for the first time, you need to check if you have permission to create tables in a specific database. You’ll need to know where to connect (the host name and port) and the username and password. You’ll also need to download an SQL editor that will connect to the database and enable you to write SQL statements. Usually, the database administrator helps you set this up, but let’s go through the process anyway.

For example, I have a database on my computer. When I want to connect to my database, I’m connecting to the host name ‘127.0.0.1’ and port ‘3306’ (the default port for MySQL). I’m using MySQL Workbench, but there are many different SQL editors that you can use.

You may also notice from the image below that I’m using the username ‘marija’ with the appropriate password. All these parameters are mandatory when connecting to a database. If the database is not created by you (i.e. it’s not on your computer), you need to contact your database administrator (or the database owner) and ask them for a host name and username.

MySQL database - localhost

Keep in mind also that each database can have many users, and users can have different rights in the database. Your DBA needs to grant the table creation privilege to your user account. There are two important things that you need to properly communicate about this:

1. The database name where you want to create tables (i.e. the name you can see when you make a connection).
2. Your username (so the DBA can grant you table creation rights in the database).

Once you have all this taken care of, you can start creating tables with SQL.

When Should I Create a Table?

If you’re planning on sharing your insights and analysis, writing SELECT statements and joining tables is not going to be enough; you’ll need to create a table. Here are the most frequent reasons to create new tables in a database:

1. Your data model needs to be expanded because new functionality was added into your application. For example, when a bank starts to offer a new product, the data model needs to be expanded?. In that case, ?many new tables are created. For example, if a bank starts to offer mobile banking, new tables like application and mobile_transactions will probably be needed.
2. You need to share the result of your data analysis. One way to do this is to share an SQL script that allows them to run SELECT statements you wrote that will return the insights, but this isn’t a convenient way. It’s much better to store the final data in a table and share that table with others.
3. You’re going to create a report based on the result of your analysis. Reports have predefined columns, so you can create a table and periodically refresh the data as you need to.
4. You want to store an intermediate result and then continue the analysis without waiting for a query. Sometimes scripts take a long time to run and you don’t want to run one each time you turn on your SQL editor and work on your analysis. By saving the intermediate results in the database, you can get right to work. In this case, you can create a table and drop it when you don’t need the intermediate results anymore.

By now, you probably realize that CREATE TABLE is an important SQL feature. Once you have the right permissions, you’re ready to start creating tables. Who knows – maybe you will like it so much you’ll want to become a data engineer.

So, let’s learn how to create a table in SQL.

SQL’s CREATE TABLE Command

Earlier, I showed you the customer table. Here is the CREATE TABLE statement I used to develop it:

CREATE TABLE customer (
customer_id int primary key,
name varchar(60) default null,
gender char(1) default null,
age int default null,
income decimal(18,2) default null
);

I’ll explain this syntax:

• The CREATE TABLE statement tells the database you want to create a new table.
• After the CREATE TABLE keywords, we define a table name. In our example, the table is named customer.
• Columns (i.e. table attributes) are defined inside round brackets. Our table contains five columns.
• Each column is defined with a name and data type. Our columns are named customer_id, name , gender, age, and income. There are two integer columns (customer_id and age). The two string columns (name and gender) are defined as varchar and char. There is one decimal column, income , which can hold decimal numbers. (The int (integer) data type can only hold whole numbers.)
• Next, we define the primary key using the keyword PRIMARY KEY. A primary key holds unique values that can be used to identify each record. In our case, the primary key is customer_id.
• Notice that we’ve defined initial or default values for some columns. This tells the database which value the engine should display if that field is not populated. All columns other than the primary key (which must have a unique value) will be marked NULL if no value is provided. So, after each column’s data type, we wrote default null.

This is just one example. If you would like to practice this syntax, I recommend you try the LearnSQL.com course The Basics of Creating Tables. You’ll find more details and some examples you can practice on.

Table Naming Conventions

Choosing the right names for your tables and columns makes them easier to understand. It also helps you and other people work on the database later on. Large companies have their own naming conventions and procedures for all developers to follow. Here are some naming tips that you might find useful:

• First, decide whether you are going to use singular or plural names. Whatever you decide, be consistent – I used singular names like customer, loan, and balance instead of customers, loans, and balances. Usually, singular names are preferred.
• Choose meaningful names, i.e. the type of content is clear from the table or column name.
• Avoid long names if possible. They can be confusing and some databases restrict name lengths. If necessary, use abbreviations to shorten long names. For example, instead of income_current_month, use inc_curr_mth. For clarity’s sake, it’s best to use full words, but it’s better to use abbreviations than to have long column/table names.
• If you use abbreviations, stay consistent?: use ‘curr’ for ‘current’ or ‘mth’ for month in all column/table names, even the short names.
• Separate words with an underscore, not a space. For example, use customer_id instead of customer id.
• Don't use reserved words as names. Each database has its own reserved words (e.g. ‘table’, ‘case ‘, etc.); find out what they are and don't use them when defining names.

Column Data Types

The trickiest part of table creation is defining the data type for each column. To do it properly, you need to be aware of what data will be stored in each table/column. Here are the common data types:

• Text or string values are stored as varchar or char.

  Use varchar when you are not sure how long of a text will be stored in this field. In our example, the column name is a varchar(60), which means that name can store a customer name of up to 60 characters. (We don’t know the exact length of each customer’s name, but we do know it will not exceed 60 characters.)

  Use char for storing texts that you know will be a fixed size. In our example, gender will always be represented with one character (“F” or “M”), so we are using char(1) and not varchar.

• Whole numbers are stored as integers.

  Customer_id and age are of the integer (or int) data type. They are always whole numbers.

• Decimal-point numbers are stored as numeric or decimal types.

  The income data type is decimal — we use this type when we need to store numeric values that aren’t whole numbers. Decimal is commonly used to store money-related values (like salary, income, assets, etc.). In our example, income is defined as decimal(18,2), which means that this field can take 18 total digits. Of those 18, 2 digits are reserved for numbers after the decimal point. To learn more, check out this article that explains numerical data types in SQL.

  Many databases also have a float data type. Although it looks similar, it is not the same as decimal. If you choose float instead of decimal for business calculations, float calculations will not be exact. So avoid using float for money values

  .

• Dates are usually stored as date or timestamp columns

  In our example, we don’t deal with dates. But keep in mind that there are several special data types (date, time, and timestamp) for date and time columns.

I'm not going to get into the details of data types, but if you’re keen to learn more, check out our Data Types in SQL course.

Inserting Data into a Table

The last step we’ll cover is row insertion. Once you have an empty table, it’s time to populate it with data. An INSERT INTO statement can do that.

Let’s insert one new row into a table. The values that need to be inserted are:

• 1 for customer_id.
• “Marc Lutuin” for name.
• “M” for gender.
• 56 for age.
• 1100 for income.

Here is INSERT INTO in action:

INSERT INTO customer(customer_id, name,gender,age,income) values(1,“Marc Lutuin”,“M”, 56, 11000);

Lets explain the syntax:

• This is called the INSERT INTO statement.
• After the INSERT INTO keywords, write the name of the table where you’re adding data.
• Inside the round brackets after the table name, define the column names that are going to receive the data.
• After the values keyword, put round brackets around the values you want to insert. Make sure the values are in the same order as the column names.

Again, I’m not going to dive deep into record insertion in this article. To really understand it, try the LearnSQL.com course How to INSERT, UPDATE, and DELETE Data in SQL. This course will teach you how to modify your data in the database.

There’s More to Learn About Creating Tables in SQL

In this article, I explained the CREATE TABLE statement and why it’s so important. I also showed you how to insert a row into a table. Of course, creating tables and doing data modeling isn’t simple; there are a lot of things I didn’t cover here.

If you really want to master table creation and manage the structure of a relational database, I would definitely recommend that you check out the track Creating Database Structure. This learning track is intended for data engineers and contains five different courses. It starts with table creation, continues on to data types, constraints, and indexes, and finally introduces views.

All LearnSQL.com courses are interactive, which means that everything is explained through examples and exercises. This easiest way to learn is by doing, so I encourage you to give this a try.