... This is a contextualization of CS50's Fiftyville (Problem Set 7) ...

It happened on a cold July afternoon, Thursday the 7th to be exact. Marurui estate was abuzz with activity as usual; sellers were selling, buyers were buying and idlers were idling. Ghafla bin vuu wezi wenye misuli tinginya na miraba minne walitokea na kuipora Mpesa shop ya Mama Shiku. (I hope you read that in Kennar's voice). By the time Mama Shiku came out screaming for help the thieves were long gone.

The police arrived at the scene on Mucene Drive and took statements from the victim and a few local witnesses which they recorded in their database. Preliminary reports indicate that the thieves flew out of town with the help of an accomplice. Determined to keep the crime rate low, the police decide to call in their top detective to follow the leads and get to the bottom of the mystery. This, my dear reader, is where YOU come in.

Your mandate is to use a series of SQL queries on the local estate database estate.db prepared by the police to find the thieves, their accomplice and the town to which they flew. The estate.db contains the following tables: airports, atm_transactions, mpesa_shop_security_logs, bank_accounts, crime_scene_reports, flights, interviews, passengers, people, phone_calls. Each of the tables have a different schema depending on the data stored therein.

The clock is ticking Detective. Don't let them get away!

SQL

SQL (usually pronounced 'sequel') is a Standard-Query-Language used to store, access and manipulate data on a database. In Relational Database Management Systems (like MySQL), data is stored on tables which can be inter-related. Let's take a look at some of the SQL queries (in bold text) you may have used on estate.db to solve the Marurui Crime Mystery.

SELECT description FROM crime_scene_reports WHERE day=7 AND month=7 AND year=2022 AND street='Mucene Drive';

The description of that crime scene report indicates that the theft occurred at around 4:30 pm and that 3 people were interviewed at the scene. You might want to read the transcripts from the interviews table, right?

SELECT transcript FROM interviews WHERE day=7 AND month=7 AND year=2022;

Among many other clues, one of the interviewees says that they spotted one of the thieves making a phone call that lasted less than a minute before entering the Mpesa shop. Lets query the phone_calls table next.

SELECT caller FROM phone_calls WHERE day=7 AND month=7 AND year=2022 AND duration < 60;

You get a list of 3 phone numbers belonging to people who made calls that lasted less than a minute on that day. Lets find their names from the people table.

SELECT name FROM people WHERE phone_number IN (SELECT caller FROM phone_calls WHERE day=7 AND month=7 AND year=2022 AND duration < 60);

Voila! By correlating the data from the phone_calls and the people table, you have your 1st three suspects. If you follow the other clues from the interview transcripts and use the right SQL queries, you will definitely catch the thieves and their accomplice right where they are hiding! After this, Alfred Mutua should probably cast you in Cobra Squad Season 3.

It's safe to say that I'm in love with the teaching model at Harvard. Learning needs not be dull!

Afterword (Na Msiniite Omosh)

If you enjoyed the article and found it helpful, kindly consider Buying A Kahawa ☕ to support a brother and his creation of quality tech content for the common mwananchi :-)

Buy me a coffee is an easy and thoughtful way to support creators for their time and effort in making helpful content. This in turn eases them of the pressure to barrage you with ads for monetization.

Until next time; Tuko Freshi Barida!