You can now filter, join, aggregate, and package logic inside the database. This module covers the techniques that solve the harder problems, starting with recursive CTEs, which let a query refer to itself so you can walk hierarchies such as org charts, category trees, and folder structures.
You'll then learn to pivot rows into columns for reporting, work with JSON data stored inside relational tables, and build queries at run time with dynamic SQL. Finally you'll look at cursors, and why set-based SQL is usually the better choice, and at table partitioning for keeping very large tables manageable.
Syntax for these features varies a lot between databases, so focus on the ideas and check your own database's documentation for the exact form. Work through the lessons in order, then continue on to Security & Administration.
How a recursive CTE works, with an anchor member and a recursive member, and how to use it on hierarchical data.
Turning rows into columns, and back again, to reshape data for reports and analysis.
Parsing and querying JSON stored in SQL, from simple objects to nested data using OPENJSON and CROSS APPLY.
Building SQL statements as strings at run time, and how to do it safely so it doesn't open the door to SQL injection.
How to create a cursor, how it processes rows one at a time, and when it is (and isn't) the right tool.
A visual explanation of how splitting a large table into partitions improves performance and manageability.