
PostgreSQL Exercises - pvsukale3
https://pgexercises.com/
======
tekkk
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.

~~~
AlisdairO
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 :-).

~~~
gigatexal
Kudos on the new budding programmer you got there. ;-)

~~~
AlisdairO
Thank you!

------
danso
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';
    

[https://stackoverflow.com/questions/38040631/postgresql-
does...](https://stackoverflow.com/questions/38040631/postgresql-does-not-
accept-column-alias-in-where-clause)

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

[http://tinman.cs.gsu.edu/~raj/sql/node22.html](http://tinman.cs.gsu.edu/~raj/sql/node22.html)

~~~
combatentropy
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).

~~~
Volrath89
You just described LINQ

------
Devac
Cool.SQL Zoo [http://sqlzoo.net/](http://sqlzoo.net/) is also worth
recommending for beginners.

~~~
bmn__
Another one is [https://www.sqlteaching.com/](https://www.sqlteaching.com/)

------
thibaut_barrere
Neat - 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.

------
nik736
Great 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
Well, it _is_ using the Ubuntu font!
[https://pgexercises.com/css/site.css](https://pgexercises.com/css/site.css)

~~~
AlisdairO
Yeah, I have zero design sense and just went with the defaults of a bootswatch
theme that I liked :-)

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

------
genghisjahn
Zipcode is an integer? What about zips in New Jersey?

~~~
AlisdairO
_grins_ 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
As a general rule of thumb, if something isn't used for calculations, it
probably shouldn't be a number.

~~~
AlisdairO
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.

~~~
marvy
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.

~~~
AlisdairO
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.

~~~
marvy
Ah; makes sense

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

------
nbrigmon
i was just googling for these yesterday! this is great

------
antoaravinth
Very good site. Just a question here, is there any book or tutorial which
teach (like the site do) index's, triggers, function etc?

------
bogomipz
What a great tool/site. Thanks for sharing!

