Aggregating, joining, filtering, and more
SQL is a programming language used to manage data in a relational database. Data is in tabular form with labelled rows and columns. A relational database typically consists of many tables that are related by means of shared columns.
In this article, we will go over 6 examples to query a relational database. The examples aim to solve data analysis and manipulation tasks including filtering, aggregation, sorting, and joining tables.
I previously created a sales database with 4 tables. The following figure illustrates the structure of the database and tables.
The columns marked with green are the primary keys and the ones in pink represent the foreign keys.
- Primary key is the column that uniquely identifies each row. It is like the index of a pandas dataframe.
- Foreign key is what relates a table to another one. Foreign key contains the primary key of another table. For instance, the “item_id” in the purchase table is a foreign key. It stores the rows from the primary key in the item table.
After the brief introduction, let’s start with the examples.
Note: There are many relational database management systems (e.g. MySQL, PostgreSQL, SQL Server). Although the SQL syntax is mostly the same for all, there might be small differences. We will be using MySQL in this article.