URL: https://linuxfr.org/users/gilcot/journaux/encore-du-sql-noir
Title: encore du SQL noir 🎭
Authors: Gil Cot ✔
Date: 2026年04月06日T21:29:00+02:00
License: CC By-SA
Tags: sql, retour_d_expérience et jeu_libre
Score: 8
On s’était laissé précédemment avec la promesse d’examiner plus d’enquêtes s’il y a de la demande.[^unandeja]
[^unandeja]: Un petit coup de `stat` m’indique que mon fichier a été édité le 22 avril 2025 pour la dernière fois, et je le reprends le 6 avril 2026 (on va dire encore deux semaines et ça fera un an...) Du coup, il se peut que le contenu du site ait évolué depuis ; mais je n’ai pas pris la peine de vérifier et valider mon écrit ressorti du carton où il prenait la poussière.
# 4^th Case
Le dossier est présenté en commençant par sa date précise, mais ce serait trop facile
```sql
SELECT COUNT(*) FROM crime_scene WHERE date = 19871031; -- 51
SELECT COUNT(*) FROM crime_scene WHERE location LIKE '%Coconut%'; -- 2
SELECT COUNT(*) FROM crime_scene WHERE description LIKE '%Leonard%'; -- 0
```
On peut associer le lieu, mais ce n’est pas `'Coconut Grove'` comme je l’aurais cru.
## 1^st step
Un fois le dossier ouvert, on nous résume les deux lignes de `witness_statements.clue` à trouver.
```sql
-- imbrication
SELECT * FROM witness_statements
WHERE crime_scene_id = (
 SELECT id FROM crime_scene
 WHERE date = 19871031 AND location LIKE '%Coconut %'
);
-- jointure
SELECT w.* FROM witness_statements w
 INNER JOIN crime_scene c ON w.crime_scene_id = c.id
WHERE c.date = 19871031 AND c.location LIKE '%Coconut %';
```
Petite interrogation ici : que représente ce champ `witness_id` ?
Poursuivons. Les deux confessions renvoient à une réservation de chambre.
```sql
SELECT COUNT(*) FROM hotel_checkins; -- 200
SELECT COUNT(*) FROM hotel_checkins WHERE hotel_name LIKE '% regency'; -- 16
SELECT COUNT(*) FROM hotel_checkins WHERE hotel_name LIKE 'grand %'; -- 1
SELECT COUNT(*) FROM hotel_checkins WHERE check_in_date = 19871030; -- 65
SELECT COUNT(*) FROM hotel_checkins WHERE room_number = 707; -- 14
SELECT COUNT(*) FROM hotel_checkins
WHERE (room_number = 707) AND (check_in_date = 19871030); -- 10
```
Étrange toutes les réservations faites au même hôtel, même jour et même chambre. Est-ce que le journal des observations va nous apprendre quelque chose ?
```sql
-- imbrication
SELECT id, note
FROM surveillance_records
WHERE hotel_checkin_id IN (
 SELECT id FROM hotel_checkins
 WHERE (room_number = 707) AND (check_in_date = 19871030)
);
-- jointure
SELECT s.id, s.note
FROM surveillance_records s
 INNER JOIN hotel_checkins h ON s.hotel_checkin_id = h.id
WHERE (h.room_number = 707) AND (h.check_in_date = 19871030)
;
```
Quatre lignes sans note (valeur `NULL`) et une grosse comme un nez de clown...> Subject was overheard yelling on a phone: "Did you kill him?"
Mais si l’on tente de remonter naïvement la personne qui a fait cette réservation, cela ne marche pas...
```sql
--- requêtes imbriquées (en remontant le puzzle)
SELECT * FROM person WHERE id IN (
 SELECT person_id FROM hotel_checkins
 WHERE (room_number = 707) AND (check_in_date = 19871030)
 AND (id =(
 SELECT hotel_checkin_id FROM surveillance_records
 WHERE note LIKE '%Did you kill him?%'
 ) -- surveillance_records.hotel_checkin_id
 ) -- hotel_checkins.person_id
);
-- jointure
SELECT p.*
FROM surveillance_records s
 INNER JOIN hotel_checkins h ON s.hotel_checkin_id = h.id
 INNER JOIN person p ON h.person_id = p.id
WHERE (h.room_number = 707) AND (h.check_in_date = 19871030)
 AND (s.note LIKE '%Did you kill him?%')
;
```
En fait, n’allons pas croire que le coupable se trouve parmi les sept personnes ayant réservée...
```sql
--- imbrication
SELECT * FROM person WHERE id IN (
 SELECT person_id FROM hotel_checkins
 WHERE (room_number = 707) AND (check_in_date = 19871030)
);
-- jointure
SELECT p.*
FROM hotel_checkins h
 INNER JOIN person p ON h.person_id = p.id
WHERE (h.room_number = 707) AND (h.check_in_date = 19871030)
;
```
Au passage, je note l’égalité entre `surveillance_records.id` et `surveillance_records.hotel_checkin_id` sans savoir si cela a quelque signification.
## 2^nd step
Bien, au stade actuel, on sait qui a causé de meurtre mais on n’a pas encore d’assassin, tout au plus un commanditaire ou une personne qui en sait trop ?
Bonne nouvelle, comme une certaine [police secrète d’époque](https://www.historyhit.com/the-stasi-the-most-terrifying-secret-police-in-history/) ou une certaine [agence gouvernementale actuelle](https://www.techtarget.com/searchsecurity/definition/National-Security-Agency), nous surveillons les conversations [afin de préserver les libertés](https://www.vie-publique.fr/loi/277157-loi-pour-une-securite-globale-preservant-les-libertes)...
```sql
SELECT COUNT(*) FROM phone_records; -- 200
SELECT COUNT(DISTINCT call_date) FROM phone_records; -- 3
SELECT DISTINCT call_date FROM phone_records; -- 19871028 19871029 19871030
SELECT COUNT(*) FROM phone_records WHERE call_date = 19871030 -- 67
SELECT COUNT(*) FROM phone_records WHERE call_date = 19871028 -- 66
SELECT COUNT(*) FROM phone_records WHERE call_date = 19871028 -- 67
```
Ça va, que septante-sept lignes à scruter... Ou on peut ruser en cherchant directement les appels depuis ou vers la personne ayant réservé...
```sql
SELECT * FROM phone_records
WHERE (call_date = 19871030) AND (
 ( caller_id IN (11, 34, 78, 123, 178, 156, 198) )
 OR
 ( recipient_id IN (11, 34, 78, 123, 178, 156, 198) )
 );
```
Ça nous fait deux personnes à entendre...
```sql
SELECT id, confession FROM final_interviews WHERE
 person_id = (SELECT caller_id FROM phone_records WHERE note LIKE '%kill %')
 OR
 person_id = (SELECT recipient_id FROM phone_records WHERE note LIKE '%kill %')
 ;
```
Noter qu’ici aussi on a `final_interviews.id` qui a la même valeur que `final_interviews.person_id` pour une raison que j’ignore.
Mais on n’est pas plus avancé... Mais la conversion téléphonique contient un indice que l’on peut tenter d’exploiter.> You should have left the carpenter do it himself!
On va donc s’intéresser aux menuisiers...
```sql
-- imbrication
SELECT id, confession FROM final_interviews WHERE
WHERE person_id IN (
 SELECT id FROM person
 WHERE occupation LIKE 'carpenter'
);
-- jointure
SELECT i.id, i.confession FROM final_interviews i
 INNER JOIN person p ON i.person_id = p.id
WHERE p.occupation LIKE 'carpenter';
```
...et il y en a bien un qui avoue la commandite.
Dans le lot, il y a deux tables qui n’ont pas été utilisées : `vehicle_registry` et `catering_orders`. Dites moi si vous savez quelle est leur utilité...
# 5^th Case
Pour finir en beauté, temporairement (puisqu’on espère d’autres enquêtes), on va appliquer notre [forensic](https://en.wikipedia.org/wiki/Forensic_science) à l’industrie des [μprocs](https://en.wikipedia.org/wiki/Microprocessor).
## access logs check
Plus de table `crime_scene` et aucune idée de laquelle par laquelle commencer. Mais le `incident_reports` me semble bien...
```sql
SELECT * FROM incident_reports LIMIT 9; -- confirmation
SELECT * FROM incident_reports WHERE location LIKE '%quantum%'; -- bingo
```
Comme les tables sont données pratiquement dans l’ordre d’exploration conseillé, la seconde étape va être la consultations des témoignages comme précédemment.
```sql
SELECT * FROM witness_statements WHERE incident_id =
(SELECT id FROM incident_reports WHERE location LIKE 'Quantum%');
```
Le premier témoignage mentionne un serveur
```sql
SELECT DISTINCT server_location FROM computer_access_logs LIMIT 9; -- check
SELECT * FROM computer_access_logs
WHERE server_location = 'Helsinki'; -- 14
SELECT * FROM computer_access_logs
WHERE server_location = 'Helsinki' AND access_date =
 (SELECT date FROM incident_reports WHERE location LIKE 'Quantum%'); -- 4
```
Le second témoignage mentionne une carte d’accès
```sql
SELECT COUNT(*) FROM keycard_access_logs; -- 200
SELECT COUNT(*) FROM keycard_access_logs WHERE access_date =
 (SELECT date FROM incident_reports WHERE location LIKE 'Quantum%'); -- 200
SELECT DISTINCT keycard_code FROM keycard_access_logs LIMIT 9; -- overview
SELECT COUNT(*) FROM keycard_access_logs WHERE keycard_code LIKE 'QX-0%'; -- 70
SELECT * FROM keycard_access_logs
WHERE REGEX_LIKE(keycard_code,'QX-0[0-9][13579]');
-- [Error in query] no such function: REGEX_LIKE
SELECT * FROM keycard_access_logs
WHERE keycard_code REGEX 'QX-0[0-9][13579]';
-- [Error in query] near "REGEX": syntax error
SELECT * FROM keycard_access_logs
WHERE keycard_code SIMILAR TO 'QX-0[0-9][13579]';
-- [Error in query] near "SIMILAR": syntax error
SELECT * FROM keycard_access_logs
WHERE keycard_code LIKE 'QX-0%'
 --AND (SUBSTR(keycard_code,5,1) BETWEEN 0 AND 9)
 AND SUBSTR(keycard_code,6,1) IN (1,3,5,7,9); -- 0
SELECT * FROM keycard_access_logs
WHERE keycard_code LIKE 'QX-0%'
 --AND (SUBSTR(keycard_code,5,1) BETWEEN '0' AND '9')
 AND SUBSTR(keycard_code,6,1) IN ('1','3','5','7','9'); -- 36
SELECT * FROM keycard_access_logs
WHERE keycard_code LIKE 'QX-0%'
 --AND (SUBSTRING(keycard_code,5,1) BETWEEN '0' AND '9')
 AND SUBSTRING(keycard_code,6,1) IN ('1','3','5','7','9'); -- 36
SELECT * FROM keycard_access_logs
WHERE keycard_code LIKE 'QX-0%'
 --AND (SUBSTRING(keycard_code FROM 5 FOR 1) BETWEEN '0' AND '9')
 AND SUBSTRING(keycard_code FROM 6 FOR 1) IN ('1','3','5','7','9'); -- 36
-- [Error in query] near "from": syntax error
```
Ici, tous les accès sont datés du même jour ; donc ce critère ne sera pas utile/pertinent. J’ai pensé à utiliser l’information d’[imparité](https://fr.wiktionary.org/wiki/imparit%C3%A9) en prenant en compte le fait que tous les badges ici commencent par « `QX-` » avec trois chiffres (et non deux... à moins que ce ne soient deux impaires et que j’ai mal compris ?) Je pense que l’usage des expressions rationnelles est fort utile (on réduit de 70 à 36 entrées, ce qui est encore beaucoup.)
 - Sauf erreur, la fonction `REGEX_LIKE(column,'POSIX ERE'[,opt-flags])`
 connue de plusieurs éditeurs (en tout cas DB2, My/Maria, Oracle, Pg,
 T-SQL, et certainement d’autres), est dans le standard (1999 ?) Mais ce
 n’est pas reconnu ici...
 - On retrouve aussi la syntaxe `column REGEX 'some RE'` chez certains
 éditeurs (dont Sybase/SAP, [SQLite via une fonction
 personnalisée](https://www.slingacademy.com/article/how-to-use-regular-expressions-regex-in-sqlite/),
 MySQL et MariaDB qui ont aussi l’alias `RLIKE`, etc.) Ce n’est pas
 reconnu non plus...
 - Il y a la syntaxe `column SIMILAR TO 'SQL RE'` qui est dans SQL:1999 et
 fonctionne comme `column LIKE 'pattern'` (les méta caractères `_` et `%`
 ont la même signification et c’est tout le champ qui est validé), mais
 avec des extensions des expressions rationnelles POSIX (il n’y a pas le
 méta caractère `.` ni les ancres `^` et `$`.) C’est un excellent entre
 deux supporté par certains éditeurs (dont PostgreSQL et SQLanywhere) mais
 pas encore par tous (notamment pas My/Maria et SQLite), ce qui est le cas
 (non support) ici.
 - Moins connu (et ne fonctionne pas ici...) SQL:2003 a également spécifié
 `SUBSTRING(column SIMILAR 'pattern' [ESCAPE escape])` comme écriture
 alternative. Pour l’instant, ça ne semble reconnu que par PostgreSQL...
Tout espoir n’est pas perdu car l’expression rationnelle peut être traduite facilement par des comparaisons sur des sous-chaînes... Mais j’y vais avec un peu d’appréhension car c’est un peu le zoo...
 - Dans le sillage d’Oracle, beaucoup de SGBD (dont SQLite) utilisent
 `SUBSTR(column,start[,length])` avec le comptage à partir de `1` et
 optionnellement le nombre de caractères à extraire (tout le reste si
 rien n’est précisé.) Beaucoup d’implémentations permettent de compter à
 partir de la fin en utilisant un nombre négatif (avec `-1` donc pour le
 dernier caractère.) Ça marche ici...
 - D’autres éditeurs (dont SQL Anywhere), dans le sillage de T-SQL,
 utilisent plutôt `SUBSTRING(column,start[,length])` pareillement. Avec
 des signatures similaires, on retrouvera les deux formes (c’est le cas
 ici...), `SUBSTRING` étant alors un alias de `SUBSTR` (cas par exemple de
 SQLite≥3.34) ou l’inverse (cas par exemple de Sybase, mais aussi My/Maria
 qui ajoutent aussi l’alias `MID` en clin d’œil aux BASIC.)
 - On peut retrouver d’autres fonctions (comme `MID(column,start[,length])`
 déjà évoqué et qui n’est pas reconnu ici...) et SQL:2008 a finalement
 retenu `SUBSTRING(column [FROM start] [FOR length])` (pas reconnu ici non
 plus, mais pris en charge uniquement dans My/Maria, Ingres, Pg, aucunement
 par les grosses solutions propriétaires au moment où j’écris...) évidemment
 (le standard privilégie toujours une formulation explicite et un peu
 verbeuse.)
 - Parmi les fonctions d’extraction de chaîne, SQL Server et d’autres dans
 son sillage proposent les raccourcis (pas exactement mais on va dire cela
 pour faire simple) `LEFT(column,length)` et `RIGHT(column,length)`
 pour `SUBSTRING(column,1,length)` (ou `SUBSTRING(column FOR length)`...) et
 `SUBSTRING(column,-1,length)` respectivement.
On a eu du bol... Mais il reste quand même beaucoup de lignes à parcourir (14 et 36) Puisque nos deux tables ont des champs communs, on peut faire faire la comparaison au serveur...
```sql
SELECT employee_id, access_date, access_time FROM computer_access_logs
WHERE server_location = 'Helsinki' AND access_date =
 (SELECT date FROM incident_reports WHERE location LIKE 'Quantum%')
INTERSECT
SELECT employee_id, access_date, access_time FROM keycard_access_logs
WHERE keycard_code LIKE 'QX-0%'
 AND SUBSTRING(keycard_code,6,1) IN ('1','3','5','7','9')
; -- 0/1 avec/sans access_time
```
Non, cette personne n’est pas notre coupable comme il en ressort de sa confession... Il est normal que nous soyons passé à côté vu que cette entrée (de `witness_statements`) n’est associée à aucun incident... 😉
## mails logs check
Bien, voyons donc les mails reçus par cette personne ce jour là.
```sql
SELECT * FROM email_logs WHERE
 (recipient_employee_id = 99) -- mettre la valeur trouvée
 AND (email_date =
 (SELECT date FROM incident_reports WHERE location LIKE 'Quantum%')
 ); -- le premier critère seul suffit en fait
```
La source du message n’a rien noté d’anormal (i.e. pas d’entrée dans `witness_statements`) et n’a envoyé que ce message. Par contre cette source a reçu deux autres messages anonymes...
```sql
SELECT * FROM email_logs WHERE recipient_employee_id =
 (SELECT sender_employee_id FROM email_logs
 WHERE recipient_employee_id = 99);
```
On connaît deux éléments de la chaîne d’événements de l’affaire.
```sql
SELECT * FROM employee_records WHERE id = 99 OR id =
 (SELECT sender_employee_id FROM email_logs
 WHERE recipient_employee_id = 99);
```
Il nous manque une troisième personne mentionnée dans l’un des mails> [...] but we need her inside F18 before 9. [...]> Unlock 18 quietly by 9. He’ll use his own credentials to access it shortly after L leaves. [...]
La lumière est, quand on comprend à quoi il est fait référence...
```sql
SELECT COUNT(*) FROM facility_access_logs; -- 100
SELECT COUNT(*) FROM facility_access_logs WHERE access_date =
 (SELECT date FROM incident_reports WHERE location LIKE 'Quantum%'); -- 100
SELECT COUNT(DISTINCT facility_name) FROM facility_access_logs; -- 79
SELECT DISTINCT facility_name FROM facility_access_logs LIMIT 9; -- check...
SELECT COUNT(*) FROM facility_access_logs WHERE facility_name LIKE '%18'; -- 3
-- jointure
SELECT e.*, f.access_time FROM facility_access_logs f
 INNER JOIN employee_records e ON e.id = f.employee_id
WHERE f.facility_name LIKE '%18'
ORDER BY f.access_time;
-- imbrication
SELECT * FROM employee_records WHERE id IN (
 SELECT employee_id FROM facility_access_logs
 WHERE facility_name LIKE '%18'
 ORDER BY access_time
);
```
Et voilà...
# à suivre
Six affaires résolues et 1900 points d’expérience engrangés.
Il y a des requêtes que j’aurais voulu refaire pour tester le support des CTE et autres constructions un peu sympathiques. Mais la longue pause (et je ne sais même plus pourquoi j’avais oublié ce [*writeup*](https://www.merriam-webster.com/dictionary/write-up) en cours) me rend l’exercice peu attrayant (en plus j’ai peu de bande passante.) Entre temps, le site s’est doté d’un blog que je viens de découvrir : [www.sqlnoir.com/blog/](https://www.sqlnoir.com/blog/page/4). Le [premier post](https://www.sqlnoir.com/blog/games-to-learn-sql) présente d’autres jeux similaires qui occuperont probablement mes prochains congés. Les deux posts suivants donnent les clés pour SQL Noir et le quotidien des analystes...
</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/encore-du-sql-noir.md">(-&gt;オリジナル)</a>
/ <label>アドレス: <input type="text" name="naked_post_url" value="httpS://linuxfr.org/users/gilcot/journaux/encore-du-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>