our investigation continues

 

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.

 

Now let's get the answer for this inquiry using the tricks we just learned in the last section.  We are after the Names of People who who traveled (Flew) to NY, Before the date of the crime.  Looking at the highlighted parts of the previous statement we can begin to construct the SQL query that will give us the answer we are looking for.

 

SELECT
 person_id,
 flightDate AS [Travel Date]
FROM
 flight
WHERE
 dest_city = 'New York' AND flightDate < "2016-2-7";

 

 

Let's get the names of all the people who flew from NY AFTER the date of the crime.

 

Let's get the answer for this inquiry using the tricks we just learned in the last section.  We are after the Names of People who who traveled (Flew) from NY, After the date of the crime.  Looking at the highlighted parts of the previous statement we can begin to construct the SQL query that will give us the answer we are looking for.

 

SELECT
 person_id,
 flightDate AS [Travel Date]
FROM
 flight
WHERE
 start_city = 'New York' AND flightDate > "2016-2-7";

 

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

 

SELECT
 person_id,
 flightDate AS [Travel Date]
FROM
 flight
WHERE
 dest_city = 'New York' AND flightDate < "2016-2-7";

 

SELECT
 person_id,
 flightDate AS [Travel Date]
FROM
 flight
WHERE
 start_city = 'New York' AND flightDate > "2016-2-7";

 

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.

SELECT
    FirstName AS [First Name],
    LastName AS [Last Name],
    residence AS [Home City],
    age AS Age

FROM
   person
WHERE
  residence = 'New York';

 

congratulations!!

YOU JUST REDUCED THE NUMBER OF SUSPECTS TREMENDOUSLY!

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