Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Show HN: SQL Police Department – Learn SQL while solving crimes

sqlpd.com

Post reply on HN
Print view View original

81–90 of 91 posts

Re: Show HN: SQL Police Department – Learn SQL while solving crimes

#81

Post by rpunkfu »

mulmen wrote: post #80

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…

Syntax is fine, it's complaining because you didn't include `JoinDate` column in your query.

Re: Show HN: SQL Police Department – Learn SQL while solving crimes

#83

Post by mulmen »

rpunkfu wrote: post #81
mulmen wrote: post #80

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…

Syntax is fine, it's complaining because you didn't include `JoinDate` column in your query.

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.

Re: Show HN: SQL Police Department – Learn SQL while solving crimes

#84

Post by as1mov »

henryfjordan wrote: post #49
as1mov wrote: post #29

Earlier quoted context omitted.

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.

What's the risk? Your email address isn't exactly a secret...

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.

Re: Show HN: SQL Police Department – Learn SQL while solving crimes

#85

Post by doronlinder »

doronlinder wrote: post #14
mason55 wrote: post #8

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.

Good call. I will.

Deployed a fix. Backspace is captured now.

Re: Show HN: SQL Police Department – Learn SQL while solving crimes

#86

Post by doronlinder »

mulmen wrote: post #80

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…

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

Re: Show HN: SQL Police Department – Learn SQL while solving crimes

#87

Post by psYchotic »

Omnipresent wrote: post #73

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?

I personally enjoyed 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.

Re: Show HN: SQL Police Department – Learn SQL while solving crimes

#89

Post by 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.

Re: Show HN: SQL Police Department – Learn SQL while solving crimes

#90

Post by faitswulff »

doronlinder wrote: post #44
aquir wrote: post #5

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

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,…

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.
Post reply on HN