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).
First, 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
Very 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.
Author 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 :-).
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.
Did 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.
That 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.
There 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.
Ah, I should have been clearer in my initial response. I actually have no opinion on whether zip codes are a number, as I'm from the UK and don't really know how they work :-). I was more responding to the idea that any numeric value you don't do calculations on should be stored as a bare string. I would be happy enough with a string that had a constraint limiting the string to characters [0-9], though (again, assuming the data in question is limited to those chars).
On the whole, pgexercises punts on the data modelling aspect, as it's focused on teaching SQL instead - in some cases choosing deliberately bad schema design to make it easier to ask interesting questions. On reflection I'm not sure this was a good approach, but I'm far past the point where I have time/inclination to revisit it.
You can store a NJ zip in an integer; you just have to be careful when printing it. Annoying, but if space is scarce, maybe worth it. (Though if space is too scarce for zipcodes, good luck storing anything else more interesting.)
This 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';
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.
It'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).
That 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.
As 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.
Yes. 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).
MSSQL has the same (very frustrating) limitation. The obvious and ugly solution would be to wrap in a subquery (bonus: still std sql compliant, and performs well):
SELECT bigname
FROM (
SELECT UPPER(name) AS bigname
FROM people
) x
WHERE bigname = 'JOE'
Add columns as necessary, it should work fine.
But this method can make large queries with lots of aliasing very difficult to read and lead to deep nesting. There is an alternative in MSSQL and (via a quick search) pgsql as well to keep the crazy nesting to a minimum:
-- MSSQL
SELECT bigname
FROM people
CROSS APPLY (SELECT UPPER(name) as bigname) x
WHERE bigname = 'JOE'
-- PGSQL (not 100% sure of syntax)
SELECT bigname
FROM people
LEFT JOIN LATERAL (SELECT UPPER(name) as bigname) x on true
WHERE bigname = 'JOE'
The problem is this might have a negative impact on performance because it mucks with the query planner. I've seen everything from huge, complex, CROSS APPLYs have literally zero performance penalty to completely benign single-column renames tanking query performance 10x, so beware if you use this.
I’m always saddened when I see this feature lacking in so many databases. Especially as Teradata supports this feature so i get to use it at my day job but not on any open source projects.
A 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.
46 comments
[ 21.6 ms ] story [ 261 ms ] threadFirst 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.
That's exactly what good branding looks like!
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
The rest of it, yeah, as you say it's not so great a fit.
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.
In my previous work, we used this to test candidates when their job involved writing SQL. Worked great.
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 :-).
In the SQL MOOC space, you can also check out 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.
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.
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.
That's what I've always done..
On the whole, pgexercises punts on the data modelling aspect, as it's focused on teaching SQL instead - in some cases choosing deliberately bad schema design to make it easier to ask interesting questions. On reflection I'm not sure this was a good approach, but I'm far past the point where I have time/inclination to revisit it.
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.
http://tinman.cs.gsu.edu/~raj/sql/node22.html
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).
But this method can make large queries with lots of aliasing very difficult to read and lead to deep nesting. There is an alternative in MSSQL and (via a quick search) pgsql as well to keep the crazy nesting to a minimum:
The problem is this might have a negative impact on performance because it mucks with the query planner. I've seen everything from huge, complex, CROSS APPLYs have literally zero performance penalty to completely benign single-column renames tanking query performance 10x, so beware if you use this.; with cte as ( select a as A, b as B, c as C from dbo.table1 tbl1 join dbo.table2 tbl2 on tbl2.id = tbl1.id) select A, B, C from cte