Journal retour sur SQL noir 🎭

Sommaire

• prise en mains
  • inventaire des objets
  • inventaire des champs
  • le reste
• 1^er cas
  • 3/13/400
  • 1/77/100
  • 1/2/200
  • cri tic
• 2^nd cas
  • affaire
  • indices
  • suspects
  • coupable
  • re marque
• 3^ème cas
  • crime scene
  • persons of interest
  • the final
• 6^ème cas
  • find case
  • find clues
  • find suspects
  • find culprit
• à suivre

Après avoir lu la récente dépêche de Benoît, j’ai voulu réviser mon SQL… (non, en fait seulement faire « mumuse » car je bouffe déjà assez de SQL ces temps ci.) Ceci est mon retour d’expérience.

prise en mains

Commençons simplement par le premier cas.

On arrive sur l’onglet « Case Brief » (soit « Résumé du Cas » en gros), que j’ai vite passé pour cliquer sur l’onglet « SQL Workspace » (soit « Espace de travail » mais) on comprend vite que c’est la fenêtre pour faire les requêtes.

inventaire des objets

Chiche, beaucoup vont pouvoir plonger tête baissée dans du « requêtage » sauvage. 🏴‍☠️ Mais quels objets se trouvent dans ce schéma ? Mes souvenirs (et quelques notes) me disent :

• Oracle : SELECT owner, table_name FROM dba_tables; …si on a de supers droits (les vues commençant par dba_ sont réservées à l’usage des DBA), ou alors SELECT owner, table_name FROM all_tables; qui devrait lister toutes les tables auxquelles l’on peut accéder… Minute papillon, il y a aussi SELECT table_name FROM user_tables; qui ne liste que les tables qui nous appartiennent (un peu comme si on avait fait plutôt un SELECT table_name FROM all_tables WHERE owner = (SELECT user FROM DUAL); directement.) Je me souviens aussi de SELECT * FROM tab; mais je ne sais pas si c’est officiel ou si le « hack » est toujours supporté.
  Pour les vues, il faut remplacer _tables par _views et donc faire l’union pour les deux. J’aime bien trier par table_name.
• MySQL et MariaDB : SHOW TABLES simplement pour les deux, et il faut utiliser SHOW FULL TABLES pour avoir l’information de qui est qui. On peut filtrer sur le nom des tables et vue avec LIKE mais on ne peut pas trier la résultat renvoyé.
• SQL Server (en tout cas pour le souvenir que j’ai des versions 2000 et précédentes) : SELECT * FROM sysobjects WHERE xtype = 'U'
• SQLite : depuis le client en ligne de commande, l’incantation .tables (qui peut être abrégé .table ou même .ta) permet de lister tables et vues en filtrant éventuellement sur le nom mais sans pouvoir trier le résultat.
  Depuis fin novembre 2021 (version 3.37 si je me souviens bien), on peut aussi lancer le sort PRAGMA table_list; depuis n’importe quelle session (autrement que via la CLI officielle donc) et obtenir une table sur laquelle on n’a pas plus de contrôle mais qui offre plus d’informations (la colonne « type » par exemple indique si c’est une table ou une vue). Ce « pragma » ajoute des informations en plus (ncol pour le nombre de colonnes, wr pour indiquer si la table est créé avec WITHOUT ROWID —1— ou pas —0—, strict pour indiquer si table est créé avec l’option STRICT ou pas, schema) comparé à la requête SELECT name, type FROM sqlite_schema; qui permet filtrage et tri… Historiquement (j’espère quand même que vous avez fait les mises à jour de sécurité) la table sqlite_schema s’appelait sqlite_master et ce nom est toujours reconnu (alias) pour des raisons de compatibilité (je crois pour une partie de l’API)
