Tracking Down a Thief

Congratulations on completing modules 1 through 3! 🎉

Here at Break Into Tech, we’ve worked hard to keep our data and challenges close to what you’ll do on the job, but it never hurts to have some fun!

Before heading to module 4, we hope you’ll enjoy using the SQL you've learned so far to solve this who-dunnit mystery. 🕵️‍♀️🔍📝

Tracking Down a Thief 🚔

There’s been a robbery, and we need your help!

Tracking Down a Thief is a self-directed activity to practice SQL concepts as a fun game. Certain pieces of information will be revealed about the crime, but players will have to use SQL to search the database for more clues.

For this SQL challenge, you'll be querying a database to see which individual is responsible for the robbery. Remember to reference the SQL Cheat Sheet if you get stuck!

The database is titled suspect_database, which is made up of 5 tables. To answer the following questions, use the database we built here:

To get started, run the following code to get a sense of the database and its columns.

The Crime 🐩

On January 13th, 2021, a thief stole the Cotton Candy Poodle, a new dog breed worth billions of dollars. The crime took place in Carnival City at 5:01pm. Thanks to a few key witnesses, the detectives were able to pin the exact time of the crime and where it took place. After a few hours, they compiled a database with all of the suspects’ information.

Can you solve the mystery for the detectives?

⚠️Each result set will return up to 6 rows⚠️

The Clues


Query 1 🏙️

We have witness testimony that the suspect was in Carnival City on January 13th, 2021, with a reported sighting at 5:01 pm.

Using cell phone location data, list all suspects who were in that location at that time.

  • Display their first and last names, their location data, as well as the timestamp.
  • Assume that the dates are stored in the MM/DD/YY HH:MM format.

Query 1

Hints

FROM suspect_info
JOIN location_info

ON suspect_info.suspect_id = location_info.suspect_id

WHERE cell_location = 'Carnival City'
AND date_time = '01/13/21 17:01';

Answer

SELECT suspect_first_name
, suspect_last_name
, cell_location
, date_time
FROM suspect_info
JOIN location_info
ON suspect_info.suspect_id = location_info.suspect_id
WHERE cell_location = 'Carnival City'
AND date_time = '01/13/21 17:01';

Query 2 🪪

The suspect was able to break into the facility that housed the poodle because of a stolen employee ID card. Based on witness testimony of an overheard phone call, detectives believe that the suspect stole the ID from a close friend and that the friend was some type of specialist.

Find suspects with associates who have occupational titles containing the word “Specialist,” and who have a relationship to the suspect labeled as “Friend”.

  • Columns to retrieve: suspect_first_name, suspect_last_name, associate_alias, occupation, relationship_to_suspect


Query 2

Hints

JOIN associates_info
ON suspect_info.suspect_id = associates_info.suspect_id

WHERE relationship_to_suspect = 'Friend'

AND occupation LIKE '%Specialist%';

Answer

SELECT suspect_first_name,
suspect_last_name,
associate_alias,
occupation,
relationship_to_suspect
FROM suspect_info
JOIN associates_info
ON suspect_info.suspect_id = associates_info.suspect_id
WHERE relationship_to_suspect = 'Friend'
AND occupation LIKE '%Specialist%';

Query 3 🚗

A key witness was able to see the suspect’s getaway vehicle. The vehicle was a minivan and the witness claimed that the license plate ended in “D0G” (D-zero-G).

Find suspects who own a minivan with a license plate that ends with “D0G”.

  • Columns to retrieve: suspect_first_name, suspect_last_name, cell_location, car_model, license_plate

Query 3

Hints

JOIN vehicle_info
ON suspect_info.suspect_id = vehicle_info.suspect_id

JOIN location_info
ON suspect_info.suspect_id = location_info.suspect_id

WHERE car_type = 'Minivan' AND license_plate LIKE '%D0G';

Answer

SELECT suspect_first_name,
suspect_last_name,
cell_location,
car_model,
license_plate
FROM suspect_info
JOIN vehicle_info
ON suspect_info.suspect_id = vehicle_info.suspect_id
JOIN location_info
ON suspect_info.suspect_id = location_info.suspect_id
WHERE car_type = 'Minivan' AND license_plate LIKE '%D0G';


🚨 The detectives have narrowed down the list of suspects thanks to your help and have interviewed them for their alibis. They are particularly interested in suspects whose alibi contains specific keywords and those whose alibi location is different than their cell phone location.


Query 4 🍭

The detectives have created a list of keywords and want to know if any alibis contain these words: “dog”, “cotton”, and “candy”.

Find all suspects that have an alibi that contains the word “dog”, “cotton”, or “candy”.

  • Columns to retrieve: suspect_first_name, suspect_last_name, alibi_statement

Query 4

Hints

WHERE alibi_statement LIKE '%dog%'

OR alibi_statement LIKE '%cotton%'

OR alibi_statement LIKE '%candy%'

Answer

SELECT suspect_first_name,
suspect_last_name,
alibi_statement
FROM suspect_info
JOIN alibi_info
ON suspect_info.suspect_id = alibi_info.suspect_id
WHERE alibi_statement LIKE '%dog%'
OR alibi_statement LIKE '%cotton%'
OR alibi_statement LIKE '%candy%';

Why can’t we use WHERE alibi_statement LIKE '%dog%' OR '%cotton%' OR '%candy%'?
When using the OR operator, each expression on either side of it must be a complete condition. In the query above, only WHERE alibi_statement LIKE '%dog%' is complete.

Query 5 🗺️

Find all suspects whose data location and alibi location do not match.

  • Columns to retrieve: suspect_id, suspect_first_name, suspect_last_name, cell location, alibi location

Query 5

Hints

FROM suspect_info
JOIN alibi_info
ON suspect_info.suspect_id = alibi_info.suspect_id

JOIN location_info
ON suspect_info.suspect_id = location_info.suspect_id

WHERE cell_location <> alibi_location;

Answer

SELECT suspect_info.suspect_id,
suspect_first_name,
suspect_last_name,
cell_location,
alibi_location
FROM suspect_info
JOIN alibi_info
ON suspect_info.suspect_id = alibi_info.suspect_id
JOIN location_info
ON suspect_info.suspect_id = location_info.suspect_id
WHERE cell_location <> alibi_location;

The Solution 📑

Take a look at each result set generated. Which name is found in all of them?

Answer

Answer

Great work!

🔍 With your SQL sleuthing skills, you are able to hand over this name and the detectives are able to recover the dog!



Next Steps

You’re ready for the SQL bootcamp! Head to module 4 to get started.



This activity was designed and developed by Ella Jew, Break Into Tech Summer Intern 2025 :)

Complete and Continue