Top 5 Free Resources for Learning Advanced SQL Techniques

Image by Author

The internet is brimming with resources for learning SQL. Most of them, of course, require paying for knowledge. These resources also mainly teach you basic or, at best, intermediate SQL.

The question is, how to learn SQL and do it for free. Are there any resources at all that will teach you advanced SQL without paying?

Yes, there are. In fact, there are at least five, as I discovered.

Before looking for the courses, I had to define what advanced SQL means.

What is Advanced SQL?

It’s not easy to pin this down, as different experts have different views (and teaching experience) on which SQL concepts can be considered advanced.

However, I think it’s safe to say that these topics are advanced SQL:

• JOINs
• CTEs
• Recursive CTEs
• Subqueries and Correlated Subqueries
• Window Functions
• Pivoting and Unpivoting Data
• Hierarchical Queries
• Views
• Stored & User-Defined Procedures and Functions
• Triggers
• Temporary Tables
• Indexes
• Data Normalization
• Performance Optimization

The list is incomplete, however. Some other topics that also belong to advanced SQL can be added. How can you say if they’re advanced? I have a highly unscientific but efficient method: if you don’t know what a particular topic means, it’s most likely advanced SQL.

Free Resources for Advanced SQL

Image by author

The resources I list here cover some or most of the advanced SQL concepts I mentioned earlier. If you combine these resources, your advanced SQL knowledge can become even more wholesome. Yes, I know it requires a bit more effort, but hey, these are all free. What did you expect?

1. Mode SQL Tutorial

Link to resource: Mode Analytics SQL Tutorial

Description: This tutorial teaches you advanced SQL in the Mode BI tool. No need to worry that you’ll have to learn yet another SQL flavor; Mode uses standard SQL.

All the concepts are explained theoretically in detail. In each lesson, there are also interactive exercises where you can practice solving problems by writing SQL queries and check the answers.

Speaking of concepts, you will learn about subqueries, window functions, tuning SQL queries, and pivoting data. In addition, there are lessons about data types, date formats, and string functions.

If you feel you’re struggling with some of the less advanced topics in these lessons, you can always revisit them in the Basic SQL and Intermediate SQL sections of the tutorial.

2. StanfordOnline: Databases: Advanced Topics in SQL

Link to resource: edX

Description: This course is one of five courses on databases offered by Stanford University and hosted by edX.

Of the advanced topics, you will be taught indexing to improve query performance, transactions, database constraints and triggers, and views.

It will take you two weeks to complete the course, with 8-10 hours per week.

In addition, you can take courses on OLAP and recursion, modeling and theory, and semistructured data if you’re interested in these topics.

3. Kaggle Advanced SQL

Link to resource: Kaggle Advanced SQL

Description: Kaggle’s cute little course will take only four hours to complete. It covers JOINs and UNIONs, analytic (or window) functions, nested and repeated data, and tips on writing efficient queries.

Each lesson provides detailed theoretical explanations complemented by practical examples. At the end of each lesson, you will find a link to a section that tests your understanding by giving you several exercises to solve by writing code.

4. Advanced SQL (Summer 2020)

Link to resource: YouTube

Description: This video course consists of lectures by the Database Research Group at the University of Tübingen, Germany.

There are 58 videos, most of them around a half-hour. They cover topics such as subqueries, set operations, GROUP BY extensions, data types, arrays, window functions, user-defined functions, recursive CTEs, and many, many more.

5. SQL for Web Nerds

Link to resource: Philip Greenspun’s personal website

Description: This online book was written by Philip Greenspun, a US computer scientist and educator.

It combines theoretical and practical explanations through code and real-world examples. Most examples use the data model for web services and are written in Oracle SQL dialect.

This resource will teach you triggers, views, trees, database tunings, and many more.

Bonus: Almost Free Resources

As a bonus, I wanted to add two almost free resources: StrataScratch (yes, the platform I founded) and LeetCode, two platforms with actual SQL coding interview questions.

Almost free, as in very cheap? No, free in a way that they require a paid subscription to access all questions but also have a substantial number of freemium questions.

We have 75 free questions at StrataScratch, most of them of medium or hard difficulty. You’ll definitely find a solid number of them to test your advanced SQL knowledge. To make searching easier for you, there’s a detailed filter where you can filter by topic, difficulty, and company, among other things.

There’s also an interactive code editor for writing your solution in several SQL flavors (PostgreSQL, SQL Server, MySQL, and Oracle) and validating it. All the questions have hints and official solutions with detailed explanations.

LeetCode has the same number of questions if I counted correctly. However, they are, in most cases, categorized as easy or medium, which could make looking for advanced topics a little trickier. Nevertheless, I’m sure you’ll be able to find some good questions for practicing the topics I mentioned at the beginning of this article. Like StrataScratch, LeetCode also has detailed solutions and an interactive code editor allowing code-writing in several SQL dialects.

Conclusion

There it is – five resources that will teach you advanced SQL techniques and do it for free. They are also quality resources, which makes for better learning.

Three resources are classic online SQL courses that cover different advanced topics. I threw in the mix something different, too: one YouTube course by a top German university and one online book by a recognized expert and educator.

Some of the topics covered in these resources overlap, but some are unique to each resource. If you don’t find what you’re looking for in one course, you can always combine the resources and fill in the gaps.

As a bonus, there are two interview question platforms, StrataScratch and LeetCode, that offer some questions for free.

I hope you’ll find all those a valuable resource for learning advanced SQL. Happy learning!

Nate Rosidi is a data scientist and in product strategy. He’s also an adjunct professor teaching analytics, and is the founder of StrataScratch, a platform helping data scientists prepare for their interviews with real interview questions from top companies. Nate writes on the latest trends in the career market, gives interview advice, shares data science projects, and covers everything SQL.