sqlnoir.com
- 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
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.
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.
Ah, that's a classic "use after free" vulnerability.
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!
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.
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.
Maybe enabled on a NG+?
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
-- 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
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.
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.
select * from crime_scene;
select * from suspects; SELECT
cs.*,
s.*
FROM crime_scene cs
FULL JOIN
suspects s ON cs.id = s.id;
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?
(I also struggled with the schema because crime_scene was singular and suspects was plural!)
The results header is screwed up, there is an extra 'id' column. At least on firefox.
select \*
from crime_scene;
select \*
from crime_scene;
--
select ----from----------------
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?
Badges for completing challenges.
Issue Closed, Won’t Fix.
Badges?? We don’t need no stinking badges!
select * from crime_scene, suspectsThis is a cross-join. It is the equivalent of a nested loop in programing and almost never what you want to do in a database unless you like watching high-combination data spewing out and CPUs burning. It gives you every combination of items in each table you name.
What you want is to find something in two or more of those tables that can be linked together somehow using (very likely!) "INNER JOINS". Look into examples or tutorials of those on the internet and use what you find to work through the tables on the "investigations". Luckily, the SQL Noir site tells you exactly the steps to take in the first case. You need to find a link somehow between the crime_scene and suspects tables and then another one between the matching suspects from the suspects table and the interviews table.
Note: there is no direct easy link between the crime_scene and suspects tables. You will have to use your investigation skills to trim the suspects list down.
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?
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
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.