• PostgreSQL : depuis le client en ligne de commandes (psql normalement), il faut lancer l’incantation \dt (pour les tables) ou \dv (pour les vues), éventuellement suffixée de + (pour afficher deux fois plus de colonnes dont celle de la description). On peut filter sur le nom mais on ne peut pas trier le résultat renvoyé.
  Depuis n’importe quelle session (pas juste le client CLI), on peut aussi simplement lancer la requête SELECT tablename, schemaname, tableowner FROM pg_catalog.pg_tables; avec toute la capacité de filtrage et de tris.
  Oracle, IBM DB2, Firebird, Derby, ont aussi un catalogue similaire.
• etc. Oui, c’est un peu la plaie à ce niveau mais il y a eu une standardisation (si je ne dis pas de bêtise c’est arrivé avec SQL-92) et on devrait pouvoir faire de nos jours cette requête commune : SELECT table_name, table_schema, table_type FROM information_schema.tables; Cette vue peut comporter d’autres informations selon les SGBD, liste aussi bien les tables que les vues, est triable et filtrable.

…ce que je teste en vain (à ce stade je commence à connaître par cœur le « Error in query no such table: ») 🥲

C’est mieux de ne pas se précipiter et prendre le temps de faire le tour du propriétaire. Après avoir perdu un bon quart d’heure, je clique sur l’onglet « Schema » (soit schéma) qui me donne la liste que je cherchais.

inventaire des champs

Bien, on a les tables présentes et donc une base de départ. Sans liste des colonnes et graphe des relations ça va cependant être coton. Je me lance aussi :

Comme on peut s’y attendre (puisque la base virtuelle des méta-données, on
l’a vu, n’est pas reconnue —mais je garde espoir que c’est peut-être la
table précédemment demandée qui n’est pas présente et que d’autres seront
là), le standard

SELECT column_name, data_type,
  character_maximum_length AS max_length,
  numeric_scale            AS num_length,
  numeric_precision        AS num_precis
FROM information_schema.columns
WHERE table_name = 'suspects';

…ne passe pas. 🥲 Et comme ce n’est pas du My/Maria non plus (puisque, j’ai eu l’erreur « Error in query near "show": syntax error » plus tôt…) ceci ne fonctionne pas non plus :

• SHOW COLUMNS IN suspects ou
• SHOW FIELDS FROM suspects ou
• EXPLAIN suspects moins connu.

Enfin j’ai testé le standard de fait introduit par Oracle : DESCRIBE suspects; ou son abréviation DESC suspects; Cela se solde par « Error in query near "describe": syntax error »

Avant de me résigner à tout « dumper » (ça va piquer s’il y a des milliers ou des millions de résultats), je tente un dernier coup de poker

• Oracle : SELECT * FROM suspects WHERE ROWNUM <= 1; ⇒ « Error in query no such column: ROWNUM » (off course…)
• avec PostgreSQL≥9.0 et probablement d’autres SGBD, on peut explorer la piste du fenêtrage pour mimer l’oracle : SELECT ROW_NUMBER() OVER () AS i, s.* FROM suspects s WHERE i <= 1; ⇒ « Error in query misuse of aliased window function i » (toute façon le OVER () —sans ORDER BY ici— me plait pas trop)
  SELECT * FROM suspects WHERE ROW_NUMBER() <= 1; ⇒ « Error in query misuse of window function row_number() » (idem avec RANK())
• Microsoft (Access, SQL Server), SAP (ASE, IQ), Teradata, etc.: SELECT * FROM suspects TOP 1; ⇒ « Error in query near "1": syntax error » (cela laisse entendre que le top est accepté…?)
  …ou plutôt (je confonds souvent) SELECT TOP 1 * FROM suspects; ⇒ « Error in query near "1": syntax error » (en tout cas pas mieux ici…)
• Ingres/Informix/Firebird : SELECT FIRST 1 * FROM suspects; ⇒ « Error in query near "1": syntax error »
• Pg/My/Maria/Db2/SQLite/HSQLDB/H2/etc. : SELECT * FROM suspects LIMIT 1; ⇒ ça marche et j’ai un joli tableau qui va bien 😊 Par contre, contrairement à une description en bonne et due forme, on peut juste déduire si le champ non null est numérique ou textuel…
• ANSI SQL:2008 préconise plutôt : SELECT * FROM suspects FETCH FIRST 1 ROWS ONLY; (supporté par : Oracle≥12c, DB2≥11, PostgreSQL≥8.4, partiellement par T-SQL≥2008, HSQLDB≥2.0, etc.) ⇒ « Error in query near "FIRST": syntax error » (dommage, mais on en déduit que le SGBD sous-jacent n’a pas ce niveau de norme/standard.)

