Project 1: The Detective

In this project, you will step into the role of a detective investigating a crime committed in a fictional city. You have access to the city’s police database. Your goal is to find the murderer by following a trail of clues.

Scenario

A crime has occurred on Jan 15, 2023, in SQL City. You need to identify the murderer and, eventually, the mastermind behind the crime.

The Database Schema

The police database consists of several tables:

• CrimeSceneReports: Descriptions of crimes, locations, and dates.
• Witnesses: Personal details of people who saw something.
• Interviews: Transcripts of what witnesses told the police.
• DriversLicenses: Details about cars and physical descriptions.
• GymMembers: Membership details for the “Get Fit Now” gym.

Investigation Steps

1. Find the Crime Report
   Start by searching the CrimeSceneReports table for the specific date and city. This will give you the first set of clues about the witnesses.

2. Locate the Witnesses
   The report will likely mention where the witnesses live. Use the Witnesses table to find their names and IDs.

3. Read the Transcripts
   Join the Witnesses table with the Interviews table. Look for the specific transcripts where the witnesses describe the killer.

4. Identify the Suspect
   The witnesses will provide physical clues (car plate, height, hair color) or membership clues (gym bag, check-in date). Join the DriversLicenses and GymMembers tables to narrow down your list.

5. Find the Mastermind
   Once you find the killer, read their interview transcript. It might lead you to the person who hired them.

Key Skills Required

• Filtering: Identifying the correct date and city.
• JOINS: Connecting witness IDs to interview transcripts and gym records.
• String Matching: Searching for partial license plates or gym IDs.

The SQL Challenge

Note

This project is based on the famous open-source “SQL Murder Mystery” by Knight Lab.

Example Starting Query

First Clue

SELECT description
FROM CrimeSceneReports
WHERE date = '20230115'
AND city = 'SQL City'
AND type = 'murder';

Final Deliverable

Your final answer should be a single SQL query that returns the name of the murderer. For a bonus, find the name of the woman who hired them by investigating the killer’s social circle and wealth.