common table expressions
TRANSCRIPT
*Property of STI K0019
Common Table Expressions
What is Common Table Expressions? Using Common Table Expressions Application of Common Table Expressions What is Recursive Common Table Expressions? Restrictions on Recursive Common Table
Expressions
*Property of STI K0019
What is Common Table Expressions?
Common table expressions (CTE) are temporary result set that are known only within the scope of a single SELECT, INSERT, UPDATE, DELETE or CREATE VIEW statement.
Common table expressions are generally useful in a query that involves multiple aggregate functions.
*Property of STI K0019
Using Common Table Expressions
The common table expressions are defined using the WITH clause.
Example 1:WITH CountEmployees(dept_id, n) AS
( SELECT dept_id, count(*) AS n
FROM employee GROUP BY dept_id )
SELECT dept_id, n
FROM CountEmployees
WHERE n = ( SELECT max(n)
FROM CountEmployees )
*Property of STI K0019
Using Common Table Expressions
Example 2: WITH CountEmployees(dept_id, n) AS
( SELECT dept_id, count(*) AS n
FROM employee GROUP BY dept_id )
SELECT a.dept_id, a.n, b.dept_id, b.n
FROM CountEmployees AS a JOIN CountEmployees AS b
ON a.n = b.n AND a.dept_id < b.dept_id
*Property of STI K0019
Using Common Table Expressions
Example 3: WITH
CountEmployees(dept_id, n) AS
( SELECT dept_id, count(*) AS n
FROM employee GROUP BY dept_id ),
DeptPayroll( dept_id, amt ) AS
( SELECT dept_id, sum(salary) AS amt
FROM employee GROUP BY dept_id )
SELECT count.dept_id, count.n, pay.amt
FROM CountEmployees AS count JOIN DeptPayroll AS pay
ON count.dept_id = pay.dept_id
WHERE count.n = ( SELECT max(n) FROM CountEmployees )
OR pay.amt = ( SELECT min(amt) FROM DeptPayroll )
*Property of STI K0019
Exercise
Assume that you need to determine which class has the most number of students. The student table lists all the students and specifies in which class each belong. Using common table expressions, find the following:
1. Extract the class with the most students. Extract the class with the fewest students.
2. List the class that has the highest GPA of students.
*Property of STI K0019
Applications of Common Table Expressions
Common table expressions are useful whenever multiple levels of aggregation must occur within a single query.
Views within a procedure that must contain a reference to a program variable.
Queries that use temporary result set to store a set of values.
*Property of STI K0019
What is Recursive Common Table Expressions?
Recursive common table expressions allow you to query tables that represent hierarchical information.
A recursive common table expression is composed of an initial subquery or seed and a recursive subquery.
*Property of STI K0019
Example
WITH RECURSIVEmanager ( emp_id, manager_id,emp_fname, emp_lname, mgmt_level ) AS( ( SELECT emp_id, manager_id, -- initial subqueryemp_fname, emp_lname, 0FROM employee AS eWHERE manager_id = emp_id )UNION ALL( SELECT e.emp_id, e.manager_id, -- recursive subquerye.emp_fname, e.emp_lname, m.mgmt_level + 1FROM employee AS e JOIN manager AS mON e.manager_id = m.emp_idAND e.manager_id <> e.emp_idAND m.mgmt_level < 20 ) )SELECT * FROM managerORDER BY mgmt_level, emp_lname, emp_fname
*Property of STI K0019
Restrictions on Recursive Common Table Expression
Recursive common table expressions cannot be mutually recursive.
The only set operator permitted between the initial subquery and the recursive subquery is UNION ALL.
Within the definition of a recursive subquery, a self-reference to the recursive table expression can appear only within the FROM clause of the recursive subquery.
*Property of STI K0019
Restrictions on Recursive Common Table Expression
The recursive subquery cannot contain DISTINCT, or a GROUP BY or an ORDER BY clause.
The recursive subquery can not make use of any aggregate function.
To prevent runaway recursive queries, an error is generated if the number of levels of recursion exceeds the current setting of the MAX_RECURSIVE_ITERATIONS option.
*Property of STI K0019
Exercise
Using the recursive common table expression, write a query that displays the Fibonacci sequence.
TIP: The Fibonacci sequence is the sequence in which each number is the sum of the two preceding numbers such as 1, 1, 2, 3, 5, 8, 13, 21, 34, 55, 89, 144, 233, 377, 610, 987, 1597, 2584, 4181, ... (each number is the sum of the previous two). The Fibonacci sequence, generated by the rule f1 = f2 = 1 , fn+1 = fn + fn-1, is well known in many different areas of mathematics and science.