C’est seulement après avoir résolu le cas que j’ai remarqué dans l’onglet « Schema » que la structure (nom et type des colonnes, ainsi que si c’est une clé primaire/étrangère) s’affiche en cliquant sur le nom de la table… puis se masque si on refait un clic sur le nom.
Mon éternel souci avec les cliquodromes : il faut cliquer partout et découvrir au petit bonheur la chance (surtout que l’on part trop souvent du principe que c’est facile et qu’il n’y a donc pas besoin de manuel…)

le reste

Fort de mes déboires, je clique sur les autres onglets pour voir un peu. J’ai été dans un vague indescriptible en arrivant sur « Notes » puis j’ai eu l’eureka en lisant la note en bas : Il s’agit d’un bloc-notes perso et ça utilise le stockage persistant.
Je regarde le suivant, « Submit » qui dit d’entrer la réponse trouvée. Bien, il faudra donc recopier la réponse finale.
Du coup, je remarque qu’on ouvre avec le « Case Brief » qui présente le dossier et qu’on termine avec le « Submit » qui permet de le clôturer. Dans ma logique, j’aurais mis le « Schema » avant le « Workspace » ; mais ce n’est que mon avis.

Et toi, journal, as-tu vu des choses intéressantes que j’ai loupées ?

1^er cas

Pour commencer, je poursuis mon exploration avec quelques requêtes simples :

SELECT COUNT(*) FROM suspects; -- 400
SELECT COUNT(*) FROM interviews; -- 200
SELECT COUNT(*) FROM crime_scene; -- 100

SELECT COUNT(DISTINCT name) FROM suspects; -- 218
SELECT COUNT(DISTINCT attire) FROM suspects; -- 13
SELECT COUNT(DISTINCT scare) FROM suspects; -- 19

SELECT COUNT(DISTINCT suspect_id) FROM interviews; -- 200

SELECT COUNT(DISTINCT type) FROM crime_scene; -- 3
SELECT COUNT(DISTINCT location) FROM crime_scene; -- 77
SELECT COUNT(DISTINCT date) FROM crime_scene; -- 99

Je m’empresse de noter tout cela dans mon calepin ainsi :

400 suspects (id): 218 names, 19 scars, 13 attires
200 interviews (suspect_id):
100 crime_scene (id): 3 types, 77 locations, 99 dates

C’est que j’aime me faire une petite idée que de juste balancer toutes les lignes sur ma sortie.
À partir d’ici, ça va un peu « divugalcher ».

3/13/400

L’exploration en nombre me permet d’examiner les (treize) tenues et les (dix-neuf) marques/cicatrices des suspects.

SELECT DISTINCT attire FROM suspects LIMIT 6;
SELECT DISTINCT scar FROM suspects LIMIT 6;

Et là, je relis les objectifs indiqués dans le « Case Brief »

1. Identify the suspect whose profile matches the witness description.

Puis la description de l’affaire juste au dessus

A witness reported that a man in a trench coat was seen fleeing the scene.

L’examen de la table m’indique que ce sera très simple en fait.

SELECT id, name FROM suspects WHERE attire LIKE 'trench coat';

Lequel de ces mousquetaires a fait le coup ? Balafre à gauche ou à droite ?

1/77/100

C’est encourageant et je retourne voir la liste des objectifs

1. Retrieve the correct crime scene details to gather the key clue.

…et le résumé de l’affaire au dessus

Set in the gritty 1980s, a valuable briefcase has disappeared from the Blue Note Lounge.

L’exploration en nombre me permet d’examiner les (trois) types de délits, ainsi que quelques lieux (septante sept) lieux et (nonante neuf) dates.

