12 ms·
Show HN: Select Star SQL, an interactive SQL book
- jannyfer 8y agoI 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 8y agoAs 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.
- duck 8y agoNice 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 8y agoDid 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).
- dawie 8y agoI 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.
- geomark 8y agosqlzoo.net might be something you would like.
- AlisdairO 8y ago(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 8y agoI learned SQL mostly by using your site, thank you for making it!
- zckao 8y agoAll 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!
- paulrd 8y agoI was wondering what you were talking about. What blue boxes? Then I saw the problem. The boxes showed up in Chromium but not Firefox.
- bhrgunatha 8y agoSame here - the front matter page mentions web components - perhaps the support is different /disabled in Firefox? The <sql-exercise data-question...> elements aren't visible in Firefox at all.
- chris_wot 8y agoThe dataset is sobering. Good on you for using this.
- SwellJoe 8y agoThis 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 8y ago> 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 8y agoC 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 8y ago>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 ;)
- kbenson 8y agoPerl. 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...
- acangiano 8y agoThis might sound like an odd statement, but I can tell from reading part of it, that it was written by a beautiful person.
- zitterbewegung 8y agoI 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 8y agoI 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'.
- deleted 8y ago[deleted]
- dm8 8y agoThis 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.
- wvenable 8y agoI 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.
- setr 8y agoTbh I usually see teachers worried about joins but its the group by/having clauses that I always watched people struggle with (at least, writing it). Ofc that might just be the emphasis on joins makes group by the next lowest hanging fruit
- deleted 8y ago[deleted]
- ranit 8y agoWhat do your abbreviations mean?
- grzm 8y agotbh = to be honest, ofc = of course Urban Dictionary (https://www.urbandictionary.com 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.
- 8y ago
- ryanj20021 8y agoOutstanding 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.
- ausjke 8y agogreat site. I also use http://www.mysqltutorial.org http://www.mysqltutorial.org and https://www.w3schools.com/sql/ https://www.w3schools.com/sql/
- shiyaam91 8y agoNice work...
- S_A_P 8y agoI 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.
- NPMaxwell 8y agoSorry for being dense, or maybe there are pop ups that I'm not seeing? I'm missing how is this "interactive". Sorry
- duxup 8y agohttps://selectstarsql.com/frontmatter.html https://selectstarsql.com/frontmatter.html 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.
- guu 8y agoI 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. The page works fine on Chrome.
- duxup 8y agoYeah I wondered if it was something like that.
- ddgflorida 8y agoDoesn't work on Chrome for me - Version 67.0.3
- zckao 8y agoSlightly 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/Using_custom_elements 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.
- 8y ago
- djrobstep 8y agoThe 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 8y agoI'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 8y agosame here.
- Kagerjay 8y agoI like the death penalty database, it makes things so much more interesting and memorable. Data doesn't have to be boring
- morganwilde 8y agoIt’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.
- samstave 8y agoImagine 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. Free is great.
- svat 8y agoSee 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-free-online-books-fobs.html http://from-a-to-remzi.blogspot.com/2014/01/the-case-for-fre...
- GedByrne 8y agoIs it possible that value can be generated without a financial transaction?
- geomark 8y ago"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?
- codeisawesome 8y agoI’m in love. I’d pay for this. Thank you.
- thingsilearned 8y agoAwesome 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-tutorial-c852eeb3d95d https://medium.com/@__dave/why-i-wrote-yet-another-sql-tutor... Do let me know if there's any collaboration we could do!
- zckao 8y agoThanks 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!
- samstave 8y agoOne 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...
- deleted 8y ago[deleted]
- jajool 8y agothanks 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.
- TheForumTroll 8y agoBuilding 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 8y agoIt supports Firefox, you just have to enable a component from your about:config.
- m_st 8y agoCongratulations! 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 8y agoIt works pretty fine on FF. However, you have to enable `dom.webcomponents.customelements.enabled` manually...
- deleted 8y ago[deleted]
- chawlamedicos 8y ago15 Harmful effect of smoking on organs https://www.chawlamedicos.com/blog/effect-of-cigarette-smoking-on-human-body/ https://www.chawlamedicos.com/blog/effect-of-cigarette-smoki...
- rootlocus 8y agoWhy 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.
- yAnonymous 8y agoWhy did you continue for 20 minutes if it disturbs you so much, little snow flake?
- wieghant 8y agoWhy did you choose executions over something mundane like blogs or comments? Reading last statements isn't exactly something I want to be distracted by. The data is interesting sure and if this just happened to be based on something you did recently, I get it. It's not something I'd recommend to students doing data science or getting acquainted with SQL when there's less morbid source material out there. The interactivity provided is cool though, no doubt.
- chris_wot 8y agoI found it engaging, and a truly real world example of how what we do can impact others, provide us with insights we might not normally have, and can help us influence the world around us.
- dang 8y agoThis breaks the site guidelines. You unfortunately have a history of posting uncivilly to HN. If you would please review https://news.ycombinator.com/newsguidelines.html https://news.ycombinator.com/newsguidelines.html and follow the rules when posting here, we'd appreciate it. The idea here is: if you have a substantive point to make, to make it thoughtfully; and if you don't, to not comment until you do.
- GedByrne 8y agoThe reason is explained in the front matter: “On one level, the data is simply a part of a mundane programming book. On another, each row represents immense suffering, lives lost, and in some cases amazing redemption and acceptance. In preparing for this dataset, I was deeply moved by a number of the statements and found myself re-evaluting my position on capital punishment. I hope that as we examine the data, you too will contemplate the deeper issues at play.”
- Agnosco 8y agoReading the final words is really moving. So much suffering.
- alx-ppv 8y agoGenuinely 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!
- rhc2104 8y agoGlad 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/ )
- krylon 8y agoI 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!
- buboard 8y agoIs 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.
- z0ltan 8y agoPuerile.
- amadeuspagel 8y agoSee https://sqlbolt.com/ https://sqlbolt.com/ for an interactive SQL tutorial with less distracting examples.
- HeavyStorm 8y agoThis commentary could have been constructive
- madeuptempacct 8y agoI am looking for something to recommend to a new dev, and your link is definitely preferable. While the story in the opening post is cute, I just don't care about said story.
- default-kramer 8y agoThe 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.
- cryptonector 8y agoThe 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.
- speeddaddy406 8y agoi need some help im at at point were im bout go all in / cause i reall dont know what to do but i need it iv beensiging up for school an online jobs an stuff but i got a call last night an for a grant froma survay i summitted /what he called a citizens demographic survey as he called it from Washington dc for enough to get me through I'm just scared cause I know how scammers work what would u do and he has a lot of my information already check-in to make sure it was me like a legit businessman i talked with him almost a hour an can call him right know if needed ....the catch i cant i got pay 250 / because of the taxes of the transaction? help !!!!!!
- cristoperb 8y agoI 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.
- orcs 8y agoAs 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.