Lab 1: SQL Murder Mystery (5 points)

This assignment is in the style of a 'whodunit' mystery. The police keep their crime data in the sql-murder-mystery schema. Download this schema and install it on your local MySQL instance.

You may work with one partner on this lab.

A detective told you that the crime you must solve was a ​murder​ that occurred sometime on ​Jan.15, 2018​ and that it took place in ​SQL City​. Start by retrieving the corresponding crime scene report from database whose schema diagram is:

This assignment is based on https://mystery.knightlab.com/ (honor code: do not look at the solution posted online).

Exercises

  1. Follow the clues, starting with the crime scene report table
  2. [3 points] Find the name of the killer
  3. [2 points] When you find the killer, look at their interview and find out who paid for the murder.

Submission Instructions

Submit one .sql file on Canvas with all queries you used to find the killer and who paid that person to commit murder. NOTE: you do not need to solve the murder with a single query. Your .sql file can contain multiple queries. Provide comments in the SQL so we can determine what you learned about the case from each query. In the submission comments on Canvas list the name of the killer and who paid.

If you worked with a partner, both partners should submit the same solution. Note your partner's name in the textbox on the Canvas submission site.

Your .sql file for this lab and all following labs should begin with: 

-- ============================================
-- Lab [Number]: [Lab title]
-- Name: [Your name] [Partner's name]
-- Course: Dartmouth CS 61 [Term] [Year]
-- ============================================

For this lab, your .sql file should begin with: 

-- ============================================
-- Lab 1: SQL Murder Mystery
-- Name: [Your name] [Partner's name]
-- Course: Dartmouth CS 61 Spring 2026
-- ============================================