11 ms·
Show HN: SQL Police Department – Learn SQL while solving crimes
- ryannevius 6y agoThis reminds me of the SQL Murder Mystery: https://mystery.knightlab.com/ https://mystery.knightlab.com/
- doronlinder 6y agoThis was certainly an inspiration.
- TallGuyShort 6y agoFor other reasons, reminded me of Space Quest: https://spacequest.fandom.com/wiki/Sequel_Police https://spacequest.fandom.com/wiki/Sequel_Police
- BadBadJellyBean 6y agoDidn't know about that. Now I am a great detective! That was fun!
- navaja 6y agoCatch 22 - I thought it's a crime to not-know SQL?
- deleted 6y ago[deleted]
- aquir 6y agoIt's great! I would buy a licence but I don't know how many cases do you have altogether?
- n2j3 6y agoSame. And on 50% discount.
- doronlinder 6y agoCurrently the first rank is 11 cases, and the second rank is 20 additional cases. The second rank covers WHERE with numbers, strings, dates and timestamps, IN, BETWEEN, AND and OR. Check out the guide tab in the game - it covers everything supported so far. Once you finish that, you go into practice mode where you're randomly asked the same kind of cases like the previous ones. I plan on adding support for GROUP BY, aggregate functions, HAVING, aliases, JOINING multiple tables, and some more stuff with more complex cases to match. And the 6 months countdown doesn't begin until the game development ends so you're enjoying longer access.
- faitswulff 6y agoI think it would be more compelling to see how much more material there is, or at least how much is planned, like a timeline or map. I'm a little hesitant to purchase it blind.
- mrageh 6y agoThat would convince me to pay for this
- zicon35 6y agoWow. I lolled.
- iagovar 6y agoAmazing
- mason55 6y agoWorth considering capturing the keyboard input. I instinctively hit "delete" partway through to delete something I had clicked on and it took me back to HN and I lost my progress.
- doronlinder 6y agoGood call. I will.
- doronlinder 6y agoDeployed a fix. Backspace is captured now.
- logicallee 6y agodo you ever actually use backspace to go back a page? If not, turn it off in your browser settings, since you'll only ever use it accidentally anyway. (And what's more, you'll only ever use it accidentally while you're trying to edit text you've input.) to me backspace going back a page = a sense of frustration. better to turn it off in the browser, so I did so.
- mason55 6y agoGood call. I use ⌘-← all the time to go back but never backspace
- taylorcooney 6y agoVery cool @doronlinder
- koolba 6y agoAre the names and email addresses in the sample data real? They seem auto generated but the domains are real providers like Hotmail, Gmail, and Outlook.
- doronlinder 6y agoEverything is randomised, nothing is based real data. It's auto generated from popular first names, last names, changing patterns and random years or numbers attached at the end. Domains are real, but randomised as well.
- as1mov 6y agoIt's kinda risky in my opinion. I just checked a few of the addresses (mostly the one's without any numbers at the end), some of them do actually belong to real people.
- henryfjordan 6y agoWhat's the risk? Your email address isn't exactly a secret...
- as1mov 6y agoWell it's not exactly a risk, but people might get miffed their email is part of some website without their permission. Also, in a rather grim coincidence, one of the emails I googled belonged to a person who had died in a car crash few years ago.
- doronlinder 6y agoI was aiming for a feeling of dealing with real data (even though it's not). Changing the email domain names to fictitious ones will solve this, but I think would undermine the feeling of real-ness.
- Drip33 6y agoI searched a few emails in some local copies of data breaches and found real results
- billatberlocks 6y agoFun idea :)
- Psyladine 6y ago>"A mailing list of an illegal online service was sent to the SQLPD hot-line. Please submit all records email addresses' details. Please make sure there are no duplicates." typo
- doronlinder 6y agoI'm missing it. Where's the typo?
- fool_wolf 6y agorecords is possessive and should have the apostrophe, addresses is not possessive and should not. This class of typo is pretty common throughout the briefs I have looked at.
- ideophobia 6y agoI literally can't get past the first level and I have no idea why.
- abledon 6y agoclick on GUI. click SELECT click * click FROM click mailing_list click run
- ideophobia 6y agoI had one before that, asking about all member data from a darknet list. It seemed logical that select * from members would be the answer based, but I couldn't get it to accept no matter how many times I tried. Kept saying failed to fetch after run. I closed and reopened the browser, tried again, and it worked on the first attempt.
- deleted 6y ago[deleted]
- code_duck 6y agoClick SELECT, then click each field, then FROM subscribers, then the play button. Seems almost too easy.
- deleted 6y ago[deleted]
- oefrha 6y agoNice concept but I really hate clicking on the buttons to form my statements... Is it possible to allow typing? Also, not ending my statement with a semicolon feels incomplete.
- greggyb 6y agoI agree. I work in Microsoft's SQL Server (incl. variations, e.g. Azure SQL DW) quite a bit. Based on my experience, I am the only person using semicolons in T-SQL.
- throwaway_pdp09 6y agoI always try to use them. It's going to be mandatory says MS: "Although the semicolon isn't required for most statements in this version of SQL Server, it will be required in a future version."
- greggyb 6y agoThat has been the guidance for a loooooong time.
- sonofaplum 6y agoI agree with this. I would much prefer if there was a freeform query mode, where I could just type the queries without the buttons or hints.
- code_duck 6y agoI was trying to determine how to use a comma.
- doronlinder 6y agoYeah, the semicolon bothers me too. I'll see how I can incorporate that in. The clicking around was meant to make life easier on mobile phone users. I'll see if I can add a keyboard support without undermining this.
- 6y ago
- PUSH_AX 6y agoPerhaps I'm missing something but I can't seem to find what I get for my money, what concepts and commands are covered? How many "cases" are there?
- doronlinder 6y agoCurrently the first rank is 11 cases, and the second rank is 20 additional cases. The second rank covers WHERE with numbers, strings, dates and timestamps, IN, BETWEEN, AND and OR. Check out the guide tab in the game - it covers everything supported so far. Once you finish that, you go into practice mode where you're randomly asked the same kind of cases like the previous ones. I plan on adding support for GROUP BY, aggregate functions, HAVING, aliases, JOINING multiple tables, and some more stuff with more complex cases to match. And the 6 months countdown doesn't begin until the game development ends so you're enjoying longer access.
- simlan 6y agoI love it that was a fun past time ;)
- MattRix 6y agoThis is a cool idea but if you accidentally hit the back button or refresh it loses all your progress with no obvious way to skip ahead. Frustrating.
- doronlinder 6y agoIt can be solved with saving stuff to local storage, but I tried to not use it to be more GDPR compliant...
- singlow 6y agoIsn't local storage pretty good for compliance? It doesn't automatically send the data to your server like a cookie would, so as long as you don't send it, you haven't collected it.
- mdaniel 6y agoI have a hard time imagining a GDPR complaint in which storing ones own data on their computer would constitute a privacy violation
- rndgermandude 6y agoAs long as you do not transmit the data ever, using localStorage isn't a GDPR concern other than that you should mention it (and the in-browser-only use, with data never leaving the users' systems) on your privacy policy page for the sake of transparency and to avoid users asking about it.
- franciscop 6y agoThis looks great! It was difficult to find out that you had to click the Play button for me "▷". Since this is a primary action you might want to consider adding a stronger Call to Action there?
- doronlinder 6y agoThanks for the feedback! I was wondering the same thing.
- murgindrag 6y agoThis seems like a neat idea. Not sure about paid, though, in its current incarnation. There's a ton of similar things unpaid. Why would I pay for this over Khan Academy's excellent SQL course, for example? It seems like a piece of a business, not a business in itself. To be successful as a product or solution, there needs to be more around it -- some way to integrate it into contexts, have it used in schools, or in afterschool, or something. Another key component would be user studies -- develop PCK about what supports and help students need.
- MivLives 6y agoCool concept but the license seems kind of weird to me. 20 bucks for six months? Will anything change in that time? You might want to add a bit of info on what the license gets you.
- doronlinder 6y agoThanks! The game is still in development. It covers SELECT, DISTINCT, FROM (with one table), ORDER BY, LIMIT in the free part. After buying a license, WHERE, IN, BETWEEN, AND and OR are also covered. Check out the guide tab in the game - it covers everything supported so far. I plan on adding support for GROUP BY, aggregate functions, HAVING, aliases, JOINING multiple tables, and some more stuff with more complex cases to match. The 6 months countdown doesn't begin until the game development ends so you're enjoying longer access.
- gremlinsinc 6y agoCool game, but I think pricing maybe a value issue. Might make more $$ if you just had a donation button, and some ads and maybe an option to upgrade to remove ads/donation nags. Plus you'd get a lot more word of mouth, and maybe add some social sharing of 'accomplishments' etc...
- mimimi31 6y agoSome of the instructions are worded weirdly, almost like they were auto-generated or mostly copy-pasted.
- doronlinder 6y agoYes, the instructions as well as the levels and the data are auto generated. If something sounds off, please email me and I'll fix it.
- code_duck 6y agoMinor, and doesn't seem worth emailing about. I got to the 4th one and 'seized' is spelled 'siezed'.
- jonny_eh 6y agoThe question where it asks for the list of unique download counts makes no sense.
- doronlinder 6y agoThe cases are randomly generated; It asks for unique values of a random column in a random table. Sometimes it asks for unique password hashes... It's an edge case I didn't account for.
- jonny_eh 6y agoInteresting, why not hand pick the questions?
- doronlinder 6y agoFor replay-ability. Each time you need to understand the problem and construct a query instead of remembering that the answer was X (when I prototyped this in some of the cases you needed to pick the right answer from a cell in the table, not submit the whole thing).
- knight17 6y agoHere are some other SQL Learning resources I have collected in my note taking TiddlyWiki (mostly from HN itself). Interactive SQL Tutorials : ▪ Chartio Interactive PostgreSQL SQL Tutorial - https://chartio.com/learn/sql/ https://chartio.com/learn/sql/ ▪ Select STAR SQL - https://selectstarsql.com/ https://selectstarsql.com/ ▪ SQL Bolt - Learn SQL with simple, interactive exercises - https://sqlbolt.com/ https://sqlbolt.com/ ▪ SQL Window functions - https://www.windowfunctions.com/ https://www.windowfunctions.com/ ▪ PostgreSQL Exercises - https://pgexercises.com/ https://pgexercises.com/ ▪ SQL Zoo - http://sqlzoo.net/ http://sqlzoo.net/ ▪ SQL Teaching - https://www.sqlteaching.com/ https://www.sqlteaching.com/ ▪ ByteScout SQL Trainer - Use live data to train - https://app.bytescout.com/sql-trainer/index.html https://app.bytescout.com/sql-trainer/index.html ▪ Schemaverse - A space-based strategy game implemented entirely within a PostgreSQL database - https://schemaverse.com/ https://schemaverse.com/ ▪ Oracle Live SQL - Lerarn and share SQL - https://livesql.oracle.com/ https://livesql.oracle.com/ ▪ GalaXQL 3.0 - an intractive tutorial with exploring the galaxy - http://sol.gfxile.net/g3/ http://sol.gfxile.net/g3/ ▪ SQL Exercises - http://sql-ex.ru/index.php http://sql-ex.ru/index.php
- antman 6y agoGreat collection thanks! Has anyone used them or have any comment on the difficulty of the sources above?
- jmhwang7 6y agoI've used sqlbolt.com! It's great. Very easy to follow and it's interactive. They have a resource for regex: https://regexone.com/ https://regexone.com/, which I've gone through as well!
- hultner 6y agoCool, must check out these
- digitallogic 6y agoPretty cool! One piece of feedback: > An illegal site's servers were siezed in a recent operation. Please submit all users number of posts' details. Please make sure there are no duplicates. There's no declaration of what attribute should be used for considering distinct users. After guessing it gave a hint to the effect of "there are duplicate Given Name values". This is rather counter intuitive since many people share a given name.
- diabeetusman 6y agoI can only solve the first one. I click submit, get a "Solved" stamp, and it prompts me with the same problem again.
- doronlinder 6y agoThe first two are cases of selecting the whole table. Since it's (completely) randomised, there is a chance of getting the same table twice. I didn't account for that :-P If you solve it again, you'll get a new case following that.
- mrfusion 6y agoI’d love to see fun stuff like this for all kinds of technical skills. Even pytorch for example. Actually why couldn’t an entire online class be working towards solving some kind of mystery. You wouldn’t even notice you’re building skills.
- geddy 6y agoReally nice work. I had a lot of fun in that. Others have already mentioned the Backspace = page back issue where you could easily lose progress, but I played all the way through :) No need to purchase however as I already know SQL quite well. But really well done.
- Omnipresent 6y agoIs this for total beginners or does it also cover advanced features like window functions as well? If not, is there a similar tool for learning advanced SQL features?
- psYchotic 6y agoI personally enjoyed https://pgexercises.com/ https://pgexercises.com/ a lot. As much as I'm not an SQL expert by any means, I learned things most of my peers would find "too complex" while doing those exercises. The site is interactive, but you can also download the dataset and do the exercises on your own database, if you so desire.
- leke 6y agoIf only this was real life.
- dayandtime 6y agoExcellent. Great fun.
- pknerd 6y agoGood idea. It could be replicated for languages as well.
- brianzelip 6y agoI like the interface, but why throw an error when a user has their mobile device in horizontal orientation?
- mulmen 6y agoThis is just like the real world because with the first example I have no idea what the deliverable should be or what data is available. It's almost too immersive.
- mulmen 6y agoSELECT Email FROM mailing_list ORDER BY JoinDate SQL Error: There is no field named JoinDate in the resulting FROM table. Ok? What engine is this using? That's valid SQL in my universe. In PostgreSQL 12: mulmen@shell> psql psql (12.1) Type "help" for help. mulmen=# CREATE TABLE foo ( bar INT, baz INT ); CREATE TABLE mulmen=# INSERT INTO foo VALUES (1, 10), (2, 20); INSERT 0 2 mulmen=# SELECT bar FROM foo ORDER BY baz; bar ----- 1 2 (2 rows) mulmen=#
- rpunkfu 6y agoSyntax is fine, it's complaining because you didn't include `JoinDate` column in your query.
- mulmen 6y agoThis appears to be a syntax error. I get a different error if I add JoinDate to the SELECT clause without adding the other columns it wants.
- doronlinder 6y agoThank you for the feedback. The SQL parsing is home made and I assumed fields in the order by clause is also a part of the SELECT. I collect a window of failed queries in memory (without knowing who sent what) to find cases like this and fix them.
- venperspy 6y agoLOVED THIS! (newbie sql learner from datacamp)
- bitwize 6y ago(in Space Quest narrator voice) Oh, no! Sequel Police!
- brozaman 6y agoIt's a neat concept. I think the experience would be way better if the the animation after submitting answer would be made way faster. I checked the network tab in firefox 75 on fedora and the post was instantaneous, about 40 ms to reply (120 if included TLS negotiation). However there is after that an animation that takes a long time and plays a sound.