
PostgreSQL Exercises - aghillo
http://pgexercises.com/
======
sampo
The Stanford database class should be sill open for self study. It contains
video lectures, and lots of database query exercises. For the exercises, you
can write the query code in the web browser.

[https://class2go.stanford.edu/db/Winter2013/preview/](https://class2go.stanford.edu/db/Winter2013/preview/)

I have taken some MOOCs in past years, including Andrew Ng's Machine Learning,
and Martin Odersky's Functional Programming Principles in Scala, and I have to
say that Jennifer Widom's database class is the most streamlined, most well
thought, and most smooth of all the MOOCs I have seen.

Everything works, the lectures are packed with information and give sufficient
material for one to complete the exercises, there are no rough edges, and
there is a large-ish amount of exercises that give good coverage of the
lectured material.

~~~
frigg
>I have to say that Jennifer Widom's database class is the most streamlined,
most well thought, and most smooth of all the MOOCs I have seen.

I absolutely agree and I recommend this course every time someone mentions
SQL. The lectures combined with the videos are the best learning tool for
people interested in SQL.

I also recommend Introduction to Networks from Stanford which is also freely
accessible with registration[1] and also very high quality similar to the DB
course level.

[1][http://f12.class2go.stanford.edu/networking/Fall2012](http://f12.class2go.stanford.edu/networking/Fall2012)

------
AlisdairO
Author here - just wanted to say thanks for the feedback and kind words. I
know there's a couple of rough UI edges in there yet, but I figured it was
about time I just shipped something :-).

I'll take note of all the suggestions and try to fix anything that needs
changing over the weekend.

~~~
scrabble
Good work.

It looks pretty good and I'm going to be going through at least some of the
exercises. It always feels good to ship something, even when you feel like it
isn't 100%.

~~~
AlisdairO
> It always feels good to ship something, even when you feel like it isn't
> 100%.

Sort of, although at first I found showing it to the world a touch nerve-
wracking :-). Realistically though, if I waited until I judged things were
100% ready I would never put anything out there.

------
mattmanser
_How can you retrieve all the information from the facilities table?_

SELECT * FROM Facilities

ERROR: relation "facilities" does not exist Position: 15

Query was: SELECT * FROM Facilities

Useful.

It looks great, it's almost there but you've _got_ to approach these things
from the point of view of a novice.

~~~
cones688
In getting started it does give the table names... which are all prefixed
cd.tablename, it is an educational tool so one would expect one to read the
getting started pages first.

~~~
mattmanser
Would one? Do you read your manuals perchance?

There are different types of learner, some learn by poking stuff with a stick
rather than meticulously reading everything.

I have no idea about postgre as I know SQL Server & MySQL, I was actually
having a quick go to see if my knowledge easily translated.

But schemas are a YAGNI 99% of the time feature in those.

~~~
asdasf
>But schemas are a YAGNI 99% of the time feature in those.

What? I literally have only ever written one web app that didn't use multiple
schemas, and that was a simple blog. I think you are confusing "I don't bother
to use X" with "X isn't very useful".

~~~
mattmanser
Most people don't bother with them, it's like juggling namespaces, mainly a
complete waste of keystrokes.

There's little to no chance of collisions but you have to use them constantly,
which is annoying. Few benefits, lots of downsides.

~~~
AlisdairO
I don't think it's just about collisions, in fairness. There's also the aspect
of managing user permissions by schema, searching for tables, and so on.

Obviously this only becomes super-useful in pretty sizeable projects, but I
tend to consider using schemas a good habit to be in.

------
kuny
Very nice!

