URL:     https://linuxfr.org/users/gilcot/journaux/encore-du-sql-noir
Title:   encore du SQL noir 🎭
Authors: Gil Cot ✔
Date:    2026-04-06T21:29:00+02:00
License: CC By-SA
Tags:    sql, retour_d_expérience et jeu_libre
Score:   2


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 20255 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 pose (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…