Scribd
Upload
5 views9 pages

SQL Graph Database Examples

The document provides SQL examples for creating and managing a graph database named 'graph_db', including the creation of nodes for companies, employees, and cities, as well as edges representing relationships such as employment and residency. It includes various SQL queries to insert, update, delete, and retrieve data from the database, demonstrating how to work with graph structures in SQL. Additionally, it showcases recursive relationships and friendship connections among employees.

Uploaded by

riyasathsafran
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
5 views9 pages

SQL Graph Database Examples

The document provides SQL examples for creating and managing a graph database named 'graph_db', including the creation of nodes for companies, employees, and cities, as well as edges representing relationships such as employment and residency. It includes various SQL queries to insert, update, delete, and retrieve data from the database, demonstrating how to work with graph structures in SQL. Additionally, it showcases recursive relationships and friendship connections among employees.

Uploaded by

riyasathsafran
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

-- Example 31.

1
CREATE DATABASE graph_db;
GO;
USE graph_db;
CREATE TABLE [Link] (
ID INT NOT NULL PRIMARY KEY,
name VARCHAR (100) NULL,
sector VARCHAR(25) NULL,
city VARCHAR (100) NULL) AS NODE;
CREATE TABLE [Link] (
ID INT NOT NULL PRIMARY KEY,
name VARCHAR (100) NULL,
age INT NULL,
sex char (10) NULL) AS NODE;
CREATE TABLE [Link] (
ID INT NOT NULL PRIMARY KEY,
name VARCHAR(100) NULL,
stateName VARCHAR(100) NULL) AS NODE;

-- Example 31.2
USE graph_db;
INSERT INTO Employee (ID,Name,Sex)
VALUES (1,'Matthew Smith','Male');
INSERT INTO Employee (ID,Name,Sex)
VALUES (2,'Ann Jones','Female');
INSERT INTO Employee (ID,Name,Sex)
VALUES (3,'John Barrimore','Male');
INSERT INTO Employee (ID,Name,Sex)
VALUES (4,'James James','Male');
INSERT INTO Employee (ID,Name,Sex)
VALUES (5,'Elsa Bertoni','Female');
INSERT INTO Employee (ID,Name,Sex)
VALUES (6,'Elke Hansel','Female');
INSERT INTO Company VALUES (1,'Comp_A','Pharma','Kansas City');
INSERT INTO Company VALUES (2,'Comp_B','Manufacturing','Hoboken');
INSERT INTO Company VALUES (3,'Comp_C','Pharma','Indianopolis');
INSERT INTO Company VALUES (4,'Comp_D','IT','Lexington');
INSERT INTO Company VALUES (5,'Comp_E','IT','Madison');
INSERT INTO City VALUES (1,'Kansas City','Kansas');
INSERT INTO City VALUES (2,'Hoboken','New Jersey');
INSERT INTO City VALUES (3,'Indianopolis','Indiana');
INSERT INTO City VALUES (4,'Lexington','Kentucky');
INSERT INTO City VALUES (5,'Minneapolis','Wisconsin');
INSERT INTO City VALUES (6,'Madison','Wisconsin');

