
Mastery with SQL: Learn Modern SQL with Postgres - nsainsbury
https://www.masterywithsql.com/
======
nsainsbury
Hi HN!

Mastery with SQL is a course I've been working on for a long time now and just
recently released. I'm a big self-learner and one thing I love in courses and
books is exercises (with full solutions). I've always wanted to make a SQL
course that's not just theory but has lots of great real-world inspired
exercises...and not just only simple exercises, but challenging ones too that
put you into problem solving mode and require some creativity. Mastery with
SQL is just that. I estimate that at least half of the time spent creating
this course was focused on exercises alone.

I also wanted to showcase all of the wonderful functionality that's available
in modern PostgreSQL - my favourite go-to relational DB.

I'll be around to answer any questions anyone has about the course - otherwise
you can always contact me directly at neil@masterywithdata.com.

~~~
langitbiru
Congratulation!

Do you have a plan to add interactive SQL coding session? Something like
Codecademy.

So a student can jump into SQL problem. The data has been setup already. The
student only needs to think hard to find the sql query for the problem. Then
the student can get the feedback based on the error.

~~~
nsainsbury
Yeah, I have been thinking about building something like this - possibly even
going beyond the course and offering just the interactive exercises for people
who only want to work through the exercises and don't need all the videos.

~~~
tpaschalis
Nice! If you want to take a look at how someone else approached this,
pgexercises [1] also handles interactive SQL sessions.

