SQLite

My personal data analysis journey started with Pandas dataframes. Being easy to manipulate in Python code, it enables further data analysis while also allowing you to make adjustments should the data change over time. So when I heard about SQL (Structured Query Language), I thought it was yet another similar way of analyzing dataframes. Turns out I was wrong, and it is slightly more complex than that.

SQL is designed for managing and querying data stored in databases. It is quite powerful as it makes it possible to perform operations directly on databases. It concretely means that you can retrieve, filter, and manipulate vast amounts of data without needing to import everything into your main program memory, making it very efficient.

These operations are conducted through series of structured commands, i.e. queries. The latter have a structure that is typically like the following:

     
SELECT column1, column2
FROM table_name
WHERE condition;
    

Here, SELECT specifies the columns you want to retrieve, FROM designates the table the data is coming from, and WHERE applies conditions to filter the results.

There are several ways to handle databases with SQL: either through serverless options or using database servers. As I started learning about SQL, I quickly found SQLite to be a good fit for my needs: it is serverless and comes pre-installed on macOS, which was especially convenient as I have an old Mac laptop. On top of that, it offers a majority of SQL’s capabilities, making it sufficient for learning SQL without needing complex configurations. Queries can be run right from the terminal or in Python scripts with the sqlite3 package, making it versatile and easy to test commands while learning.

Once I had SQLite set up, I wanted to test it on some data to practice. During my research, I discovered the Chinook database, which is a popular example database for learning SQL. It includes tables that mirror what you might find in a real-world media store, such as customers, invoices, artists, and tracks. In short, a perfect example to give a practical context for learning SQL queries. With toold such as DB Browser for SQLite or DbSchema, it is possible to view the database, which looks like the following:

I really enjoyed experimenting with SQLite on this database. I won't pretend I now possess a perfect understanding of it, but I still created a repository on Github that collects some common queries and exercises.

Of course there are other ways to explore SQL, from using setups requiring a server or through documentations and tutorials, but experimenting with SQLite and the Chinook database provided a hands-on, beginner-friendly approach that I can highly recommend.