Graph database Script- SQL 2017

Script for the video published here.

CREATE TABLE Persons (
    PersonId INT identity(1, 1) PRIMARY KEY
    ,PersonName VARCHAR(100)
    ) AS NODE

INSERT INTO Persons
SELECT 'Batman'

UNION

SELECT 'Joker'

UNION

SELECT 'Robin'

UNION

SELECT 'Bane'

UNION

SELECT 'SuperMan'

UNION

SELECT 'WonderWoman'

UNION

SELECT 'Flash'

CREATE TABLE Against AS EDGE

CREATE TABLE [With] AS EDGE

SELECT *
FROM against

SELECT *
FROM persons

-- when batman fought the joker 
INSERT INTO Against
VALUES (
    (
        SELECT $NODE_ID
        FROM Persons
        WHERE PersonName = 'Batman'
        )
    ,(
        SELECT $NODE_ID
        FROM Persons
        WHERE PersonName = 'Joker'
        )
    )

-- when batman fought the Bane 
INSERT INTO Against
VALUES (
    (
        SELECT $NODE_ID
        FROM Persons
        WHERE PersonName = 'Batman'
        )
    ,(
        SELECT $NODE_ID
        FROM Persons
        WHERE PersonName = 'Bane'
        )
    )

-- When flash fought Bane 
INSERT INTO Against
VALUES (
    (
        SELECT $NODE_ID
        FROM Persons
        WHERE PersonName = 'Flash'
        )
    ,(
        SELECT $NODE_ID
        FROM Persons
        WHERE PersonName = 'Bane'
        )
    )

-- When batman worked with Robin 
INSERT INTO [With]
VALUES (
    (
        SELECT $NODE_ID
        FROM Persons
        WHERE PersonName = 'Batman'
        )
    ,(
        SELECT $NODE_ID
        FROM Persons
        WHERE PersonName = 'Robin'
        )
    )

--When batman worked with Wonderwomen 
INSERT INTO [With]
VALUES (
    (
        SELECT $NODE_ID
        FROM Persons
        WHERE PersonName = 'Batman'
        )
    ,(
        SELECT $NODE_ID
        FROM Persons
        WHERE PersonName = 'WonderWoman'
        )
    )

--When batman worked with Superman 
INSERT INTO [With]
VALUES (
    (
        SELECT $NODE_ID
        FROM Persons
        WHERE PersonName = 'Batman'
        )
    ,(
        SELECT $NODE_ID
        FROM Persons
        WHERE PersonName = 'SuperMan'
        )
    )

--When batman worked with Flash 
INSERT INTO [With]
VALUES (
    (
        SELECT $NODE_ID
        FROM Persons
        WHERE PersonName = 'Batman'
        )
    ,(
        SELECT $NODE_ID
        FROM Persons
        WHERE PersonName = 'Flash'
        )
    )

-- when wonderwomen worked with Flash 
INSERT INTO [With]
VALUES (
    (
        SELECT $NODE_ID
        FROM Persons
        WHERE PersonName = 'WonderWoman'
        )
    ,(
        SELECT $NODE_ID
        FROM Persons
        WHERE PersonName = 'Flash'
        )
    )

--people who fought against each other 
SELECT Person1.PersonName
    ,Person2.PersonName
FROM Persons Person1
    ,Against
    ,Persons Person2
WHERE MATCH(Person1 - (Against) - > Person2)

-- people who wonder women knows 
SELECT Person1.PersonName
    ,Person2.PersonName
FROM Persons Person1
    ,[With]
    ,Persons Person2
WHERE MATCH(Person1 - ([With]) - > Person2)
    AND person1.PersonName = 'WonderWoman'

UNION

SELECT Person1.PersonName
    ,Person2.PersonName
FROM Persons Person1
    ,[With]
    ,Persons Person2
WHERE MATCH(Person2 - ([With]) - > Person1)
    AND person1.PersonName = 'WonderWoman'

-- People who know wonderwomen and have fought with bane 
SELECT Person1.PersonName
    ,Person2.PersonName
    ,Person3.PersonName
FROM Persons Person1
    ,[With]
    ,Persons Person2
    ,Against
    ,Persons Person3
WHERE MATCH(Person1 < - ([With]) - Person2 - (against) - > Person3)
    AND person1.PersonName = 'WonderWoman'
    AND Person3.PersonName = 'Bane'

CREATE TABLE Locations (CityName VARCHAR(100)) AS NODE

INSERT INTO Locations
SELECT 'Gotham'

UNION

SELECT 'Kansas'

UNION

SELECT 'Themyscira'

UNION

SELECT 'Central City'

CREATE TABLE LivesIn AS EDGE

-- batman lives in Gotham 
INSERT INTO LivesIn
VALUES (
    (
        SELECT $NODE_ID
        FROM Persons
        WHERE personName LIKE 'batman'
        )
    ,(
        SELECT $NODE_ID
        FROM Locations
        WHERE CityName LIKE 'Gotham'
        )
    )

INSERT INTO LivesIn
VALUES (
    (
        SELECT $NODE_ID
        FROM Persons
        WHERE personName LIKE 'Flash'
        )
    ,(
        SELECT $NODE_ID
        FROM Locations
        WHERE CityName LIKE 'Central City'
        )
    )

INSERT INTO LivesIn
VALUES (
    (
        SELECT $NODE_ID
        FROM Persons
        WHERE personName LIKE 'WonderWoman'
        )
    ,(
        SELECT $NODE_ID
        FROM Locations
        WHERE CityName LIKE 'Gotham'
        )
    )

INSERT INTO LivesIn
VALUES (
    (
        SELECT $NODE_ID
        FROM Persons
        WHERE personName LIKE 'SuperMan'
        )
    ,(
        SELECT $NODE_ID
        FROM Locations
        WHERE CityName LIKE 'Kansas'
        )
    ) -- Find People who live in Gotham

SELECT *
FROM livesin

SELECT CityName
    ,PersonName
FROM Locations CityName
    ,livesin
    ,Persons personid
WHERE Match(PersonId - (livesin) - > CityName)
    AND CityName = 'Gotham'

-- People from Central City who fought Bane 
SELECT Person1.PersonName
    ,CityName
    ,Person2.PersonName
FROM Persons Person1
    ,Locations CityName
    ,livesin
    ,Persons Person2
    ,Against
WHERE Match(CityName < - (livesin) - Person1 - (against) - > Person2)
    AND CityName = 'Central City'

Please Consider Subscribing

Subscribe