Disclaimer: These answers are only for educational purposes only. Please do not use them for cheating. Cheating doesn’t do any good for you!
In this post, I’ll give you the solution to the cs50 psets 7 Fiftyville problem. For this problem, we have to write SQL queries to solve a mystery.
A mystery in Fiftyville
The mystery is about the CS50 duck. It has been stolen. So you have been called to solve the mystery. According to the authorities, they believe that the thief stole the duck and then, shortly afterward, took a flight out of the town with the help of an accomplice. So the goal is to identify the thief, what city the thief escaped to, and who helped the thief.
All that we know is that the theft took place on July 28, 2021, and that it took place on Humphrey Street.
So a database is provided to us containing all the records and data from around the town. So we need to query this table and find out who the thief is and all other data.
Solution log.sql
Here are all the queries that lead us to the thief.
-- Keep a log of any SQL queries you execute as you solve the mystery.
-- crime happened on july 28, 2021 and it took place on Humphrey Street
-- check the description of the crime in the cime scene reports table at the known day and place
select description from crime_scene_reports
where year=2021 and month = 7 and day = 28 and street = "Humphrey Street";
-- checking the interview stranscripts
select name, transcript from interviews
where day = "28" and month = "7" and year = "2021";
-- checking to see how many people by the name Eugene in the peoples table
select name from people where name = 'Eugene';
-- there is only one eugene in people list
-- let's find out who are the 3 witnesses from the list of names of people who game interviews on july 28, 2021. According to crime report
-- each of them mentioned bakery in their report
select name,transcript from interviews
where year=2021 and month=7 and day=28 and transcript like '%bakery%'
order by name;
-- according to eugene the thief was withdrawing money from the ATM on Leggett Street. Let's check the detials from that atm records
select account_number, amount from atm_transactions
where year = 2021 and month =7 and day=28 and atm_location='Leggett Street' and transaction_type='withdraw';
-- let's find the account names of those transactions from the bank based on their transaction detials
select name, atm_transactions.amount, atm_transactions.account_number from people
join bank_accounts on people.id = bank_accounts.person_id
join atm_transactions on bank_accounts.account_number = atm_transactions.account_number
where atm_transactions.year=2021
and atm_transactions.month=7
and atm_transactions.day=28
and atm_transactions.atm_location='Leggett Street'
and atm_transactions.transaction_type = 'withdraw';
-- according to raymonds lead lets find out the infomation about the airport in Fiftyville
select abbreviation, full_name, city
from airports
where city = 'Fiftyville';
-- Now let's find out what the flights scheduled on 29 from Fiftyville and order them by time
select flights.id, full_name, city, flights.hour, flights.minute
from airports
join flights
on airports.id = flights.destination_airport_id
where flights.origin_airport_id = (
select id
from airports
where city = 'Fiftyville'
)
and flights.year = 2021
and flights.month = 7
and flights.day = 29
order by flights.hour, flights.minute;
-- the first flight scheduled to be 8.20 to LaGuardia Airport in New York City (flight id is 36) this might be the place where theif went
-- Now can check the passengers list to find out who are the people onboard that flight ordering them by their passport numbers
select passengers.flight_id, name, passengers.passport_number, passengers.seat
from people
join passengers
on people.passport_number = passengers.passport_number
join flights
on passengers.flight_id = flights.id
where flights.year = 2021
and flights.month = 7
and flights.day = 29
and flights.hour = 8
and flights.minute = 20
order by passengers.passport_number;
-- now let's check the phone call records to find the person who bought the tickets
-- first, need to check the possible names of the callers, and put the names in the suspect list. ordering them according to the durations of the calls
select name, phone_calls.duration
from people
join phone_calls
on people.phone_number = phone_calls.caller
where phone_calls.year = 2021
and phone_calls.month = 7
and phone_calls.day = 28
and phone_calls.duration <= 60
order by phone_calls.duration;
-- next let's check the possible names of the call-receiver. then order them by the durations of the calls
select name, phone_calls.duration
from people
join phone_calls
on people.phone_number = phone_calls.receiver
where phone_calls.year = 2021
and phone_calls.month = 7
and phone_calls.day = 28
and phone_calls.duration <= 60
order by phone_calls.duration;
-- according to Ruth the thief drove away in a car from the bakery, within 10minutes from the theft. So let's check the licencse plates of cars within that time frame with
-- the respective owners of the vehicles
select name, bakery_security_logs.hour, bakery_security_logs.minute
from people
join bakery_security_logs
on people.license_plate = bakery_security_logs.license_plate
where bakery_security_logs.year = 2021
and bakery_security_logs.month = 7
and bakery_security_logs.day = 28
and bakery_security_logs.activity = 'exit'
and bakery_security_logs.minute >= 15
and bakery_security_logs.minute <= 25
order by bakery_security_logs.minute;
-- After cosidering all the lists of list of people who drove away from bakery, list of people who called, list of passengers, list of people who withdraw monty from the atm
-- Bruce appear in all of them so he must be the thief and he ran away to New York City
-- and Robin must be the one who accomplice with Bruce
Output text file
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | description | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Theft of the CS50 duck took place at 10:15am at the Humphrey Street bakery. Interviews were conducted today with three witnesses who were present at the time – each of their interview transcripts mentions the bakery. | | Littering took place at 16:36. No known witnesses. | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | name | transcript | +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Jose | “Ah,” said he, “I forgot that I had not seen you for some weeks. It is a little souvenir from the King of Bohemia in return for my assistance in the case of the Irene Adler papers.” | | Eugene | “I suppose,” said Holmes, “that when Mr. Windibank came back from France he was very annoyed at your having gone to the ball.” | | Barbara | “You had my note?” he asked with a deep harsh voice and a strongly marked German accent. “I told you that I would call.” He looked from one to the other of us, as if uncertain which to address. | | Ruth | Sometime within ten minutes of the theft, I saw the thief get into a car in the bakery parking lot and drive away. If you have security footage from the bakery parking lot, you might want to look for cars that left the parking lot in that time frame. | | Eugene | I don't know the thief's name, but it was someone I recognized. Earlier this morning, before I arrived at Emma's bakery, I was walking by the ATM on Leggett Street and saw the thief there withdrawing some money. | | Raymond | As the thief was leaving the bakery, they called someone who talked to them for less than a minute. In the call, I heard the thief say that they were planning to take the earliest flight out of Fiftyville tomorrow. The thief then asked the person on the other end of the phone to purchase the flight ticket. | | Lily | Our neighboring courthouse has a very annoying rooster that crows loudly at 6am every day. My sons Robert and Patrick took the rooster to a city far, far away, so it may never bother us again. My sons have successfully arrived in Paris. | +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +--------+ | name | +--------+ | Eugene | +--------+ +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | name | transcript | +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Eugene | I don't know the thief's name, but it was someone I recognized. Earlier this morning, before I arrived at Emma's bakery, I was walking by the ATM on Leggett Street and saw the thief there withdrawing some money. | | Raymond | As the thief was leaving the bakery, they called someone who talked to them for less than a minute. In the call, I heard the thief say that they were planning to take the earliest flight out of Fiftyville tomorrow. The thief then asked the person on the other end of the phone to purchase the flight ticket. | | Ruth | Sometime within ten minutes of the theft, I saw the thief get into a car in the bakery parking lot and drive away. If you have security footage from the bakery parking lot, you might want to look for cars that left the parking lot in that time frame. | +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +----------------+--------+ | account_number | amount | +----------------+--------+ | 28500762 | 48 | | 28296815 | 20 | | 76054385 | 60 | | 49610011 | 50 | | 16153065 | 80 | | 25506511 | 20 | | 81061156 | 30 | | 26013199 | 35 | +----------------+--------+ +---------+--------+----------------+ | name | amount | account_number | +---------+--------+----------------+ | Bruce | 50 | 49610011 | | Diana | 35 | 26013199 | | Brooke | 80 | 16153065 | | Kenny | 20 | 28296815 | | Iman | 20 | 25506511 | | Luca | 48 | 28500762 | | Taylor | 60 | 76054385 | | Benista | 30 | 81061156 | +---------+--------+----------------+ +--------------+-----------------------------+------------+ | abbreviation | full_name | city | +--------------+-----------------------------+------------+ | CSF | Fiftyville Regional Airport | Fiftyville | +--------------+-----------------------------+------------+ +----+-------------------------------------+---------------+------+--------+ | id | full_name | city | hour | minute | +----+-------------------------------------+---------------+------+--------+ | 36 | LaGuardia Airport | New York City | 8 | 20 | | 43 | O'Hare International Airport | Chicago | 9 | 30 | | 23 | San Francisco International Airport | San Francisco | 12 | 15 | | 53 | Tokyo International Airport | Tokyo | 15 | 20 | | 18 | Logan International Airport | Boston | 16 | 0 | +----+-------------------------------------+---------------+------+--------+ +-----------+--------+-----------------+------+ | flight_id | name | passport_number | seat | +-----------+--------+-----------------+------+ | 36 | Edward | 1540955065 | 5C | | 36 | Sofia | 1695452385 | 3B | | 36 | Taylor | 1988161715 | 6D | | 36 | Bruce | 5773159633 | 4A | | 36 | Doris | 7214083635 | 2A | | 36 | Kelsey | 8294398571 | 6C | | 36 | Luca | 8496433585 | 7B | | 36 | Kenny | 9878712108 | 7A | +-----------+--------+-----------------+------+ +---------+----------+ | name | duration | +---------+----------+ | Kelsey | 36 | | Carina | 38 | | Taylor | 43 | | Bruce | 45 | | Diana | 49 | | Kelsey | 50 | | Sofia | 51 | | Benista | 54 | | Kenny | 55 | | Kathryn | 60 | +---------+----------+ +------------+----------+ | name | duration | +------------+----------+ | Larry | 36 | | Jacqueline | 38 | | James | 43 | | Robin | 45 | | Philip | 49 | | Melissa | 50 | | Jack | 51 | | Anna | 54 | | Doris | 55 | | Luca | 60 | +------------+----------+ +---------+------+--------+ | name | hour | minute | +---------+------+--------+ | Wayne | 8 | 15 | | Jordan | 8 | 15 | | Sophia | 17 | 15 | | Vanessa | 10 | 16 | | Vincent | 15 | 16 | | Jeremy | 17 | 16 | | Bruce | 10 | 18 | | Barry | 10 | 18 | | Ethan | 14 | 18 | | Brandon | 17 | 18 | | Luca | 10 | 19 | | Sofia | 10 | 20 | | Iman | 10 | 21 | | Diana | 10 | 23 | | Kelsey | 10 | 23 | | Michael | 8 | 25 | +---------+------+--------+
Conclusion based on the investigation results
The THIEF is: Bruce The city the thief ESCAPED TO: New York City The ACCOMPLICE is: Robin
Hope this solution helps you!