Where Can I Find Good SQL CTE Exercises?

Find the top online resources for SQL Common Table Expression (CTE) exercises! CTEs allow you to structure and organize SQL queries efficiently, which is essential if you want to advance your SQL knowledge.

A Common Table Expression, or CTE, is a SQL syntax that creates a temporary data set. This set contains separate data than the main query, which can typically be referenced or reused in a subsequent query. A CTE is considered temporary because the result is not permanently stored anywhere and only exists for the duration of the query. They are often considered a neat alternative to using temporary tables.

SQL CTEs, also referred to as WITH queries, have been part of standard SQL for some time now and are available in all major RDBMSs. For a more detailed overview of CTEs, see the article What Is a CTE?

CTEs can be recursive, which allows you to traverse hierarchical models of great complexity. Additionally – as you can with subqueries – you can break down a long query into smaller blocks with CTEs. But unlike subqueries, CTEs allow you to re-use the same data set over and over again in your code! Thus, SQL developers generally use CTEs to build hierarchical queries and refactor and effectively organize SQL queries for improved performance as well as better readability.

A few CTE use cases include:

• Creating an alternate view in a database.
• Referencing a derived table multiple times in a single query.
• Performing the same calculation multiple times over across multiple queries.

Our article When Should I Use a CTE? runs through a few more use cases that will help you wrap your head around CTEs’ unique application areas.

Top Online SQL CTE Exercises

Like most other aspects of SQL, practice is key to mastering CTEs – especially for long-term knowledge retention and incorporating them into your daily work. To help you accomplish that, an exercise must use incrementally more complex topics and test you through hands-on problems that you must solve for yourself.  

Below are three top online resources that will help you get up to speed on SQL CTEs.

LearnSQL.com: Recursive Queries

The Recursive Queries course at LearnSQL.com covers over 18 hours of content and encompasses the whole gamut of topics within SQL CTEs, including:

• CTE basics and s
• Organizing long queries using simple and nested CTEs.
• How and when to use nested CTEs.
• How and when to use recursive CTEs, including how to use them with hierarchical data models.
• Using CTEs with INSERT, DELETE, and

Additionally, the final part of this course is a quiz that will test your knowledge on all the above topics.

This course is intended for users with intermediate SQL knowledge. You should be familiar with basic SQL functionalities, such as selecting data from one or more tables, subqueries, WHERE conditions, GROUP BY, and HAVING.

This course contains 114 unique exercises, which are based on realistic business data sets. They’ll help you pace yourself and test your understanding and appreciation of the in-depth course content. The interactive online learning platform allows you to verify exercise solutions and offers helpful prompts if you need them.

DataCamp: Hierarchical and Recursive Queries in SQL Server

DataCamp’s Hierarchical and Recursive Queries in SQL Server provides an in-depth look at CTEs. In this course, you will have access to the following topics:

• Introduction to CTEs and r
• Hierarchical and recursive q
• Creating and modifying database tables.
• The functionality of and differences between relational and hierarchical data models.
• Application of hierarchical queries to real-world examples, including flight route analysis, car assembly, and power grid modeling.

Based on the course outline, I’d say the content coverage is quite extensive, but there is only 4 hours of instructional content. Compare this to LearnSQL’s  18 hours; to me, that suggests there may be some lack of depth in each topic. Additionally, DataCamp provides 47 practice exercises – less than LearnSQL.com, but still enough to build some confidence. This course also has a series of prerequisites that you may want to check out before starting.

Udemy: Hierarchical and Recursive SQL

The Hierarchical and Recursive SQL course at Udemy is a  much more condensed overview of CTEs. In terms of content, it covers:

• Introduction to CTEs.
• Utilizing mathematical functions to demonstrate recursive CTEs.

At only about an hour of instructional content, this course  provides a very high-level overview of CTEs and recursive functions in the Oracle framework. Like many Udemy courses, this one isn’t really interactive either; its assignments are geared more towards self-study. Hence, the course does somewhat fall short in content and exercise quality compared to both the DataCamp and LearnSQL.com offerings. That being said, this course may be helpful if a compact and basic introduction to CTE is what you are looking for. 

Now You Can Practice CTEs

There you go. Above are three vetted options to learn and master SQL CTEs and recursive queries. As you may have noticed, there isn’t really a large selection of online learning resources for this knowledge domain. Overall, LearnSQL.com’s Recursive Queries course is more extensive and more immersive than Udemy and (arguably) DataCamp. The unique selling point for this course has consistently been its interactive learning environment.