Welcome to 50ville, a thrilling SQL mystery!

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:

1. The Thief - Who committed the crime?
2. The City - Where did the thief escape to?
3. The Accomplice - Who helped the thief?

You have been provided with the fiftyville.db SQLite database file. Using SQL queries, you will explore the database to gather clues and solve the mystery.

All you know is that the theft took place on July 28, 2023 and that it took place on Humphrey Street.

1. Download the Database:

   • The database file is named fiftyville.db. Make sure you have this file in your working directory.

2. Install SQLite3:

   • If you don’t have SQLite installed, follow the instructions below for your operating system.

   Windows:

   • Download the SQLite tools from the official SQLite website: SQLite Download Page.
   • Extract the zip file and copy the .exe files to a folder, such as C:\sqlite.
   • Add this folder to your system's PATH so you can run SQLite commands from any terminal:
     1. Open Control Panel > System > Advanced system settings.
     2. Click Environment Variables.
     3. Under System Variables, find the Path variable and click Edit.
     4. Add the path to the folder containing sqlite3.exe, like C:\sqlite.
   • Open Command Prompt and type sqlite3 to ensure it’s working.

   macOS:

   • SQLite is pre-installed on macOS. To verify, open Terminal and type sqlite3.
   • If it’s not installed, you can use Homebrew:

     brew install sqlite3

   Linux:

   • SQLite is often pre-installed. If not, install it using your package manager:

     sudo apt install sqlite3

3. Open the Database:

   • Open a terminal (Command Prompt or Terminal) and navigate to the folder containing fiftyville.db. Run:

     sqlite3 fiftyville.db

   • This will open the SQLite interactive shell where you can run your SQL queries.

4. Load the Schema:

   • To see the tables and their structure, run:

     .tables

   • To view the schema of a specific table, use:

     PRAGMA table_info(table_name);

     Example:

     PRAGMA table_info(crime_scene_reports);

Your goal is to examine crime scene reports, interview records, bank transactions, phone calls, flight records, and more to piece together clues.

You need to determine:

1. Who the thief is.
2. Which city they escaped to.
3. Who helped the thief (the accomplice).

Below is the structure of the tables in the database:

You will need to run SQL queries to extract information from the database. Below are some essential SQL commands that might be helpful for your investigation:

• SELECT: Retrieve data from a database.

  SELECT * FROM table_name;

• WHERE: Filter records based on conditions.

  SELECT * FROM table_name WHERE condition;

• JOIN: Combine rows from two or more tables based on a related column.

  SELECT * FROM table1 JOIN table2 ON table1.column = table2.column;

• ORDER BY: Sort results by one or more columns.

  SELECT * FROM table_name ORDER BY column ASC/DESC;

For a full list of SQL commands, check out the SQL reference here.

Good luck with the investigation, and may your SQL sleuthing lead you to the truth!

(inspired by CS50)