Database

-- Keep a log of any SQL queries you execute as you solve the mystery.

-- Extract all information about the exact crime scene of interest
SELECT * FROM crime_scene_reports WHERE DAY = 28 AND street = "Chamberlin Street";
-- RESULT
-- id | year | month | day | street | description
-- 295 | 2020 | 7 | 28 | Chamberlin Street |
-- Theft of the CS50 duck took place at 10:15am at the Chamberlin Street courthouse.
-- Interviews were conducted today with three witnesses who were present at the time each of their interview transcripts mentions the courthouse.

-- get information about interviews held at 10: 15 am
SELECT * FROM interviews WHERE DAY = 28 AND MONTH = 7 AND YEAR = 2020 ORDER BY (name);
-- RESULT
-- id  | name | year | month | day | transcript
-- 162 | Eugene | 2020 | 7 | 28 | I don't know the thief's name, but it was someone I recognized. Earlier this morning, before I arrived at the courthouse, I was walking by the ATM on Fifer Street and saw the thief there withdrawing some money.
-- 163 | Raymond | 2020 | 7 | 28 | As the thief was leaving the courthouse, 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.
-- 161 | Ruth | 2020 | 7 | 28 | Sometime within ten minutes of the theft, I saw the thief get into a car in the courthouse parking lot and drive away. If you have security footage from the courthouse parking lot, you might want to look for cars that left the parking lot in that time frame.

-- get Engene's trail to lead anyone who withdrew on Fifer street
SELECT * FROM atm_transactions
WHERE atm_location = "Fifer Street" AND DAY = 28 AND MONTH = 7 AND YEAR = 2020 AND transaction_type = "withdraw";
-- id | account_number | year | month | day | atm_location | transaction_type | amount
-- 246 | 28500762 | 2020 | 7 | 28 | Fifer Street | withdraw | 48
-- 264 | 28296815 | 2020 | 7 | 28 | Fifer Street | withdraw | 20
-- 266 | 76054385 | 2020 | 7 | 28 | Fifer Street | withdraw | 60
-- 267 | 49610011 | 2020 | 7 | 28 | Fifer Street | withdraw | 50
-- 269 | 16153065 | 2020 | 7 | 28 | Fifer Street | withdraw | 80
-- 288 | 25506511 | 2020 | 7 | 28 | Fifer Street | withdraw | 20
-- 313 | 81061156 | 2020 | 7 | 28 | Fifer Street | withdraw | 30
-- 336 | 26013199 | 2020 | 7 | 28 | Fifer Street | withdraw | 35

-- get Raymond's trail to get calls less than a minute on the said day
SELECT * FROM phone_calls WHERE  DAY = 28 AND MONTH = 7 AND YEAR = 2020 AND duration < 60;
-- id | caller | receiver | year | month | day | duration
-- 221 | (130) 555-0289 | (996) 555-8899 | 2020 | 7 | 28 | 51
-- 224 | (499) 555-9472 | (892) 555-8872 | 2020 | 7 | 28 | 36
-- 233 | (367) 555-5533 | (375) 555-8161 | 2020 | 7 | 28 | 45
-- 251 | (499) 555-9472 | (717) 555-1342 | 2020 | 7 | 28 | 50
-- 254 | (286) 555-6063 | (676) 555-6554 | 2020 | 7 | 28 | 43
-- 255 | (770) 555-1861 | (725) 555-3243 | 2020 | 7 | 28 | 49
-- 261 | (031) 555-6622 | (910) 555-3251 | 2020 | 7 | 28 | 38
-- 279 | (826) 555-1652 | (066) 555-9701 | 2020 | 7 | 28 | 55
-- 281 | (338) 555-6650 | (704) 555-2131 | 2020 | 7 | 28 | 54

