starting your detective work

As a great detective you know that 90% of the job involves asking questions.  Getting the answers to your investigative questions is how you solve your toughest cases.  Luckily, SQLs main purpose is to provide you with answers to questions you ask about data.

 

Take a look at ALL the tables in this database. What table could contain information regarding traveling?

 

 

 

 

 

Get an idea what kind of information is stored in the table with traveling information?

 

 

 

 

 

 

Get the name of all persons who live in New York.

Now that we have figured out who are the residents of NY from our data evidence.  We are continuing to investigate by asking more relevant questions.

 

 

 

 

 

LET'S GET THE NAMES OF ALL THE PEOPLE WHO FLEW TO NY BEFORE THE DATE OF THE CRIME.

 

 

 

 

 

 

 

LET'S GET THE NAMES OF ALL THE PEOPLE WHO FLEW FROM NY AFTER THE DATE OF THE CRIME.

 

 

 

 

 

 

 

NOW LET'S COMBINE OUR LAST TWO RESULTS. ARE THERE ANY NAMES THAT EXIST IN BOTH?

 

 

 

 

 

 

WHO ARE ALL OF THE PEOPLE WHO LIVE IN NEW YORK OR SPENT TIME IN NY AT THE TIME OF THE CRIME?

We already have the answer to this question.  Let's take a look at the answer for the last question above.  Let's also look at the answer to our fist question (The names of the people who reside in NY).  If we combine all three results we find what we are after.

 

 

 

 

 

 

 

CONGRATULATIONS!!

YOU JUST REDUCED THE NUMBER OF SUSPECTS TREMENDOUSLY!

The FBI will pay these people a visit to ask a few questions.  Particularly, where they were at the day and time of the crime.

 

 

 

 

 

 

LOOKING AT DATA FROM MORE THAN ONE TABLE

 

 

 

 

 

 

What are the names of the table containing the text messages and the one containing phone contracts?

 

 

 

 

 

 

 

What foreign keys are used in the table containing TEXT messages?

which is the referenced table?

 

 

 

 

 

**REMOVE** Who is the thief? - Order by and group by

Get all text messages that were sent between 2016-2-2 and 2016-2-14

 

 

 

 

 

 

Get all PHONE contract id's THAT BELONG TO SOMEONE ON OUR LIST OF SUSPECTS

 

 

 

 

 

Get all text messages that were sent between 2016-2-2 and 2016-2-14 from someone on our list of suspects

 

 

 

 

 

 

Who are the thieves?

Hint: Read the conversations as they a give a clear trace. Check the ids of sender and receiver of the message and look it up in the person tables!

 

 

 

Because of your excellent analytical work on the data the local police found the thieves and arrested them.

The thieves had  already sold the painting on the black market two days after the heist.  They quickly disclose the buyers for a reduction in sentence.  The historic painting was brought back to the National Portrait Meuseum.

 

Your job is not quite done.  Further evidence needs to be analysed to determine:

 

WHO WAS THE MASTERMIND AND WHO WAS THE HENCHMAN?

Hint: The Mastermind would have kept more money than the Henchman

 

 

 

Which tables contain bank transaction data?

 

 

 

 

 

what are the primary and foreign keys of this table?

 

 

 

  

 

 

 

Get the account numbers of our thieves

 

 

 

 

 

 

Get a list of account details for Sarah or Philipp

 

 

 

 

 

 

Who got the money?

 

 

 

 

 

 

What is the sum of all transactions of Sarah?

 

 

 

 

 

What is the sum of all transactions of Sarah between February 2nd and February 12th?

 

 

 

 

 

 

Get a list of account numbers whose sums of transactions is greater than $10,000 at the time of the crime. 

 

 

 

 

 

Who is the the criminal mastermind?

 

 

 

 

CONGRATULATIONS!

YOUR REPUTATION AS THE GREATEST DETECTIVE IN HISTORY OF THE FBI REMAINS UNMATCHED!

The 44th President Barack Obama has awarded you the Presidential Medal of Freedom for your excellent work!

 

 

 

 

BONUS TOPICS

Let's revisit solving parts of our crime using some new techniques.