
PostgreSQL Exercises - trymas
https://pgexercises.com/
======
jackweirdy
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.

~~~
AlisdairO
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](https://github.com/AlisdairO/pgexercises/issues/19)
to cover it so I don't forget.

~~~
rhc2104
Your site looks great! I built
[https://www.sqlteaching.com](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.

~~~
AlisdairO
Thank you, I appreciate it!

------
ak39
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-...](http://blog.heapanalytics.com/postgresqls-powerful-new-join-type-
lateral/)

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.

~~~
snaky
> to investigate and start using LATERAL JOIN

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

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

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

------
peteretep
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](https://www.cs.ox.ac.uk/softeng/subjects/DAT.html)

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

~~~
rch
I also like _PostgreSQL 9.0 High Performance_ by Gregory Smith.

~~~
snaky
Talking about books, I'd say _PostgreSQL Server Programming_ is especially
worth to look at, because extensibility is the very thing where PostgreSQL
shines.

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

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

~~~
platz
Window functions... ORMs will probably never support them

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

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

------
dang
Discussion from 2013:
[https://news.ycombinator.com/item?id=6893333](https://news.ycombinator.com/item?id=6893333).

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

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

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

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

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-...](https://brianbondy.com/blog/168/sql-on-khan-
academy-enabled-by-sqlite-sqljs-asmjs-and-emscripten)

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

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

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

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

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

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

------
whytaka
Just what I need. Thank you!

~~~
trymas
You're welcome.

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

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

------
0x54MUR41
Thank you for making this. It's cool.

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

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

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

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

------
wmccullough
I've been looking for something just like this!

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

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

~~~
dang
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](https://news.ycombinator.com/newsguidelines.html))
ask.

We detached this comment from
[https://news.ycombinator.com/item?id=12023677](https://news.ycombinator.com/item?id=12023677)
and marked it off-topic.

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

------
tychuz
This website is really badly designed, here's why:
[http://i.imgur.com/RqRaDxb.png](http://i.imgur.com/RqRaDxb.png)

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

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

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

