
Show HN: Select Star SQL, an interactive SQL book - zckao
http://selectstarsql.com
======
dm8
This is good intiative. We need to simplify pedagogy.

On a related note, I always found the syntax of SQL to be great. Someone told
me that simplicity of SQL made relational databases even more popular. It is
one of the best designed language where even someone new to the field won't
feel intimidated. I'm surprised designers of SQL haven't won the Turing Award
yet.

~~~
m_st
I agree except one thing: SELECT cols FROM table. This should really be FROM
table SELECT cols as every modern SQL editor will then be able to show you a
list of available columns.

See LINQ. There they had the opportunity to fix it and it works just great.

~~~
piaste
More generally, statements should appear in the same order as they are
executed, as far as this is possible.

This nullifies some other common sources of confusion, such as which
statements are executed before a GROUP BY and which ones after (and why HAVING
exists as a keyword). It's also similar to how CTE syntax is generally much
more readable than subqueries.

~~~
hesk
The order of clauses in a SQL statement should have no relation on the order
of execution because SQL is a declarative query language. The query specifies
what the user wants and not how the system should compute the answer.

This declarative property also provides a (somewhat weak) motivation of why
SELECT should come first. Precisely because it answers part of the WHAT, i.e.,
the schema of the result.

------
SwellJoe
This is gorgeous, and a great topic. No matter what field you're working in,
understanding databases is extraordinarily valuable...and SQL is among the
longest lasting techs I've ever learned (e.g. name another programming
language that is as valuable today as it was 20 years ago, if not moreso).
Even with the rise of NoSQL databases, I suspect we'll all still need to know
a little SQL in another 20 years (and it was already more than 20 years old
when I first encountered it in the 90s). And, even if not the same language,
conceptually understanding how to query data is probably timeless.

~~~
Hendrikto
> name another programming language that is as valuable today as it was 20
> years ago, if not moreso

* C

* Python

* Java

20 years ago was 1998.

~~~
Cyph0n
C I agree with.

But Java and Python? Both languages have changed beyond recognition over the
past 20 years. A programmer who hasn't touched either for 20 years would
probably be overwhelmed.

Another difference is that even though SQL has changed, there isn't much you
need to know outside of the DBMS you are using. With Java and Python on the
other hand, you need to keep up with the changing library landscape to stay
relevant.

~~~
setr
>With Java and Python on the other hand, you need to keep up with the changing
library landscape to stay relevant.

For C, its just library management is troublesome enough that you end up
writing your own ;)

------
djrobstep
The choice of example database (the death penalty) is terrific.

So much more interesting, important and compelling than the usual tired
customer/product examples.

I'm tired of seeing the implicit assumption around the place that software is
just about business. Software should be about more than that.

~~~
lixtra
I'm not the guy for trigger warnings but I find the choice of data
unfortunate. There is a big percentage of people I won't recommend the page
because of the dataset.

~~~
nnm
same here.

------
m_st
Congratulations! I love this kind of interactive tutorials as their helping
more and more people learning stuff without a boring book and no exercises.

That said: Doesn't work in Firefox and Edge? Common, are we back in 1998?
Sorry for the rant, but please don't do that.

~~~
enz
It works pretty fine on FF. However, you have to enable
`dom.webcomponents.customelements.enabled` manually...

------
thingsilearned
Awesome work Kao!! Earlier this year I launched a similar interactive SQL
tutorial with similar goals.

