SQL City Correction

SQL City Correction
#

SQL CITY

Un meurtre a eu lieu à SQL City ! SQL Murder Mystery est conçu pour être à la fois une leçon autodirigée pour apprendre les concepts et les commandes SQL et un jeu amusant pour les utilisateurs SQL expérimentés pour résoudre un crime intriguant.

Un crime a eu lieu et le détective a besoin de votre aide. Le détective vous a remis le rapport de scène de crime, mais vous l’avez perdu. Vous vous souvenez vaguement que le crime était un ​meurtre​ qui a eu lieu le ​15 janvier 2018​ et qu’il a eu lieu à ​SQL City.​

Commencez par récupérer le rapport de scène de crime correspondant dans la base de données du service de police crime_scene_report

https://www.kaggle.com/datasets/johnp47/sql-murder-mystery-database/data

Partie 1 : Reconstitution du dossier#

Première étape : Lire la base de données et visualiser ce qu’elle contient#

%load_ext sql

%sql sqlite:///sql-murder-mystery.db

%%sql
SELECT name FROM sqlite_master WHERE type='table';

 * sqlite:///sql-murder-mystery.db
Done.
name
crime_scene_report
drivers_license
person
facebook_event_checkin
interview
get_fit_now_member
get_fit_now_check_in
income
solution

Deuxième étape : Récupérer le rapport de la scène de crime#

%%sql
PRAGMA table_info(crime_scene_report);

 * sqlite:///sql-murder-mystery.db
Done.
cid name type notnull dflt_value pk
0 date INTEGER 0 None 0
1 type TEXT 0 None 0
2 description TEXT 0 None 0
3 city TEXT 0 None 0

On cherche à récuperer les information sur un meurtre qui a eu lieu le 15 janvier 2018 :

%%sql 
SELECT * FROM crime_scene_report  WHERE date == 20180115 AND city == "SQL City" AND type =="murder" ;

 * sqlite:///sql-murder-mystery.db
Done.
date type description city
20180115 murder Security footage shows that there were 2 witnesses. The first witness lives at the last house on "Northwestern Dr". The second witness, named Annabel, lives somewhere on "Franklin Ave". SQL City

Dans notre base de données, seulement une ligne correspond à cela. On obtient des informations sur des témoins. On va donc ouvrir la base de données pour savoir de qui il s’agit.

Troisième étape : Se renseigner sur les témoignages#

%%sql
PRAGMA table_info(person);

 * sqlite:///sql-murder-mystery.db
Done.
cid name type notnull dflt_value pk
0 id INTEGER 0 None 1
1 name TEXT 0 None 0
2 license_id INTEGER 0 None 0
3 address_number INTEGER 0 None 0
4 address_street_name TEXT 0 None 0
5 ssn INTEGER 0 None 0 
%%sql 
SELECT * FROM person 
WHERE (address_street_name=="Northwestern Dr" AND address_number = (SELECT MAX(address_number) FROM person)) OR (address_street_name=="Franklin Ave" AND name LIKE "Annabel %") ;

 * sqlite:///sql-murder-mystery.db
Done.
id name license_id address_number address_street_name ssn
14887 Morty Schapiro 118009 4919 Northwestern Dr 111564949
16371 Annabel Miller 490173 103 Franklin Ave 318771143

Voici donc nos deux témoins ! Morty Schapiro et Annabel Miller. Allons voir leur témoignage

Quatrième étape : Se renseigner sur les témoignages#

%%sql
PRAGMA table_info(interview);

 * sqlite:///sql-murder-mystery.db
Done.
cid name type notnull dflt_value pk
0 person_id INTEGER 0 None 0
1 transcript TEXT 0 None 0 
%%sql 
SELECT * FROM interview
WHERE person_id == 14887 OR person_id==16371;

 * sqlite:///sql-murder-mystery.db
Done.
person_id transcript
14887 I heard a gunshot and then saw a man run out. He had a "Get Fit Now Gym" bag. The membership number on the bag started with "48Z". Only gold members have those bags. The man got into a car with a plate that included "H42W".
16371 I saw the murder happen, and I recognized the killer from my gym when I was working out last week on January the 9th.