SELECT DISTINCT type FROM crime_scene;
SELECT DISTINCT date FROM crime_scene LIMIT 6;
SELECT DISTINCT location FROM crime_scene LIMIT 6;

Petite digression avant de poursuivre : les noms date et type sont des mots à éviter (ou à utiliser comme préfixe ou suffixe), mais bon pas des mots réservés sinon la table n’aurait pas pu se créer simplement. Ici, les requêtes sont passées sans souci, sinon il aurait fallu protéger ces noms en écrivant (toutes ces formes sont reconnues, suis perplexe…)

• "date" et "type" (c’est la forme du standard ANSI SQL et dans MySQL/MariaDB quand SQL_MODE=ANSI_QUOTES est actif)
• `date` et `type` (c’est la variante spécifique de MySQL/MariaDB probablement reconnue ailleurs…)
• [date] et [type] (c’est la forme T-SQL reconnue aussi par MariaDB avec SQL_MODE=MSSQL…)

Revenons à notre vol. L’aperçu des données indique que l’on peut écrire simplement :

SELECT *
FROM crime_scene
WHERE type LIKE 'theft' AND location LIKE '%Lounge%';
-- WHERE type location LIKE '%Blue%';

La question demeure : lequel de ces loustics a fait le coup ?

1/2/200

Retour sur le dossier pour consulter l’objectif suivant

1. Verify the suspect using their interview transcript.

On va donc juste lire les interrogatoires de nos trois gredins.

SELECT * FROM interviews WHERE suspect_id IN (3,183,237);

Il y a un qui n’a pas été interrogé (en fait pas qu’un seul puisqu’il y moins de retranscriptions d’interrogatoires que de suspects) et un autre pour lequel la transcription est indéterminée/inconnue/inexistante (reste à savoir le sens exact de ce NULL.) Quand à notre coupable, il est passé aux aveux.
Il n’y en avait que trois à examiner donc il m’était facile de recopier les clés précédemment récupérées, sinon il aurait suffit de lui demander de récupérer la liste en lui passant la sous-requête

SELECT * FROM interviews
WHERE suspect_id IN (
  SELECT id FROM suspects
  WHERE attire = 'trench coat'
);

…ou mieux encore, récupérer directement le(s) nom(s)… soit en imbriquant la précédente requête dans une autre, soit en joignant les deux tables :

SELECT s.name, i.transcript
FROM interviews i
  INNER JOIN suspects s ON i.suspect_id = s.id
WHERE s.attire = 'trench coat' ;

Les autres variations sont laissées à titre d’exercice. 😌

cri tic

L’art est difficile et la critique facile, mais je vais faire la remarque que cette base de données doit être peu utile dans la vraie vie où on n’a pas juste des transcriptions associées à un nom. Il faut que les interrogatoires soient :

• Datées bien que pas utile ici. Ceci est utile classer les entretiens lorsqu’une personne est interrogée plusieurs fois. C’est également utile pour retrouver la chronologie des entrevues dans certaines affaires.
• Une transcription doit certes être associée à une personne (je pars du principe qu’il n’y a pas de truc en groupe) mais aussi à une affaire (certaines personnes se retrouvent dans plusieurs affaires, sinon j’aurais mis cette référence au niveau de la table des suspects et témoins.)
• Et probablement d’autres (on n’a par exemple pas évoqué les enquêteurs et enquêtrices sinon il faudrait indiquer la paire qui a interrogé ici.)

J’ai passé un bon moment sur ce premier cas qui prend bien cinq minutes (si l’on exclu ma découverte de l’interface.) Cela m’a donné envie de faire le second…

2^nd cas

Comme j’ai pris le pli, je vais regarder le schéma (la liste des tables donc) : on retrouve les mêmes, sauf que les suspects ont maintenant un bandana et un accessoire, et il y a en plus une table de témoignages ou pièces à convictions…
Je vais aussi suivre les étapes indiquées en objectifs. C’est parti.

affaire

On nous indique :

1. Retrieve the crime scene report for the record theft using the known date and location.

