Authorization: SQL Recursion | SQL Recursion Homework Help | SQL Recursion Assignment Help | Recursion In Sql | SQL Recursion Tutor

Recursion In Sql

SQL: 1999 permit recursive view definition.

Example: Find all employeemanager pairs, where the employee reports to the manager directly or indirectly.

WITH RECURSIVE empl(employee_name, manager_name) AS ( 

SELECT employee_name, manager_name

FROM manager

UNION

SELECT manager.employee_name, empl.manager_name

FROM manager, empl

WHERE manager.manager_name = empl.employee_name)

SELECT *

FROM empl

This example view, empl, is called the transitive closure of the manager relation.

The Power Of Recursion

Recursive views make it possible to write queries, such as transitive closure queries, that cannot be written without recursion or iteration.
Intuition: Without recursion, a non¬recursive non¬iterative program can perform only a fixed number of joins of manager with itself
This can give only a fixed number of levels of managers
Given a program we can construct a database with a greater number of levels of managers on which the program will not work
Computing transitive closure
The next slide shows a manager relation
Each step of the iterative process constructs an extended version of empl from its recursive definition.
The final result is called the fixed point of the recursive view definition.
Recursive views are required to be monotonic. That is, if we add tuples to manager the view contains all of the tuples it contained before, plus possibly more.

Authorization SQL Recursion Homework Help

Topics in database

Not the Exact Question you were looking for ? Post your question for assignment help and get instant help on your homework and assignment questions from our experts