Hector Chaires

Learning Computational Chemistry and CS

12 minute read

Exercise 3 Fiftyville - Identify the thief of the CS50 duck using SQL queries. Identify the city the thief escaped to and their accomplice based on the database created for this crime.

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

-- The THIEF is: Bruce
-- The city the thief ESCAPED TO: NYC
-- The ACCOMPLICE is: Robin


-- Robin is the accomplice lol

+--------+-------+----------------+-----------------+---------------+
|   id   | name  |  phone_number  | passport_number | license_plate |
+--------+-------+----------------+-----------------+---------------+
| 864400 | Robin | (375) 555-8161 | NULL            | 4V16VO0       |
+--------+-------+----------------+-----------------+---------------+

SELECT *
FROM people
WHERE phone_number = "(375) 555-8161";

-- It was Bruce based on the earliest flight on 29th info. The city the thief escaped to is NYC and the accomplice has this nunmber (375) 555-8161

+-----------+-----------------+------+
| 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 passengers
WHERE flight_id = 36;

-- | flight_id | year | month | day | hour | minute | origin_abbr |      origin_full_name       | origin_city | dest_abbr |           dest_full_name            |   dest_city   |
-- +-----------+------+-------+-----+------+--------+-------------+-----------------------------+-------------+-----------+-------------------------------------+---------------+
-- | 18        | 2024 | 7     | 29  | 16   | 0      | CSF         | Fiftyville Regional Airport | Fiftyville  | BOS       | Logan International Airport         | Boston        |
-- | 23        | 2024 | 7     | 29  | 12   | 15     | CSF         | Fiftyville Regional Airport | Fiftyville  | SFO       | San Francisco International Airport | San Francisco |
-- | 36        | 2024 | 7     | 29  | 8    | 20     | CSF         | Fiftyville Regional Airport | Fiftyville  | LGA       | LaGuardia Airport                   | New York City |
-- | 43        | 2024 | 7     | 29  | 9    | 30     | CSF         | Fiftyville Regional Airport | Fiftyville  | ORD       | O'Hare International Airport        | Chicago       |
-- | 53        | 2024 | 7     | 29  | 15   | 20     | CSF         | Fiftyville Regional Airport | Fiftyville  | HND       | Tokyo International Airport         | Tokyo

SELECT
    flights.id AS flight_id,
    flights.year,
    flights.month,
    flights.day,
    flights.hour,
    flights.minute,
    origin.abbreviation AS origin_abbr,
    origin.full_name AS origin_full_name,
    origin.city AS origin_city,
    destination.abbreviation AS dest_abbr,
    destination.full_name AS dest_full_name,
    destination.city AS dest_city
FROM flights
JOIN airports AS origin
    ON flights.origin_airport_id = origin.id
JOIN airports AS destination
    ON flights.destination_airport_id = destination.id
WHERE flights.day = 29;

-- so far most likely to be Bruce since both withdrew cash from Leggett but Bruce did it earlier in the day

+-------+----------------+-----------------+---------------+----------------+---------------+
| name  |  phone_number  | passport_number | license_plate | account_number | creation_year |
+-------+----------------+-----------------+---------------+----------------+---------------+
| Bruce | (367) 555-5533 | 5773159633      | 94KL13X       | 49610011       | 2010          |
| Diana | (770) 555-1861 | 3592750733      | 322W7JE       | 26013199       | 2012          |
+-------+----------------+-----------------+---------------+----------------+---------------+

SELECT people.name, people.phone_number, people.passport_number, people.license_plate, bank_accounts.account_number, bank_accounts.creation_year
FROM people
JOIN bank_accounts
    ON people.id = bank_accounts.person_id
WHERE people.name IN ('Sofia', 'Diana', 'Kelsey', 'Bruce');


+--------+----------------+-----------------+---------------+--------+
|  name  |  phone_number  | passport_number | license_plate |   id   |
+--------+----------------+-----------------+---------------+--------+
| Sofia  | (130) 555-0289 | 1695452385      | G412CB7       | 398010 |
| Diana  | (770) 555-1861 | 3592750733      | 322W7JE       | 514354 |
| Kelsey | (499) 555-9472 | 8294398571      | 0NTHK55       | 560886 |
| Bruce  | (367) 555-5533 | 5773159633      | 94KL13X       | 686048 |
+--------+----------------+-----------------+---------------+--------+

