HN Explorer

Ask HN: What is the best online resource to learn advanced SQL?

448 points by rorykoehler ↗ HN
I too often find myself getting stuck when it comes to more advanced SQL queries. I have never actively learnt SQL and realise that I don't have a great mental model of how it works. I would like to improve this as it is killing my productivity from time to time.

100 comments

[ 4.1 ms ] story [ 172 ms ] thread
PaulHoule
This book is good

https://www.amazon.com/Joe-Celkos-SQL-Smarties-Fourth/dp/012...

mwexler
Props to Joe Celko's books. I found they helped me reconceptualize how to get SQL to solve problems that I thought required another language.
matwood
I'll also thumbs up Celko's books. He takes hard problems and solves them with SQL. His books do assume you already know SQL, and are now looking for better ways to apply that knowledge.
pg314
Maybe this is obvious, but the PostgreSQL manual [1] is excellent. The first 100 or so pages (at least up to and including chapter 7) will give you a good overview. I suggest you read through them at least once. Then you can dig into the rest of that manual at leisure, exploring topics that interest you. It's a great reference too.

[1] https://www.postgresql.org/docs/manuals/

collyw
http://use-the-index-luke.com/

Is really good, though its more related to indexing than SQL specifically.

nindalf
I second this. I prefer reading on pdf, so I bought the ebook[1]. The book explains indexing perfectly, and it shouldn't take you more than a day to finish. I can't recall a book having a better benefit/time ratio. I wish the author would release more books in this vein, but he hasn't. So right now I'm looking at Designing Data-Intensive Applications to learn more about different kinds of databases [2].

[1] http://sql-performance-explained.com/

[2] http://dataintensive.net/

copperx
Sightly tangential ... which device do you read PDFs on?
Fnoord
Kobo Aura One. Bit more expensive yet high quality.

Preview on macOS is also good.

EDIT: I don't know, when that review was written the device was just released. The device received various firmware updates since then. I convert everything to EPUB with Calibre.

pps
I read that this one isn't good for PDFs http://the-digital-reader.com/2016/08/31/kobo-aura-one-pdf-e...
[deleted]
(comment deleted)
peatmoss
I really wish there were at least one mass market device that wedded a good eink screen to a reasonable CPU / RAM. There was a crazy expensive Sony PDF reader. But I really just want a Kindle DX (Aura One okay too) sized screen that doesn't struggle to render PDFs.
nindalf
I used to have a high res inexpensive tablet that I loved to bits, but I dropped it carelessly and I'm finding it impossible to replace the screen. Right now I use Preview on macOS. Since I'm on my laptop anyway, I make notes in a text editor. That helps me remember what I read.
justinclift
Original iPad (series 1).

I had a Samsung Galaxy Note 12.2 for a while, but the built in reader (iBooks) in the iPad is far superior to everything I tried on Android over a period of months.

roryrjb
Can recommend also. Very good and clear explanations.
tomcam
Not the parent poster, but I consume a huge amount of writing in PDF form.

* Most modern browsers have it built in

* Preview on MacOS is good

* Kindle and iBooks import PDF files

* SumatraPDF and Skim, depending on your OS

pthreads
Thanks for the link. This is a very nice resource. Just started reading but I am already finding it very useful.
aakriti1215
I just finished learning Postgres! I used codecademy's SQL session for the intro, but then just decided to play around with queries. This diagram was my most useful resource, and one that I still look at on a daily basis when trying to create queries - I hope it helps! https://www.sqlite.org/images/syntax/select-stmt.gif

I also look at the postgres docs very frequently for syntax and format and those help me a lot. Stack Overflow is also a great resource!

tommynicholas
I found that course excellent as well - although these days if you don't have the "pro" tier of Codecademy you miss out on the quizzes and other things. Still, it was good and pretty advanced. Learned a lot.
aakriti1215
That's very true, I actually ended up subscribing to pro because I was also learning JavaScript and HTML/CSS at the time (new programmer), so I found the pro stuff useful. However, if you're not wanting to drop the $20 or so for the month, I'd say that you can find plenty of fantastic online resources.
sn9
I'm not sure if this is the level you're looking for, but if you need a better mental model of SQL, you should take a look at Jennifer Widom's course on Stanford Lagunita on Databases: https://lagunita.stanford.edu/courses/Engineering/db/2014_1/...

Starting from the relational model and going through SQL really makes the language make sense.

If this is too basic for you, then, at worst, you'll spend a few hours reviewing the basics and strengthening your foundation.

wenc
That is an excellent course, and a good jumping off point. It is somewhat technical and expounds ideas that most DBAs probably aren't aware of.
rootme
Amazon.
[deleted]
(comment deleted)
adamnemecek
You should try to understand how databases in general work, it will help you with your query writing.

One thing you have to realize is that once you get a little advanced, you have to get to the details of the single SQL implementations, it's not about SQL but about Postgres.

I've found these books really valuable

# SQL Performance Explained Everything Developers Need to Know about SQL Performance

https://www.amazon.com/Performance-Explained-Everything-Deve...

This book fundamentally talks about how to effectively use and leverage the SQL indices. Talks about all the important implementations (Postgres, MySQL, Oracle, SQL Server).

