In How to Write SQL we saw how to write
SQL queries as separate .sql files, and we learnt about using query
parameters with the psql syntax for that (:variable, :'variable', and
:"identifier").
For writing our database model, the same tooling is all we need. An important aspect of using psql is its capacity to provide immediate feedback, and we can also have that with modeling too.
In our article Exploring a Data Set in SQL we discovered a data set related to music: the Chinook sample database.
Our discovery led us to find albums containing tracks of multiple genres, and for the analytics we were then pursuing, we wanted to clean the data set and assign a single genre per album. We did that in SQL of course, and didn’t actually edit the data.
Finding the most frequent input value in a group is a job for the mode() WITHIN GROUP (ORDER BY sort_expression) Ordered-Set Aggregate Function, as
documented in the PostgreSQL page about Aggregate
Functions.
Now that my book Mastering PostgreSQL in Application Development is released (and selling well, thanks guys!), I’ve had some questions about the title.
The idea is that to become good at anything, we need to practice. We practice a lot, and it’s even better when we are actively trying to learn, following what’s named deliberate practice.