SELECT name, phone_number, passport_number, license_plate, id
FROM people
WHERE license_plate IN (
    '5P2BI95',
    '94KL13X',
    '6P58WS2',
    '4328GD8',
    'G412CB7',
    'L93JTIZ',
    '322W7JE',
    '0NTHK55'
)
AND phone_number IN (
    '(130) 555-0289',
    '(499) 555-9472',
    '(367) 555-5533',
    '(609) 555-5876',
    '(286) 555-6063',
    '(770) 555-1861',
    '(031) 555-6622',
    '(826) 555-1652',
    '(338) 555-6650'
);

+-----+----------------+----------------+------+-------+-----+----------+
| id  |     caller     |    receiver    | year | month | day | duration |
+-----+----------------+----------------+------+-------+-----+----------+
| 221 | (130) 555-0289 | (996) 555-8899 | 2024 | 7     | 28  | 51       |
| 224 | (499) 555-9472 | (892) 555-8872 | 2024 | 7     | 28  | 36       |
| 233 | (367) 555-5533 | (375) 555-8161 | 2024 | 7     | 28  | 45       |
| 234 | (609) 555-5876 | (389) 555-5198 | 2024 | 7     | 28  | 60       |
| 251 | (499) 555-9472 | (717) 555-1342 | 2024 | 7     | 28  | 50       |
| 254 | (286) 555-6063 | (676) 555-6554 | 2024 | 7     | 28  | 43       |
| 255 | (770) 555-1861 | (725) 555-3243 | 2024 | 7     | 28  | 49       |
| 261 | (031) 555-6622 | (910) 555-3251 | 2024 | 7     | 28  | 38       |
| 279 | (826) 555-1652 | (066) 555-9701 | 2024 | 7     | 28  | 55       |
| 281 | (338) 555-6650 | (704) 555-2131 | 2024 | 7     | 28  | 54       |
+-----+----------------+----------------+------+-------+-----+----------+

SELECT id, caller, receiver, year, month, day, duration
FROM phone_calls
WHERE day = 28
AND duration <=60;

CREATE TABLE phone_calls (
    id INTEGER,
    caller TEXT,
    receiver TEXT,
    year INTEGER,
    month INTEGER,
    day INTEGER,
    duration INTEGER,
    PRIMARY KEY(id)
);

+---------+----------------+-----------------+---------------+--------+
|  name   |  phone_number  | passport_number | license_plate |   id   |
+---------+----------------+-----------------+---------------+--------+
| Vanessa | (725) 555-4692 | 2963008352      | 5P2BI95       | 221103 |
| Barry   | (301) 555-4174 | 7526138472      | 6P58WS2       | 243696 |
| Iman    | (829) 555-5269 | 7049073643      | L93JTIZ       | 396669 |
| Sofia   | (130) 555-0289 | 1695452385      | G412CB7       | 398010 |
| Luca    | (389) 555-5198 | 8496433585      | 4328GD8       | 467400 |
| Diana   | (770) 555-1861 | 3592750733      | 322W7JE       | 514354 |
| Kelsey  | (499) 555-9472 | 8294398571      | 0NTHK55       | 560886 |
| Bruce   | (367) 555-5533 | 5773159633      | 94KL13X       | 686048 |
+---------+----------------+-----------------+---------------+--------+

SELECT name, phone_number, passport_number, license_plate, id
FROM people
WHERE license_plate IN (
    '5P2BI95',
    '94KL13X',
    '6P58WS2',
    '4328GD8',
    'G412CB7',
    'L93JTIZ',
    '322W7JE',
    '0NTHK55'
);


