PostgreSQL Exercises

http://pgexercises.com/

by aghillo • 13 years ago

167 44 13 years ago

pgexercises.com

awhitty• 13 years ago

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

cipher0• 13 years ago

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

jz10• 13 years ago

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.

JonnieCache• 13 years ago

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.

AlisdairO9• 13 years ago

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

kuny• 13 years ago

Very nice!

Small suggestion: I looked at a random sample - the basic dates exercise (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".

AlisdairO9• 13 years ago

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!

mineo• 13 years ago

The index page links "Getting Started" as "an introduction to the dataset" and there you'll find descriptions of the tables.

jokull• 13 years ago

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

Zr40• 13 years ago

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

mattmanser3• 13 years ago

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• 13 years ago

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.

mattmanser3• 13 years ago

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.

jfarmer• 13 years ago

it is an educational tool so one would expect one to read the getting started pages first

This is the opposite of how it should be. You'd expect learners to be less likely to read a "getting started" page than an expert.

For example, saying to a beginner, "Oh, the tables are all prefixed with cd.tablename" will likely result in a look of confusion because the beginner didn't understand any of the words you used or the way in which you combined them. Is that period special, for example? You said prefix, so if I type "cd.tablenamefacilities" will that work? What are tables and how do I know what their "name" is? Why is the error message talking about "relations?" And so on.

AlisdairO9• 13 years ago

Thanks for the feedback - I've modified the page so a refresh should show that the question specifies the 'cd.facilities' table. Hopefully soon I should be able to implement a mini tutorial that walks you through the page the first time you see it, and points out that the help button will show you the db schema - I'm aware it's not too obvious as things stand.

sampo• 13 years ago

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/

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• 13 years ago

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

mmorris• 13 years ago

It looks like the Stanford DB class will be running again starting in Jan 2014 on the new Stanford/edX platform. Here's a link to the class: https://class.stanford.edu/courses/Engineering/db/2014_1/abo...

AlisdairO9• 13 years ago

Looks wonderful - I've been meaning to learn xpath properly for a long time, so I've signed up :-).

AlisdairO9• 13 years ago

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• 13 years ago

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

AlisdairO9• 13 years ago

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.

simonebrunozzi• 13 years ago

Hey AlisdairO, great work!! Any way to get in touch with you? My email is in my profile.

aragot• 13 years ago

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

Obscure• 13 years ago

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.

AlisdairO9• 13 years ago

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.

vijayr• 13 years ago

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

justinsteele• 13 years ago

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• 13 years ago

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.

SDGT2• 13 years ago

Or get dirty and cast it.

::floatval

boobsbr• 13 years ago

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/ to study and learn SQL, it worked fairly well with Postgres.

jtchang• 13 years ago

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• 13 years ago

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.

SDGT2• 13 years ago

Not myself directly, but my cube mate had this problem before he left for greener pastures.

There was no resolution. He tried to teach them SQL, however when that failed he fell back on at least trying to get the Cognos scripts under SVN. He left two weeks ago, and just last week I overheard a conversation along the lines of:

"He showed me this Tortoise thing, but when I hit update everything has red next to it. It used to be green, but no matter what I do it stays red."

At this point, I don't even know what could have been done. He was probably working on this for his entire employment here, which was close to a year.

atwebb• 13 years ago

I find a lot of the issues are a bit beyond the syntax as well. Looking to pivot or otherwise manipulate the data which require advanced query and data analysis skills. It does help to show that their reports aren't always as simple as they think.

j_s• 13 years ago

Nice!

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

http://sqlzoo.net/

durzagott• 13 years ago

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.

AlisdairO9• 13 years ago

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

craigkerstiens• 13 years ago

Theres also http://postgresguide.com/ which is intended to have practical examples of various Postgres and SQL features.

PostgreSQL Exercises

Related Stories