CREATE TABLE: new tables with SQL

TIP

Run through and play with the interactive database^[1] for this lecture to learn about this better!

In the last chapter we learned how to create a table using DB Browser. Now we'll learn how to create another table using only SQL with SQLite^[2].

We will be creating an accounts table, which will have two fields: name and number. Both will be text fields.

TIP

Why is the account number, the number field, a text field?

You should use numeric field types, such as INTEGER, for data on which you may perform mathematical operations. An account number is not likely to be one such datum, so we'll keep it as text.

First, open the "Execute SQL" tab in DB Browser.

Then, we'll type out our command:

CREATE TABLE accounts (name TEXT, number TEXT);

There are three parts to this command:

• CREATE TABLE accounts tells SQL we want to create a new table, and its name name will be accounts.
• (name TEXT, number TEXT) tells SQL we want to create two columns in the table. Inside brackets and separated by commas we can add many columns following this format: column_name column_type. So here the name column has a type of TEXT, and the number column has a type of TEXT too.
• Finally, we include a semicolon: ;. This tells SQL we have reached the end of the query.

Other types of data in SQLite

Every RDBMS has different types of data, but some of them are similar across RDBMS's.

The SQLite types INTEGER and TEXT also exist in PostgreSQL. In other RDBMS's though, they may be called differently but be used for the same purposes.

Since SQLite is "lite", it has fewer data types^[3] than PostgreSQL^[4]. SQLite has 5 major data types. PostgreSQL has about 30 or more. Most of which though, you won't use often.

Running the command multiple times

If you run the CREATE TABLE command again, you'll get an error because the table already exists. Often you'll want to create a table if it doesn't exist yet. Fortunately, SQL allows us to do that by adding IF NOT EXISTS to the query:

CREATE TABLE IF NOT EXISTS accounts (name TEXT, number TEXT);

I believe this is not an English-like as some other parts of SQL, but nonetheless that's how it goes!

Since now we've created two tables, let's learn to insert data into them in the next chapter!

---

1. Interactive database for this lecture (opens new window) ↩︎

2. SQLite Create Table (official documentation) (opens new window) ↩︎

3. SQLite Data Types (official documentation) (opens new window) ↩︎

4. PostgreSQL Data Types (official documentation) (opens new window) ↩︎