Sachant que :

In the neon glow of 1980s Los Angeles, the West Hollywood Records store was rocked by a daring theft. A prized vinyl record, worth over $10,000, vanished during a busy evening, leaving the store owner desperate for answers.

On va explorer la première table comme de coutume…

SELECT COUNT(*) FROM crime_scene; -- 100
SELECT COUNT(DISTINCT type) FROM crime_scene; -- 3
SELECT COUNT(DISTINCT date) FROM crime_scene; -- 99
SELECT COUNT(DISTINCT location) FROM crime_scene; -- 77
SELECT DISTINCT type FROM crime_scene; -- theft, murder, bribery
SELECT DISTINCT date FROM crime_scene LIMIT 2; -- 20120909, 19920415
SELECT DISTINCT location FROM crime_scene LIMIT 4;
-- 'City Street', 'Downtown Bank', 'Corner Café', 'Metro Station'

…et en déduire une tactique de recherche

SELECT COUNT(*) FROM crime_scene
WHERE date LIKE '198%' AND type = 'theft'; -- 2

SELECT id, date, location, description FROM crime_scene
WHERE date LIKE '198%' AND type = 'theft';

SELECT COUNT(*) FROM crime_scene
WHERE location LIKE 'West%'; -- 1

On note l’identifiant (champ id) et/ou la requête affinée à réutiliser par la suite.

indices

On nous indique :

1. Retrieve witness records linked to that crime scene to obtain their clues.

Avec le résultat précédent, on va simplement récupérer les informations…

SELECT clue FROM witnesses
WHERE crime_scene_id = (SELECT id
  FROM crime_scene WHERE location LIKE 'West%');

La chose peut se faire via une sous-requête (ci-avant) ou une jointe (ci-après)

SELECT w.clue
FROM witnesses w
  INNER JOIN crime_scene c ON w.crime_scene_id = c.id
WHERE c.location LIKE 'West%' ;

La première indique la couleur du bandana.
La deuxième indique l’accessoire vestimentaire remarquable.
Je note les deux lignes dans le calepin : je pense pas pouvoir les récupérer simplement par une sous-requête.

suspects

On nous indique :

3.Use the clues from the witnesses to find the suspect in the suspects table.

J’explore aussi cette troisième table pour décider de comment organiser la recherche.

SELECT COUNT(*) FROM suspects; -- 100
SELECT COUNT(DISTINCT bandana_color) FROM suspects; -- 11
SELECT COUNT(DISTINCT accessory) FROM suspects; -- 41
SELECT DISTINCT bandana_color FROM suspects LIMIT 3; -- green, purple, yellow
SELECT DISTINCT accessory FROM suspects LIMIT 2; -- 'silver chain', 'gold necklace'

OK, je sais donc comment extraire les lignes recherchée en utilisant les informations précédemment recueillies.

SELECT id, name FROM suspects
WHERE bandana_color = 'red' AND accessory = 'gold watch';

Voilà donc le(s trois du) podium, dont on prend note si on ne pense pas pouvoir combiner la requête plus tard.

coupable

On nous indique :

1. Retrieve the suspect's interview transcript to confirm the confession.

Comme précédemment, il suffit de coller les identifiants récupérés plus tôt…
Ou d’utiliser une requête imbriquée dans le filtrage… et récupérer le nom correspond noté plus tôt.

SELECT suspect_id, transcript FROM interviews
WHERE suspect_id IN (SELECT id FROM suspects
  WHERE bandana_color = 'red' AND accessory = 'gold watch');

Ou de faire une jointure pour lire directement le nom…

SELECT s.name, i.transcript FROM interviews i
  INNER JOIN suspects s ON i.suspect_id = s.id
WHERE bandana_color = 'red' AND accessory = 'gold watch';

Dans tous les cas, la confession donne le clé. Encore une affaire rondement menée détective Escuelle. 🕵️‍♀️

re marque

