Fiftyville
Write SQL queries to solve a mystery.
A Mystery in Fiftyville
The CS50 Duck has been stolen! The town of Fiftyville has called upon you to solve the mystery of the stolen duck. Authorities believe that the thief stole the duck and then, shortly afterwards, took a flight out of town with the help of an accomplice. Your goal is to identify:
- Who the thief is,
- What city the thief escaped to, and
- Who the thiefâs accomplice is who helped them escape
All you know is that the theft took place on July 28, 2020 and that it took place on Chamberlin Street.
How will you go about solving this mystery? The Fiftyville authorities have taken some of the townâs records from around the time of the theft and prepared a SQLite database for you, fiftyville.db
, which contains tables of data from around the town. You can query that table using SQL SELECT
queries to access the data of interest to you. Using just the information in the database, your task is to solve the mystery.
Getting Started
Instructions for Harvard College students
- Head to GitHub and, after signing in, accept this assignment on GitHub Classroom.
- After about a minute, refresh the page and click the link to visit your personal GitHub Classroom assignment page.
- On the assignment page, click the green Code button and choose Open with Codespaces.
- Cilck New codespace and then, if prompted, Create codespace.
- Once your Codespace loads, click the
+
button in the bottom section of your window (next to the word âbashâ). You should then see blue text appear that says/workspaces/fiftyville-USERNAME
(whereUSERNAME
is your GitHub username). - Execute
ls
. You should see afiftyville.db
file, alog.sql
file, and ananswers.txt
file.
Instructions for non-Harvard College students
Hereâs how to download this problem into your own CS50 IDE. Log into CS50 IDE and then, in a terminal window, execute each of the below.
- Execute
cd
to ensure that youâre in~/
(i.e., your home directory, aka~
). - If you havenât already, execute
mkdir pset7
to make (i.e., create) a directory calledpset7
in your home directory. - Execute
cd pset7
to change into (i.e., open) that directory. - Execute
wget https://cdn.cs50.net/2020/fall/psets/7/fiftyville/fiftyville.zip
to download a (compressed) ZIP file with this problemâs distribution. - Execute
unzip fiftyville.zip
to uncompress that file. - Execute
rm fiftyville.zip
followed byyes
ory
to delete that ZIP file. - Execute
ls
. You should see a directory calledfiftyville
, which was inside of that ZIP file. - Execute
cd fiftyville
to change into that directory. - Execute
ls
. You should see afiftyville.db
file, alog.sql
file, and ananswers.txt
file.
Specification
For this problem, equally as important as solving the mystery itself is the process that you use to solve the mystery. In log.sql
, keep a log of all SQL queries that you run on the database. Above each query, label each with a comment (in SQL, comments are any lines that begin with --
) describing why youâre running the query and/or what information youâre hoping to get out of that particular query. You can use comments in the log file to add additional notes about your thought process as you solve the mystery: ultimately, this file should serve as evidence of the process you used to identify the thief!
Once you solve the mystery, complete each of the lines in answers.txt
by filling in the name of the thief, the city that the thief escaped to, and the name of the thiefâs accomplice who helped them escape town. (Be sure not to change any of the existing text in the file or to add any other lines to the file!)
Ultimately, you should submit both your log.sql
and answers.txt
files.
Walkthrough
Hints
- Execute
sqlite3 fiftyville.db
to begin running queries on the database.- While running
sqlite3
, executing.tables
will list all of the tables in the database. - While running
sqlite3
, executing.schema TABLE_NAME
, whereTABLE_NAME
is the name of a table in the database, will show you theCREATE TABLE
command used to create the table. This can be helpful for knowing which columns to query!
- While running
- You may find it helpful to start with the
crime_scene_reports
table. Start by looking for a crime scene report that matches the date and the location of the crime. - See this SQL keywords reference for some SQL syntax that may be helpful!
Testing
Execute the below to evaluate the correctness of your code using check50
.
check50 cs50/problems/2021/summer/fiftyville
How to Submit
Instructions for Harvard College students
Harvard College students (those with an @college.harvard.edu email address) should submit this problem via GitHub, not via Gradescope.
In your Codespace, execute the below, replacing USERNAME
with your actual GitHub username.
submit50 classroom50/fiftyville USERNAME
Instructions for non-Harvard College students
You must submit both your log.sql
file and your answers.txt
file for this problem.
- Download your
log.sql
and youranswers.txt
files by control-clicking or right-clicking on the files in CS50 IDEâs file browser and choosing Download. - Go to CS50âs Gradescope page.
- Click âProblem Set 7: Fiftyvilleâ.
- Drag and drop your
log.sql
and youranswers.txt
files to the area that says âDrag & Dropâ. Be sure they have the correct filenames, and that you have uploaded both files, or the autograder will fail to run on them, and they will score no correctness points! - Click âUploadâ.
You should see a message that says âProblem Set 7: Fiftyville submitted successfully!â You wonât see a score just yet, but if you see the message then weâve received your submission!
Acknowledgements
Inspired by another case over at SQL City.