Really cool! One suggestion: it would be great to have the schema of the table(s) you need to query against shown above the sandbox for each exercise. The pop-up window is great but I don't have enough screen space to keep it open and refer back to it while writing a solution.
Author here: yeah, that seems to be a common theme. I will probably update the site at some point to fix this. Sadly I'm mid-emigration at the moment, so it probably won't be in the immediate future :-). I've raised https://github.com/AlisdairO/pgexercises/issues/19 to cover it so I don't forget.
Your site looks great! I built https://www.sqlteaching.com , so feel free to use that design if you want a setup that has both the schema and expected output. I made the design tradeoff of using really small datasets.
If you are made of money and in the UK, you can attend this course[0] which is all taught using Postgres, doesn't require being enrolled in the associated MSc, and will seriously up your game.
It looks like you can take this module for £1850. That's about the going rate and should pay for itself in increased productivity/earnings.
(My employer recently paid a similar fee for me to take a far less impressive msc module at a uk uni)
Why would you ever pay that kind of money for a course which teaches things that can most certainly be learned by reading 2 books which might cost 100£?
Talking about books, I'd say PostgreSQL Server Programming is especially worth to look at, because extensibility is the very thing where PostgreSQL shines.
I had a street fighting knowledge of Postgres. I picked up "Postgres Up and Running" which filled in a lot of holes in my knowledge. It was surprisingly good for an O'Reilly book.
Author here - and you're being far too kind. The site is rather hateful on mobile! Sadly you're looking at the limits of my webdev skills, as I spend most of my time on more systems-y stuff. Anyone who would be willing to contribute improved code (as long as it didn't slow things down substantially) would be welcomed with open arms and relief ;-).
I hope to improve things myself someday. Unfortunately I'm mid-emigration currently - so it will probably be a while.
I don't know if I'm too old, my eyes are too bad or hands to clumsy, but it boggles my mind that people would even try to enter SQL queries on their phone.
The one exercise I would love is calculate rolling retention. It's a very common SaaS metric but really hard to get/calculate. I don't know any ORM that does it elegantly.
Slick is the coolest ORM I have ever used. The way it lifts SQL types into Scala code is something I have never seen done elsewhere.
That being said it was also one of the hardest libraries for me to learn. The docs are okay but it seemed like I found more features in peoples github repos that were not documented on the Slick website.
SQLAlchemy isn't an ORM, it has one. The docs you've linked only show how to use window functions using the Expression Language that allows you to construct SQL in Python. There is no indication at all on how to use that with the ORM.
Despite the marketing the ORM is very much distinct. You can use SQLAlchemy without ever touching or even knowing about the ORM.
The Expression Language on which the ORM is built on is more powerful than the ORM itself. You can do asynchronous IO with the Expression Language or generate SQL with it that you couldn't with the ORM.
This distinction matters. You can't just point to somewhere in the SQLAlchemy docs and say argue that because SQLAlchemy allows you do to X, there exists an ORM that can do X. Calling SQLAlchemy an ORM is a simplification that may be fine in some contexts but it ultimately isn't true.
the ORM is built on top of the expression language, and the entire design is that Core expression units are fed into ORM functions that consume them. So there is no separation between a Core SQL construct and the ability to use it in the ORM. In earlier versions there were some limitations here and there moving from Core to ORM as far as how result sets could be constructed, but on the SELECT side all of those are ancient history. My talk at http://www.sqlalchemy.org/library.html#handcodedapplications... is entirely about translating from SQL to the ORM and focuses on a problem that uses a window function.
You're totally right of course. I was to focused on the idea of creating queries based on models and somehow getting models back. Somehow I got caught up in this completely wrong way of thinking about the problem.
I greatly enjoyed watching the talk. You make it obvious what to take away from the talk and your explanations are easy to follow along with. In the first part of the talk, you really took advantage of Python's dynamicity to describe something very static. I hope that's something that will stay in my mind and change how I write code in the future.
This is awesome! This will come in handy to me because I've switched to Elixir and Phoenix, which uses Ecto as it's default data access package - Ecto's syntax is very sql-y
I don't see this as evidence of "taking over" the thread, but rather someone excited about the applications of OP's site. Are you sure this calls for censoring their comment?
If any one here is doing serious DB based business logic with PostgreSQL (which I do recommend), I'd implore them to investigate and start using LATERAL JOIN.
Glad I found this blog. I am trying to build analytics engine for offline world and have chosen post-gres as my storage layer. These posts are invaluable and simply awesome. Thanks for sharing!!
While I agree with you that the website could use visual polish, I disagree on how you presented your critique. Your audience for this is the author, who obviously knows what the site looks like. Offer specific helpful suggestions or don't bother at all.
The author spent many hours of hard work creating this site, for free, and makes it available to anyone who wants to use it. The least that you could do is not phone in a design critique by just posting a screenshot and saying "this site is poorly designed".
That comment has nothing to do with "visual polish". Instead, the screenshot is showing a syntax error apparently being accepted as a correct answer, and I don't think that's something the author has seen.
FWIW I think the comment was perhaps a little over the top given the severity of the issue, but on the other hand I'm very happy to have received the bug report :-).
Off-topic, but why do random comments have no downvote arrow? Not that I'm at all inclined to downvote anybody, but guessing some kind of web glitch showing up. Thought I should mention it.
Edit: the downvote anchor is completely missing in random fashion. Didn't notice the problem in other topics.
Downvote arrows disappear after the time window for downvoting is up, and that time window is shorter than for upvoting. Also, no one can downvote direct replies to themselves.
74 comments
[ 5.0 ms ] story [ 141 ms ] threadThat's what I needed, as well. :) Randomly found it and decided to share.
One other suggestion: in the info displayed after answering a question, a link to the Postgres documentation for the relevant query would be nice.
http://sqlzoo.net/wiki/SQL_Tutorial
https://lagunita.stanford.edu/courses/DB/SQL/SelfPaced/info
Also, thanks for linking the one in this thread. The interface looks clean (on my laptop at least).
[0] https://www.cs.ox.ac.uk/softeng/subjects/DAT.html
They are complete, comprehensive, and well worth your time.
I hope to improve things myself someday. Unfortunately I'm mid-emigration currently - so it will probably be a while.
That being said it was also one of the hardest libraries for me to learn. The docs are okay but it seemed like I found more features in peoples github repos that were not documented on the Slick website.
"The Python SQL Toolkit and Object Relational Mapper
SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL."
So I would say it is in fact an ORM even if it is also a toolkit.
The Expression Language on which the ORM is built on is more powerful than the ORM itself. You can do asynchronous IO with the Expression Language or generate SQL with it that you couldn't with the ORM.
This distinction matters. You can't just point to somewhere in the SQLAlchemy docs and say argue that because SQLAlchemy allows you do to X, there exists an ORM that can do X. Calling SQLAlchemy an ORM is a simplification that may be fine in some contexts but it ultimately isn't true.
I greatly enjoyed watching the talk. You make it obvious what to take away from the talk and your explanations are easy to follow along with. In the first part of the talk, you really took advantage of Python's dynamicity to describe something very static. I hope that's something that will stay in my mind and change how I write code in the future.
http://blog.heapanalytics.com/postgresqls-powerful-new-join-...
This is no doubt a game changer in the way you organize (modularise for reuse) your views, functions and stored procedures.
In the MS SQL world it's CROSS APPLY.
And modern SQL at all - http://use-the-index-luke.com/blog/2015-02/modern-sql
The author spent many hours of hard work creating this site, for free, and makes it available to anyone who wants to use it. The least that you could do is not phone in a design critique by just posting a screenshot and saying "this site is poorly designed".
FWIW I think the comment was perhaps a little over the top given the severity of the issue, but on the other hand I'm very happy to have received the bug report :-).
OOT, at first glance, the design of website looks like Ubuntu documentation page.
Edit: the downvote anchor is completely missing in random fashion. Didn't notice the problem in other topics.
It's better to send questions like this to hn@ycombinator.com, though, as the site guidelines (https://news.ycombinator.com/newsguidelines.html) ask.
We detached this comment from https://news.ycombinator.com/item?id=12023677 and marked it off-topic.
The coolest part is that the sql engine is actually sqlite compiled with emscripten to javascript.
More details on the implementation here: https://brianbondy.com/blog/168/sql-on-khan-academy-enabled-...
http://www.sql-ex.ru/?Lang=1