DB Browser for SQLite (it’s also called SQLite Browser for short) is an excellent tool for practicing SQL without having to get connected to a real live server. This post will walk through how to install, open, and use SQLite Browser.

Install SQLite Browser

Go to the SQLite Browser website and choose the download for whichever operating system you are using. Open the file and follow installation instructions.

Step 1: Get Set Up

If you want to follow along, download each of these csv files: ad_info.csv, facebook_info.csv, and ad_results.csv. Save them somewhere that you’ll be able to find them (like in a folder dedicated for this example, or your Desktop). We’ll be using them as the tables in our database. Open SQLite Browser the same way you would any other program! You should see a window that looks like this:

Step 2: Make a Database

To do anything in SQLite Browser, you need to be working within a database. That means every time you start SQLite Browser, you need to either create a new database, or open an existing one. For this example, we’ll create a new one using the New Database button in the top-left corner. SQLite Browser will ask you to save your database – do this just like you would any other file. You can call it whatever you’d like, but for this example we’ll name our database “marketing-db”. Make sure you save it in a folder where you’ll be able to easily find it again, like the Desktop. Then click Save.

Step 3: Import our First Table

Once you click save, this window should appear:

This window is for typing in your data by hand. That’s super tedious and we won’t be doing that, so close this window. Instead, we’re going to go to File > Import > “Table from CSV file…” A file explorer should pop up (just like it would if you were opening any other document). In that window, browse to where you saved the .csv files. Let’s import the ad_info table first. Double click the ad_info.csv file to open it (or select the file and choose “Open”). You should then see this window:

It should have automatically populated the “Table name” box with ad_info. If it didn’t, or you want to change the table name, go ahead and type ad_info. If you’re doing this on your own for a different project, you can rename the table to whatever you’d like in this box. But for this example, let’s keep the name ad_info so it’s easier to follow. Also, make sure “Column names in first line” is checked.

Finally, take a glance at the preview of the import. For our example, you shouldn’t have to touch any of the other options. But sometimes, depending on how your .csv file is saved, DB Browser may try to squish all of your columns into one. In that case, you usually need to change the Field separator option. But again, you shouldn’t have to touch it for this example.

Step 4: Add the Other Tables

Repeat this process for the facebook_info and ad_results tables. When you’re done, the main screen (Database Structure tab) should look like this:

Step 5: Understand the Interface

Let’s take a second to understand the buttons in SQLite Browser. There are four tabs at the top: Database Structure, Browse Data, Edit Pragmas, and Execute SQL.

The Database Structure tab is like your schema. It tells you about the tables that are in your database, and the columns in each table. We’ll be coming back to this in the next step to modify our tables.

The Browse Data tab allows you to do just that – browse your data. You can check out the data in the tables, and use the drop-down to switch between tables:

The Edit Pragmas tab allows you to set more advanced options. It’s unlikely you’ll need to use this tab often.

Finally, the Execute SQL tab is where you will actually write SQL queries and run them! Which is what we will do in the next step.

Run Your First Query

Let’s run a query to see how it works! Copy and paste this query into the top box in the Execute SQL tab:

SELECT *
FROM ad_info

If you aren’t familiar with SQL, don’t worry too much about this query/syntax right now. What it’s doing is selecting all of the columns from the ad_info table. Hit the triangle button to run the query. The whole thing should look like this:

Cool right! We are able to run SQL queries without actually setting up a real live server. Also take note that SQLite Browser gives us some information about the query in the box below the preview of the results. We can see that there are 149 rows total in our result, and how long it took SQLite to run the query.

Let’s run one more query. Copy and paste this SQLite browser next and hit run:

SELECT *
FROM ad_info
WHERE total_budget > 60000

Hmmmm nothing changed. We still go the same number of rows in the result (149), and there are still rows that have a total_budget of greater than $60,000. Why? DB Browser imports all columns as text columns by default. So it isnt’ recognizing total_budget as a number, and therefore doesn’t know how to find values greater than $60,000. Don’t worry, we can fix this!

Modify the Column Types in the Tables

Since SQLite Browser automatically imports all columns in all tables as TEXT, we need to manually change the data type of the non-text columns. Go back to the Database Structure tab, and click on the ad_info table. You can tell you’ve selected it because it should be highlighted in blue. Then click the Modify Table button. Finally, change the Type dropdown for the total_budget column to integer. Once again, a GIF of the whole thing:

Now go back to the Execute SQL tab and try running the query again (just click the triangle again to re-run it).

Notice it now only returns 61 rows! And these are the correct rows – with total budgets over $60,000. It’s important to remember to change the data types as soon as you import data into SQLite Browser.

Takeaways

Congrats! If you made it here, you now have a pretty good idea of how to use SQLite Browser. This page is also a great reference to keep handy in case you forget how to do something like change the data type, or import tables.

Now, take your skills to the next level and check out PostgreSQL!