| 246 | 28500762       | 2024 | 7     | 28  | Leggett Street       | withdraw         | 48     |
| 264 | 28296815       | 2024 | 7     | 28  | Leggett Street       | withdraw         | 20     |
| 266 | 76054385       | 2024 | 7     | 28  | Leggett Street       | withdraw         | 60     |
| 267 | 49610011       | 2024 | 7     | 28  | Leggett Street       | withdraw         | 50     |
| 269 | 16153065       | 2024 | 7     | 28  | Leggett Street       | withdraw         | 80     |
| 275 | 86363979       | 2024 | 7     | 28  | Leggett Street       | deposit          | 10     |
| 288 | 25506511       | 2024 | 7     | 28  | Leggett Street       | withdraw         | 20     |
| 313 | 81061156       | 2024 | 7     | 28  | Leggett Street       | withdraw         | 30     |
| 336 | 26013199       | 2024 | 7     | 28  | Leggett Street       | withdraw         | 35     |

SELECT id, account_number, year, month, day, atm_location, transaction_type, amount
FROM atm_transactions
WHERE day = 28;

| 2024 | 7     | 28  | 9    | 14     | entrance | 4328GD8       |
| 2024 | 7     | 28  | 9    | 15     | entrance | 5P2BI95       |
| 2024 | 7     | 28  | 9    | 20     | entrance | 6P58WS2       |
| 2024 | 7     | 28  | 9    | 28     | entrance | G412CB7       |
| 2024 | 7     | 28  | 10   | 8      | entrance | R3G7486       |
| 2024 | 7     | 28  | 10   | 14     | entrance | 13FNH73       |
| 2024 | 7     | 28  | 10   | 16     | exit     | 5P2BI95       |
| 2024 | 7     | 28  | 10   | 18     | exit     | 94KL13X       |
| 2024 | 7     | 28  | 10   | 18     | exit     | 6P58WS2       |
| 2024 | 7     | 28  | 10   | 19     | exit     | 4328GD8       |
| 2024 | 7     | 28  | 10   | 20     | exit     | G412CB7       |
| 2024 | 7     | 28  | 10   | 21     | exit     | L93JTIZ       |
| 2024 | 7     | 28  | 10   | 23     | exit     | 322W7JE       |
| 2024 | 7     | 28  | 10   | 23     | exit     | 0NTHK55       |
| 2024 | 7     | 28  | 10   | 35     | exit     | 1106N58

SELECT year, month, day, hour, minute, activity, license_plate
FROM bakery_security_logs
WHERE month >= 7;

--                              | Eugene      |
-- | 2024 | 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.

--                              | Ruth        |
-- | 2024 | 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.

--                              | Barbara     |
-- | 2024 | 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.

SELECT year, month, day, transcript, name
FROM interviews
WHERE month >= 7;

-- | 2024 | 7     | 28  | 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 year, month, day, street, description
FROM crime_scene_reports;

Exercise 2 Movies - Use IMDB’s database to complete various SQL queries.

In 13.sql, write a SQL query to list the names of all people who starred in a movie in which Kevin Bacon also starred.

SELECT name
FROM people p
JOIN stars s ON p.id = s.person_id
WHERE s.movie_id IN (
    SELECT s2.movie_id
    FROM stars s2
    JOIN people p2 ON s2.person_id = p2.id
    WHERE p2.name = 'Kevin Bacon'
)
AND p.name != 'Kevin Bacon';

In 12.sql, write a SQL query to list the titles of all movies in which both Bradley Cooper and Jennifer Lawrence starred

SELECT title
FROM movies m
JOIN stars s1 ON m.id = s1.movie_id
JOIN people p1 ON s1.person_id = p1.id
JOIN stars s2 ON m.id = s2.movie_id
JOIN people p2 ON s2.person_id = p2.id
WHERE p1.name = "Bradley Cooper" AND p2.name = "Jennifer Lawrence";

In 11.sql, write a SQL query to list the titles of the five highest rated movies (in order)
that Chadwick Boseman starred in, starting with the highest rated.

SELECT title
FROM movies
JOIN ratings ON movies.id = ratings.movie_id
JOIN stars ON movies.id = stars.movie_id
JOIN people ON stars.person_id = people.id
WHERE name = "Chadwick Boseman"
ORDER BY rating DESC
LIMIT 5;

In 10.sql, write a SQL query to list the names of all people who have directed a movie that received a rating of at least 9.0.