# Designing Data-Intensive Applications: The Big Ideas Behind Reliable, Scalable, and Maintainable Systems

https://www.amazon.com/Designing-Data-Intensive-Applications...

This book gets mentioned a bunch around here and for a good reason. There aren't too many concrete resources on making your systems "webscale" and this one is really good.

# PostgreSQL 9.0 High Performance

https://www.amazon.com/PostgreSQL-High-Performance-Gregory-S...

Discusses all the different settings and tweaks you can do in Postgres. It's crazy how much of a perf gain you can get just by twiddling the parameters of the database, i.e. all the tricks you can do when the single instances are bottle necks.

There's a similar book for MySQL https://www.amazon.com/High-Performance-MySQL-Optimization-R...

# PostgreSQL 9 High Availability Cookbook

https://www.amazon.com/PostgreSQL-9-High-Availability-Cookbo...

Discusses how do you go from 1 Postgres instance to 1+ instance. Talks about replication, monitoring, cluster management, avoiding downtime etc i.e. all the tricks you can do to manage multiple instances. Again there's a similar book for MySQL https://www.amazon.com/MySQL-High-Availability-Building-Cent...

Last but not least check out the postgres documentation, people consider it a standard of what good documentation looks like https://www.postgresql.org/docs/9.6&#x...

vittore
A little bit unorthodox, but check out http://sql-ex.ru/ (even though it is in .ru , website itself is in English) especially their advanced exercises.
asafira
This was quite a gem when I was practicing some advanced SQL. I find the problems become legitimately hard and take me quite a while to solve. I recommend it to anyone interested in seeing how certain datasets can require quite a bit of creativity to extract using SQL.
paulddraper
+1 Also present at http://www.sql-ex.com/

Not shiny, but very functional. It's been around 5+ years and has lots of great practice for advanced queries.

neovintage
I would start with the Mode Analytics SQL tutorial. It's fairly robust and gets into advanced topics: https://community.modeanalytics.com/sql/tutorial/introductio...

That will get you pretty far but the advanced SQL topics will require the study of what the underlying database provides and how it works. Every database is different in terms of how it implements advanced features, if at all. For example, MySQL doesn't have window functions. For postgres related topics, the documentation is excellent, postgresguide.com gives a high level overview, or you can follow craig's blog (http://www.craigkerstiens.com/) that provides a gentle introduction to many of these topics as well.

craigkerstiens
Thanks for the mention, I also have an article that walks through not what I'd call advanced but it's at least intermediate SQL here as well - https://www.citusdata.com/blog/2016/09/12/fun-with-sql-compu...

Whenever I do come across blogs that have good articles we aim to feature them in Postgres Weekly, so if you want a regular stream of that type of content it's worth checking out - http://www.postgresweekly.com

Dowwie
The Postgres Weekly newsletter is a really valuable service. You not only aggregate information but filter for quality. Thank you for providing this! I've said this before but it warrants re-mentioning :)
agentgt
Lukas Eder has a fantastic blog about SQL, Java, FP, and his library jOOQ: https://blog.jooq.org/.

One of the things I sometimes find tricky with SQL is pattern matching a problem to a solution. It can be tricky to describe what you want and sometimes direct human help can't be beat so I do recommend Stackoverflow (normally I have mixed feelings about SO). There are few power users on SO like Craig Ringer and the horsesomethingsomething (can't recall the actual handle) that are helpful and friendly.

pmtarantino
I dont know if this is the right place to ask it, but I will go:

Sometimes I see job offers that ask for experience with large websites, large databases, large servers, etc, etc, etc (you get the idea).

How do you land those jobs if getting in that kind of subject is impossible alone? You can't simulate that kind of things at your home, so unless your side project grow and you must learnt it the hard way, or you had luck to be at a company where they allowed you to be involved, how do you learn that? Thank you

agotterer
You don't need to work for a company with large datasets to start learning and playing with large datasets. There are freely available repositories of data you can play with. Checkout the AWS public datasets - https://aws.amazon.com/public-datasets/. There should be more then enough there for you to start playing with "big data" tools and getting familiar with how to store, ETL, and query large datasets.
pmtarantino
Yes, but when a company asks for experience they aren't thinking in playing with large datasets unless you create an open source tool that becomes useful for a lot of people. Just playing with them wouldn't be suffice.
tacostakohashi
A "large dataset" is basically one that doesn't fit in memory. It doesn't need to be large in absolute terms.
aargh_aargh
Any great resources explaining normalization, ideally with real-world examples of when to stop normalizing?
garysieling
For examples of not normalizing, I would look at books on data warehousing
VLM
The thing about advanced is I keep learning there's further levels of advanced.
jeffwass
I learned in college that the words "Introductory" and "Advanced", when appearing in technical book titles, often don't mean what you think.
JoeDaDude
Gamify! Play the SQL game, The Schemaverse [1]. I don't know if this matches the request for "more advanced queries", but it is entertaining.

[1] https://schemaverse.com/

abstrct
With the ability to use pl/pgsql within the game, it can get as complicated as you want :)

It doesn't necessarily teach each advanced technique, but it does give you a good place to practice what you've learned.

(Thanks for the mention!)