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.