PostgreSQL Exercises | Hacker News Reader

PostgreSQL Exercises

pgexercises.com

576 pointstrymas70 comments

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.

Thank you, I appreciate it!

Great work!

One other suggestion: in the info displayed after answering a question, a link to the Postgres documentation for the relevant query would be nice.

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.

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.

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!!

> to investigate and start using LATERAL JOIN

And modern SQL at all - http://use-the-index-luke.com/blog/2015-02/modern-sql

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

You should know your DB whatever you use to access it.

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.

[0] https://www.cs.ox.ac.uk/softeng/subjects/DAT.html

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£?

The same question could be asked about virtually any training course.

If you are not made of money, you can thoroughly read (from A to Z, it's not a cookbook!) PostgreSQL official documentation, it's awesome.

I also like PostgreSQL 9.0 High Performance by Gregory Smith.

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.

I just want to second the postgres docs, they're excellent.

They are complete, comprehensive, and well worth your time.

Is there anything similar available online?

There are legit (free) Postgres screencasts at pgcasts.com

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.

Window functions... ORMs will probably never support them

I use Slick to access Postgres and it does support postgres window functions: https://github.com/tminglei/slick-pg#details

Impressive!

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 has, for a while: http://docs.sqlalchemy.org/en/latest/core/tutorial.html#wind...

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.

From the top of http://www.sqlalchemy.org/ :

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

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.

Discussion from 2013: https://news.ycombinator.com/item?id=6893333.

love this site for getting back into the groove before technical phone screens, can anyone recommend another site with similar problems?

Here are two I have been using from time to time:

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

Shameless plug, bug I wrote SQLBolt (http://sqlbolt.com) for the same reason, because there was a lack of good, simple interactive tutorials for SQL.

Love SQLBolt.

This is very well done and a great review of basic SQL before an interview.

As someone who has a interview focused on SQL next week, TY! Excited to dive in tonight.

I'm a big fan of http://academy.vertabelo.com/, though its not PostgreSQL specific.

Jumping in to say I would love one for CSS in general. There is a sushi-themed one for selectors and a frog-themed one for flexbox.

FYI on the Khan Academy, they have some excellent database tutorials: https://www.khanacademy.org/computing/hour-of-code/hour-of-s...

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

The site works a bit awkward on my phone (iPhone 6), something with the width. Might want to debug that :)

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.

Haha, no. But I browse HN on my phone.

Would someone with background in other sql flavors be able to use this as a into to PostgreSQL?

I don't see a reason, that's just a basics

You should be able to - there's some pg-specific stuff, but the large majority is standard.

Very fun, managed to capture my attention for a few hours so far.

Just what I need. Thank you!

You're welcome.

That's what I needed, as well. :) Randomly found it and decided to share.

Author here - thanks for sharing, it's gratifying to see it on HN!

Thank you for making this. It's cool.

OOT, at first glance, the design of website looks like Ubuntu documentation page.

cd.facilities - why can't they include that information on every page? Other than that, I love it.

5x cartesian product of the eight-row table in exercise #1: Server copes well, Chrome not so much. :(

I have been giving this website to most of our new hires and the results are phenomenal.

I've been looking for something just like this!

I want to learn more about databases and relational databases. I just started off with python (lpthw by zed shaw). Any leads ?

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.

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.

Sorry about that. It's been a while, I'll reread the document you suggest.

This website is really badly designed, here's why: http://i.imgur.com/RqRaDxb.png

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.

That is true! It was a bug - fixed now.

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 :-).

Reply on news.ycombinator.com