61 lines
3.1 KiB
SQL
61 lines
3.1 KiB
SQL
-- Keep a log of any SQL queries you execute as you solve the mystery.
|
|
|
|
-- frist query to get more info from crime scene reports based on the input we got with this tasks
|
|
select * from crime_scene_reports
|
|
where year = 2020 and month = 7 and day = 28 and street = "Chamberlin Street";
|
|
|
|
-- query to get transcripts from witnesses, we can filter it down based on the report mentioning that all witnesses mentioned courthouse
|
|
select * from interviews
|
|
where year = 2020 and month = 7 and day = 28 and transcript like "%courthouse%";
|
|
|
|
-- second query based on time of the crime + witness saying that thief left within 10 mins after the crime
|
|
select * from courthouse_security_logs
|
|
where year = 2020 and month = 7 and day = 28 and hour = 10 and minute >15 and minute <= 25;
|
|
|
|
-- query based on witness stating that thief had call shorter than 1 min before the theft
|
|
select * from phone_calls
|
|
where year = 2020 and month = 7 and day = 28 and duration < 60;
|
|
|
|
-- query based on witness stating that thief wanted to leave by earliest flight tomorrow
|
|
select * from flights
|
|
where year = 2020 and month = 7 and day = 29
|
|
order by hour, minute;
|
|
|
|
--query to get destination of earlies flight, so we can answer where the thief escaped to
|
|
select * from airports
|
|
where id = 4;
|
|
|
|
--query to get list of passenger for the flight from the previous query
|
|
select * from passengers
|
|
where flight_id = 36;
|
|
|
|
--getting more info about ppl on flight based on passport number
|
|
select * from people
|
|
where passport_number in (select passport_number from passengers
|
|
where flight_id = 36);
|
|
|
|
-- narrowing the search down based on passport number, licence plate at crime scene and call number
|
|
select * from people
|
|
where passport_number in (select passport_number from passengers
|
|
where flight_id = 36) and license_plate in (select license_plate from courthouse_security_logs
|
|
where year = 2020 and month = 7 and day = 28 and hour = 10 and minute >15 and minute <= 25) and phone_number in (select caller from phone_calls
|
|
where year = 2020 and month = 7 and day = 28 and duration < 60);
|
|
|
|
--query to follow lead on ATM
|
|
select person_id from bank_accounts
|
|
where account_number in (select account_number from atm_transactions
|
|
where year = 2020 and month = 7 and day = 28 and atm_location = "Fifer Street" and transaction_type = "withdraw");
|
|
|
|
-- combining all leads togethe we got out thief
|
|
select * from people
|
|
where passport_number in (select passport_number from passengers
|
|
where flight_id = 36) and license_plate in (select license_plate from courthouse_security_logs
|
|
where year = 2020 and month = 7 and day = 28 and hour = 10 and minute >15 and minute <= 25) and phone_number in (select caller from phone_calls
|
|
where year = 2020 and month = 7 and day = 28 and duration < 60) and id in (select person_id from bank_accounts
|
|
where account_number in (select account_number from atm_transactions
|
|
where year = 2020 and month = 7 and day = 28 and atm_location = "Fifer Street" and transaction_type = "withdraw"));
|
|
|
|
-- final query to get accomplice based on the call he received from our thief
|
|
select name from people
|
|
where phone_number = (select receiver from phone_calls
|
|
where year = 2020 and month = 7 and day = 28 and duration < 60 and caller = "(367) 555-5533"); |