I played along on the web based ui but for the life of me can't figure out how to check my solution? I see that there is an insert statement given on the github page but I get a 'Statement must be a SELECT' error when I try to execute it. What am I missing?
"A crime has taken place and the detective needs your help. The detective gave you the crime scene report, but you somehow lost it. You vaguely remember that the crime was a murder that occurred sometime on Jan.15, 2018 and that it took place in SQL City . Start by retrieving the corresponding crime scene report from the police department’s database."
Thanks! This was the key sentence missing from the original link. I hadn't a clue what I was supposed to be looking for, but this is the minimum info needed to get started. (NB: Solved it in a few minutes after I think 8 queries - though I could have combined a couple I think, for even less queries)
I love this type of story-based learning! I was playing with twilioquest this weekend, I really appreciate the developers’ creativity. How can I find more tools like these?
Be warned that there are spoilers built into the DB, if you look too closely. The trigger on the solution DB has the answer built into the SQL, as I learned when I ruined the game for myself.
I accidentally revealed the solution after running the .schema command (the solution is in the trigger code on that table). ️ Might be a record though - I got the answer in only a few seconds.
Indeed, Noah's project was a direct inspiration for us, and we tried to clearly credit it.
We love that people are enjoying this so much, so we're also grateful to Simon Willison for reviving attention on the original project, and Zi Chong Kao, whose SQL tutorial site showed us the possibility of mounting the whole thing in a browser page.
(I'm the person who guided the Knight Lab students' original work and ported it to the web hosted version at mystery.knightlab.com)
Really enjoyed playing through the game, and especially enjoyed seeing your name in the credits and thinking hey, I was pretty suspicious of you at one point.
OMG I love this. I just sent it to my buddy who as a PM is learning SQL to not rely on the BI team since the turnaround can be days.
Ultra tiny desire - I only wish there was more on the story line/intro framing. Took at look at the schema and was like "What is Get fit now? - ohhhh, it's a gym... got it..."
One of the productive things we did as a team is to give our SQL savvy PM read access to a production BI replica.
We'll still get occasional requests for information or questions, but by and large he can deal with the day to day.
So good on your buddy for wanting to learn how to do it himself. If he's even halfway successful it'll save him and his teammates time in the long run.
This was a lot of fun; I'd definitely play level 2.
The nice thing about it was that you had some flexibility around getting the exact SQL query, and just visualizing a slightly larger superset of the data.
I've already put 10 with a data analyst course so i know what a sqlite , select and joins are. I've created a not trivial query once.
Is this OK for learn some more ?
If you have even more time, check out the different things an SQL server can do when you DROP a row that contains references, how you define CONSTRAINTs, and some particular features that only some subset of SQL dialects have, such as LISTEN/NOTIFY for PostgreSQL.
If you're a developer, I would look into CONSTRAINTS way before getting to anything like this. Learning how to model your constraints correctly in the database (preventing duplicates, etc) is one of the most important bits of schema design because it will save you tons of code and likely a bunch of race conditions.
Knowledge is power and knowing more deeply about what your database is capable of is very helpful. It's worth knowing every tool in your tool chest!
That said, be careful with going too overboard with one specific tool (SQL). You may find yourself using a hammer for everything, when another tool may be more appropriate.
More specifically, it's easy to abuse advanced SQL constructs to create a mess for yourself. Too much business logic in the database is something I've seen become a nightmare for other programmers looking at software later. :)
This post[0] goes a bit into more detail about explaining where you should keep your "business logic" when choosing to put it in code or into SQL.
Yes, I think it's very useful. There are lots of things that SQL can do that are being done by code right now because the developers don't know any better.
The game itself has a walkthrough [0] of all the SQL features you need to solve the murder mystery. From skimming through it, it seems to be pretty good and succinct.
I accidentally revealed the solution after running the .schema command (the solution is in the trigger code on that table). Might be a record though - I got the answer in only a few seconds.
It was a very long time ago and, then, everyone was wondering where Snowden was. I think this was after he was identified, and just before he ended up in Russia.
Anyway, you are given an NSA Prism terminal, complete with Windows 95 look and feel and a paperclip.
You have to use your leet SQL skills to find out what flight he was on, and then book your agents onto intercepting flights.
Because it was all client-side javascript, and hosted on github pages, its still playable all these years later:
In reality, the government took the easy route and just revoked his passport while he was one his way from Hong Kong to Moscow, to catch a connecting flight to another flight to Ecuador. They forced him to set up residency in Russia of all places!
81 comments
[ 3.0 ms ] story [ 154 ms ] threadhttps://github.com/NUKnightLab/sql-mysteries/blob/master/pro...
:-D
EDIT: Running the queries from the other URL (http://mystery.knightlab.com/) works. Admins, you should change the URL to this one.
"A crime has taken place and the detective needs your help. The detective gave you the crime scene report, but you somehow lost it. You vaguely remember that the crime was a murder that occurred sometime on Jan.15, 2018 and that it took place in SQL City . Start by retrieving the corresponding crime scene report from the police department’s database."
Fun little thing, thanks!
https://github.com/veltman/clmystery
We love that people are enjoying this so much, so we're also grateful to Simon Willison for reviving attention on the original project, and Zi Chong Kao, whose SQL tutorial site showed us the possibility of mounting the whole thing in a browser page.
(I'm the person who guided the Knight Lab students' original work and ported it to the web hosted version at mystery.knightlab.com)
Ultra tiny desire - I only wish there was more on the story line/intro framing. Took at look at the schema and was like "What is Get fit now? - ohhhh, it's a gym... got it..."
We'll still get occasional requests for information or questions, but by and large he can deal with the day to day.
So good on your buddy for wanting to learn how to do it himself. If he's even halfway successful it'll save him and his teammates time in the long run.
This was a lot of fun; I'd definitely play level 2.
The nice thing about it was that you had some flexibility around getting the exact SQL query, and just visualizing a slightly larger superset of the data.
I've already put 10 with a data analyst course so i know what a sqlite , select and joins are. I've created a not trivial query once. Is this OK for learn some more ?
Try looking into SQL functions & TRIGGERs, LATERAL JOINs, ROLLUP & CUBE.
If you have even more time, check out the different things an SQL server can do when you DROP a row that contains references, how you define CONSTRAINTs, and some particular features that only some subset of SQL dialects have, such as LISTEN/NOTIFY for PostgreSQL.
www.sqlcourse.com
www.sqlcourse2.com
If you're a developer, I would look into CONSTRAINTS way before getting to anything like this. Learning how to model your constraints correctly in the database (preventing duplicates, etc) is one of the most important bits of schema design because it will save you tons of code and likely a bunch of race conditions.
That said, be careful with going too overboard with one specific tool (SQL). You may find yourself using a hammer for everything, when another tool may be more appropriate.
More specifically, it's easy to abuse advanced SQL constructs to create a mess for yourself. Too much business logic in the database is something I've seen become a nightmare for other programmers looking at software later. :)
This post[0] goes a bit into more detail about explaining where you should keep your "business logic" when choosing to put it in code or into SQL.
0: https://softwareengineering.stackexchange.com/questions/1944...
[0]: https://mystery.knightlab.com/walkthrough.htmlhttps://myster...
https://lagunita.stanford.edu/courses/DB/2014/SelfPaced/abou...
I got it in around 15 minutes though, really wish there was a bit more too it, or that more complex queries were required.
And really, the most valuable piece, IMO, are the SQL web components, from SelectStarSQL, which are CC0 public domain.
http://ludumdare.com/compo/ludum-dare-27/?action=preview&uid...
It was a very long time ago and, then, everyone was wondering where Snowden was. I think this was after he was identified, and just before he ended up in Russia.
Anyway, you are given an NSA Prism terminal, complete with Windows 95 look and feel and a paperclip.
You have to use your leet SQL skills to find out what flight he was on, and then book your agents onto intercepting flights.
Because it was all client-side javascript, and hosted on github pages, its still playable all these years later:
http://williame.github.io/ludum_dare_27_snowden/index.html
Of course it was a complete flop!
But making an sql engine under that kind of time pressure was pretty intense.
https://williamedwardscoder.tumblr.com/post/59997353762/runn...
Hard to believe that whole Snowden thing was 6 years ago now!?
But wasn't Emscripten already a thing in 2013? SQLite requires nearly no dependencies so it should have been possible to compile it.
Writing an SQL engine is like writing a ray tracer or implementing a compression algorithm: every programmer should do it!