Small suggestion: I looked at a random sample - the basic dates exercise
([http://pgexercises.com/questions/basic/date.html](http://pgexercises.com/questions/basic/date.html))
and had a bit of trouble finding the name of the table / schema description
generally. It was not obviously that this is a pop-out under "Help".

~~~
AlisdairO
Yes, I had that feedback on proggit too - I think I'll try making a mini-
tutorial that points out a couple of the more important/less obvious UI
elements the first time you load one of the exercises pages.

Thanks for the feedback!

------
awhitty
As someone trying to sit down and learn PostgreSQL right now, I am so happy
this exists.

------
JonnieCache
Very nice! Bookmarked.

A little suggestion: The "home" link in the menu points to index.html rather
than the bare domain. Thankfully, I'm no SEO expert, but I'm fairly sure this
is duplicate content in google's eyes and will penalise you in result
rankings. Looking forward to the github repo.

~~~
AlisdairO
Thanks - I'll put it on my list of things to fix :-)

------
jtchang
Does anyone else have the problem of teaching SQL to business people who
aren't necessarily that technical but still need to run reports?

I have found direct access to run queries is a very powerful tool but teaching
SQL is tough to people who are not that technical.

~~~
redidas
I also keep running into this. I spent several years working in the "business
intelligence" field. This essentially involved me using a reporting tool of
some kind and writing SQL for business people that didn't know how. I never
got to do any actual analysis like I wanted because I was too backlogged with
report requests from the less technical.

I think SQL is something every business analyst should know how to write. It's
an extremely powerful tool.

------
aragot
We've had a different solution to the same observation: When they start
learning SQL, people struggle writing their INSERT INTO when they should start
learning SELECT first.

Congrats on your idea, I've been looking for resources to learn the Postgres
dialect specifically and it's great we're all in the same ecosystem. My
solution was to make a Postgres front-end that would be as easy as
spreadsheets [1].

I'm looking for a few customers who would help me get into the
improvement/feedback loop. Please do not post me yet on the first page of
HackerNews, the product is too young to be judged;) And sorry for people who
think this is wrong advertising.

[1] [http://www.play-sql.com](http://www.play-sql.com)

------
Obscure
[http://pgexercises.com/questions/basic/where3.html](http://pgexercises.com/questions/basic/where3.html)

The query I used was "select * from cd.facilities where name ~ 'Tennis';"
which got me the tick, but the page didn't detect that I hadn't used LIKE and
the feedback was therefore wrong. You might want to think a little more about
different solutions to the exercises.

Other than that it's slick and impressive so far, thanks. I shall be sending
the URL onwards.

~~~
AlisdairO
Good point. In the early exercises I'm trying to limit the number of possible
routes I throw out there - but I'll modify the exercise in question to mention
some of the other possibilities!

Hope you find the site useful.

------
craigkerstiens
Theres also [http://postgresguide.com/](http://postgresguide.com/) which is
intended to have practical examples of various Postgres and SQL features.

------
jokull
Pretty sure it’s just Postgres now, not Postgre or PostgreSQL.

~~~
Zr40
No, it's called PostgreSQL. Some people abbreviate it to Postgres or pg. See
also
[http://wiki.postgresql.org/wiki/ProjectName](http://wiki.postgresql.org/wiki/ProjectName).

------
cipher0
Thank you, this is excellent!! Just when I needed it too.

------
justinsteele
Problem "Where2"

Fails: SELECT facid, name, membercost, monthlymaintenance FROM cd.facilities
WHERE membercost > 0 AND (membercost / monthlymaintenance) < (1/50);

Succeeds: SELECT facid, name, membercost, monthlymaintenance, (membercost /
monthlymaintenance) as a FROM cd.facilities WHERE membercost > 0 AND
(membercost / monthlymaintenance) < (0.02);

~~~
knightni
You're running into the fact that Postgres doesn't automatically cast ints to
floats - so 1/50 = 0. Try using 1.0/50.0 instead.

~~~
SDGT
Or get dirty and cast it.

::floatval

------
durzagott
The "Show" button in "Answers and Discussions" should really switch to a
"Hide" button when the text is revealed. The current toggled state, which is
the down-state of the "Show" button, is too subtle and I couldn't tell that it
was toggled to the down-state. I thought it was a bug at first.

~~~
AlisdairO
Good thought, I'll put it on my list.

------
j_s
Nice!

Several options were recommended when I asked for help learning SQL several
months ago
([https://news.ycombinator.com/item?id=5713882](https://news.ycombinator.com/item?id=5713882)
), including one interactive website:

[http://sqlzoo.net/](http://sqlzoo.net/)

------
boobsbr
Nice, I like Postgres a lot, but sadly now I live in Oracle world. Will give
it a try at home.

Back in 2005-6 I used [http://www.sql-ex.ru/](http://www.sql-ex.ru/) to study
and learn SQL, it worked fairly well with Postgres.

------
vijayr
Very cool. Does something like this exist for other databases too?

------
jz10
aghillo, thank you for this. Seems that the exercises are comprehensive enough
to cover beginner SQL. As someone also starting out SQL, I appreciate all the
hard work you put into this project.

