Build up a BMW?

In this exercise, you will answer the following question: What is the total required quantity Total of each component to build the car until level 3 in the hierarchy?

Your task is to create the CTE construction_Plan to track the level of components and to calculate the total quantity of components in the field Total. The table is set up by the fields PartID, SubPartID, Level, Component, and Total. You have to consider all components starting from level 1 up to level 3.

This exercise is part of the course

Hierarchical and Recursive Queries in SQL Server

View Course

Exercise instructions

Define construction_Plan with the necessary fields.
Initialize Total with the Quantity in the anchor element of CTE.
Increase Total with the Quantity of the child element in the recursion element.
Use SUM() to create the sum of Total on the aggregated information on IDs of the hierarchy.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

-- Define CTE with the fields: PartID, SubPartID, Level, Component, Total
WITH ___ (___, ___, ___, ___, ___) AS (
	SELECT 
  		PartID,SubPartID,
  		0,
  		Component,
  		-- Initialize Total
  		___
	FROM partList
	WHERE PartID = '1'
	UNION ALL
	SELECT 
		CHILD.PartID, CHILD.SubPartID,
  		PARENT.Level + 1,
  		CHILD.Component,
  		-- Increase Total by the quantity of the child element
  		PARENT.___ + CHILD.___
	FROM construction_Plan PARENT, partList CHILD
  	WHERE CHILD.SubPartID = PARENT.PartID
	  AND PARENT.Level < 3)
      
SELECT 
    PartID, SubPartID,Component,
    -- Calculate the sum of total on the aggregated information
    ___(___)
FROM construction_Plan
GROUP BY PartID, SubPartID, Component
ORDER BY PartID, SubPartID;

Edit and Run Code