Back to articles list January 21, 2021 - 5 minutes read Where Can I Find Good SQL CTE Exercises? Zahin Rahman Nuclear Engineer by day and Data Scientist by night, Zahin is passionate about driving business with data. He has 5+ years of industry experience in energy and power generation and has a master’s degree in Engineering with a focus in Data Science. He loves learning, whether from an industry veteran or a high school science enthusiast. Tags: sql learn sql CTE online practice 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. Tags: sql learn sql CTE online practice You may also like What Is a CTE? We explain what CTE is and how to use common table expressions in SQL scripts. Read more Long SQL Query vs. Recursive SQL Query Explore recursive CTE hierarchy in SQL server and find out how to tune your SQL query performance. Read more How to Organize SQL Queries with CTEs Common table expressions (CTEs) allow you to structure and organize your SQL queries. It is a necessity when you begin to move deeper into SQL. Read more Simplify SQL Code: Recursive Queries in DBMS Are you repeating the same query in every report? Are they getting too complicated? Use recursive queries to simplify SQL code! Read more How to Draw a Christmas Tree in SQL Learn how to use SQL to manipulate all kinds of data, from huge analytical queries to brief single-purpose statements. Read more Improving Query Readability with Common Table Expressions What is a Common Table Expression, or CTE? Where do you use them, and why? This post answers your questions. Read more How to Organize SQL Queries When They Get Long Long queries are very hard for beginners to structure and understand. Learn the best practices for writing and formatting complex SQL code! Read more Subquery vs. CTE: A SQL Primer SQL subqueries and CTEs seem similar, but even pros may not know all their quirks. Learn their similarities, differences, and best use cases. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.