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.

Diese Übung ist Teil des Kurses

Hierarchical and Recursive Queries in SQL Server

Kurs anzeigen

Anleitung zur Übung

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.

Interaktive Übung

Versuche dich an dieser Übung, indem du diesen Beispielcode vervollständigst.

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

Code bearbeiten und ausführen