This blog post is inspired from SQL Queries Joes 2 Pros: SQL Query Techniques For Microsoft SQL Server 2008 – SQL Exam Prep Series 70-433 – Volume 2.[Amazon] | [Flipkart] | [Kindle] | [IndiaPlaza]
This is follow up blog post of my earlier blog post on the same subject - SQL SERVER – Introduction to Hierarchical Query using a Recursive CTE – A Primer. In the article we discussed various basics terminology of the CTE. The article further covers following important concepts of common table expression.
What is a Common Table Expression (CTE)
Building a Recursive CTE
Identify the Anchor and Recursive Query
Add the Anchor and Recursive query to a CTE
Add an expression to track hierarchical level
Add a self-referencing INNER JOIN statement
Above six are the most important concepts related to CTE and SQL Server. There are many more things one has to learn but without beginners fundamentals one can’t learn the advanced concepts. Let us have small quiz and check how many of you get the fundamentals right.
Quiz
1) You have an employee table with the following data.
EmpID
FirstName
LastName
MgrID
1
David
Kennson
11
2
Eric
Bender
11
3
Lisa
Kendall
4
4
David
Lonning
11
5
John
Marshbank
4
6
James
Newton
3
7
Sally
Smith
NULL
You need to write a recursive CTE that shows the EmpID, FirstName, LastName, MgrID, and employee level. The CEO should be listed at Level 1. All people who work for the CEO will be listed at Level 2. All of the people who work for those people will be listed at Level 3. Which CTE code will achieve this result?
WITH EmpList AS
(SELECT Boss.EmpID, Boss.FName, Boss.LName, Boss.MgrID,
1 AS Lvl
FROM Employee AS Boss WHERE Boss.MgrID IS NULL
UNION ALL
SELECT E.EmpID, E.FirstName, E.LastName, E.MgrID, EmpList.Lvl + 1
FROM Employee AS E INNER JOIN EmpList
ON E.MgrID = EmpList.EmpID)
SELECT * FROM EmpList
WITH EmpListAS
(SELECT EmpID, FirstName, LastName, MgrID, 1 as Lvl
FROM Employee WHERE MgrID IS NULL
UNION ALL
SELECT EmpID, FirstName, LastName, MgrID, 2 as Lvl )
SELECT * FROM BossList
WITH EmpList AS
(SELECT EmpID, FirstName, LastName, MgrID, 1 as Lvl
FROM Employee WHERE MgrID is NOT NULL
UNION
SELECT EmpID, FirstName, LastName, MgrID, BossList.Lvl + 1
FROM Employee INNER JOIN EmpList BossList
ON Employee.MgrID = BossList.EmpID)
SELECT * FROM EmpList
2) You have a table named Employee. The EmployeeID of each employee’s manager is in the ManagerID column. You need to write a recursive query that produces a list of employees and their manager. The query must also include the employee’s level in the hierarchy. You write the following code segment:
WITH EmployeeList (EmployeeID, FullName, ManagerName, Level)
AS (
–PICK ANSWER CODE HERE
)
SELECT EmployeeID, FullName, ” AS [ManagerID], 1 AS [Level]
FROM Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT emp.EmployeeID, emp.FullName mgr.FullName, 1 + 1 AS [Level]
FROM Employee emp JOIN Employee mgr
ON emp.ManagerID = mgr.EmployeeId
SELECT EmployeeID, FullName, ” AS [ManagerID], 1 AS [Level]
FROM Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT emp.EmployeeID, emp.FullName, mgr.FullName, mgr.Level + 1
FROM EmployeeList mgr JOIN Employee emp
ON emp.ManagerID = mgr.EmployeeId
Now make sure that you write down all the answers on the piece of paper.
Watch following video and read earlier article over here. If you want to change the answer you still have chance.
Solution
1) 1
2) 2
Now compare let us check the answers and compare your answers to following answers. I am very confident you will get them correct.
Available at
USA: Amazon
India: Flipkart | IndiaPlaza
Volume: 1, 2, 3, 4, 5
Please leave your feedback in the comment area for the quiz and video. Did you know all the answers of the quiz?
Reference: Pinal Dave (http://blog.sqlauthority.com)
Filed under: Joes 2 Pros, PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology
