1. Recursion and Common Table Expression (CTE)
</> A CTE is … Find the wrong fact!
Please find the statement that is NOT true about CTEs.
- A CTE could be applied to recursive and non-recursive functions.
- A CTE is a temporary result that could be referenced by SELECT, INSERT, UPDATE, or DELETE statements.
- A CTE never returns a result set.
- A CTE could be used for substituting a view.
</> A CTE for IT-positions
Create the CTE ITjobs.
Define the fields of the CTE as ID, Name, and Position.
Find the positions starting with IT and the name starting with A.
WITH ITjobs (ID, Name, Position) AS (
SELECT
ID,
Name,
Position
FROM employee
WHERE Position LIKE 'IT%' AND Name LIKE 'A%')
SELECT *
FROM ITjobs;
ID Name Position
2 Andreas Sitter IT Security Manager
10 Andrea Sternig IT Architecture Manager
</> A CTE for high-paid IT-positions
Define the second CTE, ITSalary, with the fields ID and Salary.
Find salaries above 3000.
Combine the two CTEs by using a JOIN of matching IDs and select the name, the salary, and the position of the selected employees.
WITH ITjobs (ID, Name, Position) AS (
SELECT
ID,
Name,
Position
FROM employee
WHERE Position like 'IT%'),
ITSalary (ID, Salary) AS (
SELECT
ID,
Salary
FROM Salary
WHERE Salary > 3000)
SELECT
ITjobs.NAME,
ITjobs.POSITION,
ITsalary.Salary
FROM ITjobs
INNER JOIN ITsalary
ON ITjobs.ID = ITsalary.ID;
NAME POSITION Salary
Heinz Griesser IT Director 6000
Andreas Sitter IT Security Manager 3500
Thomas Bergmann IT Innovation Manager 3250
Andrea Sternig IT Architecture Manager 3500
</> Facts about recursion.
In this exercise, your job is to find the wrong fact about recursion.
- Complex functions can be simplified with recursion.
- Recursively defined functions cannot be solved iteratively.
- A termination condition is always necessary.
- The execution time can be slow because a recursion function is called many times.
</> Calculate the factorial of 5
Set the @target factorial, which will also serve as the termination condition, to 5.
Initialize the @factorial result.
Calculate the @factorial number by taking the product of the factorial result so far and the current iteration.
Reduce the termination condition by 1 at the end of the iteration.
DECLARE @target float = 5
DECLARE @factorial float = 1
WHILE @target > 0
BEGIN
SET @factorial = @factorial * @target
SET @target = @target - 1
END
SELECT @factorial;
120
</> How to query the factorial of 6 recursively
Initialize the fields factorial and step to 1.
Calculate the recursive part with factorial * (step + 1).
Stop the recursion process when the current iteration value is smaller than the target factorial number.
WITH calculate_factorial AS (
SELECT
1 AS step,
1 AS factorial
UNION ALL
SELECT
step + 1,
factorial * (step + 1)
FROM calculate_factorial
WHERE step < 6)
SELECT factorial
FROM calculate_factorial;
factorial
1
2
6
24
120
720
</> Counting numbers recursively
Limit the recursion step to 50 in the recursive query.
Define the CTE with the name counting_numbers.
Initialize number in the anchor query.
Add 1 to number each recursion step.
WITH counting_numbers AS (
SELECT
1 AS number
UNION ALL
SELECT
number + 1
FROM counting_numbers
WHERE number < 50)
SELECT number
FROM counting_numbers;
number
1
2
...
50
</> Calculate the sum of potencies
Define the CTE calculate_potencies with the fields step and result.
Initialize step and result.
Add the next step to the POWER() step + 1 to result.
WITH calculate_potencies (step, result) AS (
SELECT
1 AS step,
1 AS result
UNION ALL
SELECT
step + 1,
result + POWER(step + 1, step + 1)
FROM calculate_potencies
WHERE step < 9)
SELECT
step,
result
FROM calculate_potencies;
step result
1 1
2 5
...
9 405071317
2. Hierarchical and Recursive Queries
</> Right or wrong?
Which of the statements below is true?
- A recursive CTE is used to create iterative queries.
- Using a CTE call can improve readability.
- The items GROUP BY, SELECT DISTINCT, and HAVING are allowed in a recursive query.
- A recursive CTE needs an anchor query and a recursive query.
- It is not possible to self-reference a table using a CTE.
</> Create the alphabet recursively
Initialize number_of_letter to the number representing the letter A.
Increase the value of number_of_letter by 1 in each step and set the limit to 90, the value of Z.
Select the recursive member from the defined CTE.
WITH alphabet AS (
SELECT
65 AS number_of_letter
UNION ALL
SELECT
number_of_letter + 1
FROM alphabet
WHERE number_of_letter < 90)
SELECT char(number_of_letter)
FROM alphabet;
A
B
...
Z
</> Create a time series of a year
To get a series of days for a year you need 365 recursion steps. Therefore, increase the number of iterations by OPTION (MAXRECURSION n) where n represents the number of iterations.
Initialize the current time as time.
Select the CTE recursively and combine the anchor and recursive member with the correct statement.
Limit the number of iterations to days in a year minus 1
Increase the maximum number of iterations to the number of days in a year with OPTION (MAXRECURSION n)
WITH time_series AS (
SELECT
GETDATE() AS time
UNION ALL
SELECT
DATEADD(day, 1, time)
FROM time_series
WHERE time < GETDATE() + 364)
SELECT time
FROM time_series
OPTION(MAXRECURSION 365)
time
2020-07-28 11:57:25.187000
2020-07-29 11:57:25.187000
2020-07-30 11:57:25.187000
...
</> Who is your manager?
Create a CTE with the name employee_hierarchy.
Select the information of the IT director as the initial step of the CTE by filtering on his Supervisor ID.
Perform a join with employee to get the name of the manager.
WITH employee_hierarchy AS (
SELECT
ID,
NAME,
Supervisor
FROM employee
WHERE Supervisor = 0
UNION ALL
SELECT
emp.ID,
emp.NAME,
emp.Supervisor
FROM employee emp
JOIN employee_hierarchy
ON emp.Supervisor = employee_hierarchy.ID)
SELECT
cte.Name as EmployeeName,
emp.Name as ManagerName
FROM employee_hierarchy as cte
JOIN employee as emp
ON cte.Supervisor = emp.ID;
EmployeeName ManagerName
Andreas Sitter Heinz Griesser
Thomas Bergmann Heinz Griesser
Hannes Berg Heinz Griesser
...
</> Get the hierarchy position
Initialize the field LEVEL to 1 at the start of the recursion.
Select the information of the IT director as the initial step of the CTE by filtering on Supervisor.
Set LEVEL to the current recursion step.
Perform a JOIN with the defined CTE on the IDs of the supervisor and the employee.
WITH employee_hierarchy AS (
SELECT
ID,
NAME,
Supervisor,
1 as LEVEL
FROM employee
WHERE Supervisor = 0
UNION ALL
SELECT
emp.ID,
emp.NAME,
emp.Supervisor,
LEVEL + 1
FROM employee emp
JOIN employee_hierarchy
ON emp.Supervisor = employee_hierarchy.ID)
SELECT
cte.Name, cte.Level,
emp.Name as ManagerID
FROM employee_hierarchy as cte
JOIN employee as emp
ON cte.Supervisor = emp.ID
ORDER BY Level;
Name Level ManagerID
Andreas Sitter 2 Heinz Griesser
Thomas Bergmann 2 Heinz Griesser
Hannes Berg 2 Heinz Griesser
...
</> Which supervisor do I have?
Initialize Path to the ID of the supervisor (0) and the start condition of the recursion.
UNION the anchor member and define the recursive member fields (ID, Name, Supervisor).
Add the the ID of the supervisor Supervisor to the Path in each step.
Select the IDs of employees Christian Feierabend and Jasmin Mentil in the CTE.
WITH employee_Hierarchy AS (
SELECT
ID,
NAME,
Supervisor,
CAST('0' AS VARCHAR(MAX)) as Path
FROM employee
WHERE Supervisor = 0
UNION ALL
SELECT
emp.ID,
emp.NAME,
emp.Supervisor,
Path + '->' + CAST(emp.Supervisor AS VARCHAR(MAX))
FROM employee emp
INNER JOIN employee_Hierarchy
ON emp.Supervisor = employee_Hierarchy.ID
)
SELECT Path
FROM employee_Hierarchy
WHERE ID = 16 OR ID = 18;
Path
0->1->7
0->1->4
</> Get the number of generations?
Initialize the recursion start by setting the ParentID to 101.
Set LEVEL to the current recursion step.
Join the anchor member to the CTE on the ID of the parent and the child.
COUNT() the number of generations.
WITH children AS (
SELECT
ID,
Name,
ParentID,
0 as LEVEL
FROM family
WHERE ParentID = 101
UNION ALL
SELECT
child.ID,
child.NAME,
child.ParentID,
LEVEL + 1
FROM family child
INNER JOIN children
ON child.ParentID = children.ID)
SELECT
COUNT(DISTINCT LEVEL) as Generations
FROM children
OPTION(MAXRECURSION 300);
Generations
199
</> Get all possible parents in one field?
Set the ParentID of 290 as starting point.
If Parent.ID = ‘’ in the CASE operation, the Parent field needs to be set to the current ParentID.
If Parent.ID <> ‘’ in the CASE operation, the Parent ID needs to be added to the current Parent for each iteration.
Select the Name and Parent from the defined CTE.
WITH tree AS (
SELECT
ID,
Name,
ParentID,
CAST('0' AS VARCHAR(MAX)) as Parent
FROM family
WHERE ParentID = 290
UNION ALL
SELECT
Next.ID,
Next.Name,
Parent.ID,
CAST(CASE WHEN Parent.ID = ''
THEN(CAST(Next.ParentID AS VARCHAR(MAX)))
ELSE(Parent.Parent + ' -> ' + CAST(Next.ParentID AS VARCHAR(MAX)))
END AS VARCHAR(MAX))
FROM family AS Next
INNER JOIN tree AS Parent
ON Next.ParentID = Parent.ID)
SELECT Name, Parent
FROM tree;
Name Parent
Otoniel Tsosie 0
Danica Contreras 0 -> 291
Matias Clingan 0 -> 291 -> 292
Ignacio Galvan 0 -> 291 -> 292 -> 293
Konda Rea 0 -> 291 -> 292 -> 293 -> 294
...
3. Creating Data Models on Your Own
</> Creating a table
Define the table Person.
Define a field IndividualID.
Set Firstname and Lastname not to be NULL and of type VARCHAR(255).
Define Birthday as DATE.
CREATE TABLE Person (
IndividualID INT NOT NULL,
Firstname VARCHAR(255) NOT NULL,
Lastname VARCHAR(255) NOT NULL,
Address VARCHAR(255) NOT NULL,
City CHAR(32) NOT NULL,
Birthday DATE
);
SELECT *
FROM Person;
</> Inserting and updating a table
Insert the new values for ID = 1 into the Person table.
Insert the values Peter, Jackson, 342 Flushing st, New York, and 1986-12-30 in the corresponding fields of the table.
INSERT INTO Person
VALUES ('1','Andrew','Anderson','Union Ave 10','New York','1986-12-30');
INSERT INTO Person
VALUES ('2','Peter','Jackson','342 Flushing st','New York','1986-12-30');
SELECT *
FROM Person;
ID Firstname Lastname Adress City Birthday
1 Andrew Anderson Union Ave 10 New York 1986-12-30
2 Peter Jackson 342 Flushing st New York 1986-12-30
3 Jessica Patterson Pearson Ave 264 San Fransciso 1987-02-03
4 Michael Knight Center Ave 2 Torronte 1990-07-11
Change the first name of the person whose ID = 1 to Jones.
Update the birthday of the person with the last name Jackson to 1980-01-05.
INSERT INTO Person
VALUES ('1','Andrew','Anderson','Union Ave 10','New York','1986-12-30');
INSERT INTO Person
VALUES ('2','Peter','Jackson','342 Flushing st','New York','1986-12-30');
UPDATE Person
SET Firstname = 'Jones'
WHERE ID = 1;
UPDATE Person
SET Birthday = '1980-01-05'
WHERE Lastname = 'Jackson';
SELECT *
FROM Person;
ID Firstname Lastname Adress City Birthday
1 Jones Anderson Union Ave 10 New York 1986-12-30
2 Peter Jackson 342 Flushing st New York 1980-01-05
3 Jessica Patterson Pearson Ave 264 San Fransciso 1987-02-03
4 Michael Knight Center Ave 2 Torronte 1990-07-11
</> Deleting data and dropping table
Delete the person with an ID equal to 1 in the table.
Delete the person whose last name is Jackson.
INSERT INTO Person
VALUES ('1','Andrew','Anderson','Adress 1','City 1','1986-12-30');
INSERT INTO Person
VALUES ('2','Peter','Jackson','Adress 2','City 2','1986-12-30');
INSERT INTO Person
VALUES ('5','Michaela','James','Adress 3','City 3','1976-03-07');
DELETE FROM Person
WHERE ID = '1';
DELETE FROM Person
WHERE Lastname = 'Jackson';
SELECT *
FROM Person;
ID Firstname Lastname Adress City Birthday
3 Jessica Patterson Pearson Ave 264 San Fransciso 1987-02-03
4 Michael Knight Center Ave 2 Torronte 1990-07-11
5 Michaela James Adress 3 City 3 1976-03-07
Delete the table Person.
INSERT INTO Person
VALUES ( '1', 'Andrew', 'Anderson', 'Address 1', 'City 1', '1986-12-30');
INSERT INTO Person
VALUES ( '2', 'Peter', 'Jackson', 'Address 2', 'City 2', '1986-12-30');
INSERT INTO Person
VALUES ( '3', 'Michaela', 'James', 'Address 3', 'City 3', '1976-03-07');
DELETE FROM Person
WHERE ID = 1;
DELETE FROM Person
WHERE Lastname = 'Jackson';
DROP TABLE Person;
SELECT *
FROM Person;
</> Changing a table structure
Add the column Email to Person.
Delete the column Birthday from Person.
ALTER TABLE Person
ADD Email VARCHAR(255);
ALTER TABLE Person
DROP COLUMN Birthday;
SELECT *
FROM Person;
ID Firstname Lastname Adress City Email
3 Jessica Patterson Pearson Ave 264 San Fransciso null
4 Michael Knight Center Ave 2 Torronte null
</> Defining primary and foreign keys
Define the primary key PersonID for Person of type INT.
Define the primary key OrderID for History.
Define the foreign key PersonID referencing the primary key of Person.
CREATE TABLE Person (
PersonID INT NOT NULL PRIMARY KEY,
Firstname VARCHAR(255) NOT NULL,
Lastname VARCHAR(255) NOT NULL,
);
CREATE TABLE History (
OrderID INT NOT NULL PRIMARY KEY,
Item VARCHAR(255) NOT NULL,
Price INT NOT NULL,
PersonID INT FOREIGN KEY REFERENCES Person(PersonID)
);
SELECT *
FROM History;
</> Inserting data to person and order history
Insert new data for the person with ID=1, Andrew Anderson.
Insert new data for the second person with ID=2, Sam Smith.
Insert a new order for Andrew Anderson: iPhone XS for 1000.
Insert a new order for Sam Smith: MacBook Pro for 1800.
INSERT INTO Person
VALUES ('1','Andrew','Anderson','Union Ave 10','New York','1986-12-30');
INSERT INTO Person
VALUES ('2','Sam','Smith','Flushing Ave 342','New York','1986-12-30');
INSERT INTO History
VALUES ('1','IPhone XS','1000','1');
INSERT INTO History
VALUES ('2','MacBook Pro','1800','2');
SELECT *
FROM History;
OrderID Item Price PersonID
1 IPhone XS 1000 1
2 MacBook Pro 1800 2
3 Samsung TV 2000 3
4 Bose Soundbar 600 3
</> Getting the number of orders & total costs
COUNT() the number of orders and alias it as Orders.
SUM() the total price of all orders and alias it as Costs.
Join the Person and History tables.
Aggregate the information on ID using GROUP BY.
INSERT INTO Person
VALUES ('1', 'Andrew', 'Anderson','Union Ave 10','New York','1986-12-30');
INSERT INTO Person
VALUES ('2', 'Sam', 'Smith','Flushing Ave 342','New York','1986-12-30');
INSERT INTO History VALUES ( '1', 'IPhone XS', '1000', '1');
INSERT INTO History VALUES ( '2', 'MacBook Pro', '1800', '1');
INSERT INTO History VALUES ( '5', 'IPhone XR', '600', '2');
INSERT INTO History VALUES ( '6', 'IWatch 4', '400', '1');
SELECT
Person.ID,
COUNT(OrderID) as Orders,
SUM(Price) as Costs
FROM Person
JOIN History
ON Person.ID = History.PersonID
GROUP BY Person.ID;
ID Orders Costs
1 3 3200
2 1 600
3 2 2600
</> Creating a hierarchical data model
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.
CREATE TABLE Equipment (
ID INT NOT NULL,
Equipment VARCHAR(255) NOT NULL,
ParentID INT
);
INSERT INTO Equipment VALUES ('1','Asset',NULL);
INSERT INTO Equipment VALUES ('2','Hardware','1');
INSERT INTO Equipment VALUES ('3','Software','1');
INSERT INTO Equipment VALUES ('4','Application','3');
INSERT INTO Equipment VALUES ('5','Tool','3');
INSERT INTO Equipment VALUES ('6','PC','2');
INSERT INTO Equipment VALUES ('7','Monitor','2');
INSERT INTO Equipment VALUES ('8','Phone','2');
INSERT INTO Equipment VALUES ('9','IPhone','8');
INSERT INTO Equipment VALUES ('10','Microsoft Office','4');
SELECT *
FROM Equipment;
ID Equipment ParentID
1 Asset null
2 Hardware 1
3 Software 1
...
</> Networked and hierarchical models
Which statement is not true about networked and hierarchical data models?
- In hierarchical data models each child record needs to have only one parent record.
- In networked data models each child record can have only one parent record.
- The hierarchical data model can be represented by a tree.
- A networked data model has at least one parent record.
</> Creating a networked data model
Define the fields Departure and Destination, neither of which can be NULL.
Insert the route from San Francisco to New York for Bus 1.
Insert the route from Florida to San Francisco for Bus 9.
Select all possible departure locations.
CREATE TABLE Trip (
Departure VARCHAR(255) NOT NULL,
BusName VARCHAR(255) NOT NULL,
Destination VARCHAR(255) NOT NULL
);
INSERT INTO Trip VALUES ( 'San Francisco' , 'Bus 1' , 'New York');
INSERT INTO Trip VALUES ( 'Florida', 'Bus 9' , 'San Francisco');
INSERT INTO Trip VALUES ( 'San Francisco', 'Bus 2','Texas');
INSERT INTO Trip VALUES ( 'San Francisco', 'Bus 3','Florida');
INSERT INTO Trip VALUES ( 'San Francisco', 'Bus 4','Washington');
INSERT INTO Trip VALUES ( 'New York', 'Bus 5','Texas');
INSERT INTO Trip VALUES ( 'New York', 'Bus 6','Washington');
INSERT INTO Trip VALUES ( 'Florida', 'Bus 7','New York');
INSERT INTO Trip VALUES ( 'Florida', 'Bus 8','Toronto');
SELECT DISTINCT Departure
FROM Trip;
Departure
Florida
New York
San Francisco
4. Hierarchical Queries of Real-World Examples
</> Get the anchor & recursive member
Please find the correct combination of fields to create a flying route.
- Flight number and departure airport
- Destination airport and flight number
- Flight costs and departure airport
- Departure airport and the destination airport
</> Get all possible airports
Define the CTE table possible_Airports with the field Airports.
Select the airports by combining Departure and Arrival airports.
Combine the departure with the destination airports using the correct statement.
Select all possible Airports from possible_Airports.
WITH possible_Airports (Airports) AS(
SELECT Departure AS Airports
FROM flightPlan
UNION
SELECT Arrival AS Airports
FROM flightPlan)
SELECT DISTINCT Airports
FROM possible_Airports;
Airports
Chicago
Franfurt
Frankfurt
...
</> All flight routes from Vienna
Initialize the number of stops, increment it in the recursive query, and limit it to less than 5.
WITH flight_route (Departure, Arrival, stops) AS(
SELECT
f.Departure, f.Arrival,
0
FROM flightPlan f
WHERE Departure = 'Vienna'
UNION ALL
SELECT
p.Departure, f.Arrival,
p.stops + 1
FROM flightPlan f, flight_route p
WHERE p.Arrival = f.Departure AND
p.stops < 5)
SELECT
DISTINCT Arrival,
Departure,
stops
FROM flight_route;
Arrival Departure stops
Chicago Vienna 0
Chicago Vienna 2
Chicago Vienna 3
...
Define the field route describing the flight route (Departure to Arrival)
Track each recursion step with the Departure and Arrival airport.
WITH flight_route (Departure, Arrival, stops, route) AS(
SELECT
f.Departure, f.Arrival,
0,
CAST(f.Departure + ' -> ' + f.Arrival AS NVARCHAR(MAX))
FROM flightPlan f
WHERE Departure = 'Vienna'
UNION ALL
SELECT
p.Departure, f.Arrival,
p.stops + 1,
p.route + ' -> ' + f.Arrival
FROM flightPlan f, flight_route p
WHERE p.Arrival = f.Departure AND
p.stops < 5)
SELECT
DISTINCT Arrival,
Departure,
route,
stops
FROM flight_route;
Arrival Departure route stops
Chicago Vienna Vienna -> Chicago 0
Chicago Vienna Vienna -> New York -> Paris -> Frankfurt -> New York -> Vienna -> Chicago 5
Chicago Vienna Vienna -> New York -> Paris -> Frankfurt -> Paris -> Vienna -> Chicago 5
...
Add a totalCost field to the CTE and define it with the flight cost of the first flight.
Add the cost for each layover to the total costs.
Output the destinations where the totalCost is less than 500.
WITH flight_route (Departure, Arrival, stops, totalCost, route) AS(
SELECT
f.Departure, f.Arrival,
0,
f.Cost,
CAST(Departure + ' -> ' + Arrival AS NVARCHAR(MAX))
FROM flightPlan f
WHERE Departure = 'Vienna'
UNION ALL
SELECT
p.Departure, f.Arrival,
p.stops + 1,
p.totalCost + f.Cost,
p.route + ' -> ' + f.Arrival
FROM flightPlan f, flight_route p
WHERE p.Arrival = f.Departure AND
p.stops < 5)
SELECT
DISTINCT Arrival,
totalCost
FROM flight_route
WHERE totalCost < 500;
Arrival totalCost
Chicago 391.23
New York 482.42
Paris 74.88
</> Create the parts list
Define PartID as PRIMARY KEY of type INT.
Define Cost of type INT and not to be NULL.
Insert the root element SUV as described in the context section.
Insert the entry Wheels as described in the context section.
CREATE TABLE Bill_Of_Material (
PartID INT NOT NULL PRIMARY KEY,
SubPartID INT,
Component VARCHAR(255) NOT NULL,
Title VARCHAR(255) NOT NULL,
Vendor VARCHAR(255) NOT NULL,
ProductKey CHAR(32) NOT NULL,
Cost INT NOT NULL,
Quantity INT NOT NULL);
INSERT INTO Bill_Of_Material
VALUES ('1',NULL,'SUV','BMW X1','BMW','F48',50000,1);
INSERT INTO Bill_Of_Material
VALUES ('2','1','Engine','V6BiTurbro','BMW','EV3891ASF',3000,1);
INSERT INTO Bill_Of_Material
VALUES ('3','1','Body','AL_Race_Body','BMW','BD39281PUO',5000,1);
INSERT INTO Bill_Of_Material
VALUES ('4','1','Interior Decoration','All_Leather_Brown','BMW','ZEU198292',2500,1);
INSERT INTO Bill_Of_Material
VALUES ('5','1','Wheels','M-Performance 19/255','BMW','MKQ134098URZ',400,4);
SELECT *
FROM Bill_Of_Material;
PartID SubPartID Component Title Vendor ProductKey Cost Quantity
1 null SUV BMW X1 BMW F48 50000 1
2 1 Engine V6BiTurbro BMW EV3891ASF 3000 1
3 1 Body AL_Race_Body BMW BD39281PUO 5000 1
4 1 Interior Decoration All_Leather_Brown BMW ZEU198292 2500 1
5 1 Wheels M-Performance 19/255 BMW MKQ134098URZ 400 4
</> Create a car’s bill of material
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 to Level = 2.
WITH construction_Plan (PartID, SubPartID, Title, Component, Level) AS (
SELECT
PartID,
SubPartID,
Title,
Component,
1
FROM partList
WHERE PartID = '1'
UNION ALL
SELECT
CHILD.PartID,
CHILD.SubPartID,
CHILD.Title,
CHILD.Component,
PARENT.Level + 1
FROM construction_Plan PARENT, partList CHILD
WHERE CHILD.SubPartID = PARENT.PartID
AND PARENT.Level < 2)
SELECT DISTINCT PartID, SubPartID, Title, Component, Level
FROM construction_Plan
ORDER BY PartID, SubPartID, Level;
PartID SubPartID Title Component Level
1 null BMW X1 SUV 1
2 1 V6BiTurbro Engine 2
3 1 AL_Race_Body Body 2
4 1 All_Leather_Brown Interior Decoration 2
5 1 M-Performance 19/255 Wheels 2
</> Build up a BMW?
Define construction_Plan with the necessary fields.
Initialize Total with the Quantity in the anchor element of CTE.
Increase Total with the Quantity of the child element in the recursion element.
Use SUM() to create the sum of Total on the aggregated information on IDs of the hierarchy.
WITH construction_Plan (PartID, SubPartID, Level, Component, Total) AS (
SELECT
PartID,SubPartID,
0,
Component,
Quantity
FROM partList
WHERE PartID = '1'
UNION ALL
SELECT
CHILD.PartID, CHILD.SubPartID,
PARENT.Level + 1,
CHILD.Component,
PARENT.Total + CHILD.Quantity
FROM construction_Plan PARENT, partList CHILD
WHERE CHILD.SubPartID = PARENT.PartID
AND PARENT.Level < 3)
SELECT
PartID, SubPartID,Component,
SUM(Total)
FROM construction_Plan
GROUP BY PartID, SubPartID, Component
ORDER BY PartID, SubPartID;
PartID SubPartID Component
1 null SUV 1
2 1 Engine 2
3 1 Body 2
...
</> Create a power grid
CREATE the structure table.
Define the EquipmentID field as a PRIMARY KEY of type INT.
Insert the record for line 1: 1, 2, , ‘HV’, ‘Cable’, 2000, 2016, 'good’
Insert the record for line 14: - 14, 15, 3, ‘MV’, ‘Cable’, 1976, 2002, 'bad’
CREATE TABLE structure (
EquipmentID INT NOT NULL PRIMARY KEY,
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 INTO structure
VALUES ( 1, 2, NULL, 'HV', 'Cable', 2000, 2016, 'good');
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 INTO Structure
VALUES ( 14, 15, 3 , 'MV', 'Cable', 1976, 2002, 'bad');
SELECT *
FROM structure;
EquipmentID EquipmentID_To EquipmentID_From VoltageLevel Description ConstructionYear InspectionYear ConditionAssessment
1 2 null HV Cable 2000 2016 good
2 3 1 HV Overhead Line 1968 2016 bad
3 14 2 HV TRANSFORMER 1972 2001 good
14 15 3 MV Cable 1976 2002 bad
</> Get power lines to maintain
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.
WITH maintenance_List (Line, Destination, Source, Description, ConditionAssessment, VoltageLevel) AS (
SELECT
EquipmentID,
EquipmentID_To,
EquipmentID_From,
Description,
ConditionAssessment,
VoltageLevel
FROM GridStructure
WHERE EquipmentID = 3
UNION ALL
SELECT
Child.EquipmentID,
Child.EquipmentID_To,
Child.EquipmentID_FROM,
Child.Description,
Child.ConditionAssessment,
Child.VoltageLevel
FROM GridStructure Child
JOIN maintenance_List
ON maintenance_List.Line = Child.EquipmentID_From)
SELECT Line, Description, ConditionAssessment
FROM maintenance_List
WHERE
(ConditionAssessment LIKE '%exchange%' OR ConditionAssessment LIKE '%repair%') AND
VoltageLevel LIKE '%HV%'
Line Description ConditionAssessment
3 Cabel repair
6 OverheadLine exchange
8 OverheadLine exchange