-- Get Ruth's trail to identify who left the parking lot within 10 minutes of the theft
SELECT * FROM courthouse_security_logs
WHERE DAY = 28 AND MONTH = 7 AND YEAR = 2020 AND HOUR = 10 AND activity = "exit" AND (MINUTE > 15 AND MINUTE < 25);
-- id | year | month | day | hour | minute | activity | license_plate
-- 260 | 2020 | 7 | 28 | 10 | 16 | exit | 5P2BI95
-- 261 | 2020 | 7 | 28 | 10 | 18 | exit | 94KL13X
-- 262 | 2020 | 7 | 28 | 10 | 18 | exit | 6P58WS2
-- 263 | 2020 | 7 | 28 | 10 | 19 | exit | 4328GD8
-- 264 | 2020 | 7 | 28 | 10 | 20 | exit | G412CB7
-- 265 | 2020 | 7 | 28 | 10 | 21 | exit | L93JTIZ
-- 266 | 2020 | 7 | 28 | 10 | 23 | exit | 322W7JE
-- 267 | 2020 | 7 | 28 | 10 | 23 | exit | 0NTHK55

-- GET the names of all airport flight the day after the theft
SELECT airports.id, abbreviation, full_name, destination_airport_id, city, HOUR, MINUTE FROM airports
JOIN flights ON flights.destination_airport_id = airports.id
WHERE origin_airport_id = 8 AND DAY = 29 AND MONTH = 7 AND YEAR = 2020;
-- id | abbreviation | full_name | destination_airport_id | city | hour | minute
-- 6 | BOS | Logan International Airport | 6 | Boston | 16 | 0
-- 11 | SFO | San Francisco International Airport | 11 | San Francisco | 12 | 15
-- 4 | LHR | Heathrow Airport | 4 | London | 8 | 20
-- 1 | ORD | O'Hare International Airport | 1 | Chicago | 9 | 30
-- 9 | HND | Tokyo International Airport | 9 | Tokyo | 15 | 20

-- get all airport flights with origin being fiftyville (flight will be the following day - 29th july)
SELECT * FROM flights WHERE DAY = 29 AND MONTH = 7 AND YEAR = 2020;
-- id | origin_airport_id | destination_airport_id | year | month | day | hour | minute
-- 18 | 8 | 6 | 2020 | 7 | 29 | 16 | 0
-- 23 | 8 | 11 | 2020 | 7 | 29 | 12 | 15
-- 36 | 8 | 4 | 2020 | 7 | 29 | 8 | 20
-- 43 | 8 | 1 | 2020 | 7 | 29 | 9 | 30
-- 53 | 8 | 9 | 2020 | 7 | 29 | 15 | 20

--- get all passengers in the earliest flight of the day
SELECT people.name, people.id, people.license_plate, people.phone_number, people.passport_number FROM people
JOIN passengers ON passengers.passport_number = people.passport_number
WHERE flight_id = 36;
-- name | id | license_plate | phone_number | passport_number
-- Doris | 953679 | M51FA04 | (066) 555-9701 | 7214083635
-- Roger | 398010 | G412CB7 | (130) 555-0289 | 1695452385
-- Ernest | 686048 | 94KL13X | (367) 555-5533 | 5773159633
-- Edward | 651714 | 130LD9Z | (328) 555-1152 | 1540955065
-- Evelyn | 560886 | 0NTHK55 | (499) 555-9472 | 8294398571
-- Madison | 449774 | 1106N58 | (286) 555-6063 | 1988161715
-- Bobby | 395717 | 30G67EN | (826) 555-1652 | 9878712108
-- Danielle | 467400 | 4328GD8 | (389) 555-5198 | 8496433585


