
Show HN: SQL Police Department – Learn SQL while solving crimes - doronlinder
https://sqlpd.com
======
mason55
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.

~~~
logicallee
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.

~~~
mason55
Good call. I use ⌘-← all the time to go back but never backspace

------
ryannevius
This reminds me of the SQL Murder Mystery:
[https://mystery.knightlab.com/](https://mystery.knightlab.com/)

~~~
doronlinder
This was certainly an inspiration.

------
mulmen
This 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.

------
oefrha
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.

~~~
doronlinder
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
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).

------
digitallogic
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.

------
brozaman
It'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.

------
PUSH_AX
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
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.

------
MattRix
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
It can be solved with saving stuff to local storage, but I tried to not use it
to be more GDPR compliant...

~~~
singlow
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.

------
mrfusion
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.

------
franciscop
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
Thanks for the feedback! I was wondering the same thing.

------
mulmen

      SELECT 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
Syntax is fine, it's complaining because you didn't include `JoinDate` column
in your query.

~~~
mulmen
This 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.

------
murgindrag
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.

------
aquir
It's great! I would buy a licence but I don't know how many cases do you have
altogether?

~~~
doronlinder
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.

~~~
faitswulff
I 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
That would convince me to pay for this

------
Omnipresent
Is 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
I 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.

------
brianzelip
I like the interface, but why throw an error when a user has their mobile
device in horizontal orientation?

------
mimimi31
Some of the instructions are worded weirdly, almost like they were auto-
generated or mostly copy-pasted.

~~~
doronlinder
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
Minor, and doesn't seem worth emailing about. I got to the 4th one and
'seized' is spelled 'siezed'.

------
geddy
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.

------
MivLives
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
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
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...

------
Psyladine
>"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
I'm missing it. Where's the typo?

~~~
fool_wolf
records 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.

------
koolba
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
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
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
What's the risk? Your email address isn't exactly a secret...

~~~
as1mov
Well 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.

------
diabeetusman
I can only solve the first one. I click submit, get a "Solved" stamp, and it
prompts me with the same problem again.

~~~
doronlinder
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.

------
jonny_eh
The question where it asks for the list of unique download counts makes no
sense.

~~~
doronlinder
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
Interesting, why not hand pick the questions?

~~~
doronlinder
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).

------
ideophobia
I literally can't get past the first level and I have no idea why.

~~~
abledon
click on GUI.

click SELECT

click *

click FROM

click mailing_list

click run

~~~
ideophobia
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.

------
pknerd
Good idea. It could be replicated for languages as well.

------
bitwize
(in Space Quest narrator voice) Oh, no! Sequel Police!

------
simlan
I love it that was a fun past time ;)

------
billatberlocks
Fun idea :)

------
leke
If only this was real life.

------
dayandtime
Excellent. Great fun.

------
venperspy
LOVED THIS! (newbie sql learner from datacamp)

------
iagovar
Amazing

------
taylorcooney
Very cool @doronlinder

------
zicon35
Wow. I lolled.

------
knight17
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/](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
Great collection thanks! Has anyone used them or have any comment on the
difficulty of the sources above?

~~~
jmhwang7
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/](https://regexone.com/), which I've gone through as
well!

