9 ms·
PostgreSQL Exercises
- ak39 10y agoIf 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-type-lateral/ 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.
- bhanu423 10y agoGlad 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!!
- snaky 10y ago> 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
- whytaka 10y agoJust what I need. Thank you!
- jackweirdy 10y agoReally 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 10y agoAuthor 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 10y agoYour 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 10y agoThank you, I appreciate it!
- the_duke 10y agoGreat 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.
- Wonnk13 10y agolove this site for getting back into the groove before technical phone screens, can anyone recommend another site with similar problems?
- Adam_O 10y agoHere are two I have been using from time to time: http://sqlzoo.net/wiki/SQL_Tutorial http://sqlzoo.net/wiki/SQL_Tutorial https://lagunita.stanford.edu/courses/DB/SQL/SelfPaced/info 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).
- kgen 10y agoShameless 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.
- oceankid 10y agoLove SQLBolt.
- jnbiche 10y agoThis is very well done and a great review of basic SQL before an interview.
- GiselleDarlene 10y agoAs someone who has a interview focused on SQL next week, TY! Excited to dive in tonight.
- cjcampbell 10y agoI'm a big fan of http://academy.vertabelo.com/ http://academy.vertabelo.com/, though its not PostgreSQL specific.
- afarrell 10y agoJumping 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.
- peteretep 10y agoIf 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
- throwaway049 10y agoIt 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)
- snaky 10y agoIf 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.
- rahkiin 10y agoThe site works a bit awkward on my phone (iPhone 6), something with the width. Might want to debug that :)
- AlisdairO 10y agoAuthor 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.
- comboy 10y agoI 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 10y agoHaha, no. But I browse HN on my phone.
- jtchang 10y agoThe 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 10y agoWindow functions... ORMs will probably never support them
- joostdevries 10y agoI 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
- platz 10y agoImpressive!
- Deinumite 10y agoSlick 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.
- urschrei 10y agoSQLAlchemy has, for a while: http://docs.sqlalchemy.org/en/latest/core/tutorial.html#window-functions http://docs.sqlalchemy.org/en/latest/core/tutorial.html#wind...
- DasIch 10y agoSQLAlchemy 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.
- sergiotapia 10y agoThis 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 10y agoYou should know your DB whatever you use to access it.
- wears_sweaters 10y agoWould someone with background in other sql flavors be able to use this as a into to PostgreSQL?
- judofyr 10y agoFYI: I downvoted this because I dislike the trend of Elixir/Phoenix discussions taking over every single thread.
- aj_n 10y agoI 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?
- wmccullough 10y agoDoes that even really qualify as a good reason to downvote someone? I'd hardly call this "taking over the thread".
- haskal 10y agoI would downvote this comment if I could.
- dang 10y agoWe detached this subthread from https://news.ycombinator.com/item?id=12023209 https://news.ycombinator.com/item?id=12023209 and marked it off-topic.
- seomis 10y ago5x cartesian product of the eight-row table in exercise #1: Server copes well, Chrome not so much. :(
- wmccullough 10y agoI've been looking for something just like this!
- bhassfurt 10y agoVery fun, managed to capture my attention for a few hours so far.
- dang 10y agoDiscussion from 2013: https://news.ycombinator.com/item?id=6893333 https://news.ycombinator.com/item?id=6893333.
- tychuz 10y agoThis website is really badly designed, here's why: http://i.imgur.com/RqRaDxb.png http://i.imgur.com/RqRaDxb.png
- Dangeranger 10y agoWhile 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 10y agoThat 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 10y agoThat 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 :-).
- vincentgagne 10y agoI have been giving this website to most of our new hires and the results are phenomenal.
- 0x54MUR41 10y agoThank you for making this. It's cool. OOT, at first glance, the design of website looks like Ubuntu documentation page.
- jrapdx3 10y agoOff-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 10y agoDownvote 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 10y agoSorry about that. It's been a while, I'll reread the document you suggest.
- deleted 10y ago[deleted]
- SEJeff 10y agoFYI on the Khan Academy, they have some excellent database tutorials: https://www.khanacademy.org/computing/hour-of-code/hour-of-sql 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-by-sqlite-sqljs-asmjs-and-emscripten https://brianbondy.com/blog/168/sql-on-khan-academy-enabled-...
- uber1geek 10y agoI want to learn more about databases and relational databases. I just started off with python (lpthw by zed shaw). Any leads ?
- mlvljr 10y agoSomething good for plain SQL: http://www.sql-ex.ru/?Lang=1 http://www.sql-ex.ru/?Lang=1
- punnerud 10y agocd.facilities - why can't they include that information on every page? Other than that, I love it.
- deleted 10y ago[deleted]