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

Exercise

Get power lines to maintain

In the provided GridStructure table, the fields that describe the connection between lines (EquipmentID,EquipmentID_To,EquipmentID_From) and the characteristics of the lines (e.g. Description, ConditionAssessment, VoltageLevel) are already defined.

Now, your task is to find the connected lines of the line with EquipmentID = 3 that have bad or repair as ConditionAssessment and have a VoltageLevel equal to HV. By doing this, you can answer the following question:

Which lines have to be replaced or repaired according to their description and their current condition?

You have to create a CTE to find the connected lines and finally, to filter on the desired characteristics.

Instructions

100 XP
  • Define the CTE maintenance_List.
  • Start the evaluation for line 3.
  • Join GridStructure with maintenance_List on the corresponding endpoints.
  • Use LIKE to filter the power lines with ConditionAssessment of either exchange or repair, and a VoltageLevel of HV.