1. Learn
    2. /
  2. Courses
    3. /
  3. Hierarchical and Recursive Queries in SQL Server

Exercise

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.

Instructions

100 XP
  • 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.