-- Example 31.3
USE graph_db;
CREATE TABLE WorksIn (starts INT) AS EDGE;
CREATE TABLE LocatedIn AS EDGE;
CREATE TABLE LivesIn (Since DATE NULL
CONSTRAINT Emp_to_City CONNECTION (Employee TO City)) AS EDGE;
-- Example 31.4
--To insert data into an edge table we need to provide the reference for
-- the $from_id and $to_id as a reference point to both nodes
USE graph_db;
INSERT INTO WorksIn VALUES ((SELECT $node_id FROM Employee WHERE id = 1),
(SELECT $node_id FROM Company WHERE id = 1), 2015);
INSERT INTO WorksIn VALUES ((SELECT $node_id FROM Employee WHERE id = 2),
(SELECT $node_id FROM Company WHERE id = 2), 2018);
INSERT INTO WorksIn VALUES ((SELECT $node_id FROM Employee WHERE id = 3),
(SELECT $node_id FROM Company WHERE id = 3), 2015);
INSERT INTO WorksIn VALUES ((SELECT $node_id FROM Employee WHERE id = 4),
(SELECT $node_id FROM Company WHERE id = 3), 2016);
INSERT INTO WorksIn VALUES ((SELECT $node_id FROM Employee WHERE id = 5),
(SELECT $node_id FROM Company WHERE id = 3), 2017);
INSERT INTO WorksIn VALUES ((SELECT $node_id FROM Employee WHERE id = 6),
(SELECT $node_id FROM Company WHERE id = 4), 2018);
-- Example 31.5
USE graph_db;
INSERT INTO LocatedIn VALUES ((SELECT $node_id FROM Company WHERE id = 1),
(SELECT $node_id FROM City WHERE id=2))
INSERT INTO LocatedIn VALUES ((SELECT $node_id FROM Company WHERE id = 2),
(SELECT $node_id FROM City WHERE id=1));
INSERT INTO LocatedIn VALUES ((SELECT $node_id FROM Company WHERE id = 3),
(SELECT $node_id FROM City WHERE id=3));
INSERT INTO LocatedIn VALUES ((SELECT $node_id FROM Company WHERE id = 4),
(SELECT $node_id FROM City WHERE id=2));

-- Example 31.6
USE graph_db;
INSERT INTO LivesIn VALUES ((SELECT $node_id FROM Employee WHERE id = 1),
(SELECT $node_id FROM City WHERE id=6), '1.1.2018');
INSERT INTO LivesIn VALUES ((SELECT $node_id FROM Employee WHERE id = 2),
(SELECT $node_id FROM City WHERE id=5), '2.1.2018');
INSERT INTO LivesIn VALUES ((select $node_id FROM Employee WHERE id = 3),
(SELECT $node_id FROM City WHERE id=4), '3.1.2018');
INSERT INTO LivesIn VALUES ((SELECT $node_id FROM Employee WHERE id = 4),
(SELECT $node_id FROM City WHERE id=2), '4.1.2018');
INSERT INTO LivesIn VALUES ((SELECT $node_id FROM Employee WHERE id = 5),
(SELECT $node_id FROM City WHERE id=3), '5.1.2018')
INSERT INTO LivesIn VALUES ((SELECT $node_id FROM Employee WHERE id = 6),
(SELECT $node_id FROM City WHERE id=1), '6.1.2018');
-- Example 31.7
-- Get the names of the companies and the names of their employees
USE graph_db;
SELECT DISTINCT [Link] CName, [Link] EName
FROM Employee Emp, WorksIn, Company Cmp
WHERE MATCH(Emp-(WorksIn)->Cmp);

-- Example 31.8
-- Get the name of the company where Matthew Smith works
USE graph_db;
SELECT [Link] CName
FROM Employee Emp, WorksIn, Company Cmp
WHERE MATCH(Emp-(WorksIn)->Cmp)
AND [Link]= 'Matthew Smith';

-- Example 31.9
-- Get the list of the employees who live in Madison
USE graph_db;
SELECT [Link] EName
FROM Employee Emp, LivesIn, City
WHERE MATCH(Emp-(LivesIn)->City)
AND [Link]='Madison';

-- Example 31.10
-- Get the list of companies located in the city of Hoboken
-- Sort the companies according to their names
USE graph_db;
SELECT [Link] CName
FROM City C, LocatedIn, Company Cmp
WHERE MATCH(Cmp-(LocatedIn)->C)
AND [Link]='Hoboken' ORDER BY 1;

-- Example 31.11
USE graph_db;
SELECT [Link] EName, [Link] CName
FROM Employee, WorksIn, Company, LocatedIn, City
WHERE MATCH(Employee-(WorksIn)->Company
AND Company-(LocatedIn)->City )
AND [Link]='2017' AND [Link]='Comp_C';

