Get Started

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.

This is a part of the course

“Hierarchical and Recursive Queries in SQL Server”

View Course

Exercise instructions

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

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

CREATE TABLE Equipment (   
    -- Define ID and ParentID 
	___ ___ NOT NULL,
    Equipment VARCHAR(255) NOT NULL,
    ___ ___ 
);

INSERT INTO Equipment VALUES (1,'Asset',NULL);
INSERT INTO Equipment VALUES (2,'Hardware',1);
-- Insert the type Software
INSERT INTO Equipment VALUES (___,____,1);
INSERT INTO Equipment VALUES (4,'Application',3);
INSERT INTO Equipment VALUES (5,'Tool',3);
INSERT INTO Equipment VALUES (6,'PC',2);
-- Insert the type Monitor 
INSERT INTO Equipment VALUES (___,'Monitor',2);
INSERT INTO Equipment VALUES (8,'Phone',2);
INSERT INTO Equipment VALUES (9,'IPhone',8);
-- Insert the type Microsoft Office 
INSERT INTO Equipment VALUES (___,___,4);

SELECT * 
FROM Equipment;
Edit and Run Code

This exercise is part of the course

Hierarchical and Recursive Queries in SQL Server

AdvancedSkill Level
2.0+
1 reviews

Learn how to write recursive queries and query hierarchical data structures.

Start Course for Free

In this chapter, you will learn how to create and modify database tables. You will learn about relational and hierarchical data models, how they differ, and when each model should be used.

Exercise 1: How to work with tablesExercise 2: Creating a tableExercise 3: Inserting and updating a tableExercise 4: Deleting data and dropping tableExercise 5: Changing a table structureExercise 6: Working with relational data modelsExercise 7: Defining primary and foreign keysExercise 8: Inserting data to person and order historyExercise 9: Getting the number of orders & total costsExercise 10: Working with hierarchical data modelsExercise 11: Creating a hierarchical data model
Exercise 12: Networked and hierarchical modelsExercise 13: Creating a networked data model

What is DataCamp?

Learn the data skills you need online at your own pace—from non-coding essentials to data science and machine learning.

Start Learning for Free