Create a power grid

In this exercise, you will create the structure table. This table describes how power lines are connected to each other. For this task, three ID values are needed:

EquipmentID: the unique key
EquipmentID_To: the first end of the power line with ID of the connected line
EquipmentID_From: the second end of the power line with ID of the connected line

The other fields to describe a power line, such as VoltageLevel and ConditionAssessment, are already defined.

An example is shown in the following picture:

For the line with EquipmentID = 3 the field EquipmentID_To is 4 and the field EquipmentID_From is 2.

This exercise is part of the course

Hierarchical and Recursive Queries in SQL Server

View Course

Exercise instructions

CREATE the structure table.
Define the EquipmentID field as a PRIMARY KEY of type INT.
Insert the record for line 1: 1, 2, <no from line>, 'HV', 'Cable', 2000, 2016, 'good'.
Insert the record for line 14: - 14, 15, 3, 'MV', 'Cable', 1976, 2002, 'bad'.

Hands-on interactive exercise

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

-- Create the table
___ TABLE ___ (
    -- Define the field EquipmentID 
  	___ ___ NOT ___ PRIMARY ___,
    EquipmentID_To INT ,
    EquipmentID_From INT, 
    VoltageLevel TEXT NOT NULL,
    Description TEXT NOT NULL,
    ConstructionYear INT NOT NULL,
    InspectionYear INT NOT NULL,
    ConditionAssessment TEXT NOT NULL
);

-- Insert the record for line 1 as described
INSERT INTO structure
VALUES ( 1, ___, NULL, 'HV', ___, 2000, 2016, ___);
INSERT INTO Structure
VALUES ( 2, 3 , 1, 'HV', 'Overhead Line', 1968, 2016, 'bad');
INSERT INTO Structure
VALUES ( 3, 14, 2, 'HV', 'TRANSFORMER', 1972, 2001, 'good');
-- Insert the record for line 14 as described
INSERT INTO Structure
VALUES ( ___, 15, ___ , ___, 'Cable', 1976, ___, 'bad');

SELECT * 
FROM structure

Edit and Run Code