Build an org chart with hierarchyid, query descendants and levels, move a team safely, and compare it with recursive queries.
Translating Oracle CONNECT BY Queries to Recursive CTEs
Translate CONNECT BY into recursive CTEs with root selection, depth, paths, sibling ordering, cycle checks, and a recursion limit.
Common Table Expressions Quiz: Why Did My Second Query Fail?
This Common Table Expressions Quiz defines one CTE, reads it once, and then reads it again. The answer is error 208, because a CTE lives for one statement only. A tested script shows the error, the missing semicolon trap and the recursion limit.
WHERE Clause Quiz: Why Can’t It See My Column Alias?
This WHERE Clause Quiz asks what happens when a query filters on a column alias. The answer is error 207, because WHERE runs before SELECT. A tested script shows three working fixes and a trap where an alias hides a real column.
Common Table Expressions for Readability
Common table expressions for readability name query steps and support recursion, but they do not promise cached intermediate results.
A Bill of Materials With a Recursive CTE
Expand a bill of materials with a recursive CTE, multiply quantities across levels, trace component paths, and guard against cycles.
Derived Table, CTE or View: Choose the Scope
A derived table, CTE and view can express the same grouped result while offering different scopes. Compare complete outputs with duplicates and NULLs, then choose a statement-local expression or a saved definition for the task.