Recursive Queries in PostgreSQL

Table of contents

1. 1. Intro Quiz

   0/14

   Test your knowledge of PostgreSQL

   List of exercises Start now
   1. Introduction
      1. Welcome
      2. Recipe table
      3. Ingredient table
      4. recipe_ingredients table
   2. Quiz
      1. Exercise 1
      2. Exercise 2
      3. Exercise 3
      4. Exercise 4
      5. Exercise 5
      6. Exercise 6
      7. Exercise 7
      8. Exercise 8
      9. Exercise 9
   3. Congratulations
      1. Congratulations

2. 2. CTE syntax

   0/17

   Your first encounter with Common Table Expressions

   List of exercises Buy course
   1. Introduction
      1. Welcome
      2. Supporter table
      3. Project table
      4. Donation table
   2. Basic CTE
      1. Basic syntax
      2. Basic syntax – exercise
      3. Syntax with columns
      4. Syntax with columns – exercise
      5. Multiple CTEs
      6. Multiple CTEs – practice 1
      7. Multiple CTEs – practice 2
      8. CTE vs. subquery
      9. CTE vs. subquery – exercise
   3. Summary
      1. Summary
      2. Exercise 1
      3. Exercise 2
      4. Congratulations

3. 3. Nested CTEs

   0/15

   You'll get to know how to use nested CTEs

   List of exercises Buy course
   1. Introduction
      1. Welcome
      2. Salesman table
      3. Daily_sales table
      4. City table
   2. Nested CTEs
      1. Simple nested CTEs – explanation
      2. Simple nested CTEs – practice 1
      3. Nested CTE with subqueries – explanation
      4. Nested CTEs in complex queries – exercise 1
      5. Nested CTEs in complex queries – exercise 2
      6. Nested CTEs in complex queries – exercise 3
      7. Nested CTEs in complex queries – exercise 4
   3. Summary
      1. Summary
      2. Exercise 1
      3. Exercise 2
      4. Congratulations

4. 4. Recursive CTEs

   0/36

   You'll find out what recursion is and how to use it in SQL queries

   List of exercises Buy course
   1. Introduction
      1. Welcome
      2. Agenda
   2. The very basics
      1. Recursion explained: real world examples
      2. Recursion explained: computer science
      3. Recursion explained: syntax 1
      4. Recursion explained: syntax 2
      5. Recursion explained: number of columns
      6. Basic recursion: exercise
      7. Multiple CTEs
   3. Infinite looping
      1. Infinite queries
      2. LIMIT keyword
   4. Processing trees with recursive CTEs
      1. Trees in computer science
      2. Trees in computer science – exercise
      3. Hierarchical company 1
      4. Hierarchical company 2
      5. Hierarchical company 3
      6. Common recursive CTEs error
      7. Cast the type with recursive CTEs
      8. Hierarchical company – exercise 1
      9. Hierarchical company – exercise 2
      10. Hierarchical company – exercise 3
      11. Additional exercise – new table
      12. Additional exercise – trees
   5. Processing graphs with recursive CTEs
      1. Graphs in computer science
      2. Graphs – real life example
      3. Traveling salesman 1
      4. Traveling salesman 2
      5. Traveling salesman 3
      6. Traveling salesman – exercise 1
      7. Traveling salesman – exercise 2
      8. Graphs – additional exercise
   6. Summary
      1. Revision
      2. Exercise 1
      3. Exercise 2
      4. Exercise 3
      5. Congratulations

5. 5. Using CTEs with INSERT, DELETE, and UPDATE

   0/18

   You'll learn how to use INSERT, DELETE, and UPDATE statements with CTEs.

   List of exercises Buy course
   1. Introduction
      1. Welcome
      2. Grocery_store table
      3. Product table
      4. Customer table
      5. Customer_order table
   2. Using CTEs with data-modifying statements
      1. INSERT statement
      2. CTEs with INSERT statement 1
      3. CTEs with INSERT statement 2
      4. UPDATE statement
      5. CTEs with UPDATE statement
      6. DELETE statement
      7. CTEs with DELETE statement
      8. Nested CTEs with data modifying statements
   3. Summary
      1. Summary
      2. Exercise 1
      3. Exercise 2
      4. Exercise 3
      5. Congratulations

6. 6. Final Quiz

   0/14

   Test your knowledge in this final quiz.

   List of exercises Buy course
   1. Introduction
      1. Welcome
      2. Project table
      3. Employee table
      4. Allocation table
   2. Quiz
      1. Single CTE
      2. Multiple CTEs
      3. Nested CTEs
      4. Nested CTEs with a subquery
      5. Simple recursive CTE
      6. Complex recursive CTE – trees
      7. Complex recursive CTE – graphs
      8. UPDATE with CTE
      9. INSERT with CTE
   3. Summary
      1. Congratulations

Get the Certificate of Competency in SQL

This certificate will confirm your practical SQL problem-solving skills

Obtain the Certificate Learn more