-- get names and id of everyone who exited the parking lot within 10 minutesof the crime
SELECT people.name, people.id, people.license_plate, people.phone_number, people.passport_number FROM people
JOIN courthouse_security_logs ON courthouse_security_logs.license_plate = people.license_plate
WHERE DAY = 28 AND MONTH = 7 AND YEAR = 2020 AND HOUR = 10 AND activity = "exit" AND MINUTE  < 25;
-- name | id | license_plate | phone_number | passport_number
-- Patrick | 221103 | 5P2BI95 | (725) 555-4692 | 2963008352
-- Amber | 243696 | 6P58WS2 | (301) 555-4174 | 7526138472
-- Elizabeth | 396669 | L93JTIZ | (829) 555-5269 | 7049073643
-- Roger | 398010 | G412CB7 | (130) 555-0289 | 1695452385
-- Danielle | 467400 | 4328GD8 | (389) 555-5198 | 8496433585
-- Russell | 514354 | 322W7JE | (770) 555-1861 | 3592750733
-- Evelyn | 560886 | 0NTHK55 | (499) 555-9472 | 8294398571
-- Ernest | 686048 | 94KL13X | (367) 555-5533 | 5773159633

-- selects the name and bank account of everyone who exited the premises on the said day
SELECT people.name, account_number, people.id, people.license_plate, people.phone_number, people.passport_number FROM people
JOIN bank_accounts ON bank_accounts.person_id = people.id
JOIN passengers ON passengers.passport_number = people.passport_number
JOIN courthouse_security_logs ON courthouse_security_logs.license_plate = people.license_plate
WHERE DAY = 28 AND MONTH = 7 AND YEAR = 2020 AND HOUR = 10 AND activity = "exit" AND MINUTE  < 25 AND flight_id = 4;

-- name | account_number | id | license_plate | phone_number | passport_number
-- Ernest | 49610011 | 686048 | 94KL13X | (367) 555-5533 | 5773159633
-- Russell | 26013199 | 514354 | 322W7JE | (770) 555-1861 | 3592750733
-- Elizabeth | 25506511 | 396669 | L93JTIZ | (829) 555-5269 | 7049073643
-- Danielle | 28500762 | 467400 | 4328GD8 | (389) 555-5198 | 8496433585
-- Amber | 56171033 | 243696 | 6P58WS2 | (301) 555-4174 | 7526138472

-- tag the passport number to the suspect
SELECT people.name, account_number, people.id, people.license_plate, people.phone_number, people.passport_number FROM people
JOIN bank_accounts ON bank_accounts.person_id = people.id
JOIN passengers ON passengers.passport_number = people.passport_number
JOIN courthouse_security_logs ON courthouse_security_logs.license_plate = people.license_plate
WHERE DAY = 28 AND MONTH = 7 AND YEAR = 2020 AND HOUR = 10 AND activity = "exit" AND MINUTE  < 25
GROUP BY people.name;
-- name | account_number | id | license_plate | phone_number | passport_number
-- Danielle | 28500762 | 467400 | 4328GD8 | (389) 555-5198 | 8496433585
-- Elizabeth | 25506511 | 396669 | L93JTIZ | (829) 555-5269 | 7049073643
-- Ernest | 49610011 | 686048 | 94KL13X | (367) 555-5533 | 5773159633
-- Russell | 26013199 | 514354 | 322W7JE | (770) 555-1861 | 3592750733

-- Link bank account, is, hone number, licence plates of everyone to see who matches the query of the said day

SELECT people.name, bank_accounts.account_number, people.id, people.license_plate, people.phone_number, people.passport_number FROM people
JOIN bank_accounts ON bank_accounts.person_id = people.id
JOIN atm_transactions ON atm_transactions.account_number = bank_accounts.account_number
JOIN phone_calls ON phone_calls.caller = people.phone_number
JOIN passengers ON passengers.passport_number = people.passport_number
JOIN courthouse_security_logs ON courthouse_security_logs.license_plate = people.license_plate
WHERE courthouse_security_logs.DAY = 28 AND courthouse_security_logs.MONTH = 7 AND courthouse_security_logs.YEAR = 2020
AND courthouse_security_logs.HOUR = 10 AND courthouse_security_logs.activity = "exit"
AND courthouse_security_logs.MINUTE  < 25 AND atm_location = "Fifer Street" AND transaction_type = "withdraw"
GROUP BY people.name;