-- Example 31.12
USE graph_db;
SELECT [Link], [Link]
FROM Employee, WorksIn, Company, LocatedIn, City
WHERE MATCH(Employee-(WorksIn)->Company-(LocatedIn)->City)
AND [Link]='2017' and [Link]='Comp_C';
-- Example 31.13
-- Create an edge table for the recursive relationship
USE graph_db;
CREATE TABLE dbo.Is_Liked(start_date DATE) AS EDGE;
-- Insert several rows
INSERT INTO Is_Liked VALUES
((SELECT $node_id FROM employee WHERE ID = 1),
(SELECT $node_id FROM Employee WHERE ID = 2),'1.1.2017');
INSERT INTO Is_Liked VALUES
((SELECT $node_id FROM employee WHERE ID = 1),
(SELECT $node_id FROM Employee WHERE ID = 3),'2.1.2018');
INSERT INTO Is_Liked VALUES
((SELECT $node_id FROM employee WHERE ID = 1),
(SELECT $node_id FROM Employee WHERE ID = 4),'3.1.2019');
INSERT INTO Is_Liked VALUES
((SELECT $node_id FROM employee WHERE ID = 2),
(SELECT $node_id FROM Employee WHERE ID = 3),'4.1.2016');
INSERT INTO Is_Liked VALUES
((SELECT $node_id FROM employee WHERE ID = 2),
(SELECT $node_id FROM Employee WHERE ID = 5),'5.1.2017');
INSERT INTO Is_Liked VALUES
((SELECT $node_id FROM employee WHERE ID = 2),
(SELECT $node_id FROM Employee WHERE ID = 6),'6.1.2017');
INSERT INTO Is_Liked VALUES
((SELECT $node_id FROM employee where ID = 3),
(SELECT $node_id FROM Employee WHERE ID = 4),'7.1.2018');
INSERT INTO Is_Liked VALUES
((SELECT $node_id FROM employee where ID = 4),
(SELECT $node_id FROM Employee WHERE ID = 5),'8.1.2016');
INSERT INTO Is_Liked VALUES
((SELECT $node_id FROM employee where ID = 4),
(SELECT $node_id FROM Employee WHERE ID = 6),'9.1.2017');
INSERT INTO Is_Liked VALUES
((SELECT $node_id FROM employee where ID = 5),
(SELECT $node_id FROM Employee WHERE ID = 6),'10.1.2019');

-- Example 31.14
USE graph_db;
SELECT [Link] AS SourceName, [Link] AS TargetName
FROM Employee E1, Is_Liked, Employee E2
WHERE MATCH(E1-(Is_Liked)->E2);

-- Example 31.15
-- Display all employees that like Matthew Smith
USE graph_db;
SELECT [Link] AS FriendName
FROM Employee E1, Is_Liked, Employee E2
WHERE MATCH(E1-(Is_Liked)->E2)
AND [Link] = 'Matthew Smith';

-- Example 31.16
-- Display all employees who are liked by Matthew Smith
USE graph_db
SELECT [Link] AS FriendName
FROM Employee E1, Is_Liked, Employee E2
WHERE MATCH(E2-(Is_Liked)->E1) AND [Link] = 'Matthew Smith';

-- Example 31.17
-- Display all employees who like employees that like Ann Jones
USE graph_db;
SELECT [Link] AS FriendName
FROM Employee Person1, Employee Person2,
Is_Liked, Is_Liked Is_Liked2, Employee Person3
WHERE MATCH(Person1-(Is_Liked)->Person2-(Is_Liked2)->Person3)
AND [Link] = 'Ann Jones';
-- Example 31.18
USE graph_db;
INSERT into LivesIn VALUES ((SELECT $node_id FROM Employee WHERE id = 6),
(SELECT $node_id FROM City WHERE id = 5), '2.1.2018');

-- Example 31.19
USE graph_db;
SELECT [Link], [Link]
FROM Employee E, LivesIn, City C
WHERE MATCH(E -(LivesIn)->C) AND [Link] = 6 AND [Link] = 5;

-- Example 31.20
USE graph_db;
DELETE FROM LivesIn
WHERE $from_id =(SELECT $node_id FROM Employee WHERE id = 6)
AND $to_id = (SELECT $node_id FROM City WHERE id = 5);