Bon, cette fois ci la table des témoignages, contrairement à celle des interrogatoires, a bien été liée à celle des affaires. Par contre, inversement, on n’a pas les noms des témoins. Et pareillement, il manque la date du recueil et le policier ou la policière qui pris le témoignage.

Pendant qu’on y est, il aurait aussi, dans la vraie vie, une table des victimes d’une affaire (donc reliée à la table des dossiers…) Personne n’a dit cependant qu’il s’agissait de modéliser la vie réelle, et le jeu ici fourni juste le nécessaire pour résoudre les cas soumis.

3^ème cas

Je ne l’ai pas signalé, mais je me suis créé un compte pour recevoir les points. Je ne sais pas si c’est lié (après tout, s’il y a du stockage local ça pourrait le faire aussi) mais j’ai les deux cas intermédiaires suivants qui sont débloqués.

crime scene

Tiens, dans le « Case Brief » il n’y a plus qu’un seul objectif (bien que le titre soit au pluriel)

1. Find the murderer. ( Start by finding the crime scene and go from there )

En fait les deux premiers cas nous ont pris par la main et maintenant nous allons rouler sans les petites roues supplémentaires sur notre vélo. Bien, ouvrons le dossier : la table, dans l’onglet « Schema », a toujours les mêmes colonnes… Donc en partant de cet extrait de la présentation dans l’onglet de débriefing

[…] near the docks of Coral Bay Marina in the early hours of August 14, 1986.

Je tente directement

SELECT * FROM crime_scene WHERE date = '19860814'; -- 3 lignes
SELECT * FROM crime_scene WHERE location LIKE 'Coral%'; -- 5 lignes

SELECT * FROM crime_scene WHERE date = 19860814 AND location LIKE 'Coral%';
SELECT * FROM crime_scene WHERE location = 'Coral Bay Marina';

Je prends note de la seconde phrase puis m’oriente sur la table des personnes.

persons of interest

On va juste traduire les notes prises plus tôt en requêtes pour trouver les suspects…

--- one who lives on 300ish "Ocean Drive"
SELECT * FROM person WHERE address LIKE '%Ocean%Drive%'; -- 5 lignes
SELECT * FROM person WHERE address LIKE '3__ Ocean Drive'; -- 1 ligne

-- another whose first name ends with "ul" and his last name ends with "ez"
SELECT * FROM person WHERE name LIKE '%ul %ez' -- 1 ligne

On suppose que ces personnes ont été entendues et on va donc rechercher les comptes rendus de ces entretiens…

SELECT * FROM interviews WHERE person_id IN (
  SELECT id FROM person WHERE
    (address LIKE '3__ Ocean Drive')
    OR
    (name LIKE '%ul %ez')
);

Il aurait fallu passer par une jointure si l’on voulait afficher aussi les noms au/en lieu/plus des « id » mais je n’en ai pas le besoin pour le moment.

Les deux témoignages nous orientent vers des hôtels. On va donc interroger les fiches quotidiennes obligatoires de l’hôtellerie cette fois « pour la manifestation de la vérité judiciaire » et non à fins marketing… Par chance pour nous, tout est numérisé et tout le monde dans la chaîne a travaillé proprement… 🙃

-- I saw someone check into a hotel on August 13.
SELECT COUNT(*) FROM hotel_checkins WHERE check_in_date = 19860813; -- 87

-- I heard someone checked into a hotel with "Sunset" in the name.
SELECT COUNT(*) FROM hotel_checkins WHERE hotel_name LIKE '%Sunset%'; -- 50

-- both
SELECT COUNT(*) FROM hotel_checkins
WHERE (check_in_date = 19860813) AND (hotel_name LIKE '%Sunset%') ; -- 50

-- The guy looked nervous.
SELECT COUNT(*) FROM surveillance_records
WHERE person_id IN (
  SELECT person_id FROM hotel_checkins
  WHERE (check_in_date = 19860813) AND (hotel_name LIKE '%Sunset%')
) AND (suspicious_activity IS NOT NULL); -- 30

J’ai affiché les trente et y a pas grand chose de folichon…

the final

…mais on peut retenir par exemple