Nos témoins nous enseignent qu’un homme portait un sac “get fit now gym bag” avec les initiales 48Z qui est le sac typique des abonnés gold. Il est parti dans une voiture dont l’imatriculation comportait la séquence H42W.

Le criminel a été vu et le 9 janvier il était bien à la salle.

Quatrième étape : Menons l’enquête !#

%%sql
PRAGMA table_info(get_fit_now_member);

 * sqlite:///sql-murder-mystery.db
Done.
cid name type notnull dflt_value pk
0 id TEXT 0 None 1
1 person_id INTEGER 0 None 0
2 name TEXT 0 None 0
3 membership_start_date INTEGER 0 None 0
4 membership_status TEXT 0 None 0 
%%sql 
SELECT * FROM get_fit_now_member 
WHERE id LIKE "48Z%" and membership_status =="gold";

 * sqlite:///sql-murder-mystery.db
Done.
id person_id name membership_start_date membership_status
48Z7A 28819 Joe Germuska 20160305 gold
48Z55 67318 Jeremy Bowers 20160101 gold

Voici donc les 2 principaux suspects !

%%sql
PRAGMA table_info(get_fit_now_check_in);

 * sqlite:///sql-murder-mystery.db
Done.
cid name type notnull dflt_value pk
0 membership_id TEXT 0 None 0
1 check_in_date INTEGER 0 None 0
2 check_in_time INTEGER 0 None 0
3 check_out_time INTEGER 0 None 0 
%%sql 
SELECT * FROM get_fit_now_check_in
WHERE check_in_date = 20180109 AND membership_id == "48Z7A" OR membership_id == "48Z55"	 ;

 * sqlite:///sql-murder-mystery.db
Done.
membership_id check_in_date check_in_time check_out_time
48Z7A 20180109 1600 1730
48Z55 20180109 1530 1700

Il s’agit à priori de Joe Germuska ou de Jeremy Bowers. A ce stade impossible de savoir qui est le pincipal suspect. Il faut donc utiliser la deuxième information sur la plaque d’immatriculation et le fait qu’il s’agit à priori d’un homme

%%sql
PRAGMA table_info(drivers_license);

 * sqlite:///sql-murder-mystery.db
Done.
cid name type notnull dflt_value pk
0 id INTEGER 0 None 1
1 age INTEGER 0 None 0
2 height INTEGER 0 None 0
3 eye_color TEXT 0 None 0
4 hair_color TEXT 0 None 0
5 gender TEXT 0 None 0
6 plate_number TEXT 0 None 0
7 car_make TEXT 0 None 0
8 car_model TEXT 0 None 0 
%%sql 
SELECT * FROM drivers_license
WHERE plate_number LIKE "%H42W%" and gender =="male";

 * sqlite:///sql-murder-mystery.db
Done.
id age height eye_color hair_color gender plate_number car_make car_model
423327 30 70 brown brown male 0H42W2 Chevrolet Spark LS
664760 21 71 black black male 4H42WR Nissan Altima

On s’approche du but, maintenant il faut relier ces informations à la base de données sur les individus de SQL City pour savoir à qui appartient ces voitures.

%%sql
PRAGMA table_info(person);

 * sqlite:///sql-murder-mystery.db
Done.
cid name type notnull dflt_value pk
0 id INTEGER 0 None 1
1 name TEXT 0 None 0
2 license_id INTEGER 0 None 0
3 address_number INTEGER 0 None 0
4 address_street_name TEXT 0 None 0
5 ssn INTEGER 0 None 0 
%%sql 
SELECT * FROM person
WHERE license_id ==423327 or license_id==664760;

 * sqlite:///sql-murder-mystery.db
Done.
id name license_id address_number address_street_name ssn
51739 Tushar Chandra 664760 312 Phi St 137882671
67318 Jeremy Bowers 423327 530 Washington Pl, Apt 3A 871539279

Il semblerait que le suspect numéro 1 soit donc Jérémy Bowers. Bravo !

%%sql
INSERT INTO solution VALUES (1, 'Jeremy Bowers'); 
SELECT value FROM solution

 * sqlite:///sql-murder-mystery.db
