Course of the Month: Recursive Queries in SQL Server

Do you want to become a SQL expert? To do this, you need to learn SQL CTEs or Common Table Expressions. They can make your queries clearer and more readable. And during July 2021, you can learn CTEs for free using our interactive Recursive Queries in MS SQL Server course!

How can you get this free SQL course?

1. Create a free LearnSQL.com account or log in if you already have one.
2. Start the course. You will have free access to the course content until the end of the month.

What Are CTEs?

Common Table Expressions (CTEs) are one of SQL’s advanced features. They’re worth knowing because they make writing long, complex SQL queries easier. By allowing us to define a temporary named result set that can be used for the rest of the query, it makes the whole query more readable and understandable.

Because Common Table Expressions start with the WITH keyword, they are often called WITH queries. It's good to remember this – it is a common question during job interviews.

CTEs are a relatively new feature of SQL. They first appeared in the SQL: 1999 standard. MS SQL Server users had to wait until 2005 to be able to use this great tool. CTEs became available in other SQL dialects, including PostgreSQL in 2009 and MySQL in 2018.

What Do CTEs Do?

Common Table Expressions let you define a result set that you can refer to later in the query. Let's say you are creating a complex report and you need to apply multiple levels of aggregation with a lot of intermediate steps. This is where CTEs really help; they can save you from getting bogged down in endless lines of code.

If you want to know more about how to use CTEs, I recommend reading SQL CTEs Explained, What are SQL CTE Best Practices?, SQL CTEs Explained with Examples, and How CTEs Work.

At LearnSQL.com, we like CTEs a lot – especially in their most impressive and spectacular version: recursive queries.

What Are Recursive CTEs?

Recursive queries are designed for analyzing hierarchical data such as trees or graphs. For example, imagine that your company gets a product from a supplier, who gets it from another supplier, who gets it from another supplier, and so on. At the end of this long supply chain is the product manufacturer. Recursive queries let you analyze these structures easily. Other examples of hierarchical structures include:

• Files and directories in a file system.
• Product categories and subcategories in a store.
• Road or tram connections that form a graph.

All of these, and lots more, can be analyzed with recursive queries.

If you work with MS SQL Server, you should take this opportunity to do our Recursive Queries in MS SQL Server course. You’ll be able to apply this knowledge immediately in your projects. If you work in other dialects, such as PostgreSQL or MySQL, you can also take the course. The concepts and general structures are the same, and you can check the minor differences in syntax in your dialect’s documentation.

What Will You Learn from This Free SQL Course?

The course consists of 112 interactive exercises you can do at your own pace, from anywhere, at any time. It is divided into six parts. Completing all of them should take you about 18 hours.

First, you will learn WITH syntax and simple Common Table Expressions. Then it's time for nested CTEs, which let you create complex reports and calculate statistics using multi-level aggregates. You will also see how to properly organize and simplify your queries.

Next, you’ll learn what recursive queries are and when to use them. After completing the course, you’ll be able to write recursive queries on your own. There are lots of examples, and you can practice writing recursive queries on different data sets. This is the most challenging part of the entire course.

Finally, you’ll explore how CTEs can be used in INSERT, UPDATE and DELETE statements.

This course, like all SQL courses on our platform, is interactive; you can immediately test what you have learned in practice. We believe that only practical exercises allow you to master a given skill. Therefore, you’ll write real SQL queries in our online console and immediately see how they work. If you make a mistake, you’ll get instant feedback. And support is available through hints and our discussion panel.

It’s important to realize that WITH syntax is usually not taught in online SQL courses. But we think it’s important to learn, so we added it to our course list.

You want more SQL? The Recursive Queries in MS SQL Server course is part of our comprehensive SQL from A to Z in MS SQL Server track. There, you will find everything you need to become fluent in MS SQL.

Who Should Take This Course?

We have created the course mainly for analysts and developers who want to expand their MS SQL Server skills. It will also be useful for marketers, accountants, and others who work with data. Are you a student preparing for the exam in computer science or relational databases? Are you applying for an MS SQL Server job or preparing for an interview? This is the course for you!

This course is intended for intermediate users. We assume the user knows the basics of T-SQL, including:

• How to SELECT from a single table, including writing complex WHERE conditions.
• How to JOIN tables.
• How GROUP BY and HAVING work.
• How T-SQL subqueries work.

If you don't know the above areas, start your learning journey with the SQL Basics in MS SQL Server course. Once you’re comfortable with these concepts, you can try your hand at CTEs.

Free SQL Course for July: Recursive Queries in MS SQL Server

Interested in learning CTEs in MS SQL Server? Great! Our Recursive Queries in MS SQL Server course is free throughout July. You can learn without paying anything.

After completing the course, you’ll receive a personal certificate of completion that you’ll be able to post on your LinkedIn profile or show to your boss. (Maybe while asking for a raise?)

If you’re hesitating to learn Microsoft’s database solution, check out Is MS SQL Server Still Worth Learning in 2021?  Our guide How to Install Microsoft SQL Server 2019 and SQL Server Management Studio will help you get set up so you can use their power in your personal projects.

Start learning today!