6   6   34  Spotted entering late at night
7   7   89  Seen arguing with an unknown person
8   8   2   Left suddenly at 3 AM
15  15  44  Asked for directions to beach

De là, on peut se focaliser sur ces alibis ou confessions (là, c’est un peu cousu du fil blanc mais bon)

SELECT COUNT(*) FROM confessions; -- 100
SELECT * FROM confessions WHERE person_id IN (6,7,8,15); -- 4

Tiens, l’introduction nous a averti que

This case might require the use of JOINs, wildcard searches, and logical deduction.

Alors, pour la route, voici quelques idées de jointures.

SELECT COUNT(*) FROM surveillance_records s
  INNER JOIN hotel_checkins h ON h.person_id = s.person_id
                              AND h.id = s.hotel_checkin_id
WHERE
  (h.check_in_date = 19860813)
  AND
  (h.hotel_name LIKE '%Sunset%')
  AND
  (s.suspicious_activity IS NOT NULL); -- 0

SELECT COUNT(*) FROM surveillance_records s
  INNER JOIN hotel_checkins h ON h.person_id = s.person_id
WHERE
  (h.check_in_date = 19860813)
  AND
  (h.hotel_name LIKE '%Sunset%')
  AND
  (s.suspicious_activity IS NOT NULL); -- 30

SELECT COUNT(*) FROM surveillance_records s
  INNER JOIN hotel_checkins h ON h.person_id = s.person_id
  INNER JOIN interviews i ON i.person_id = s.person_id
WHERE
  (h.check_in_date = 19860813)
  AND
  (h.hotel_name LIKE '%Sunset%')
  AND
  (s.suspicious_activity IS NOT NULL); -- 50

SELECT COUNT(*) FROM surveillance_records s
  INNER JOIN hotel_checkins h ON h.person_id = s.person_id
  INNER JOIN interviews i ON i.person_id = s.person_id
WHERE
  (h.check_in_date = 19860813)
  AND
  (h.hotel_name LIKE '%Sunset%')
  AND
  (s.suspicious_activity IS NOT NULL)
  AND
  (i.transcript IS NOT NULL); -- 27

SELECT COUNT(*) FROM surveillance_records s
  INNER JOIN hotel_checkins h ON h.person_id = s.person_id
  INNER JOIN confessions i ON c.person_id = s.person_id
WHERE
  (h.check_in_date = 19860813)
  AND
  (h.hotel_name LIKE '%Sunset%')
  AND
  (s.suspicious_activity IS NOT NULL)
  AND
  (c.confession IS NOT NULL); -- 30

Sur ce, nous remercions detective Esucelle. 🕵🏻‍♂️

6^ème cas

Ici aussi, on a assez rodage pour ne pas être guidés ; on a juste pour instruction :

1. Find who stole the diamond.

Voilà le décor planté, c’est parti.

find case

La phrase d’introduction est lacunaire, mais c’est la seule piste de départ

SELECT COUNT(DISTINCT location) FROM crime_scene; -- 99
SELECT DISTINCT location FROM crime_scene LIMIT 10;
SELECT COUNT(*) FROM crime_scene WHERE location LIKE '%Miami%'; -- 19
SELECT COUNT(*) FROM crime_scene WHERE location LIKE '%Fontaineb%'; -- 1
SELECT COUNT(*) FROM crime_scene WHERE location LIKE '%Hotel%'; -- 1
SELECT * FROM crime_scene WHERE location LIKE '%Fontaineb%'; -- Fontainebleau Hotel

On aurait pu passer aussi par la description : après coup, je peux constater qu’il y a le nom du fameux diamant.

find clues

La description indique qu’il n’y a que deux témoignages valables et donne des pistes pour les atteindre

SELECT COUNT(DISTINCT occupation) FROM guest; -- 93
SELECT DISTINCT occupation FROM guest LIMIT 10;

-- One of them is a really famous actor.
SELECT COUNT(*) FROM guest WHERE occupation LIKE 'actor'; -- 5
SELECT id, name, invitation_code FROM guest WHERE occupation LIKE 'actor';

