[HN Gopher] Show HN: SQL Police Department - Learn SQL while sol...
___________________________________________________________________
Show HN: SQL Police Department - Learn SQL while solving crimes
Author : doronlinder
Score : 390 points
Date : 2020-05-04 11:36 UTC (11 hours ago)
(HTM) web link (sqlpd.com)
(TXT) w3m dump (sqlpd.com)
| mason55 wrote:
| Worth 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 wrote:
| Good call. I will.
| logicallee wrote:
| do 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.
| mimimi31 wrote:
| Some of the instructions are worded weirdly, almost like they
| were auto-generated or mostly copy-pasted.
| doronlinder wrote:
| Yes, 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 wrote:
| Minor, and doesn't seem worth emailing about. I got to the
| 4th one and 'seized' is spelled 'siezed'.
| knight17 wrote:
| Here 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/
|
| # Select STAR SQL - https://selectstarsql.com/
|
| # SQL Bolt - Learn SQL with simple, interactive exercises -
| https://sqlbolt.com/
|
| # SQL Window functions - https://www.windowfunctions.com/
|
| # PostgreSQL Exercises - https://pgexercises.com/
|
| # SQL Zoo - http://sqlzoo.net/
|
| # SQL Teaching - https://www.sqlteaching.com/
|
| # ByteScout SQL Trainer - Use live data to train -
| https://app.bytescout.com/sql-trainer/index.html
|
| # Schemaverse - A space-based strategy game implemented entirely
| within a PostgreSQL database - https://schemaverse.com/
|
| # Oracle Live SQL - Lerarn and share SQL -
| https://livesql.oracle.com/
|
| # GalaXQL 3.0 - an intractive tutorial with exploring the galaxy
| - http://sol.gfxile.net/g3/
|
| # SQL Exercises - http://sql-ex.ru/index.php
| antman wrote:
| Great collection thanks! Has anyone used them or have any
| comment on the difficulty of the sources above?
| jmhwang7 wrote:
| I've used sqlbolt.com! It's great. Very easy to follow and
| it's interactive. They have a resource for regex:
| https://regexone.com/, which I've gone through as well!
| hultner wrote:
| Cool, must check out these
| [deleted]
| MivLives wrote:
| Cool 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 wrote:
| Thanks! 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 wrote:
| Cool 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...
| oefrha wrote:
| Nice 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.
| code_duck wrote:
| I was trying to determine how to use a comma.
| doronlinder wrote:
| Yeah, 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.
| geddy wrote:
| This makes a lot of sense, actually. Definitely great for
| learning.
|
| You could do it the way Duolingo does it - on desktop, it
| allows for "Hard Mode", where it lets you type the responses
| freely. On mobile however, it doesn't let you do that (unless
| you're using the app).
| sonofaplum wrote:
| I 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.
| greggyb wrote:
| I 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 wrote:
| I 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 wrote:
| That has been the guidance for a loooooong time.
| aquir wrote:
| It's great! I would buy a licence but I don't know how many cases
| do you have altogether?
| doronlinder wrote:
| Currently 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.
| n2j3 wrote:
| Same. And on 50% discount.
| geddy wrote:
| Really 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.
| koolba wrote:
| Are 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 wrote:
| Everything 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 wrote:
| It'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 wrote:
| What's the risk? Your email address isn't exactly a
| secret...
| doronlinder wrote:
| I 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.
| code_duck wrote:
| I was wondering the same thing. As combos of common names and
| providers, it seems extremely likely that many of those email
| addresses are valid addresses belonging to real people.
| Drip33 wrote:
| I searched a few emails in some local copies of data breaches
| and found real results
| zicon35 wrote:
| Wow. I lolled.
| MattRix wrote:
| This 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 wrote:
| It can be solved with saving stuff to local storage, but I
| tried to not use it to be more GDPR compliant...
| rndgermandude wrote:
| As 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.
| mdaniel wrote:
| I have a hard time imagining a GDPR complaint in which
| storing ones own data on their computer would constitute a
| privacy violation
| singlow wrote:
| Isn'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.
| PUSH_AX wrote:
| Perhaps 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 wrote:
| Currently 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.
| digitallogic wrote:
| Pretty 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.
| navaja wrote:
| Catch 22 - I thought it's a crime to not-know SQL?
| diabeetusman wrote:
| I can only solve the first one. I click submit, get a "Solved"
| stamp, and it prompts me with the same problem again.
| doronlinder wrote:
| The 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.
| simlan wrote:
| I love it that was a fun past time ;)
| Psyladine wrote:
| >"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 wrote:
| I'm missing it. Where's the typo?
| ideophobia wrote:
| I literally can't get past the first level and I have no idea
| why.
| code_duck wrote:
| Click SELECT, then click each field, then FROM subscribers,
| then the play button. Seems almost too easy.
| abledon wrote:
| click on GUI.
|
| click SELECT
|
| click *
|
| click FROM
|
| click mailing_list
|
| click run
| ideophobia wrote:
| I 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]
| billatberlocks wrote:
| Fun idea :)
| [deleted]
| ryannevius wrote:
| This reminds me of the SQL Murder Mystery:
| https://mystery.knightlab.com/
| doronlinder wrote:
| This was certainly an inspiration.
| TallGuyShort wrote:
| For other reasons, reminded me of Space Quest:
| https://spacequest.fandom.com/wiki/Sequel_Police
| BadBadJellyBean wrote:
| Didn't know about that. Now I am a great detective! That was
| fun!
| jonny_eh wrote:
| The question where it asks for the list of unique download counts
| makes no sense.
| doronlinder wrote:
| The 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 wrote:
| Interesting, why not hand pick the questions?
| doronlinder wrote:
| For 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).
| taylorcooney wrote:
| Very cool @doronlinder
| franciscop wrote:
| This 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 wrote:
| Thanks for the feedback! I was wondering the same thing.
| mrfusion wrote:
| I'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.
| iagovar wrote:
| Amazing
| murgindrag wrote:
| This 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.
___________________________________________________________________
(page generated 2020-05-04 23:00 UTC)