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 firstname.lastname@example.org.
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.
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 :)
I want to understand the quality and depth of what I'm paying for before I shell out.
...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.
I've never been big on security through obfuscation.
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?
Are both pretty much the same from a stand point of being able to change them in the future once they have a substantial amount of data saved?
For example if you have 1,000,000 rows of varchar(50) vs. text with a check_length(50). Which one wins if you want to decrease X from 50 to 25? Does the answer change if you want to increase X from 50 to 100?
This is with PG11 by the way if it matters.
With the text type and check constraints, none of this is necessary.
Really just pulling at straws here to see if it's ever viable to use text / constraint when it sounds like varchar(N) might be the way to go? Even if it's only there for backwards compatibility, I must say varchar(50) is a lot friendlier to type than having to wire up a char_length constraint.
I think you're thinking of the Wiki rather than the manual: https://wiki.postgresql.org/wiki/Don%27t_Do_This#Text_storag...
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.
There's pretty much no difference between those types, they are all stored exactly the same way. Varchar has a bit of extra cost for the length check, but that's OK when you want to enforce that limit.
That's crashing with my full name
Working with large, real datasets where the SQL optimiser has decided that it doesn't like your face today, well, you need lots of those headaches before you get expert.
I realise that often leaves people in the tough position of "can't get a job without experience, can't get experience without a job". Sorry, I don't know what to suggest.
But good luck and don't let me put you off!
PostgreSQL does have a lot of options, and this includes indexing on binary serialized JSON data, that can help a lot depending on what you want to do. PLV8 is also incredibly interesting.
As an aside, I tend to find that if I can do something in a Docker container (locally), I will do it in a container. This lets you spin everything up, and down and cleanup and retry without leaving remnants of stuff behind on your host environment (desktop). The only gotcha is don't use volume mounts for your db data if you're using docker desktop for windows/mac, it does not perform well.
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.
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/> 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!
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?
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.
At least having the add-apt-repository line in the course manual saves you looking for it on the DBeaver page.
Aside: I've been doing a the Standford machine learning course and it includes installing in the required work, so I did the install on Windows as instructed (what a pallaver) and only then realised it was entirely optional just not presented as such and so then did `sudo apt install octave`, messed around with a few test queries, and was done with the entire second lesson.
I'll make a note to record a setup video for Linux as well (though in truth, the Mac and Windows setup videos are practically identical anyway owing to the cross-platform software in-use)
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?
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?
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.
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.
For example, I've been building a new web app with Elixir and Phoenix for the last few weeks, learning as I go. Couple hours a day (when I can basically). Did nothing but skim the docs and look at open source examples.
I got pretty far and made real progress but the other day I decided to just buy the Programming Phoenix 1.4 book which is written by the people who made Elixir and Phoenix.
I'm now 80% done with the book and I'm very happy I bought it. It filled in SO many gaps and I have a ton of actionable "TODOs" in my app to make changes based on what I learned in the book. Some of these are really big wins and the insights learned help me understand how to write better code in the future (even outside of Elixir).
When it comes to courses, I can't speak for OP but I personally offer free life time updates and support (I happen to create courses too, but not on SQL). Having a get out of jail free card where you can ask someone a question and get an answer is worth the course's price alone. Especially considering if you were to hire someone for that level of support you would probably end up paying 3 or 4 times the cost of the entire course just for 1 hour's worth of support.
> ...instead of just learn it on your own on postgresql.org...
The important thing is to just do it, accept failures, apply some self-criticism and keep going. How you get started is far less important in terms of quality of output as time progresses in one's career.
Courses are a perfectly good way to jump-start the process as is learning from source materials. Different things work for different people.
I still contented the £2K (back in the day) that my employer spent on a week long intro to Oracle was a good investment for them and Me.
As for how my employer at the time pointed me toward SQL was something like; "Here is yahoo.com, learn SybaseSQL by Monday on your own, you start a new project on Monday that requires it". It was Friday afternoon, in 1998 and Google was still in it's infancy, hence all searches were done via yahoo or altavista. And I had to learn it by myself over the week-end. Not even a £0.02 investment from my boss.
Most people are not going to "learn databases" over the weekend. A boss or company that has employees capable of this type of independent learning and routinely expects it will soon lose the former. That costs more than 2 cents.