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.
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.
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).
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.
All the blue boxes are actually interactive code editors. You can run arbitrary queries against the dataset in them. I changed some of the wording to make it clearer. Hopefully that helps!
One option is to learn on bigquery (a google cloud service) - its "pre-loaded" with all kinds of public datasets that you can just start querying, with a web interface for doing sql.
Its pretty easy to stay within the free tier (1TB of data per month)
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.
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.
Perl. And no, that's not just a factor of its current popularity, as the language has evolved and has regular releases.
Perl has amazing backwards compatibility, but has also solidified on much better coding practices and libraries in the last 20 years. You can still run that code from 20 years ago with a new interpreter and it will be safer and faster, or you can choose a more modern style or library and it will work just as well.
And to forestall anyone trying to bring up Perl 6 as a counterargument, unless you actually know what it is and how it interacts with Perl 5 and the community, I doubt the argument is actually saying what you think...
Sure, and I still use Perl as my primary language, just as I did 20 years ago, with some forays into other languages (like Python and C) along the way. But, this is survivor bias (one could argue SQL is also benefiting from survivor bias, though SQL was the dominant data query language then and it is the dominant data query language today, whereas one could argue only C was/is dominant in its space at both points in history). But, where's Pascal, Visual Basic (or Basic of any sort), Tcl, etc.? There's dozens of other languages that came and went, including data-oriented languages. All of which were popular during the 90s, and effectively gone now. And, only C among your examples is actually exhibiting the same kind of longevity (SQL is more than 40 years old, so it predates Python, Java, Perl, etc. by quite a lot)...I probably should have put the starting point for this thought experiment at 30+ years ago, but 20 years ago is when I first started using SQL, so it's what I used, which isn't really useful. Who cares when I learned the value of SQL, when it's an industry-wide phenomenon?
My point wasn't that one can't name languages with staying power. There are several. But, SQL is still special, I think. It's almost universally needed. If you go to work in a Node or Go shop today, you're probably still gonna need to know some SQL, just like if you went to work in a Python or Perl or Java shop in the 90s.
To your point. I learned SQL almost 30 years ago. Along Unix, vi, Perl it is a skill that I still use often. It is one of the best learning investments that I ever made.
>e.g. name another programming language that is as valuable today as it was 20 years ago, if not moreso
If we include non-Turing-complete languages as “programming languages”, which we must for SQL to get that 20-year label since SQL wasn't Turing-complete until recursive CTEs were part of the language, then XML.
Also, even without that caveat, candidates include Bash, C, C++, Objective-C, Perl, elisp, Java, JavaScript, MATLAB, VHDL, and others. Some, while still being as useful as they were 20 years ago, are down from a peak in the intervening period, but arguably that's true of SQL, as well.
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).
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.
I teach SQL to financial people and, for the most part, it isn't too bad. When I get to joins, I teach a subset of SQL where all joins are done through INNER JOIN / LEFT JOIN syntax and that helps a lot. Joins are the part that everyone has trouble with.
Urban Dictionary (https://www.urbandictionary.com) or even just typing something like "what does tbh mean?" into your preferred search engine will often help you find likely interpretations of unknown abbreviations, in my experience.
In Postgresql, Lateral Joins enable function chaining for set returning functions. ie you can feed the results of earlier srf function to the arguments of following function.
This gets around one of Postgresql most glaring omissions - table valued parameters. Is incredibly useful once you get used to them.
I might be misunderstanding your point, but Postgresql functions can take (and return) tables:
CREATE FUNCTION fullname(person) RETURNS TEXT AS $$
SELECT $1.firstname || ' ' || $1.lastname
$$ LANGUAGE SQL
Could you go into a more detail wrt using lateral joins with set returning functions? I'm used to using them as a way to "extend" the row with later terms depending on prior terms as you say, but I'm not comprehending the srf case so well (or tbh the results that pop up when I search for "posrgresql set returning function").
In my experience it’s relatively rare that you need them. I think of them as the for-each of joins. It evaluates row by row, not a single time. Another analogy would be a correlated subquery.
> Joins are the part that everyone has trouble with.
There is significant problem-solution mismatch in joins and some other SQL constructs which are therefore semantically quite controversial in many use cases:
Yes,I also suggest standards committee allow the option of putting FROM clause first - this would make it easier for beginners to grok what’s going on and enable better tooling such as auto-complete.
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.
> Once you type the column names, it can guess the table name easily.
If the query is simple enough that a natural join would work and you aren't doing FROM clause aliasing (which can be useful to make reusable queries self-documenting), sure.
For more complex queries and obviously any time table or column aliasing are used, that becomes somewhere between less likely and logically impossible.
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.
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.
I'd rather have the enjoyment of being able to think in normal English speak rather than Yoda speak just to fix one edge case that appeared decades after they came up with the syntax.
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.
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.
I was confused too. It appears the page does not work on Firefox. I get a "customElements is not defined" error in the console in Firefox and none of the interactive sections render.
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/...). 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.
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.
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.
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.
Imagine people in low paying jobs who dont have the means to pay - there should be stellar free resources like this for them to grasp things with a solid enough foundation to get onto more complex projects.
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!
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...
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.
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.
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.
120 comments
[ 3.1 ms ] story [ 161 ms ] threadAre 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.
I don't know that I'd use "funky" or "quirky" to describe it though. Instead it comes across as clear, concise and approachable.
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).
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.
The code is open source, anyone is welcome to fork and populate with new data like this.
The <sql-exercise data-question...> elements aren't visible in Firefox at all.
Its pretty easy to stay within the free tier (1TB of data per month)
* C
* Python
* Java
20 years ago was 1998.
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.
For C, its just library management is troublesome enough that you end up writing your own ;)
Perl has amazing backwards compatibility, but has also solidified on much better coding practices and libraries in the last 20 years. You can still run that code from 20 years ago with a new interpreter and it will be safer and faster, or you can choose a more modern style or library and it will work just as well.
And to forestall anyone trying to bring up Perl 6 as a counterargument, unless you actually know what it is and how it interacts with Perl 5 and the community, I doubt the argument is actually saying what you think...
My point wasn't that one can't name languages with staying power. There are several. But, SQL is still special, I think. It's almost universally needed. If you go to work in a Node or Go shop today, you're probably still gonna need to know some SQL, just like if you went to work in a Python or Perl or Java shop in the 90s.
If we include non-Turing-complete languages as “programming languages”, which we must for SQL to get that 20-year label since SQL wasn't Turing-complete until recursive CTEs were part of the language, then XML.
Also, even without that caveat, candidates include Bash, C, C++, Objective-C, Perl, elisp, Java, JavaScript, MATLAB, VHDL, and others. Some, while still being as useful as they were 20 years ago, are down from a peak in the intervening period, but arguably that's true of SQL, as well.
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.
Ofc that might just be the emphasis on joins makes group by the next lowest hanging fruit
Urban Dictionary (https://www.urbandictionary.com) or even just typing something like "what does tbh mean?" into your preferred search engine will often help you find likely interpretations of unknown abbreviations, in my experience.
I mean, that's what they did.
There is significant problem-solution mismatch in joins and some other SQL constructs which are therefore semantically quite controversial in many use cases:
https://github.com/asavinov/bistro#why-column-orientation
But for my taste the language is to close to English and therefore redundant and inconsistent in an annoying way. Example:
Select x from tbl ...
Delete from tbl ...
Update tbl set x=...
Why change the word order?
Codd wrote a couple of query languages before, but they apparently weren't usable by mere mortals.
SQL is over 40 years old, and still dominates. The most recent contenders had to position themselves in terms of SQL (NoSQL).
See LINQ. There they had the opportunity to fix it and it works just great.
I use DataGrip at work and it can complete the column names without knowing the table name, so it ends up not being a problem.
If the query is simple enough that a natural join would work and you aren't doing FROM clause aliasing (which can be useful to make reusable queries self-documenting), sure.
For more complex queries and obviously any time table or column aliasing are used, that becomes somewhere between less likely and logically impossible.
However,
is syntactically equivalent to Python list comprehension: Here the use of attributes (SELECT) is also written before the iterator.SELECT is also analogous to normal loops:
Here we first provide the loop specification while the usage of elements is written only in the body.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.
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.
So, I will start with a SELECT * FROM myTable t, then go back and replace * with t.<columns appear here>.
I'll use the same in other places in the query like WHERE conditions.
I also use http://www.mysqltutorial.org and https://www.w3schools.com/sql/
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.
Scroll down to Dataset.
After "I’ve prepared for this book:"
There is a little interactive code editor you can type in and run the query.
The page works fine on Chrome.
edit: the site will now warn you if your browser is incompatible.
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.
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.
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.
Free is great.
• 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...
"Now you have the tools you need to complete our project."
Where is this quiz and this project? Am I missing something?
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...
Do let me know if there's any collaboration we could do!
>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...
https://github.com/zichongkao/selectstarsql
here is the Github repository in case anyone wants to send PRs or start the project.
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.