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.

Questo esercizio fa parte del corso

Hierarchical and Recursive Queries in SQL Server

Visualizza il corso

Istruzioni dell'esercizio

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.

Esercizio pratico interattivo

Prova a risolvere questo esercizio completando il codice di esempio.

-- 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;

Modifica ed esegui il codice