TP - Meurtre à SQL City

source

Ce TP est une adaptation en français de l'excellent travail réalisé par le site https://mystery.knightlab.com/, mis au point par la northwestern university (Chicago - San Francisco).

Grâce à vos nouvelles connaissances en SQL, vous avez décidé d'ouvrir votre propre agence de détective privé. Votre client vous a mandaté pour retrouver un meurtrier car il n'a pas confiance dans le travail de la police.

Après un petit peu de « fouinage », vous avez pu récupérer la base de données de la police  ainsi que quelques informations incontestables :

• le meurtre a eu lieu le 15 janvier 2018 ;
• le meurtre s'est déroulé à SQL City ;
• le club de sport "Get Fit Now !" a un lien avec cette affaire.

En ouvrant la base de données avec DB Browser et en lançant la requête :

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

vous avez pu modéliser le schéma suivant pour la base de données :

Remarque

La requête :

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

permet de visualiser le schéma de n'importe quelle base de données si vous ne le connaissez pas.

Attention

Les témoins que vous allez interroger ne sont pas toujours très fiables.
Pour les interrogations sur les chaînes de caractères, il est conseillé d'utiliser le mot-clef LIKE...

A présent, deux « enquêtes » s'offrent à vous :

• La partie A n'est pas du tout guidée ;
• La partie B est guidée.

Faites votre choix !

Conseil

Faîtes un clic droit sur le schéma de la base de données pour enregistrer l'image dans votre répertoire personnel. En l'ouvrant, vous pourrez alors mieux visualiser ce schéma en zoomant si nécessaire.

Partie A - Enquête non guidée

Recherchez l'assassin en formulant des requêtes SQL successives dans l'éditeur de DB Browser.

Une fois que vous pensez avoir identifié cet assassin, terminez par les deux requêtes suivantes :

1. insertion d'un nouvel enregistrement dans la table solution, dans lequel vous remplacer la chaîne 'le nom du suspect' par le nom de votre suspect écrit entre apostrophes :

   INSERT INTO solution
   VALUES (1, 'le nom du suspect');
   

2. vérification de cette proposition à l'aide d'un déclencheur (hors programme) :

   SELECT value
   FROM solution;        
   

Partie B - Enquête guidée

Encore peu sûr de vous, vous faîtes appel à votre prof de lycée pour vous guider.

1. Déterminez toutes les scènes de crime de SQL City.

2. Comme la base n'est pas forcément normalisée, une requête sur les chaînes de caractères peut ne pas renvoyer de réponse si cette requête porte sur 'SQL city' au lieu de 'SQL City'.
   Afin d'éviter ce désagrément, on peut utiliser les fonctions LOWER() ou UPPER() pour passer tous les caractères en minuscule (ou en majuscule).

   Un exemple

   SELECT DISTINCT city 
   FROM crime_scene_report 
   WHERE LOWER(city) = 'sql city';
   

   Centrez votre recherche sur le crime relatif à votre enquête.

3. Identifiez les deux témoins du meurtre sur lequel vous enquêtez.

4. Retrouvez les transcriptions des interrogatoires de ces témoins.
   Essayez de les retrouver en utilisant des jointures.

5. Complétez à l'aide des requêtes nécessaires et retrouvez le suspect.

6. Une fois que vous pensez avoir identifié cet assassin, terminez par les deux requêtes suivantes :

   1. insertion d'un nouvel enregistrement dans la table solution, dans lequel vous remplacer la chaîne 'le nom du suspect' par le nom de votre suspect écrit entre apostrophes : :

      INSERT INTO solution
      VALUES (1, 'le nom du suspect');
      

   2. vérification de cette proposition à l'aide d'un déclencheur (hors programme) :

      SELECT value
      FROM solution;