URL: https://linuxfr.org/users/gilcot/journaux/retour-sur-sql-noir
Title: retour sur SQL noir 🎭
Authors: Gil Cot ✔
Date: 2025年04月13日T12:05:00+02:00
License: CC By-SA
Tags: sql, retour_d_expérience et jeu_libre
Score: 22
Après avoir lu la [récente dépêche de Benoît](https://linuxfr.org/news/reviser-sql-en-jouant-au-detective-sqlnoir), 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
```sql
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 : ```sql 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 : ```text 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. ```sql 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 »> 2. 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.
```sql
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.
```sql
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 :
```sql
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> 3. Verify the suspect using their interview transcript.
On va donc juste lire les interrogatoires de nos trois gredins.
```sql
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
```sql
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 :
```sql
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...
```sql
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
```sql
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 :> 2. 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...
```sql
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)
```sql
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.
```sql
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.
```sql
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 :> 4. 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.
```sql
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...
```sql
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
```sql
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...
```sql
--- 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...
```sql
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](https://www.lhotellerie-restauration.fr/sos-experts/question-reponse/demander-les-papiers-d-identite-aux-clients-quelles-sont-les-obligations-reelles-de-l-hotelier-25338#) cette fois « pour la manifestation de la vérité judiciaire » et non [à fins marketing](https://www.lhotellerie-restauration.fr/sos-experts/question-reponse/demander-les-papiers-d-identite-aux-clients-quelles-sont-les-obligations-reelles-de-l-hotelier-25338#)... Par chance pour nous, tout est numérisé et tout le monde dans la chaîne a travaillé [proprement](https://www.lhotellerie-restauration.fr/sos-experts/question-reponse/demander-les-papiers-d-identite-aux-clients-quelles-sont-les-obligations-reelles-de-l-hotelier-25338#)... 🙃
```sql
-- 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
```text
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)
```sql
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.
```sql
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
```sql
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
```sql
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.
```sql
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.
```sql
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.
```sql
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...
```sql
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](#toc-find-suspects), 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 ?
</div><div class="naked_ctrl">
<form action="/index.cgi/contrast" method="get" name="gate">
<p><a href="http://altstyle.alfasado.net">AltStyle</a> によって変換されたページ <a href="httpS://linuxfr.org/users/gilcot/journaux/retour-sur-sql-noir.md">(-&gt;オリジナル)</a>
/ <label>アドレス: <input type="text" name="naked_post_url" value="httpS://linuxfr.org/users/gilcot/journaux/retour-sur-sql-noir.md" size="22" /></label> <label>モード: <select name="naked_post_mode">
<option value="default">デフォルト</option>
<option value="speech">音声ブラウザ</option>
<option value="ruby">ルビ付き</option>
<option value="contrast" selected="selected">配色反転</option>
<option value="larger-text">文字拡大</option>
<option value="mobile">モバイル</option>
</select>
<input type="submit" value="表示" />
</p>
</form>
</div>