Skip to content

Create a Database and Build a Table with SQL

Let's get started!

We will:

  • Create an SQLite Database with DB Browser for SQLite
  • Practice Creating Tables with DB Browser for SQLite using SQL

Info

Remember, SQL is an abbreviation of Structured Query Language, and it's a computer language designed for use in building and using relational databases. Learning SQL means you can work with any relational database, not just SQLite. We have a short SQL explainer here.

Let's create a table to hold these records:

idrootnicodemusenglishoriginator
1aahello.Lawrence Nicodemus
2bcbutsboots.Lawrence Nicodemus
3bstbilsodragonfly.null

Create a Database

Let's start with by using DB Browser for SQLite to write the SQL for us.

Open DB Browser for SQLite.

Click the button New Database.

A dialog should show up. Go to the project directory you created and save the file with a name of database.db.

Tip

It's common to save SQLite database files with an extension of .db. Sometimes also .sqlite.

Create Your First Table

After doing that, it might prompt you to create a new table right away.

If it doesn't, click the button Create Table.

Then you will see the dialog to create a new table.

So, let's create a new table called roots with the following columns:

  • id: an INTEGER that will be the primary key (check PK ✅).

Info

We have another brief explainer about database keys here.

  • root: a TEXT, it should be NOT NULL (check NN ✅), so, it should always have a value (i.e., it can never be NULL). A root is the portion of a word that contains its basic meaning.
  • nicodemus: a TEXT, it should be marked NOT NULL as well (check NN ✅). In this table, nicodemus specifies the word written in the Nicodemus orthography.
  • english: a TEXT, it should be marked NOT NULL as well (check NN ✅). This specifies the word as translated into English. This shows a design decision that we won't enter words into the dictionary before they are translated into English. You may decide to make a different decision, i.e. you may decide to allow words into a dictionary without an English translation, in which case this column would be nullable.
  • originator: a TEXT, this one can be NULL, so you don't have to check anything else.

Click OK to create the table.

While you click on the Add button and add the information, it will create and update the SQL statement that is executed to create the table:

CREATE TABLE "roots" ( (1)
    "id"    INTEGER NOT NULL, (2)
    "root"  TEXT NOT NULL, (3)
    "nicodemus" TEXT NOT NULL, (4)
    "english"   TEXT NOT NULL, (5)
    "originator"    TEXT, (6)
    PRIMARY KEY("id") (7)
); (8)
  1. Create a table with the name roots. Also notice that the columns for this table are declared inside the parenthesis " (" that starts here.
  2. The id column, an INTEGER. This is declared as the primary key at the end.
  3. The root column, a TEXT, and it should always have a value NOT NULL.
  4. The nicodemus column, another TEXT, also NOT NULL.
  5. The english column, another TEXT, also NOT NULL.
  6. The originator column, another TEXT. This one doesn't have NOT NULL, so it can be NULL.
  7. The PRIMARY KEY of all this is the id column.
  8. This is the end of the SQL table, with the final parenthesis ")". It also has the semicolon ";" that marks the end of the SQL statement. There could be more SQL statements in the same SQL string.

Now you will see that it shows up in the list of Tables with the columns we specified. 🎉

The only step left is to click Write Changes to save the changes to the file.

After that, the new table is saved in this database on the file ./database.db.

Confirm the Table

Let's confirm that it's all saved.

First click the button Close Database to close the database.

Now click on Open Database to open the database again, and select the same file ./database.db.

You will see again the same table we created.

Create the Table again, with SQL

Now, to see how is it that SQL works, let's create the table again, but with SQL.

Click the Close Database button again.

And delete that ./database.db file in your project directory.

And click again on New Database.

This time, if you see the dialog to create a new table, just close it by clicking the Cancel button.

And now, go to the tab Execute SQL.

Write the same SQL that was generated in the previous step:

CREATE TABLE "roots" (
    "id"    INTEGER NOT NULL,
    "root"  TEXT NOT NULL,
    "nicodemus" TEXT NOT NULL,
    "english"   TEXT NOT NULL,
    "originator"    TEXT,
    PRIMARY KEY("id")
);

Then click the "Execute all" ▶ button.

You will see the "execution finished successfully" message.

And if you go back to the Database Structure tab, you will see that you effectively created again the same table.

Learn More SQL

I will keep showing you small bits of SQL through this tutorial. You don't have to be a SQL expert to create and use databases, but the more that you learn the better you will be able to use databases and design databases.

If you are curious and want to get a quick, albeit technical overview of SQL, I recommend the visual documentation from SQLite, on SQL As Understood By SQLite.

Tip

If you're feeling ambitious, you can start with CREATE TABLE. If you're not, that's OK too.

Of course, you can also go and take a full SQL course or read a book about SQL, but you don't need more than what will be explained here to create and use databases. 🤓

Recap

We saw how to interact with SQLite databases in files using DB Browser for SQLite in a graphical user interface.

We also saw how to use it to write some SQL directly to the SQLite database. This will be useful to verify the data in the database is looking correctly, to debug, etc.

Next Steps

It's time to create our tables, and load the information from the spreadsheet into the database. 🚀