11 ms·
Mastery with SQL: Learn Modern SQL with Postgres
- nsainsbury 7y agoHi 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.
- deleted 7y ago[deleted]
- langitbiru 7y agoCongratulation! 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 7y agoYeah, 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 7y agoNice! 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
- dotdi 7y agoThis 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?
- nickjj 7y agoIn 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?
- nsainsbury 7y agoYep, I tend to prefer using the text type in most cases (in Postgres in particular, both varchar and text have the same performance characteristics with varchar effectively being just the text type with a check constraint on the length). I'm not super strict about it though - it's more just a preference and there's some good arguments I've seen around 'signalling intent' by using fixed length text types vs "unlimited" length text types.
- nickjj 7y ago> In Postgres in particular, both varchar and text have the same performance characteristics with varchar effectively being just the text type with a check constraint on the length 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.
- Svip 7y agoPerformance wise, there is no distinction in Postgres 11. As I understand it, internally varchar(N) is basically just text with a character limit. varchar(N) really only exists in Postgres these days for backwards compatibility, and the fact that it is ANSI SQL.
- C1sc0cat 7y agoWhich is why I still use this nomenclature for SQL
- davorbadrov 7y agoThe 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 7y agoThanks! 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 7y agoThis looks awesome. If we purchase now, will we get updated/new chapters in the future?
- nsainsbury 7y agoYep - all content I release in the future will be available to all customers. I will be sending out emails as new content is released.
- devinjflick 7y agoI'm definitely interested especially if there will be DevOps topics covered in the future.
- sudhirj 7y agoSimilar feedback here - the only chapters that'll be useful to me are 7. and 8. It would be great if you can add the tentative list of chapters and topics you plan to cover in the website itself, so I can see what's going to come later.
- z3t4 7y agoI 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.
- sashavingardt2 7y agoGreat looking site. A big motivator for me to do something similar.
- unnouinceput 7y agoThis HN ad does put a smile on my face. Why? Because if you need to pay $50 for a course to learn PGSQL instead of just learn it on your own on postgresql.org then it's not your calling. And if it's not your calling then you'll produce crappy SQL queries when faced with a medium or above complexity problem. Problem which later I will get to sort it out, for a hefty price. Hence the smile I'm talking about. So I love this 100%, it puts food on my table
- C1sc0cat 7y agoNot quite sure what you mean is this a positive comment or a passive aggressive negative one. 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.
- unnouinceput 7y agoIt's definitely a positive comment. 100% positive for 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.
- rosege 7y agoSmall typo under HD Video and Top Notch Audio: vidoes
- pbhjpbhj 7y agoI'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 7y agoI 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.
- pbhjpbhj 7y agoYes, but exclusion suggests that there might be use of software later that doesn't allow it, or that there's some difference -- like they do some DB thing and it doesn't work if you're using ext/zfs/whatever. Remember it's for non-experts, people may not even know(?) that pgsql and such are freely available to them. 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.
- swebs 7y agoThat'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.
- anonymous5133 7y agoThe problem with coursera and udemy is that they lock you into their platform and limit you in terms of what you can do. When you build the product with its own website, you can basically do whatever you want in terms of innovation.
- wigster 7y agoFYI your site is blocked by our forcepoint system as a "parked domain"
- oauea 7y agoSounds like a problem with your forcepoint system, whatever that may be.
- mellosouls 7y agoThis 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 7y agoYes, 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 7y agoDidn't know that, the guidelines a good idea, thanks.
- jfmercer 7y agoThis looks like a very promising resource.
- p1esk 7y agoIf 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 7y agoAny 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 7y agoIs 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 7y agoAs 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 7y agoSo 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.
- ore0s 7y agoI'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
- founderling 7y agoDo new coders still learn SQL, now that they all use frameworks that abstract away the database queries?
- tracker1 7y agoUgh... 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.
- molsson 7y agoIt should be really nice to have variable playback speed for the videos.
- nsainsbury 7y agoYep, there is! It's just the promo videos on the landing page that don't have this.
- molsson 7y agoThe 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 7y agoThe 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_serial https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_s...
- nsainsbury 7y agoI 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.
- molsson 7y agoThe OWASP Postgres hardening page recommends that one removes the default "public" schema in postgres: https://www.owasp.org/index.php/OWASP_Backend_Security_Project_PostgreSQL_Hardening#Removing_the_default_.22public.22_schema https://www.owasp.org/index.php/OWASP_Backend_Security_Proje... ...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.
- just_myles 7y agoI had that same problem when I switched from postgres to mysql and big query with their "projects"..
- paulddraper 7y agoWow, OWASP recommends keeping your database schema secret. I've never been big on security through obfuscation.
- nsainsbury 7y agoI 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 7y agoI agree. Some more info here for those interested: https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-1058:_Protect_Your_Search_Path https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-1058:_P...
- techsin101 7y agoLooks for beginner correct
- deleted 7y ago[deleted]
- Vaslo 7y agoHow translatable is this to MS SQL Server? That’s the database we use at my company.
- tracker1 7y agoPl/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.