[1] [https://pgexercises.com/about.html](https://pgexercises.com/about.html)

------
davorbadrov
The course seems great, congratulations on putting everything together, it
must've taken a lot of time and effort. Maybe you'll find my feedback
instructive: I would say that I have intermediate knowledge of SQL, and most
of the things are familiar to me, and what's stopping me from buying it is the
lack of advanced topics.

Are you planning on adding any advanced topics like query optimizations /
performance tuning, triggers, transaction types, full text search, etc.?

Do you plan on expanding this course with something like that? Because that
would definitely make it a "from zero to hero" type of course.

That's my 2 cents, great work once again :)

~~~
nsainsbury
Thanks! I am planning on adding several additional chapters over the coming
months - the next one will be on query performance and indexes and I've got
plans to add a full chapter on transactions too. I think some devops topics
covering setting up postgres in production, users/roles, security, etc. would
be great as well...I definitely see the course as evolving over time (much
like how books get multiple editions over their lifetime).

~~~
cpursley
This looks awesome. If we purchase now, will we get updated/new chapters in
the future?

~~~
nsainsbury
Yep - all content I release in the future will be available to all customers.
I will be sending out emails as new content is released.

------
mellosouls
This looks interesting but really needs some clear pathway to free samples to
evaluate it. The first thing I did was click "Start Learning" and it took me
to"Pricing".

I want to understand the quality and depth of what I'm paying for before I
shell out.

~~~
dang
Yes, and the Show HN guidelines require a way for uses to try out the product
or project. We've taken "Show HN" out of the title for now.

[https://news.ycombinator.com/showhn.html](https://news.ycombinator.com/showhn.html).

~~~
mellosouls
Didn't know that, the guidelines a good idea, thanks.

------
molsson
The OWASP Postgres hardening page recommends that one removes the default
"public" schema in postgres:
[https://www.owasp.org/index.php/OWASP_Backend_Security_Proje...](https://www.owasp.org/index.php/OWASP_Backend_Security_Project_PostgreSQL_Hardening#Removing_the_default_.22public.22_schema)

...whereas this course seems to use the public schema and just query for
"SELECT * IN public.foobar" etc.

I remember being a bit confused about "schemas" when I switched from mysql to
postgres. I think it would be good to have a special section that explains
what schemas are typically used for, and in particular when/how to use the
public schema correctly.

~~~
nsainsbury
I think that's a reasonable recommendation if you're deploying Postgres in
production. Not something I'd want to cover in any of my current chapters,
where the focus is on teaching people SQL, but definitely something I'll
mention down the road when adding a chapter around production deployment and
security recommendations.

~~~
molsson
I agree. Some more info here for those interested:
[https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-1058:_P...](https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-1058:_Protect_Your_Search_Path)

------
nickjj
In your main example video, you're using varchar(45) for your name fields.

What's your take on using varchar(X) vs having a text field with a
char_length(X) constraint? Isn't the text approach considered more of a best
practice nowadays?

~~~
gmueckl
The PostgresQL manual recommends using text over char or varchar
unconditionally. I think that taking it straight from the source is the best
approach here.

~~~
sbuttgereit
I just took a look at the manual (version for 9.3 and 11) and saw no such
recommendation for character or character varying types... seems like the
manual should be agnostic to such choices outside of deprecation. It did
mention that common assumptions of performance differences weren't real in the
PostgreSQL implementation.

I think you're thinking of the Wiki rather than the manual:
[https://wiki.postgresql.org/wiki/Don%27t_Do_This#Text_storag...](https://wiki.postgresql.org/wiki/Don%27t_Do_This#Text_storage)

I generally agree with the advice in this section of the Wiki, but recognize
that it's more opinionated... and less authoritative... than the manual.

~~~
gmueckl
I was indeed referring to the section on text types in the manual. But I admit
that I somehow lumped char and varchar together in my head while reading that
section a couple of days ago. So you shouldn't ever use char (and only char)
unless you have very specific requirements. This means that yes, I didn't get
it straight.

------
z3t4
I think it's really nice that's it's possible to self publish something like
this. It would be cool with a blog about creating the product, thoughts about
marketing strategy, sales channels, etc. And the experience selling it. I
would love to make something like this for a niche topic. Although I'm worried
it will be hard to sell.

------
p1esk
If a machine learning engineer job posting asks for ‘SQL’ in ‘desired skills’
section, will this be a good resource to work through, or are there better
resources for that? I don’t have any experience with databases.

~~~
Kronen
Any resource to learn SQL will do, there are minor syntax differences between
databases but they are not important. And there is not difference to learn SQL
for big data, machine learning or any other purpose

~~~
p1esk
Is there a good guide on which database to choose for an ML project? Like ease
of use, or speed, or maybe some particular features?

~~~
tempguy9999
As no-one's answered this, I'll try though my area is DBs but certainly not
ML.

AFAIK ML doesn't really care about the source of data so DB is irrelevant. The
conjunction of DB and ML in your question makes me wonder if you're sort of
confusing the two. AFAIK they are totally independent, though some DBs come
with some data mining tools, which is ML eg. MSSQL (you can prob download an
eval version for nothing. It's time limited though, 6 months or so).

But to repeat AFAIK ML and DBs are entirely different things.

If you want to grab a DB, well I've no experience with postgres but I've heard
a lot that's good, so perhaps start here.

~~~
p1esk
So far I’ve been using Excel and Pandas to maintain/pipeline my data, but I
feel like a database might be a better way. I’m not sure about built in data
mining tools, as I’m used to writing them myself. Also, most of my pipeline
configs are in json format so I’ve been thinking about MongoDB.

~~~
tempguy9999
Sounds like you know ML then. Anything non-trivial on a spreadsheet is usually
best ported to a DB, that's a pretty good rule of thumb.

I don't understand about your configs being in json, nor why that makes it
more suitable for mongo. If you need to store semistructured blobs of data,
relational DBs can do that fine (in BLOB fields), but IIRC postgres supports
json, including indexing into individual json subfields.
<[http://www.postgresqltutorial.com/postgresql-
json/>](http://www.postgresqltutorial.com/postgresql-json/>) but to repeat,
I've never used postgres so speak to someone else first who has.

If you can do ML as it seems you are, install postgres and just get started on
this tutorial stuff here - best of luck!

------
pbhjpbhj
I'm curious if you considered providing the course using one of the online
courseware providers (Coursera, Udemy, etc.)?

Second, no love for Linux? DBeaver and pgsql are obviously available, is there
any particular reason that Linux isn't included; or anything in the course
that's specific to MacOS/MS Windows?

~~~
rmsaksida
I think the assumption is that the average Linux user will easily figure out
installing DBeaver and PostgreSQL by themselves, plus installation methods
will vary depending on distribution. I'm a Linux user and I'd certainly skip
that video.

~~~
swebs
That's a pretty lazy assumption if you're providing a how-to video. Especially
since:

1\. There are new Linux users every day

2\. The Mac/Windows users will most likely have to install PostgreSQL on a
Linux server eventually for production anyway

It would be very beneficial to at least add instructions for installing on a
Debian based distribution like Ubuntu, since that would cover the vast
majority of Linux users, especially newer users.

------
dotdi
This looks quite nice!

I had a look at the content overview and I was missing any operations
considerations. While one might have the comfort of somebody doing the DevOps
for them, many people will need to do that themselves. Any plans on that
front?

------
molsson
The content overview doesn't include anything about escaping data to prevent
SQL injection (escaping of literals vs escaping of table/field names etc).
This is important to get right and postgres has a slightly different approach
compared to mysql and others.

For example, you can and should use parametric queries like:

client.query('INSERT INTO mytable(a) VALUES ($1)', ['hello'])

...but that doesn't work for all types of queries, for example you get an
error for:

client.query('SET LOCAL SEED = $1', [someVal])

Another example, you might need to build a dynamic where-clause based on data
from an "advanced search" UI query builder, and then you need to make a list
of all the "foo = $N" parts (and increment $N as you go), plus another list of
the actual variables. Maybe there are some nice tricks / techniques to
simplify that?

------
molsson
The free "teaser video" starts off showing "actor_id serial NOT NULL" but in
postgres 10 and above identity columns can/should be used instead?

[https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_s...](https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_serial)

~~~
nsainsbury
I actually do cover this in the course! I actively recommend and give examples
of using autogenerated identity columns for the primary key, but still in many
places in the course do expose people to using the serial types due to their
popularity.

------
founderling
Do new coders still learn SQL, now that they all use frameworks that abstract
away the database queries?

~~~
tracker1
Ugh... I tend to prefer SQL database interaction via scripting languages, and
the abstractions are a LOT more work than just knowing SQL and writing
parameterized queries directly.

Also, conceptually it's a VERY good idea to understand how your database, and
databases in general work. You can avoid a lot of weird behaviors and
bottlenecks if you know more.

Aside/Joking/Rant: Except mySQL, I don't want to know its' weird behaviors any
more than I already do, it can die in a fiery inferno and suffer in hell
incarnate.

------
ore0s
I'm deciding between doing this and the Kaggle SQL summer camp. How does it
compare?

[https://www.kaggle.com/sql-summer-camp](https://www.kaggle.com/sql-summer-
camp)

------
sashavingardt2
Great looking site. A big motivator for me to do something similar.

------
rosege
Small typo under HD Video and Top Notch Audio: vidoes

------
Vaslo
How translatable is this to MS SQL Server? That’s the database we use at my
company.

~~~
tracker1
Pl/SQL in PostgreSQL is a bit different for some things vs MSSQL's T-SQL
syntax. Most of the syntax will work without much modification, and all of the
stuff that seems to be covered should apply to both. You can take this course,
then use google to cover differences, once you understand the concepts it's
pretty easy.

Most databases have some minor differences. mySQL/mariaDB and SQLite seem to
be the most odd in terms of how standards are supported and fallback
mechanisms. For mySQL this is mostly out of historical context, and for SQLite
its the nature of a small/fast/light SQL library.

------
wigster
FYI your site is blocked by our forcepoint system as a "parked domain"

~~~
oauea
Sounds like a problem with your forcepoint system, whatever that may be.

------
molsson
It should be really nice to have variable playback speed for the videos.

~~~
nsainsbury
Yep, there is! It's just the promo videos on the landing page that don't have
this.

------
jfmercer
This looks like a very promising resource.

------
techsin101
Looks for beginner correct

