13 ms·
PostgreSQL Exercises
- nik736 9y agoGreat idea, does what it should and can be extended to cover a lot more. Keep up the great work. First time looking at it I thought I was on a official Ubuntu site, what a font and color can do to a branding is fascinating.
- 2T1Qka0rEiPr 9y agoWell, it is using the Ubuntu font! https://pgexercises.com/css/site.css https://pgexercises.com/css/site.css
- AlisdairO 9y agoAuthor here: out of curiosity, what would be your priorities for future exercises? Right now my list looks roughly like: - DDL - Advanced datatypes (JSON, arrays, etc) - GIS This order is partly informed by the current support of the site: adding DDL exercises is a relatively minor bit of technical work, and then it's just writing. Everything else involves schema changes (or, possibly, a second database).
- aargh_aargh 9y agoThe order sounds reasonable - IMHO it correlates with the number of users who need that area of knowledge.
- snowplay 9y agoFirst, I appreciate the work you've done. Great stuff. Thanks. Second, I don't imagine these topics fit well with the exercise based course material, but they are of interest to me: Window Functions, Logic in the database vs in the code, Serialization, Normalization vs Denormalization, Object Relational Mapping, Concurrency & Transactions, Triggers
- AlisdairO 9y agoGlad you've found it useful! FWIW window functions are already covered in the Aggregation section. The rest of it, yeah, as you say it's not so great a fit.
- tekkk 9y agoVery impressive! Works well, nice UI, informative. After doing a couple exercises what I don't like is the naming convention. Something like "montlymaintenance" or "recommendedby" are just hard to read and type and I'd much rather use snake-case and/or shorter names. Eg. "monthly_maintenance" or "maintenance_per_month" or just "maintenance" if it's explicit enough that it's always per month. Also to me using shorthands for ids is a bit hmm hmm I mean sure you write them a lot so it's useful to spare your fingers when typing queries but "facid" just seems wrong. Again maybe underscore? "fac_id" or even "facility_id". Well that's at least my opinion. Also would it be a better practise to use "timestamptz" instead of "timestamp"? And FYI I had to zoom to 125% because the font was so small on my laptop.
- pinouchon 9y agoSame thing, took me a while to understand that facid was indeed facility_id. In my previous work, we used this to test candidates when their job involved writing SQL. Worked great.
- adwf 9y agoTimestamptz is better practice. Same size (8 bytes) on disk and saves you from headaches further down the line.
- AlisdairO 9y agoAuthor here - thanks a lot for the feedback, it's appreciated. You're right on the naming convention, it's very haphazard. I'll raise an issue on Github to revisit that - although it may take me quite a while to get to it as I have a two week old to look after right now :-).
- thibaut_barrere 9y agoNeat - thanks for sharing! In the SQL MOOC space, you can also check out https://academy.vertabelo.com https://academy.vertabelo.com. If other people have resources, please share them out, I think this is an area where spreading the knowledge is particularly useful to most developers.
- Devac 9y agoCool.SQL Zoo http://sqlzoo.net/ http://sqlzoo.net/ is also worth recommending for beginners.
- bmn__ 9y agoAnother one is https://www.sqlteaching.com/ https://www.sqlteaching.com/
- hultner 9y agoDid a few exercises and seems nice. An improvement would be if hints included links to PostgreSQL documentation, would make it easier for beginners to find good information.
- nbrigmon 9y agoi was just googling for these yesterday! this is great
- bogomipz 9y agoWhat a great tool/site. Thanks for sharing!
- genghisjahn 9y agoZipcode is an integer? What about zips in New Jersey?
- AlisdairO 9y agogrins for that I'll have to say that I'm from the UK and that situating the exercises in the US was probably an error :-)
- Chickenosaurus 9y agoAs a general rule of thumb, if something isn't used for calculations, it probably shouldn't be a number.
- AlisdairO 9y agoThat wouldn't generally be my approach. In my view if something is a number it should be typed as such. That said, I recognise that there are pros and cons to this approach similar to those in strongly vs weakly typed PLs. My personal preference is for a strong schema.
- marvy 9y agoThere is a hidden assumption in your phrase "if something is a number". You imply that a zipcode is "obviously" a number. But it's not obvious. A zipcode is obviously a sequence of digits, but not everything that we represent as a sequence of digits is meaningfully treated as a number. Leading zeros are one sign that zip codes are just digit strings, not numbers. Another (silly) example: if you REALLY have a number, then it doesn't matter what base you write it in. The number four can be written as 4 in base ten, or as 100 in base two. If you write a zip code in anything other than base ten, it's not really a zip code anymore. Likewise, you can't add zip codes, nor subtract them, nor even meaningfully say that 11229 is more than 11228. You might as well use the letters A through J instead of the digits zero through nine, and suffer no serious inconvenience. And indeed, some countries use something like zip codes, except that they have letters and numbers, yet I would hesitate to say that Canadian zip codes are so fundamentally different from US zip codes that they deserve to be stored under a different data type. In conclusion, even you prefer a strong schema, a case can be made that zip codes should not be integers. Of course, that leaves open the question of what they should be, since most SQL databases have no built-in data type for digit sequences or anything similar. I have no answer to this and would personally just use integers anyway.
- danso 9y agoThis seems as good of place as any to ask novice-level questions about PostgreSQL. I teach students SQLite and one of the most massive pain points in transitioning to PgSQL is how the latter, in the `WHERE` clause, fails to recognize aliases in the `SELECT` clause, e.g. SELECT UPPER(name) AS bigname FROM people WHERE bigname = 'JOE'; https://stackoverflow.com/questions/38040631/postgresql-does-not-accept-column-alias-in-where-clause https://stackoverflow.com/questions/38040631/postgresql-does... Apparently this is the SQL standard, which PgSQL seems to do a better job of following than SQLite, but I'm at a loss to understand why this computation is particularly problematic for PgSQL (or any variant) to adopt? Unlike other SQL standard rules that PgSQL follows that SQLite/MySQL doesn't (such as forbidding the selection of column names that aren't being GROUPed by in a GROUP BY clause), this strictness seems to be all inconvenience.
- hungerstrike 9y agoIt's because the WHERE clause is evaluated before the SELECT. The reason that SQLite allows you to do that is because SQLite is much, much simpler than PgSQL, MySQL and SQL Server (all of which don't allow you to do this). http://tinman.cs.gsu.edu/~raj/sql/node22.html http://tinman.cs.gsu.edu/~raj/sql/node22.html
- danso 9y agoThat seems straightforward enough, don't know why I assumed SQLite's implementation of it was a feature as opposed to a consequence of its overall simplified model. I think I assumed/hoped that aliasing worked as a kind of function definition which WHERE could magically understand and evaluate.
- stats_n_trends 9y agoAs a counter point teradata (a ridiculously expensive closed source but fairly complex database) supports this feature. So it is definitely possible for this feature to be supported.
- combatentropy 9y agoYes. I wish SQL select-statements put the select-clause last: from people where name = 'Joe' select upper(name) as bigname 1. It would mirror the other clauses (insert, update, and delete) which all begin with the table name. 2. It would be easier on my head. I skip the select-clause anyway when reading SQL, then come back up to it at the end, when I know the columns' source. 3. It would show that using column aliases in the where-clause would fail (unless the database did some kind of two-pass processing).
- antoaravinth 9y agoVery good site. Just a question here, is there any book or tutorial which teach (like the site do) index's, triggers, function etc?
- koffiezet 9y agoA suggestion: repeat the table names on every exercise in the description. I had been going trough them casually - and always forgot what the table name was and had to go back or press the "show answer" button.