[HN Gopher] Show HN: SQL Noir - Learn SQL by solving crimes
___________________________________________________________________
Show HN: SQL Noir - Learn SQL by solving crimes
I built SQL Noir, an interactive detective game that challenges you
to solve mysteries using real SQL queries. It's fully open source,
designed to give you a practical and immersive way to learn SQL
while engaging with a narrative-driven mystery.
Author : chrisBHappy
Score : 402 points
Date : 2025-02-13 21:49 UTC (1 days ago)
(HTM) web link (www.sqlnoir.com)
(TXT) w3m dump (www.sqlnoir.com)
| deanebarker wrote:
| This is really lovely. It makes me happy that you invested time
| into building this for other people.
| jppope wrote:
| this is awesome! soooo fun. Thank you for building this. Sharing
| it immediately
| ryanianian wrote:
| 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.
| SlackingOff123 wrote:
| I agree. I also wish it was possible to run multiple queries in
| the same window e.g.: select * from
| crime_scene; select * from suspects;
| sgarland wrote:
| SELECT cs.*, s.* FROM crime_scene
| cs FULL JOIN suspects s ON cs.id = s.id;
|
| This maintains zero relationship between the tables, of
| course, but it shows you both. You could also specify
| individual columns.
| chrisBHappy wrote:
| That's a good suggestion. I'm not sure if screen real estate
| won't be a problem though.
| hinkley wrote:
| Maybe modeled after browser dev tools. Big display at the
| top, small repl at the bottom with a dragged to resize. Also
| tab structure and the "menu" are chewing up a lot of vertical
| real estate for two classes of data most users won't see as
| separate.
|
| The schema ui seems to be big because of the graph display...
| which is not at all done baking yet. The hard part of
| displaying graphs is the pathing, and the very first one I
| opened has an arrow coming from the left, going across the
| mode, and attaching to the right side. In a perfectly
| horizontal line. That's gonna need to cook a lot longer. It
| it worth having a bad vis over having no vis?
| chrisBHappy wrote:
| Just implemented it. Looks really nice on Desktop. Nice and
| compact.
| __float wrote:
| +1 to this. Remembering the schema is kind of a big barrier
| when you're just jumping in, and that's when you want to be
| able to explore at random the most.
|
| (I also struggled with the schema because crime_scene was
| singular and suspects was plural!)
| chrisBHappy wrote:
| Yes! I just implemented a "Side By Side" view that will enable
| you to view 2 separate tabs side by side. Only on Desktop.
| chocks wrote:
| 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 :)
| chrisBHappy wrote:
| You should be able to add -- and comment out lines of queries.
| If not, this is a bug that I will investigate. Also you can use
| the Notes tab to store any past queries and findings.
| mymacmachine wrote:
| Hey!
|
| Firstly - great work - one of my favourite games is
| Chronicles of Crime and this immediately made me think back
| to how I would've gone about investigating some of those
| stories using SQL.
|
| w.r.t the comments - you can add `--` to comment out lines of
| queries but when I do it on two or more lines they get mashed
| into a single line and I can't reliably uncomment them again
| without the queries getting mangled.
|
| I've found you can use multiline comments /* and */ though
| which worked for me - just a bit more back and forth on the
| keyboard.
|
| Anyway - again - awesome work.
| chrisBHappy wrote:
| Thank you very much. That's actually a great suggestion. I
| completely forgot about that. As for the single line
| comments, indeed this is a bug, I'll try to fix it.
| pledg wrote:
| It works for the first line, but doing it on a second seems
| to make it merge lines.
| chrisBHappy wrote:
| Yep. This was a bug. I just fixed it.
| Terr_ wrote:
| This gives me a childhood flashback to a show called MathNet, an
| educational police procedural (a la Dragnet) where both
| investigators have holstered calculators.
|
| https://en.m.wikipedia.org/wiki/Mathnet
| bhollan wrote:
| I cannot even tell you how many glorious memories this brings
| up! Thanks for a walk down memory lane.
| nedrylandJP wrote:
| and there were infamous hackers too
|
| https://youtu.be/vNhBq3kjq_Q?t=2011
| Terr_ wrote:
| Taking advantage of an extra that wasn't charged to the
| customer?
|
| Ah, that's a classic "use after free" vulnerability.
| agumonkey wrote:
| reminds me of https://mystery.knightlab.com/
| chrisBHappy wrote:
| An honorable mention. This was in fact my inspiration for
| creating SQL Noir.
| yoda97 wrote:
| That was fun! One question, is there a way to execute the current
| hovered/selected query and not the first on in the workspace?
| chrisBHappy wrote:
| You can try commenting out the first query with -- and running
| it like that. I have not implemented yet the ability to run
| multiple queries.
| hobs wrote:
| 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.
| chrisBHappy wrote:
| Thank you for pointing that out. Will look into it.
| hinkley wrote:
| Feature request:
|
| Badges for completing challenges.
|
| Issue Closed, Won't Fix.
|
| Badges?? We don't need no _stinking_ badges!
| doruk101 wrote:
| When I was in university, my instructor linked something like
| this for SQL practice (a crime solving minigame, just like this
| one).
|
| I 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.
| worble wrote:
| This is super fun!
|
| 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.
| haliskerbas wrote:
| 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?
| chrisBHappy wrote:
| Yep, comments are broken. Currently working on a fix.
| RandomUser4976 wrote:
| This is so much fun! I'm going to play this with my kids ages 6,
| 8, 10, and 12. Thank you for your efforts!
| yesthisiswes wrote:
| 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.
| chrisBHappy wrote:
| Thanks for pointing that out. I just fixed the single line
| comments. It should work as expected now.
| yesthisiswes wrote:
| Awesome thanks! I'll give the second challenge a go
| wbakst wrote:
| Love this! The style is unique and awesome.
|
| 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!
| dylan604 wrote:
| > - 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.
|
| I imagined myself with my flip notepad at the crime scene
| taking notes every time I'd copy queries/results to the notepad
| section. All I needed was a hat, and then say "just the facts,
| ma'am" a couple of times.
| wbakst wrote:
| I like that way of thinking about it!
| chrisBHappy wrote:
| Good notes indeed. I have just implemented a side by side view
| so that you can view 2 tabs at once. I hope this helps. Also as
| other users suggested you can use the notes for saving queries
| but you can also comment them out in the editor.
| wbakst wrote:
| I love the updates! Side-by-side is great, the graph view is
| great. Awesome stuff! Thanks for sharing.
| rockfishroll wrote:
| 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%'.
| sprobertson wrote:
| That's how I solved #3, still trying to reverse-engineer the
| correct solution.
| dylan604 wrote:
| Isn't that the reversed method? You mean you're now trying to
| solve the crime the normal way instead of jumping to the last
| page of the book first?
| chrisBHappy wrote:
| Haha. You found a cheat code I guess. I would have to think of
| hiding the answers from plain sight for the next cases or
| adding some more rows that include these specific strings.
| sgarland wrote:
| This is very nice, thank you for making it.
| wagslane wrote:
| haha this is such a cool aesthetic! nice work.
|
| education needs more stuff like this - less matter-of-fact
| regurgitation of information (which ai does an okay job of
| anyhow) and more creativity.
| Factory wrote:
| On case #4: select * from phone_records where caller_id= 11 or
| recipient_id = 11
|
| The results header is screwed up, there is an extra 'id' column.
| At least on firefox.
| Geezus_42 wrote:
| 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.
| abrookewood wrote:
| Agreed, but there is a suggested query to get you started at
| each step.
| dylan604 wrote:
| Some games require reading of the instructions before playing.
| Not all games are Candyland or Chutes&Ladders simple. Sometimes
| you need a bit of basic knowledge gathering to play the most
| basic levels of the game.
|
| There's also plenty of ways to teach. Some methods assume a
| basic level of the concepts involved, but by continuing, you'll
| be exposed to new concepts that level up those skillz. Just
| like how arithmetic comes before algebra comes before trig
| comes before calculus.
| thomaspark wrote:
| Querymon is a more tutorial-like SQL game:
|
| https://www.youtube.com/watch?v=kkP_W8xFz5s
| exabrial wrote:
| Just wanted to say this is awesome! sent it to a few coworkers
| webninja wrote:
| How do you make money doing this and if you earn nothing, how can
| you afford to in this economy?
| solumunus wrote:
| The bitterness is palpable.
| dylan604 wrote:
| hobby--noun--An activity that one enjoys doing in one's spare
| time.
|
| also: https://en.wikipedia.org/wiki/Hobby
| chrisBHappy wrote:
| Glad you asked. I don't want to put a price tag on it because I
| believe everyone should have access to learning for free. The
| right to learn shouldn't be behind a paywall. That said, I've
| added a 'Buy Me a Coffee' link for anyone who wants to support
| the project. No pressure, just an option!
| Foofoobar12345 wrote:
| Nice work. Just what I need to train the non-tech ppl in my team.
| Wish more things were fun to learn like this.
| dylan604 wrote:
| 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
| chrisBHappy wrote:
| Thank you for pointing that out. During the creation of the
| cases I did not take much consideration on naming the tables. I
| will either make all of them plural or singular.
| kaeruct wrote:
| 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
| chrisBHappy wrote:
| Indeed. I think I just fixed this bug.
| 8mobile wrote:
| 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
| gregorvand wrote:
| Fun! Nice work and will be great to intro newbies to SQL
| arlanrakh wrote:
| congrats!
| vim-guru wrote:
| What a great idea!
| laowantong wrote:
| 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.
| frontalier wrote:
| https://sqlbolt.com, https://regexone.com,
| https://pgexercises.com
| daluc wrote:
| Great project, totally agree! Also further great projects
| posted here. I'll chip in here my project of an SQL Game
| Console that comes with a SQL Game Editor -- allowing students
| to build their own SQL escape game in the browser:
| https://eskuel.de . It's been fun trying this out in the class
| room (only available in German right now). Example game:
| https://eskuel.de/zoo/
| hotfrost wrote:
| I'm a big SQL noob, so I barely know what I'm doing, but... I
| managed to crash both Firefox and Edge whenever I ran this query:
| select * from crime_scene, suspects
| Gazoche wrote:
| Oh this is perfect. I was just looking for exercises to practice
| my SQL, and detective games are my jam.
| RvdV wrote:
| I like it! Very fun! Few ideas:
|
| - 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
| caro_kann wrote:
| > - The "submit" box isn't clear on whether it needs the name
| or the suspect id for the first case
|
| I think it's implemented to be general, they wrote what to
| submit in 'Objectives' section though. For me as a user, I
| deduced that they wanted "suspect" name, after all, this is a
| detective game.
| scns wrote:
| 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?
| fodkodrasz wrote:
| The idea is good, but the UI is all but immersive.
|
| 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.
| tiu wrote:
| Agree with all but autocomplete should either be a hidden
| feature or disabled by default since I think it takes away from
| the immersion as well forces you to recall.
|
| Maybe enabled on a NG+?
| jkubicek wrote:
| Commenting out blocks of code with command-/ would be super
| useful as well
| chrisBHappy wrote:
| Thank you for the suggestions. I have just implemented the
| following: - Side by Side view to open up two different tabs at
| the same time - Copy button to copy table names
| fodkodrasz wrote:
| Great job! I'm happy if I could provide useful feedback. I
| wish you luck, this is a fun learning tool!
| normie3000 wrote:
| When I click on SQL Workspace, I get an error:
|
| > Failed to load database Can't find variable: WebAssembly
| chrisBHappy wrote:
| Oh wow. That's strange. What browser are you using?
___________________________________________________________________
(page generated 2025-02-14 23:01 UTC)