BRIAN YU: In Fiftyville, your task is going
to be to use SQL to solve a mystery.
In the town of Fiftyville, you've been told
that the CS50 duck has been stolen, and it's
going to be your job to figure out what happened.
Here's the information that we'll give to you.
What you know is that, first, the theft took place on July 28,
and second, that the theft took place on Chamberlain Street.
But beyond those two pieces of information, you don't know much else.
And in particular, your task is going to be to identify three things.
Your goal is to determine, first, who the thief is,
second, where the thief escaped to.
You believe the thief probably escaped out
of town to a different city other than Fiftyville,
and your goal is to figure out where they went.
And third, you're supposed to identify who the thief's accomplice was
who helped them escape out of town.
How are you going to figure out all of this information?
Well, you've been provided with a database, fiftyville.db,
which is a SQLite database that contains a number of tables with data collected
from around the town.
And what you're going to do is query that database
in order to access information in order to try and figure out
who the thief actually is.
In order to do that, on the command line,
you can run sqlite3 followed by fiftyville.db, the name of the database
file that you'd like to open and query, to start
running queries on that database.
After you run this command in the terminal,
you'll be presented with the SQLite prompt, where
you'll see the word "sqlite" followed by a greater than sign.
And it's here that you can start running your SQL queries,
like your SELECT queries, for example.
But before you do that, you'll probably want
to know what the structure of the data in this database
is, for example, what the tables are and what columns those tables have.
To find out that information, one command you might want to run first is
.tables, which is a command in SQLite that will list for you all
of the tables that are inside of this database.
If you type .tables at the sqlite prompt and press Return,
you'll be presented with something that looks like this,
a listing of all of the tables that are inside of this database, and therefore,
all of the types of data you might have access to.
Taking a glance at the tables you see here,
you'll see that we have information about airports and bank accounts
and crime scene reports and flights and interviews, for example.
And in particular, the crime scene reports
one looks potentially interesting.
Especially since all we know at the outset
is that there's been some crime that took place on some day in some place,
the crime scene reports table might offer us
some additional information that can help us
along the way to solving this mystery.
But .tables will just tell you what the names of all of the tables are,
but won't tell you, for example, what columns those tables have or what
the types of those columns actually are.
So if we want to see the database schema for a table,
in other words, what columns make it up, we can use a command like this.
I can run a .schema, followed by the name of a table, for example,
.schema crime scene reports sine the crime scene reports table was a table
that looked interesting when I was looking at that full listing of all
of the tables.
If I press Enter now, what I'll be presented with
is the CREATE TABLE command that was used in order to create the crime scene
reports table.
And what we'll notice here is that there are some interesting fields
that I might find useful.
For example, there are columns in this table that correspond
to the date on which a crime happened.
There's a street column that corresponds to what street
the crime scene report was taken on.
And there's a description field, just some text describing what happened.
And in particular, for this crime, one place to start
might be trying to figure out what the description is
for the crime in question.
And for the crime in question, we know what day it took place,
for example, a month and a day.
And we also know on what street it took place.
So in order to query the crime scene reports table for this information,
I can look at what columns the table has and run a SQL query that
looks something like this, for example.
SELECT description, that's the column that I'm interested in, FROM crime
scene reports, that's the table that I'm querying from,
WHERE a particular condition is true.
And in this case, the condition is that the date
is the date on which the crime took place and the street
is the street where I know that the crime took place as well.
By executing this query within SQLite, I'll
be presented with the description from the crime scene reports table
for this particular crime.
As you work through solving this mystery,
you'll probably find that you'll need to look
at some other tables other than just the crime scene reports table.
And recall that if you ever need to remember what all of the tables
in the database are, you can run .tables at the sqlite prompt to see a listing
of all of the tables that you might want to access.
A few suggestions as you go about your process
of trying to solve this mystery.
First, explore the table schemas by running .schema,
followed by the name of a table, to better understand what data is
available to you and how those tables connect to one another.
Oftentimes, one column of one table might
correspond to another column of another table,
and getting a sense for how those tables interact with each other
is going to be a helpful way of making sure
that you know how to get access to the data that you're looking for.
Additionally, you might find it helpful to run queries
across multiple different tables if you're
trying to access information that might be stored
across two or more different tables.
In order to do that, there are multiple strategies
you could try to approach the problem with.
But one approach would be to nest queries within each other.
And another approach is to use JOIN to join multiple tables together
in order to access data across multiple columns that might
be found in multiple different tables.
And as you go about the process of trying
to figure out who the thief actually is, where they escaped to,
and who their accomplice is, it might be helpful to maintain a list of suspects,
people that you believe might be the thief.
And you might narrow down that suspect list over time
as you discover more evidence and look through these tables
to find even more information to help you
along the way to trying to solve this mystery.
Ultimately, you should be keeping track of all of the queries
that you run inside of your log file.
And you can use this file as well as a place to add comments, to take notes,
and to describe your thought process as you go through the process of trying
to solve this mystery.
Ultimately, after looking through these tables and running these SQL queries,
you should be able to identify who the thief is, where they left to,
and who their accomplice was, and ultimately solve this mystery.
My name is Brian, and this was Fiftyville.