Create a car's bill of material
In this exercise, you will answer the following question: What are the levels of the different components that build up a car?
For example, an SUV (1st level), is made of an engine (2nd level), and a body (2nd level), and the body is made of a door (3rd level) and a hood (3rd level).
Your task is to create a query to get the hierarchy level of the table partList
. You have to create the CTE construction_Plan
and should keep track of the position of a component in the hierarchy. Keep track of all components starting at level 1 going up to level 2.
This exercise is part of the course
Hierarchical and Recursive Queries in SQL Server
Exercise instructions
- Define
construction_Plan
with the fields:PartID
,SubPartID
,Title
,Component
andLevel
. - Initialize the field
Level
to 1. - Increase
Level
by 1 in every recursion step. - Limit the number of steps (
Level
) smaller than2
.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Define CTE with the fields: PartID, SubPartID, Title, Component, Level
___ construction_Plan (___, ___,___, ___, ___) AS (
SELECT
PartID,
SubPartID,
Title,
Component,
-- Initialize the field Level
___
FROM partList
WHERE PartID = '1'
UNION ALL
SELECT
CHILD.PartID,
CHILD.SubPartID,
CHILD.Title,
CHILD.Component,
-- Increment the field Level each recursion step
PARENT.___ + ___
FROM construction_Plan PARENT, partList CHILD
WHERE CHILD.SubPartID = PARENT.PartID
-- Limit the number of iterations to Level < 2
AND PARENT.___ < ___)
SELECT DISTINCT PartID, SubPartID, Title, Component, Level
FROM construction_Plan
ORDER BY PartID, SubPartID, Level;