-- The other one is a woman who works as a consultant for a big company
-- and her first name ends with "an".
SELECT COUNT(*) FROM guest WHERE occupation LIKE 'consultant'; -- 8
SELECT id, name, invitation_code FROM guest
WHERE occupation LIKE 'consultant' AND name LIKE '%an %';

En effet, l’acteur est bien connu des dinosaures dont je fais partie.

Bien, suivons le fil d’Ariane et voyons ce que nous ont confié ces deux témoins.

SELECT * FROM witness_statements WHERE guest_id IN (
  SELECT id FROM guest WHERE
    (occupation='Actor' AND name LIKE 'c% e%') OR
    (occupation='Consultant' AND name LIKE '%an %')
);

Y a plus qu’à passer les personnes présentes en revue…

find suspects

C’est marrant qu’il y ait un registre des attirails, mais cela va être bien utile ici.

SELECT note FROM attire_registry LIMIT 10;

SELECT g.*, a.note
FROM guest g
  INNER JOIN attire_registry a ON g.id = a.guest_id
WHERE
  -- I saw someone holding an invitation ending with "-R".
  g.invitation_code LIKE '%-R'
  AND
  -- He was wearing a navy suit and a white tie.
  a.note LIKE 'navy suit, white tie'
;

Les registres de plaisance maritime vont être mis à profit aussi.

SELECT dock_number FROM marina_rentals LIMIT 3;

SELECT g.*, m.boat_name, m.rental_date
FROM guest g
  INNER JOIN marina_rentals m ON g.id = m.renter_guest_id
WHERE
  -- I overheard someone say, "Meet me at the marina, dock 3."
  m.dock_number = 3
;

Ici, il y a du monde… (remplacer g.*, m.boat_name, m.rental_date par COUNT(*) pour afficher 20) …mais deux dates quand on y prête attention (du coup, on peut remplacer le COUNT(*) par COUNT(*), m.rental_date, et ajouter un GROUP BY m.rental_date à la fin…) donc seulement la moitié utile quand on se restreint au jour des faits…

find culprit

Pour finir, il faut lire les aveux de notre liste réduite…

SELECT COUNT(*) FROM guest; -- 200
SELECT COUNT(*) FROM final_interviews; -- 199

SELECT g.*, i.confession
FROM guest g
  INNER JOIN final_interviews i ON g.id = i.guest_id
WHERE
  (g.invitation_code LIKE '%-R'
    AND
    g.id = (SELECT guest_id FROM attire_registry
      WHERE note = 'navy suit, white tie')
  ) OR
  guest_id IN (
    SELECT renter_guest_id FROM marina_rentals
      WHERE dock_number = 3
      AND
      rental_date = (SELECT date FROM crime_scene
        WHERE location = 'Fontainebleau Hotel')
  );

La requête est un peu …(mettre le mot qui vient à l’esprit)… mais illustre l’idée d’assemblage de puzzle… 😉 Ce n’est pas optimum quand on a beaucoup de données (des milliers de lignes…) mais ça c’est laissé à titre d’exercice au lectorat.

En fait dans les jointures de la sous-section précédente, on pouvait interpoler (ou alors ne sélectionner que g.* pour les deux et appliquer INTERSECT…), la table des aveux servant alors juste à valider. 😉

En tout cas, bon boulot détective Escuelle ! 🕵🏾

à suivre

Je fatigue un peu et par conséquence vais arrêter ma rédaction ici. Je pourrai faire une seconde partie s’il y a de la demande. Mais le plus important pour moi est de partager vos trouvailles, astuces, conseils, remarques, etc.

Pour le site, je trouve que c’est bien fait (et je donne un B+ comme notation) et espère que de nouveaux cas vont s’ajouter rapidement. Une des améliorations que je demanderai serait d’améliorer la coloration syntaxique (attention qu’il ne s’agit pas de rajouter plus de couleurs mais de ne pas avoir les bizarreries comme avec le champ crime_scene.location.) Et toi, quelles améliorations verrais tu ?