[https://chartio.com/learn/sql/](https://chartio.com/learn/sql/)

With the writeup on why i made it here: [https://medium.com/@__dave/why-i-
wrote-yet-another-sql-tutor...](https://medium.com/@__dave/why-i-wrote-yet-
another-sql-tutorial-c852eeb3d95d)

Do let me know if there's any collaboration we could do!

~~~
zckao
Thanks Dave! I share many of the sentiments in your writeup, especially about
existing tutorials. Admittedly, I have been impressed by the quality of a few
of the tutorials that others have suggested here. Will let you know if I think
of anything!

------
NPMaxwell
Sorry for being dense, or maybe there are pop ups that I'm not seeing? I'm
missing how is this "interactive". Sorry

~~~
zckao
Slightly embarrassing -- but the problem may be that the site isn't compatible
with FireFox (at least pre-v63 FireFox). The site makes use of custom HTML
elements which are not supported by default in pre-v63 FireFox
([https://developer.mozilla.org/en-
US/docs/Web/Web_Components/...](https://developer.mozilla.org/en-
US/docs/Web/Web_Components/Using_custom_elements)). The good news is that all
you need to do is to turn on a flag as set forth in the link provided. The bad
news is that many other less tech-savvy people are going to be stymied by
this.

edit: the site will now warn you if your browser is incompatible.

~~~
blencdr
It's 2018 and we're back in 1998 where website proudly showed "Optimized for
Internet Explorer".

~~~
favadi
In 1998 sites might looks bad but at least user can still read the content. In
2018, some sites just refuses to work for anything but Google Chrome.

~~~
jrockway
In 1998 you had to be running Windows. Now you can run any OS, even if the
browser you have to use for a certain site isn't your favorite. It's an
improvement.

People have always wanted to use "the web" as an application platform. It
didn't work that well in 1998, but it's pretty good now. Progress has been
made.

------
TheForumTroll
Building a site for Google Chrome is like building for IE ActiveX back in the
day. I'm surprised this isn't the main topic discussed here.If it doesn't work
in Firefox, it isn't worth supporting. We need an open web.

~~~
jyriand
It supports Firefox, you just have to enable a component from your
about:config.

------
duck
Nice job, this is a great way to teach something like SQL. Any plans to add
more chapters? Would love to see something around window functions AND CTEs as
this format would go a long way in showing when and how to use them.

~~~
theodorton
Did you have a look at
[https://www.windowfunctions.com](https://www.windowfunctions.com) ? Their
exercises were a great introduction for me.

I also discovered the ability to select from multiple tables and do joins
using where clauses instead, which to me is a lot more intuitive than
explicit/imperative join statements. I didn’t see anything covering queries
like those (maybe because there’s only one table in the dataset, idk i’m on
mobile).

------
cryptonector
The O'Reilly "SQL Pocket Guide" is very well written, and, IMO, one of the
best tools for learning SQL. It's extremely concise, and yet packed chock full
of information. It's missing some things, sure, but once you have a strong
foundation, the rest you can get from RDBMS docs. The fact that this is such a
short book, and not a reference guide but an actual guide to using SQL, makes
this book approachable and unintimidating. I highly recommend it.

------
amadeuspagel
See [https://sqlbolt.com/](https://sqlbolt.com/) for an interactive SQL
tutorial with less distracting examples.

~~~
HeavyStorm
This commentary could have been constructive

------
jajool
thanks a lot. it's an awesome project. in the introduction it was stated that
this project is using Github pages for hosting but i couldn't find the
repository address anywhere.

[https://github.com/zichongkao/selectstarsql](https://github.com/zichongkao/selectstarsql)

here is the Github repository in case anyone wants to send PRs or start the
project.

------
samstave
One suggestion to make things even more clear than they already are with this
paragraph:

> __ _The SQL query may look like an ordinary sentence, but you should view it
> as three Lego blocks: SELECT_ FROM executions LIMIT 3. As with Lego, each
> block has a fixed format and the different blocks have to fit together in
> particular ways. __*

Explain what SQL is.

\---

One example for this could be:

SQL means ___Structured Query Language_ __, and with SQL we can build __
_(structured)_ __questions __ _(query)_ __using a syntax __ _(language)_ __.
To find what we want in the data, much like we build structures with various
Lego bricks, we can use the following statement:

SELECT * FROM executions LIMIT 3

The SQL query may look like an ordinary sentence, but viewed as three Lego
blocks, each block has a fixed format and the different blocks have to fit
together in particular ways.

\---

Just thinking of how to distill it even further...

------
morganwilde
It’s a lovely product and you can tell a lot of work went into making this
course. But I fail to understand the author’s logic behind “a good course has
to be free”. Why though? What’s wrong with creating value (like The OP) and
then extracting it from the people that benefit from it?

In fact, there’s tons of anecdotal evidence that free MOOCS haven’t faired
well in terms of retention precisely because of a lack of a buy-in from the
people taking those courses.

~~~
svat
See this, from one of the authors of one of the best undergraduate textbooks
on operating systems:

• _The Case for Free Online Books (FOBs): Experiences with "Operating Systems:
Three Easy Pieces"_ by Remzi Arpaci-Dusseau, [http://from-a-to-
remzi.blogspot.com/2014/01/the-case-for-fre...](http://from-a-to-
remzi.blogspot.com/2014/01/the-case-for-free-online-books-fobs.html)

------
ryanj20021
Outstanding work. SQL was the gateway drug to coding for me and I wish this
was around. SQL is my top recommendation for people looking to become more
technical, and this will likely be what I send them first.

------
chris_wot
The dataset is sobering. Good on you for using this.

------
cristoperb
I just finished working through it. It is well written with an interesting if
macabre example dataset. The interactive bits worked well once I enabled web
components in Firefox.

------
jannyfer
I find the writing style interesting, but I'm not like others at my work who
could benefit from learning SQL (business managers at a bank back office who
rely mostly on Excel, then get stuck when Excel crashes with large datasets).

Are these funky, quirky books generally well-received outside the programming
community? I quite liked Why's Poignant Guide to Ruby, for example, but I
would never recommend it to anyone at work.

~~~
maroonblazer
As a hobbyist programmer I absolutely love this style of instruction.

I don't know that I'd use "funky" or "quirky" to describe it though. Instead
it comes across as clear, concise and approachable.

------
S_A_P
I really like this. Bookmarked. I have spotted a few bugs that you may want to
check into.

Search the page for the word “llanguage”. The sum/count checker told me it was
incorrect for using lowercase nulls. I normally write all my sql lowercase for
brevity, but I also mostly use sql server.

I really like what you have done here and I hesitate to report it in your show
hn thread because this really is great.

~~~
zckao
Thanks so much for the feedback! Both the typo and the validity-checking bug
have been fixed.

------
dawie
I really like your explanations. I have never been able to wrap my head around
joins.

You say that: "Programming is best learned by doing", but I am struggling to
do anything...

It would be great if there was some online area like SQL Fiddle with the data
loaded, so I can run queries and see results in my browser without having to
set anything up or am I missing something.

~~~
AlisdairO
(self plug) pgexercises.com provides something like this.

The code is open source, anyone is welcome to fork and populate with new data
like this.

~~~
pixelperfect
I learned SQL mostly by using your site, thank you for making it!

~~~
dwdz
Me too! Thanks Alisdair!

~~~
AlisdairO
pleasure, glad you both found it useful :-)

------
zitterbewegung
I have picked up SQL and statements from my time in industry. I really like
how this book provides not only interaction but a dataset that actually means
something. Also, interactive systems like this for teaching are amazing! I
learned about how "_" is a wildcard for one character (I have been making do
with % for LIKE statements).

~~~
krylon
I only recently learned that LIKE also supports something vaguely similar to
regex character classes:

    
    
        SELECT * FROM customers
        WHERE company_name LIKE 'foo[lt]'
    

will only return customers whose company_name is 'fool' or 'foot', while

    
    
        SELECT * FROM customers
        WHERE zipcode LIKE '12[^30]45'
    

will return customers whose zipcode is LIKE '12_45', except '12345' and
'12045'.

------
rootlocus
Why did you use death sentence cases as data? I've spent around 20 minutes
just reading the last words of various executed people. It's... disturbing and
distracting.

~~~
baby
It's deep and moving, it makes this course more than just a course. I just
fetched a few rows and read Beazley's last statement and I now have a great
respect for the creator of this course.

~~~
stbn
I strongly agree. Rarely do I see such a humanistic approach in tech. It
brings an important matter to our attention and is meaningful. As a side note,
what is meaningful is pedagogically valuable, because what has meaning is
easier to remember.

------
acangiano
This might sound like an odd statement, but I can tell from reading part of
it, that it was written by a beautiful person.

------
alx-ppv
Genuinely enjoyed the book. A couple of notes:

1\. In The Long Tail > Nested Queries, the first example contains the function
LEN, which gives an error as LENGTH is expected.

2\. On the questions with multiple answers, when showing hints, I would use
different font colors for correct/incorrect statements as it will be easier to
check your results.

Wonderful website!

------
orcs
As someone who has never really touched SQL except very briefly during a
degree module, which basically taught me nothing, I enjoyed this. I think it
taught me some good stuff. I will need to go back through it as I didn't pick
it all up.

That said, as a novice, the challenge questions are not in anyway suitable for
a beginner. I wouldn't, and didn't, have any idea how to do them; or know
where to look to find a starting point. The previous chapters do not prepare
you for the challenges and I came away thinking I was useless until I click
the solutions and realised there was no way I would realistically ever been
able to solve them without some form of further training in SQL.

------
rhc2104
Glad to see that the Creative Commons license is used!

I used the MIT license for the interactive SQL tutorial I built (
[https://www.sqlteaching.com/](https://www.sqlteaching.com/) )

------
buboard
Is there a trend towards pure SQL lately? Not that i m complaining (quite the
opposite but let me not jinx it), but it seems we might stop seeing a new ORM
each week for a while.

------
default-kramer
The book says this is a valid where clause:

> WHERE 0

> 1 and 0 are the most basic Boolean statements. This block guarantees that no
> rows will be returned.

This doesn't work in SQL Server and it has always bothered me. But is it part
of any SQL standard?

The book also says this is an invalid where clause:

> WHERE '%obert%' LIKE first_name

> More than one wildcard is fine. But the pattern has to come after the LIKE
> operator.

This one is probably a bug/mistake, but it's definitely not invalid in the SQL
language.

------
krylon
I can only imagine how much work this must have been (and not very accurately,
I think). I did not have time to look over it in great detail, but I like what
I have seen.

Thank you for creating and sharing this!

------
geomark
"Let’s take a quick quiz to cement your understanding."

"Now you have the tools you need to complete our project."

Where is this quiz and this project? Am I missing something?

------
ausjke
great site.

I also use [http://www.mysqltutorial.org](http://www.mysqltutorial.org) and
[https://www.w3schools.com/sql/](https://www.w3schools.com/sql/)

------
Agnosco
Reading the final words is really moving. So much suffering.

------
codeisawesome
I’m in love. I’d pay for this. Thank you.

------
shiyaam91
Nice work...

