One small thing which would be nice is the ability to just download the sqlite database myself so I could use my preferred application to query it, and just use the site for the brief, notes and submission.
Also a nitpick, while you say it's for "learning" SQL, I would probably expect something a little more guided, or at least some example queries, if the intention was to teach SQL. As it stands, if you don't know SQL you're probably going be completely lost as to what you should be doing. It's really cool, just not specifically as a learning tool.
A great addition to a field where there aren't many offerings: SQL Island (https://sql-island.informatik.uni-kl.de) and SQL Murder Mystery (https://mystery.knightlab.com) come to mind. The mechanism of SQL Noir is close to that of the latter, i.e., an undirected, essentially standalone adventure.
I myself am working on SQLab, a SQL game engine that allows you to augment an arbitrary base with exercises on that base to produce directed, standalone adventures: https://github.com/laowantong/sqlab.
You download a dump of the database (currently MySQL, SQLite, Postgres), and can play under any administrator interface. On the same page there's a link to the long police investigation I designed for my (French) students. If you're a teacher or researcher in the field, the principles of SQLab are explained here: https://arxiv.org/abs/2410.16120.
This gives me a childhood flashback to a show called MathNet, an educational police procedural (a la Dragnet) where both investigators have holstered calculators.
Wow what a great idea, I had fun solving the cases. I think maybe a comic version would help junior programmers. The SQL editor sometimes behaves strangely and does not allow correct writing.
Thanks
The editor behaves weirdly when I try to add comments.
It's hard to explain, but you can reproduce like this:
1. Write several lines, for example:
select i.*, s.*
from interviews i
join suspects s
on s.id = i.suspect_id
2. Try to comment each line on its own by typing -- in front of each line.
-- select i.*, s.*
from interviews i
join suspects s
on s.id = i.suspect_id
-- select i.*, s.*
-- from interviews i
join suspects s
on s.id = i.suspect_id
3. As soon as you do it for "from interviews i", that line will move itself to the previous line, and the syntax highlighting will be broken
This is pretty fun, I tried the two free mysteries and was fun solving them. One nit, would be nice if the SQL editor supported comments so we can comment out old queries before running new one so as keeping a history esp if we need to run the same queries again. Good stuff :)
Really cute. But I really want the ability to put the different tabs -- Brief, Workspace, Schema -- side-by-side. I know SQL and wanted to play with this, but the UX was frustrating enough to drive me away, even though it is really pretty.
Something is weird in at least firefox.
Type or paste:
select \*
from crime_scene;
then TYPE so you add a comment
select \*
from crime_scene;
--
I see
select ----from----------------
but when I select the text I see what I wrote - I like the comment my text (and I was pulling in the instructions) but it renders some interesting garbage pretty fast.
This is cool! Great job! I completed the first challenge.
I guess I’m used to sql server management studio I tried running multiple queries at once and wasn’t able to. I also tried writing a comment with two dashes to keep track of the id’s and it replaced most of my query with dashes. It might have something to do with being on mobile on iOS.
I just shared this with folks, it looks incredible! I’m giving it a try myself too.
By the way, I was trying to use a bunch of one-liner SQL statements to explore the data, and it seems like the editor doesn’t handle comments very well. Is there a way to make it work better?
On case #004, even though I found the killer by occupation and the confession. Person 57 does not give me any clue about a Lamborghini, as the solution text mentions. Is there a bug, or what did I miss?
Great fun, thank you for building this! Was confused since submitting the confession didn't work, would make sense to ask for the name of the person no?
I solved all of them. Minor nitpick: Since every criminal confesses to their crime, the fastest way to solve most of these is to query the confessions table for strings like '%i did%' or '%kill%'.
tables: crime_scene, witnesses, interviews, suspects. one of these is not like the others.
I've seen it advised to list table names as plurals as it holds more than one of the things. I've been told plural is dumb. I've not yet run into mixed plurals and singular table names. Sure, it's a style, but pick one???
I'm guessing this is like tabs vs spaces, but would a tab person use spaces randomly or vice versa?
otherwise, it's a fun way to kill some time. clearly, i played with it long enough to notice this little bitty bit of something. however, if only modern police departments had this ability to link clues. there's no way to drip donut cremes or spill coffee on these notes
I'm on mobile, so maybe I'm missing something, but I don't understand how youbare even supposed to get started without knowing SQL. If this is supposed to teach SQL then there needs to be some tutorial to guide the way.
Show HN: SQL Noir – Learn SQL by solving crimes
(sqlnoir.com)461 points by chrisBHappy 13 February 2025 | 85 comments
Comments
- The "submit" box isn't clear on whether it needs the name or the suspect id for the first case
- It would be nice to have a "copy to notes" button in the output
- It would be nice to have some docs on what functions are available / which dialect this is.
I think this very valuable, it's so much more easy to learn if you actually have a small project to work on
One small thing which would be nice is the ability to just download the sqlite database myself so I could use my preferred application to query it, and just use the site for the brief, notes and submission.
Also a nitpick, while you say it's for "learning" SQL, I would probably expect something a little more guided, or at least some example queries, if the intention was to teach SQL. As it stands, if you don't know SQL you're probably going be completely lost as to what you should be doing. It's really cool, just not specifically as a learning tool.
https://en.m.wikipedia.org/wiki/Mathnet
I see the beginnings of a really fun way to learn / practice / remember SQL.
A few notes:
- Would be cool if it was a single workspace (no tabs). Was constantly switching tabs back and forth.
- Saving previous SQL queries and results would be cool. I was copying results into the notes. Feel like this is important as things get more complex.
Excited to see where you take this!
Things that would it make more immersive:
- autocomplete
- and/or the ability to view the schema alongside the query (on a wide enough screen)
- a way to copy table and column names easily with a click (table names cannot be selected at all!)
- ability to just add more queryies under/over the already executed ones, instead of only being able to replace them.
It's hard to explain, but you can reproduce like this:
1. Write several lines, for example:
2. Try to comment each line on its own by typing -- in front of each line. 3. As soon as you do it for "from interviews i", that line will move itself to the previous line, and the syntax highlighting will be brokenI remember getting really into it, even going to the extreme of trying to find the most efficient one-liner solution.
Thanks for making this. I’ll be passing the torch by linking it to anyone interested in learning SQL.
The results header is screwed up, there is an extra 'id' column. At least on firefox.
I guess I’m used to sql server management studio I tried running multiple queries at once and wasn’t able to. I also tried writing a comment with two dashes to keep track of the id’s and it replaced most of my query with dashes. It might have something to do with being on mobile on iOS.
By the way, I was trying to use a bunch of one-liner SQL statements to explore the data, and it seems like the editor doesn’t handle comments very well. Is there a way to make it work better?
On case #004, even though I found the killer by occupation and the confession. Person 57 does not give me any clue about a Lamborghini, as the solution text mentions. Is there a bug, or what did I miss?
Badges for completing challenges.
Issue Closed, Won’t Fix.
Badges?? We don’t need no stinking badges!
education needs more stuff like this - less matter-of-fact regurgitation of information (which ai does an okay job of anyhow) and more creativity.
> Failed to load database Can't find variable: WebAssembly
I've seen it advised to list table names as plurals as it holds more than one of the things. I've been told plural is dumb. I've not yet run into mixed plurals and singular table names. Sure, it's a style, but pick one???
I'm guessing this is like tabs vs spaces, but would a tab person use spaces randomly or vice versa?
otherwise, it's a fun way to kill some time. clearly, i played with it long enough to notice this little bitty bit of something. however, if only modern police departments had this ability to link clues. there's no way to drip donut cremes or spill coffee on these notes