SELECT people.name
FROM people
JOIN directors ON people.id = directors.person_id
JOIN ratings ON directors.movie_id = ratings.movie_id
WHERE ratings.rating >= 9.0;

In 9.sql, write a SQL query to list the names of all people who starred in a movie released in 2004, ordered by birth year.

SELECT name
FROM people
JOIN movies ON people.id = movies.id
WHERE movies.year = 2004
ORDER BY birth;

In 8.sql, write a SQL query to list the names of all people who starred in Toy Story.
SELECT name
FROM people
JOIN stars ON people.id = stars.person_id
JOIN movies ON stars.movie_id = movies.id
WHERE movies.title = "Toy Story";

In 7.sql, write a SQL query to list all movies released in 2010 and their ratings,
in descending order by rating. For movies with the same rating, order them alphabetically by title.
SELECT movies.title, ratings.rating
FROM movies
JOIN ratings ON movies.id = ratings.movie_id
WHERE movies.year = 2010
ORDER BY ratings.rating DESC, movies.title ASC;

In 6.sql, write a SQL query to determine the average rating of all movies released in 2012

SELECT AVG(rating)
FROM ratings
JOIN movies ON ratings.movie_id = movies.id
WHERE movies.year = 2012;

In 5.sql, write a SQL query to list the titles and release years of all Harry Potter movies, in chronological order

SELECT title, year
FROM movies
WHERE title LIKE 'Harry Potter%'
ORDER BY year;

In 4.sql, write a SQL query to determine the number of movies with an IMDb rating of 10.0

SELECT COUNT(*)
FROM ratings
WHERE rating >= 10;

In 3.sql, write a SQL query to list the titles of all movies with a release date on or after 2018, in alphabetical order.

SELECT title
FROM movies
WHERE year >= 2018
ORDER BY year;

In 2.sql, write a SQL query to determine the birth year of Emma Stone.

SELECT birth
FROM people
WHERE name = "Emma Stone";

In 1.sql, write a SQL query to list the titles of all movies released in 2008.

SELECT title
FROM movies
WHERE year = 2008;

Exercise 1 Songs - SQL queries on the top 100 songs and associated data from Spotify. In 8.sql, list names of songs that feature other artists (songs with “feat.” in the name). In 7.sql, return the average energy of songs by Drake. In 6.sql, list names of songs by Post Malone. In 5.sql, return the average energy of all songs. In 4.sql, list names of songs with danceability, energy, and valence > 0.75. In 3.sql, list names of the top 5 longest songs, in descending order of length. In 2.sql, list names of all songs in increasing order of tempo. In 1.sql, list names of all songs in the database.

SELECT name
FROM songs
WHERE name LIKE '%feat%';

SELECT AVG(energy)
FROM songs
WHERE artist_id =
(SELECT id
FROM artists
WHERE name = "Drake"
);

SELECT name
FROM songs
WHERE artist_id =
(SELECT id
FROM artists
WHERE name = 'Post Malone'
);

SELECT AVG(energy)
FROM songs;

SELECT name
FROM songs
WHERE danceability > 0.75
AND energy > 0.75
AND valence > 0.75;

SELECT name, duration_ms
FROM songs
ORDER BY duration_ms DESC
LIMIT 5;

SELECT name
FROM songs
ORDER BY TEMPO;

SELECT name
FROM songs;

Updated:

You May Also Enjoy

2025-12-19-cs50_ch6python_pset

4 minute read

Exercise 7 DNA - Implement a program that takes in a database of str repeats for different people as a csv and an individual sequence to compare to and tries...

2025-12-11-cs50_ch5datastructures_pset

10 minute read

Exercise 2 Spell Checker - Implement the hash, load, unload, check, and size functions of a spellchecker initially using a 26 index hash function. V3 does ha...

2025-11-27-cs50_ch4memory_pset

5 minute read

Exercise 3 Recover - Make a program that takes a raw file of lost jpgs as input and sorts through the bytes to regenerate the individual images.

2025-11-14-cs50_ch3algorithms_pset

10 minute read

Exercise 3.2 Challenge Problem Tideman - Make all the necessary functions to run a tideman election where candidates are compared based on head on matchups w...