-- name | account_number | id | license_plate | phone_number | passport_number
-- Danielle | 28500762 | 467400 | 4328GD8 | (389) 555-5198 | 8496433585
-- Elizabeth | 25506511 | 396669 | L93JTIZ | (829) 555-5269 | 7049073643
-- Ernest | 49610011 | 686048 | 94KL13X | (367) 555-5533 | 5773159633
-- Russell | 26013199 | 514354 | 322W7JE | (770) 555-1861 | 3592750733

-- Use phone calls to filter out suspects
SELECT people.name, bank_accounts.account_number, people.id, people.license_plate, people.phone_number, people.passport_number FROM people
JOIN bank_accounts ON bank_accounts.person_id = people.id
JOIN atm_transactions ON atm_transactions.account_number = bank_accounts.account_number
JOIN phone_calls ON phone_calls.caller = people.phone_number
JOIN passengers ON passengers.passport_number = people.passport_number
JOIN flights ON flights.id = passengers.flight_id
JOIN courthouse_security_logs ON courthouse_security_logs.license_plate = people.license_plate
WHERE courthouse_security_logs.DAY = 28 AND courthouse_security_logs.MONTH = 7
AND courthouse_security_logs.YEAR = 2020 AND courthouse_security_logs.HOUR = 10
AND courthouse_security_logs.activity = "exit" AND courthouse_security_logs.MINUTE < 25
AND atm_location = "Fifer Street" AND phone_calls.duration < 60 AND flights.DAY = 29
AND flights.MONTH = 7 AND flights.YEAR = 2020 AND transaction_type = "withdraw"
GROUP BY people.name;

-- name | account_number | id | license_plate | phone_number | passport_number
-- Ernest | 49610011 | 686048 | 94KL13X | (367) 555-5533 | 5773159633
-- Russell | 26013199 | 514354 | 322W7JE | (770) 555-1861 | 3592750733

-- use the earliest flight to filter the culprit (Raymond said they were planning to take the earliest flight)
-- Who was the thief?
SELECT people.name, bank_accounts.account_number, people.id, people.license_plate, people.phone_number, people.passport_number FROM people
JOIN bank_accounts ON bank_accounts.person_id = people.id
JOIN atm_transactions ON atm_transactions.account_number = bank_accounts.account_number
JOIN phone_calls ON phone_calls.caller = people.phone_number
JOIN passengers ON passengers.passport_number = people.passport_number
JOIN flights ON flights.id = passengers.flight_id
JOIN courthouse_security_logs ON courthouse_security_logs.license_plate = people.license_plate
WHERE courthouse_security_logs.DAY = 28 AND courthouse_security_logs.MONTH = 7
AND courthouse_security_logs.YEAR = 2020 AND courthouse_security_logs.HOUR = 10
AND courthouse_security_logs.activity = "exit" AND courthouse_security_logs.MINUTE < 25
AND atm_location = "Fifer Street" AND phone_calls.duration < 60 AND flights.DAY = 29
AND flights.MONTH = 7 AND flights.YEAR = 2020 AND transaction_type = "withdraw"
AND flights.id = 36
GROUP BY people.name;

-- Ernest | 49610011 | 686048 | 94KL13X | (367) 555-5533 | 5773159633


-- Who did Ernest call?
SELECT * FROM phone_calls WHERE phone_calls.caller = "(367) 555-5533" AND DAY = 28 AND MONTH = 7 AND YEAR = 2020 AND duration < 60;
-- id | caller | receiver | year | month | day | duration
-- 233 | (367) 555-5533 | (375) 555-8161 | 2020 | 7 | 28 | 45

-- get the name of the accomplist
SELECT * FROM people WHERE people.phone_number = "(375) 555-8161";
-- id | name | phone_number | passport_number | license_plate
-- 864400 | Berthold | (375) 555-8161 |  | 4V16VO0

-- Berthold was his accomplice.