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

View Course

Exercise instructions

Define construction_Plan with the fields: PartID, SubPartID, Title, Component and Level.
Initialize the field Level to 1.
Increase Level by 1 in every recursion step.
Limit the number of steps (Level) smaller than 2.

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;

Edit and Run Code