1 rows affected.
Done.
value
Congrats, you found the murderer! But wait, there's more... If you think you're up for a challenge, try querying the interview transcript of the murderer to find the real villain behind this crime. If you feel especially confident in your SQL skills, try to complete this final step with no more than 2 queries. Use this same INSERT statement with your new suspect to check your answer. 
%%sql 
SELECT * FROM solution;

 * sqlite:///sql-murder-mystery.db
Done.
user value
0 Congrats, you found the murderer! But wait, there's more... If you think you're up for a challenge, try querying the interview transcript of the murderer to find the real villain behind this crime. If you feel especially confident in your SQL skills, try to complete this final step with no more than 2 queries. Use this same INSERT statement with your new suspect to check your answer.

Vous avez reconstitué le dossier principal que vous aviez perdu. Mais quelque chose vous dérange, vous trouvez ça trop facile. Votre expérience d’apprenti vous a plusieurs fois démontré qu’on ne peut jamais se fier aux premières conclusions. Quelque chose ne va pas et il faut le découvrir !

Partie 2 : Votre enquête#

Le dossier a été reconstitué. Le suspect numéro 1 a été interpellé. Commencez par voir sa déposition et menez votre enquête !

%%sql 
SELECT * FROM interview
WHERE person_id == 67318;

 * sqlite:///sql-murder-mystery.db
Done.
person_id transcript
67318 I was hired by a woman with a lot of money. I don't know her name but I know she's around 5'5" (65") or 5'7" (67"). She has red hair and she drives a Tesla Model S. I know that she attended the SQL Symphony Concert 3 times in December 2017.

Et voilà ! C’était sûr ! L’enquête est loin d’être terminée ! Une femme est dans le coup !

%%sql 
SELECT * FROM drivers_license
WHERE hair_color = "red" and car_make == "Tesla" and car_model =="Model S" and (height BETWEEN 65 and 67) and gender == "female" ;

 * sqlite:///sql-murder-mystery.db
Done.
id age height eye_color hair_color gender plate_number car_make car_model
202298 68 66 green red female 500123 Tesla Model S
291182 65 66 blue red female 08CM64 Tesla Model S
918773 48 65 black red female 917UU3 Tesla Model S 
%%sql 
SELECT * FROM person 
WHERE license_id == 202298 or license_id == 291182 or license_id == 918773;

 * sqlite:///sql-murder-mystery.db
Done.
id name license_id address_number address_street_name ssn
78881 Red Korb 918773 107 Camerata Dr 961388910
90700 Regina George 291182 332 Maple Ave 337169072
99716 Miranda Priestly 202298 1883 Golden Ave 987756388

Voici nos 3 principales suspectes ! Il faut identifier celle qui est fincanièrement aisée et qui est allée plusieurs fois à l’évènement SQL Symphony.

%%sql 
SELECT * FROM income 
WHERE ssn == 961388910 or ssn == 337169072 or ssn == 987756388;

 * sqlite:///sql-murder-mystery.db
Done.
ssn annual_income
961388910 278000
987756388 310000

Nous avons des informations sur Miranda Priestly et Red Korb. Elles sont plutôt aisée mais à ce stade on ne peut pas dire qui est la coupable et nous n’avons pas d’infos sur Regina George.

%%sql
SELECT * FROM facebook_event_checkin
WHERE person_id == 78881 or person_id == 90700 or person_id == 99716;

 * sqlite:///sql-murder-mystery.db
Done.
person_id event_id event_name date
99716 1143 SQL Symphony Concert 20171206
99716 1143 SQL Symphony Concert 20171212
99716 1143 SQL Symphony Concert 20171229

La coupable est très clairement Mirinda Priestly ! Vous en informez immédiatement l’enquêteur !

%%sql 
INSERT INTO solution VALUES (1, 'Miranda Priestly'); SELECT value FROM solution

 * sqlite:///sql-murder-mystery.db
1 rows affected.
Done.
value
Congrats, you found the brains behind the murder! Everyone in SQL City hails you as the greatest SQL detective of all time. Time to break out the champagne!
Contents