Top 5 Free Resources to Learn Advanced SQL Techniques

Image by the author

The Internet is full of resources for learning SQL. Most of them, of course, require payment to obtain the knowledge. These resources also mainly teach basic or at best intermediate SQL.

The thing is, How to learn SQL and do it for free. Is there any resource that teaches you advanced SQL without paying?

Yes, there are. In fact, I found out there are at least five.

Before searching for 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 what SQL concepts can be considered advanced.

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

• UNE
• CTEs
• Recursive CTEs
• Subqueries and correlated subqueries
• Window Functions
• Pivoting and unpivoting data
• Hierarchical queries
• Views
• Stored and user-defined procedures and functions
• Triggers
• Temporary tables
• Indexes
• Data normalization
• Performance Optimization

However, the list is incomplete. Other topics can be added that also belong to advanced SQL. How can you tell if they are advanced? I have a very unscientific but efficient method: if you don't know what a particular topic means, it is most likely advanced SQL.

Free resources for advanced SQL

Image by the author

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

1. Tutorial on SQL mode

Link to resource: SQL Mode Analysis Tutorial

Description: This tutorial teaches you advanced SQL in the Mode BI tool. Don't worry about having to learn another version of SQL; Mode uses standard SQL.

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

Speaking of concepts, you'll learn about subqueries, window functions, SQL query tuning, and data pivoting. Additionally, there are lessons on data types, date formats, and string functions.

If you feel like 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 database courses offered by Stanford University and hosted by edX.

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

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

In addition, you will be able to take courses on OLAP and recursion, modeling and theoryand semi-structured data If you are interested in these topics.

3. Kaggle Advanced SQL

Link to resource: Kaggle Advanced SQL

Description: This short, sweet Kaggle course takes just four hours to complete. It covers JOIN and UNION functions, analytic (or window) functions, nested and repeated data, and offers tips on how to write efficient queries.

Each lesson offers 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 presenting you with various exercises to solve by writing code.

4. Advanced SQL (Summer 2020)

Link to resource: YouTube

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

There are 58 videos, most of them around half an hour long. 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 Experts

Link to resource: Philip Greenspun's personal website

Description: This online book was written by Philip Greenspunan American 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 the Oracle SQL dialect.

This resource will teach you about triggers, views, trees, database tuning, and much 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 real SQL coding interview questions.

Almost free, meaning very cheap. No, free in the sense that they require a paid subscription to access all the questions, but they also have a substantial amount of freemium questions.

Have 75 free questions on StrataScratchMost of them are of medium or high difficulty. You are sure to find a good number of them to test your advanced SQL knowledge. To make your search easier, there is a detailed filter where you can filter by topic, difficulty and company, among other things.

There is also an interactive code editor to write your solution in various SQL versions (PostgreSQL, SQL Server, MySQL and Oracle) and validate it. All questions have hints and official solutions with detailed explanations.

Leet Code It has the same number of questions if I counted them correctly. However, in most cases they are categorized as easy or intermediate, which can make searching for advanced topics a bit more difficult. Nevertheless, I am sure you will be able to find some good questions to practice the topics I mentioned at the beginning of this article. Like StrataScratch, LeetCode also has detailed solutions and an interactive code editor that allows you to write code in various SQL dialects.

Conclusion

Here are five resources that will teach you advanced SQL techniques, and they will do so for free. Plus, they are quality resources, which makes learning easy.

All three resources are classic online SQL courses covering different advanced topics. I also included something different: a YouTube course taught by a top German university and an online book written by a renowned expert and educator.

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

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

I hope you find this a valuable resource for learning advanced SQL. Happy learning!

twitter.com/StrataScratch” rel=”noopener”>twitter.com/StrataScratch” target=”_blank” rel=”noopener noreferrer”>Nate Rosidi Nate is a data scientist and product strategy specialist. He is also an adjunct professor of analytics and is the founder of StrataScratch, a platform that helps data scientists prepare for their interviews with real interview questions from top companies. Nate writes about the latest trends in the job market, provides interview tips, shares data science projects, and covers all things SQL.