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

Exercise

Creating a hierarchical data model

In this exercise, you will construct a simple hierarchical data model by creating the hierarchy of IT assets. An asset can be either Hardware or Software. A Software asset can be split up into Application or Tools and so on. The hierarchy is shown in the following picture.

To model this hierarchy, a suitable data structure is needed. This structure can be accomplished by using a data model that consists of the child record ID and the parent record ParentID. The IDs are consecutive values from 1 to 10.

Your task is to create the corresponding Equipment table and to insert the assets Software, Monitor, and Microsoft Office into the table. Keep in mind that you have to set the correct IDs for each asset to achieve the desired hierarchy of assets.

Instructions

100 XP
  • Define the fields ID and ParentID of type INT. ID should not be NULL, ParentID can be NULL.
  • Insert the equipment Software into the table with the correct IDs. The software is part of Asset.
  • Insert the equipment Monitor into the table with the correct IDs. The monitor is part of Hardware.
  • Insert the equipment Microsoft Office into the table with the correct IDs. This software is part of Application.