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

Exercise

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.

Instructions

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