This week we will be solving a murder mystery with SQL. SQL or Structured Query Language is a programming language used to manage and manipulate data in relational database management systems. Its a very powerful tool that allows you to update, insert, join, aggregate and delete tables. Much like ice-cream, SQL comes in different flavours and they include: NoSQL, SQLite, MySQL, and PostgreSQL to name a few. The core functionalists remain the same but they have slight differences depending on the use case.

This week it was all about using SQL queries like ‘SELECT’, ‘LIKE’, ‘WHERE’, and ‘JOIN’ just to name few, and using this to query a database in order to find the murder. The setting is that there has been a murder in SQL City on the 15th January, 2018 and its up to you to find the culprit. I won’t be showing who it actually is but just the SQL querying that was used to get to the answer.

So, to start off with the first step was to query the crime scene report like so:

In plain English, SELECT everything (denoted by the *) FROM the ‘crime_scene_report’ table, WHERE it matches these parameters: the type of crime, and the date that it happened on. With this query we are able to identify the witnesses of the crime. Next, we want to identify the witnesses and for this we can use this query:

In plain English, SELECT everything (denoted by the *) FROM the ‘person’ table, WHERE it roughly matches (LIKE) the street address. From this we were able to get who the witness was. Furthermore, we can now join the ‘person’ table and the ‘interview’ table to get more information. As a result, the join query looks like this:

In plain English, SELECT everything (denoted by the *) FROM the ‘person’ table, JOIN with the ‘interview’ table ON the ‘id’ column (person table) in person with the ‘person_id’ (interview table). With this we can get the testimony of the witness. From this we can now join the ‘person’ table to the gym membership table, and it looks like this:

In plain English, SELECT everything (denoted by the *) FROM the ‘person’ table, JOIN with the “gym membership table” table ON the ‘id’ column (person table) in person with the ‘person_id’ (gym membership table). With this we have narrowed our list of suspects. Finally, we can join on the drivers license table to get the culprit, and it looks like this:

In plain English, SELECT everything (denoted by the *) FROM the ‘person’ table, JOIN with the ‘drivers_license’ table ON the ‘id’ column (person table) in person with the ‘license_id’ (gym membership table). And finally we have our murderer.

In conclusion, with some basic querying of a database we were able to find the culprit and bring them to justice. There is still a loose thread, who was the mastermind behind the murder. Stay tuned to find out.

The Data School
Author: The Data School