-- Example 31.21
USE graph_db;
DELETE LivesIn
FROM Employee E, LivesIn, City C
WHERE MATCH(E-(LivesIn)->C) AND [Link] = 6 AND [Link] = 5;

-- Example 31.22
USE graph_db;
UPDATE WorksIn SET Starts = 2020
WHERE $from_id = (SELECT $node_id FROM Employee WHERE id = 6)
AND $to_id = (SELECT $node_id FROM Company WHERE id = 1);

-- Example 31.23
USE graph_db;
SELECT name, is_node, is_edge
FROM [Link]
WHERE is_node = 1 OR is_edge = 1;
-- Example 31.24
USE graph_db;
SELECT [Link] AS Edge_constraint
, OBJECT_NAME(EC.parent_object_id) AS Edge_table
, OBJECT_NAME(ECC.from_object_id) AS From_node_table
, OBJECT_NAME(ECC.to_object_id) AS To_node_table
FROM sys.edge_constraints EC
INNER JOIN sys.edge_constraint_clauses ECC
ON EC.object_id = ECC.object_id
WHERE EC.parent_object_id = object_id('LivesIn');

-- Example 31.25
USE graph_db;
SELECT NODE_ID_FROM_PARTS(OBJECT_ID('[Link]'), 0);

-- Example 31.26
DECLARE @table1 INT = OBJECT_ID('[Link]');
DECLARE @table2 INT = OBJECT_ID('[Link]');
INSERT INTO LocatedIn ($from_id, $to_id)
VALUES (NODE_ID_FROM_PARTS(@table1, 1),
NODE_ID_FROM_PARTS(@table2, 2));

-- Example 31.27
USE graph_db;
CREATE TABLE dbo.Employee1 (
ID int NOT NULL PRIMARY KEY,
name varchar (100) NULL, sex char (10) NULL);
INSERT INTO Employee1 VALUES (1,'Matthew Smith','Male');
INSERT INTO Employee1 VALUES (2,'Ann Jones','FeMale');
INSERT INTO Employee1 VALUES (3,'John Barrimore','Male');
INSERT INTO Employee1 VALUES (4,'James James','Male');
INSERT INTO Employee1 VALUES (5,'Elsa Bertoni','FeMale');
INSERT INTO Employee1 VALUES (6,'Elke Hansel','FeMale');

-- Example 31.28
USE graph_db;
CREATE TABLE Employee1_Friend
(EmployeeID INT NOT NULL, FriendID INT);
INSERT INTO Employee1_Friend values (1,2)
INSERT INTO Employee1_Friend values (1,3)
INSERT INTO Employee1_Friend values (1,4)
INSERT INTO Employee1_Friend values (2,3)
INSERT INTO Employee1_Friend values (2,5)
INSERT INTO Employee1_Friend values (2,6)
INSERT INTO Employee1_Friend values (3,4)
INSERT INTO Employee1_Friend values (4,5)
INSERT INTO Employee1_Friend values (4,6)
INSERT INTO Employee1_Friend values (5,6)

-- Example 31.29
USE graph_db;
SELECT [Link]
FROM employee1 e1 JOIN Employee1_Friend
ON Employee1_Friend.FriendID = [Link] JOIN employee1 e2
ON Employee1_Friend.EmployeeID = [Link]
WHERE [Link] = 'Matthew Smith';

-- Example 31.30
USE graph_db;
SELECT [Link]
FROM employee1 e1 JOIN Employee1_Friend
ON Employee1_Friend.EmployeeID = [Link]
JOIN employee1 e2
ON Employee1_Friend.FriendID = [Link]
WHERE [Link] = 'Matthew Smith';

-- Example 31.31
USE graph_db;
SELECT [Link] AS EMP_Name, [Link] AS FriendOfFriend
FROM Employee1_Friend ef1 JOIN employee1 e1
ON [Link] = [Link]
JOIN Employee1_Friend ef2 ON [Link] = [Link]
JOIN employee1 e2 ON [Link] = [Link]
WHERE [Link] = 'Ann Jones' AND [Link] <> [Link];

You might also like