Skip to content

Instantly share code, notes, and snippets.

@andrija-zikovic

andrija-zikovic/log.sql

Created May 26, 2023 11:02
Show Gist options
  • Save andrija-zikovic/77e584feb3f885fcea93951ffbe8d93f to your computer and use it in GitHub Desktop.
Save andrija-zikovic/77e584feb3f885fcea93951ffbe8d93f to your computer and use it in GitHub Desktop.
Download ZIP
CS50/fiftyville
Raw
log.sql
-- Keep a log of any SQL queries you execute as you solve the mystery.
SELECT description FROM crime_scene_reports WHERE year = '2021' AND day = '28' AND month = '7' AND street = 'Humphrey Street';
-- 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.
SELECT * FROM interviews WHERE year = '2021' AND day = '28' AND month = '7' AND transcript LIKE '%bakery%' ;
-- | 161 | Ruth | 2021 | 7 | 28 | 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.
-- | 162 | Eugene | 2021 | 7 | 28 | 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.
-- | 163 | Raymond | 2021 | 7 | 28 | 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.
SELECT * FROM bakery_security_logs WHERE year = '2021' AND day = '28' AND month = '7' AND hour = '10' AND minute >= '10' AND minute <= '25' AND activity = 'exit' ORDER BY hour, minute;
-- All the exits from a parking lot in time aroud the robery
--+-----+------+-------+-----+------+--------+----------+---------------+
--| id | year | month | day | hour | minute | activity | license_plate |
--+-----+------+-------+-----+------+--------+----------+---------------+
--| 260 | 2021 | 7 | 28 | 10 | 16 | exit | 5P2BI95 |
--| 261 | 2021 | 7 | 28 | 10 | 18 | exit | 94KL13X |
--| 262 | 2021 | 7 | 28 | 10 | 18 | exit | 6P58WS2 |
--| 263 | 2021 | 7 | 28 | 10 | 19 | exit | 4328GD8 |
--| 264 | 2021 | 7 | 28 | 10 | 20 | exit | G412CB7 |
--| 265 | 2021 | 7 | 28 | 10 | 21 | exit | L93JTIZ |
--| 266 | 2021 | 7 | 28 | 10 | 23 | exit | 322W7JE |
--| 267 | 2021 | 7 | 28 | 10 | 23 | exit | 0NTHK55 |
SELECT * FROM people WHERE license_plate IN (SELECT license_plate FROM bakery_security_logs WHERE year = '2021' AND day = '28' AND month = '7' AND hour = '10' AND minute >= '10' AND minute <= '25' AND activity = 'exit' ORDER BY hour, minute);
-- All the peoples license_plate that exit a parking lot at bakery
-- +--------+---------+----------------+-----------------+---------------+
--| id | name | phone_number | passport_number | license_plate |
--+--------+---------+----------------+-----------------+---------------+
--| 221103 | Vanessa | (725) 555-4692 | 2963008352 | 5P2BI95 |
--| 243696 | Barry | (301) 555-4174 | 7526138472 | 6P58WS2 |
--| 396669 | Iman | (829) 555-5269 | 7049073643 | L93JTIZ |
--| 398010 | Sofia | (130) 555-0289 | 1695452385 | G412CB7 |
--| 467400 | Luca | (389) 555-5198 | 8496433585 | 4328GD8 |
--| 514354 | Diana | (770) 555-1861 | 3592750733 | 322W7JE |
--| 560886 | Kelsey | (499) 555-9472 | 8294398571 | 0NTHK55 |
--| 686048 | Bruce | (367) 555-5533 | 5773159633 | 94KL13X |
--+--------+---------+----------------+-----------------+---------------+
SELECT * FROM atm_transactions WHERE year = '2021' AND day = '28' AND month = '7' AND transaction_type = 'withdraw' AND atm_location = 'Leggett Street' ORDER BY account_number ASC;
-- This are the accounts that windraw money from ATM machine that day when are witness sow are suspect
--+-----+----------------+------+-------+-----+----------------+------------------+--------+
--| id | account_number | year | month | day | atm_location | transaction_type | amount |
--+-----+----------------+------+-------+-----+----------------+------------------+--------+
--| 269 | 16153065 | 2021 | 7 | 28 | Leggett Street | withdraw | 80 |
--| 288 | 25506511 | 2021 | 7 | 28 | Leggett Street | withdraw | 20 |
--| 336 | 26013199 | 2021 | 7 | 28 | Leggett Street | withdraw | 35 |
--| 264 | 28296815 | 2021 | 7 | 28 | Leggett Street | withdraw | 20 |
--| 246 | 28500762 | 2021 | 7 | 28 | Leggett Street | withdraw | 48 |
--| 267 | 49610011 | 2021 | 7 | 28 | Leggett Street | withdraw | 50 |
--| 266 | 76054385 | 2021 | 7 | 28 | Leggett Street | withdraw | 60 |
--| 313 | 81061156 | 2021 | 7 | 28 | Leggett Street | withdraw | 30 |
--+-----+----------------+------+-------+-----+----------------+------------------+--------+
SELECT * FROM people JOIN bank_accounts ON people.id = person_id WHERE account_number
IN (SELECT account_number FROM atm_transactions WHERE year = '2021' AND day = '28' AND month = '7' AND transaction_type = 'withdraw' AND atm_location = 'Leggett Street')
ORDER BY account_number ASC;
-- This are the people that windraw money from ATM machine that day when are witness sow are suspect
--+--------+---------+----------------+-----------------+---------------+----------------+-----------+---------------+
--| id | name | phone_number | passport_number | license_plate | account_number | person_id | creation_year |
--+--------+---------+----------------+-----------------+---------------+----------------+-----------+---------------+
--| 458378 | Brooke | (122) 555-4581 | 4408372428 | QX4YZN3 | 16153065 | 458378 | 2012 |
--| 396669 | Iman | (829) 555-5269 | 7049073643 | L93JTIZ | 25506511 | 396669 | 2014 |
--| 514354 | Diana | (770) 555-1861 | 3592750733 | 322W7JE | 26013199 | 514354 | 2012 |
--| 395717 | Kenny | (826) 555-1652 | 9878712108 | 30G67EN | 28296815 | 395717 | 2014 |
--| 467400 | Luca | (389) 555-5198 | 8496433585 | 4328GD8 | 28500762 | 467400 | 2014 |
--| 686048 | Bruce | (367) 555-5533 | 5773159633 | 94KL13X | 49610011 | 686048 | 2010 |
--| 449774 | Taylor | (286) 555-6063 | 1988161715 | 1106N58 | 76054385 | 449774 | 2015 |
--| 438727 | Benista | (338) 555-6650 | 9586786673 | 8X428L0 | 81061156 | 438727 | 2018 |
--+--------+---------+----------------+-----------------+---------------+----------------+-----------+---------------+
SELECT * FROM people JOIN bank_accounts ON people.id = person_id WHERE account_number
IN (SELECT account_number FROM atm_transactions WHERE year = '2021' AND day = '28' AND month = '7' AND transaction_type = 'withdraw' AND atm_location = 'Leggett Street')
AND phone_number IN (SELECT caller FROM phone_calls WHERE year = '2021' AND day = '28' AND month = '7' AND duration < 90)
ORDER BY account_number ASC;
-- This are the people that windraw money from ATM machine and make a call that day when are witness sow are suspect
--+--------+---------+----------------+-----------------+---------------+----------------+-----------+---------------+
--| id | name | phone_number | passport_number | license_plate | account_number | person_id | creation_year |
--+--------+---------+----------------+-----------------+---------------+----------------+-----------+---------------+
--| 514354 | Diana | (770) 555-1861 | 3592750733 | 322W7JE | 26013199 | 514354 | 2012 |
--| 395717 | Kenny | (826) 555-1652 | 9878712108 | 30G67EN | 28296815 | 395717 | 2014 |
--| 686048 | Bruce | (367) 555-5533 | 5773159633 | 94KL13X | 49610011 | 686048 | 2010 |
--| 449774 | Taylor | (286) 555-6063 | 1988161715 | 1106N58 | 76054385 | 449774 | 2015 |
--| 438727 | Benista | (338) 555-6650 | 9586786673 | 8X428L0 | 81061156 | 438727 | 2018 |
--+--------+---------+----------------+-----------------+---------------+----------------+-----------+---------------+
SELECT * FROM phone_calls WHERE caller IN (SELECT phone_number FROM people JOIN bank_accounts ON people.id = person_id WHERE account_number
IN (SELECT account_number FROM atm_transactions WHERE year = '2021' AND day = '28' AND month = '7' AND transaction_type = 'withdraw' AND atm_location = 'Leggett Street')
AND phone_number IN (SELECT caller FROM phone_calls WHERE year = '2021' AND day = '28' AND month = '7' AND duration < 90)) AND duration < 90 AND day = 28 ORDER BY caller;
-- This are all the calls at time after robery
--+-----+----------------+----------------+------+-------+-----+----------+
--| id | caller | receiver | year | month | day | duration |
--+-----+----------------+----------------+------+-------+-----+----------+
--| 254 | (286) 555-6063 | (676) 555-6554 | 2021 | 7 | 28 | 43 |
--| 281 | (338) 555-6650 | (704) 555-2131 | 2021 | 7 | 28 | 54 |
--| 233 | (367) 555-5533 | (375) 555-8161 | 2021 | 7 | 28 | 45 |
--| 285 | (367) 555-5533 | (704) 555-5790 | 2021 | 7 | 28 | 75 |
--| 255 | (770) 555-1861 | (725) 555-3243 | 2021 | 7 | 28 | 49 |
--| 279 | (826) 555-1652 | (066) 555-9701 | 2021 | 7 | 28 | 55 |
--+-----+----------------+----------------+------+-------+-----+----------+
SELECT * FROM people WHERE phone_number
IN (SELECT receiver FROM phone_calls WHERE caller IN (SELECT phone_number FROM people JOIN bank_accounts ON people.id = person_id WHERE account_number
IN (SELECT account_number FROM atm_transactions WHERE year = '2021' AND day = '28' AND month = '7' AND transaction_type = 'withdraw' AND atm_location = 'Leggett Street')
AND phone_number IN (SELECT caller FROM phone_calls WHERE year = '2021' AND day = '28' AND month = '7' AND duration < 90)) AND duration < 90 AND day = 28 );
-- This are people that recive call from are suspects
--+--------+--------+----------------+-----------------+---------------+
--| id | name | phone_number | passport_number | license_plate |
--+--------+--------+----------------+-----------------+---------------+
--| 250277 | James | (676) 555-6554 | 2438825627 | Q13SVG6 |
--| 484375 | Anna | (704) 555-2131 | | |
--| 652398 | Carl | (704) 555-5790 | 7771405611 | 81MZ921 |
--| 847116 | Philip | (725) 555-3243 | 3391710505 | GW362R6 |
--| 864400 | Robin | (375) 555-8161 | | 4V16VO0 |
--| 953679 | Doris | (066) 555-9701 | 7214083635 | M51FA04 |
--+--------+--------+----------------+-----------------+---------------+
SELECT * FROM airports WHERE city = 'Fiftyville';
-- This is are airport
--+----+--------------+-----------------------------+------------+
--| id | abbreviation | full_name | city |
--+----+--------------+-----------------------------+------------+
--| 8 | CSF | Fiftyville Regional Airport | Fiftyville |
--+----+--------------+-----------------------------+------------+
SELECT flights.* FROM flights JOIN airports ON origin_airport_id = airports.id WHERE airports.id = (SELECT id FROM airports WHERE city = 'Fiftyville') AND year = 2021 AND month = 7 AND day = 29 ORDER BY hour ASC LIMIT 1 ;
-- This is the first flight next morning
--+----+-------------------+------------------------+------+-------+-----+------+--------+
--| id | origin_airport_id | destination_airport_id | year | month | day | hour | minute |
--+----+-------------------+------------------------+------+-------+-----+------+--------+
--| 36 | 8 | 4 | 2021 | 7 | 29 | 8 | 20 |
--+----+-------------------+------------------------+------+-------+-----+------+--------+
SELECT * FROM passengers WHERE
flight_id = (SELECT flights.id FROM flights JOIN airports ON origin_airport_id = airports.id
WHERE airports.id = (SELECT id FROM airports WHERE city = 'Fiftyville')
AND year = 2021 AND month = 7 AND day = 29 ORDER BY hour ASC LIMIT 1 );
-- This are all passenger from that flight
--+-----------+-----------------+------+
--| flight_id | passport_number | seat |
--+-----------+-----------------+------+
--| 36 | 7214083635 | 2A |
--| 36 | 1695452385 | 3B |
--| 36 | 5773159633 | 4A |
--| 36 | 1540955065 | 5C |
--| 36 | 8294398571 | 6C |
--| 36 | 1988161715 | 6D |
--| 36 | 9878712108 | 7A |
--| 36 | 8496433585 | 7B |
--+-----------+-----------------+------+
SELECT * FROM people WHERE passport_number IN (SELECT passport_number FROM passengers WHERE
flight_id = (SELECT flights.id FROM flights JOIN airports ON origin_airport_id = airports.id
WHERE airports.id = (SELECT id FROM airports WHERE city = 'Fiftyville')
AND year = 2021 AND month = 7 AND day = 29 ORDER BY hour ASC LIMIT 1 ));
-- This are passenger from flight id = 36
--+--------+--------+----------------+-----------------+---------------+
--| id | name | phone_number | passport_number | license_plate |
--+--------+--------+----------------+-----------------+---------------+
--| 395717 | Kenny | (826) 555-1652 | 9878712108 | 30G67EN |
--| 398010 | Sofia | (130) 555-0289 | 1695452385 | G412CB7 |
--| 449774 | Taylor | (286) 555-6063 | 1988161715 | 1106N58 |
--| 467400 | Luca | (389) 555-5198 | 8496433585 | 4328GD8 |
--| 560886 | Kelsey | (499) 555-9472 | 8294398571 | 0NTHK55 |
--| 651714 | Edward | (328) 555-1152 | 1540955065 | 130LD9Z |
--| 686048 | Bruce | (367) 555-5533 | 5773159633 | 94KL13X |
--| 953679 | Doris | (066) 555-9701 | 7214083635 | M51FA04 |
--+--------+--------+----------------+-----------------+---------------+
-- Creating new table of suspects
CREATE TABLE suspects (
id INTEGER,
name TEXT,
phone_number INTEGER,
passport_number INTEGER,
license_plate INTEGER,
PRIMARY KEY(id)
);
-- Filling the table with INFO
INSERT INTO suspects (id, name, phone_number, passport_number, license_plate)
SELECT id, name, phone_number, passport_number, license_plate
FROM (SELECT * FROM people WHERE passport_number IN (SELECT passport_number FROM passengers WHERE
flight_id = (SELECT flights.id FROM flights JOIN airports ON origin_airport_id = airports.id
WHERE airports.id = (SELECT id FROM airports WHERE city = 'Fiftyville')
AND year = 2021 AND month = 7 AND day = 29 ORDER BY hour ASC LIMIT 1 )));
SELECT * FROM suspects WHERE id IN (SELECT id FROM people WHERE license_plate IN (SELECT license_plate FROM bakery_security_logs WHERE year = '2021' AND day = '28' AND month = '7' AND hour = '10' AND minute >= '10' AND minute <= '25' AND activity = 'exit' ORDER BY hour, minute));
-- This are all the people that are living the parking loot 10 min after the robery and are on the flight 36
-- +--------+--------+----------------+-----------------+---------------+
--| id | name | phone_number | passport_number | license_plate |
--+--------+--------+----------------+-----------------+---------------+
--| 398010 | Sofia | (130) 555-0289 | 1695452385 | G412CB7 |
--| 467400 | Luca | (389) 555-5198 | 8496433585 | 4328GD8 |
--| 560886 | Kelsey | (499) 555-9472 | 8294398571 | 0NTHK55 |
--| 686048 | Bruce | (367) 555-5533 | 5773159633 | 94KL13X |
--+--------+--------+----------------+-----------------+---------------+
SELECT * FROM suspects WHERE id IN (SELECT id FROM people WHERE license_plate IN (SELECT license_plate FROM bakery_security_logs WHERE year = '2021' AND day = '28' AND month = '7' AND hour = '10' AND minute >= '10' AND minute <= '25' AND activity = 'exit' ORDER BY hour, minute)) AND phone_number IN (SELECT caller FROM phone_calls WHERE caller IN (SELECT phone_number FROM people JOIN bank_accounts ON people.id = person_id WHERE account_number
IN (SELECT account_number FROM atm_transactions WHERE year = '2021' AND day = '28' AND month = '7' AND transaction_type = 'withdraw' AND atm_location = 'Leggett Street')
AND phone_number IN (SELECT caller FROM phone_calls WHERE year = '2021' AND day = '28' AND month = '7' AND duration < 90)) AND duration < 90 AND day = 28 ORDER BY caller);
-- This are the people that windthraw money from atm, living the parking lot after robery, making a call after robery and are on the Flight 36
-- +--------+-------+----------------+-----------------+---------------+
-- | id | name | phone_number | passport_number | license_plate |
-- +--------+-------+----------------+-----------------+---------------+
-- | 686048 | Bruce | (367) 555-5533 | 5773159633 | 94KL13X |
-- +--------+-------+----------------+-----------------+---------------+
-- This is are rober
SELECT * FROM phone_calls WHERE caller IN (SELECT phone_number FROM suspects WHERE id IN (SELECT id FROM people WHERE license_plate IN (SELECT license_plate FROM bakery_security_logs WHERE year = '2021' AND day = '28' AND month = '7' AND hour = '10' AND minute >= '10' AND minute <= '25' AND activity = 'exit' ORDER BY hour, minute)) AND phone_number IN (SELECT caller FROM phone_calls WHERE caller IN (SELECT phone_number FROM people JOIN bank_accounts ON people.id = person_id WHERE account_number
IN (SELECT account_number FROM atm_transactions WHERE year = '2021' AND day = '28' AND month = '7' AND transaction_type = 'withdraw' AND atm_location = 'Leggett Street')
AND phone_number IN (SELECT caller FROM phone_calls WHERE year = '2021' AND day = '28' AND month = '7' AND duration < 90)) AND duration < 90 AND day = 28 ORDER BY caller)) AND year = 2021 AND month = 7 AND day = 28 AND duration < 60;
-- This is the calls that are rober make
-- +-----+----------------+----------------+------+-------+-----+----------+
-- | id | caller | receiver | year | month | day | duration |
-- +-----+----------------+----------------+------+-------+-----+----------+
-- | 233 | (367) 555-5533 | (375) 555-8161 | 2021 | 7 | 28 | 45 |
-- +-----+----------------+----------------+------+-------+-----+----------+
SELECT * FROM people WHERE phone_number IN (SELECT receiver FROM phone_calls WHERE caller IN (SELECT phone_number FROM suspects WHERE id IN (SELECT id FROM people WHERE license_plate IN (SELECT license_plate FROM bakery_security_logs WHERE year = '2021' AND day = '28' AND month = '7' AND hour = '10' AND minute >= '10' AND minute <= '25' AND activity = 'exit' ORDER BY hour, minute)) AND phone_number IN (SELECT caller FROM phone_calls WHERE caller IN (SELECT phone_number FROM people JOIN bank_accounts ON people.id = person_id WHERE account_number
IN (SELECT account_number FROM atm_transactions WHERE year = '2021' AND day = '28' AND month = '7' AND transaction_type = 'withdraw' AND atm_location = 'Leggett Street')
AND phone_number IN (SELECT caller FROM phone_calls WHERE year = '2021' AND day = '28' AND month = '7' AND duration < 90)) AND duration < 90 AND day = 28 ORDER BY caller)) AND year = 2021 AND month = 7 AND day = 28 AND duration < 60);
-- This is the person that are rober call for buying a ticket
-- +--------+-------+----------------+-----------------+---------------+
-- | id | name | phone_number | passport_number | license_plate |
-- +--------+-------+----------------+-----------------+---------------+
-- | 864400 | Robin | (375) 555-8161 | | 4V16VO0 |
-- +--------+-------+----------------+-----------------+---------------+
SELECT * FROM airports WHERE id IN (SELECT flights.destination_airport_id FROM flights JOIN airports ON origin_airport_id = airports.id WHERE airports.id = (SELECT id FROM airports WHERE city = 'Fiftyville') AND year = 2021 AND month = 7 AND day = 29 ORDER BY hour ASC LIMIT 1 )
-- This is are destination airport
-- +----+--------------+-------------------+---------------+
-- | id | abbreviation | full_name | city |
-- +----+--------------+-------------------+---------------+
-- | 4 | LGA | LaGuardia Airport | New York City |
-- +----+--------------+-------------------+---------------+
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment