Skip to content

Commit efba500

Browse files
julieMSFTjulieMSFT
committed
updated for issue 1877
1 parent d24d9c3 commit efba500

1 file changed

Lines changed: 49 additions & 48 deletions

File tree

‎docs/relational-databases/graphs/sql-graph-sample.md‎

Lines changed: 49 additions & 48 deletions
Original file line numberDiff line numberDiff line change
@@ -17,19 +17,22 @@ ms.author: shkale
1717
manager: craigg
1818
monikerRange: "=azuresqldb-current||>=sql-server-2017||=sqlallproducts-allversions||>=sql-server-linux-2017||=azuresqldb-mi-current"
1919
---
20+
2021
# Create a graph database and run some pattern matching queries using T-SQL
22+
2123
[!INCLUDE[tsql-appliesto-ss2017-asdb-xxxx-xxx-md](../../includes/tsql-appliesto-ss2017-asdb-xxxx-xxx-md.md)]
2224

2325
This sample provides a [!INCLUDE[tsql-md](../../includes/tsql-md.md)] script to create a graph database with nodes and edges and then use the new MATCH clause to match some patterns and traverse through the graph. This sample script will work on both Azure SQL Database and [!INCLUDE[sssqlv14](../../includes/sssqlv14-md.md)]
24-
25-
## Sample Schema
26-
This sample creates a graph schema, as showed in Figure 1, for a hypothetical social network that has People, Restaurant and City nodes. These nodes are connected to each other using Friends, Likes, LivesIn and LocatedIn edges.
26+
27+
## Sample Schema
28+
29+
This sample creates a graph schema, as showed in Figure 1, for a hypothetical social network that has People, Restaurant and City nodes. These nodes are connected to each other using Friends, Likes, LivesIn and LocatedIn edges.
2730

2831
![person-cities-restaurants-tables](../../relational-databases/graphs/media/person-cities-restaurants-tables.png "Sql graph database sample")
2932
Figure 1: Sample schema with restaurant, city, person nodes and LivesIn, LocatedIn, Likes edges.
3033

31-
3234
## Sample Script
35+
3336
```
3437
-- Create a graph demo database
3538
CREATE DATABASE graphdemo;
@@ -40,19 +43,19 @@ go
4043
4144
-- Create NODE tables
4245
CREATE TABLE Person (
43-
ID INTEGER PRIMARY KEY,
46+
ID INTEGER PRIMARY KEY,
4447
name VARCHAR(100)
4548
) AS NODE;
4649
4750
CREATE TABLE Restaurant (
48-
ID INTEGER NOT NULL,
49-
name VARCHAR(100),
51+
ID INTEGER NOT NULL,
52+
name VARCHAR(100),
5053
city VARCHAR(100)
5154
) AS NODE;
5255
5356
CREATE TABLE City (
54-
ID INTEGER PRIMARY KEY,
55-
name VARCHAR(100),
57+
ID INTEGER PRIMARY KEY,
58+
name VARCHAR(100),
5659
stateName VARCHAR(100)
5760
) AS NODE;
5861
@@ -77,43 +80,43 @@ INSERT INTO City VALUES (1,'Bellevue','wa');
7780
INSERT INTO City VALUES (2,'Seattle','wa');
7881
INSERT INTO City VALUES (3,'Redmond','wa');
7982
80-
-- Insert into edge table. While inserting into an edge table,
83+
-- Insert into edge table. While inserting into an edge table,
8184
-- you need to provide the $node_id from $from_id and $to_id columns.
82-
INSERT INTO likes VALUES ((SELECT $node_id FROM Person WHERE id = 1),
83-
(SELECT $node_id FROM Restaurant WHERE id = 1),9);
84-
INSERT INTO likes VALUES ((SELECT $node_id FROM Person WHERE id = 2),
85-
(SELECT $node_id FROM Restaurant WHERE id = 2),9);
86-
INSERT INTO likes VALUES ((SELECT $node_id FROM Person WHERE id = 3),
87-
(SELECT $node_id FROM Restaurant WHERE id = 3),9);
88-
INSERT INTO likes VALUES ((SELECT $node_id FROM Person WHERE id = 4),
89-
(SELECT $node_id FROM Restaurant WHERE id = 3),9);
90-
INSERT INTO likes VALUES ((SELECT $node_id FROM Person WHERE id = 5),
91-
(SELECT $node_id FROM Restaurant WHERE id = 3),9);
92-
93-
INSERT INTO livesIn VALUES ((SELECT $node_id FROM Person WHERE id = 1),
94-
(SELECT $node_id FROM City WHERE id = 1));
95-
INSERT INTO livesIn VALUES ((SELECT $node_id FROM Person WHERE id = 2),
96-
(SELECT $node_id FROM City WHERE id = 2));
97-
INSERT INTO livesIn VALUES ((SELECT $node_id FROM Person WHERE id = 3),
98-
(SELECT $node_id FROM City WHERE id = 3));
99-
INSERT INTO livesIn VALUES ((SELECT $node_id FROM Person WHERE id = 4),
100-
(SELECT $node_id FROM City WHERE id = 3));
101-
INSERT INTO livesIn VALUES ((SELECT $node_id FROM Person WHERE id = 5),
102-
(SELECT $node_id FROM City WHERE id = 1));
103-
104-
INSERT INTO locatedIn VALUES ((SELECT $node_id FROM Restaurant WHERE id = 1),
105-
(SELECT $node_id FROM City WHERE id =1));
106-
INSERT INTO locatedIn VALUES ((SELECT $node_id FROM Restaurant WHERE id = 2),
107-
(SELECT $node_id FROM City WHERE id =2));
108-
INSERT INTO locatedIn VALUES ((SELECT $node_id FROM Restaurant WHERE id = 3),
109-
(SELECT $node_id FROM City WHERE id =3));
110-
111-
-- Insert data into the friendof edge.
112-
INSERT INTO friendof VALUES ((SELECT $NODE_ID FROM person WHERE ID = 1), (SELECT $NODE_ID FROM person WHERE ID = 2));
113-
INSERT INTO friendof VALUES ((SELECT $NODE_ID FROM person WHERE ID = 2), (SELECT $NODE_ID FROM person WHERE ID = 3));
114-
INSERT INTO friendof VALUES ((SELECT $NODE_ID FROM person WHERE ID = 3), (SELECT $NODE_ID FROM person WHERE ID = 1));
115-
INSERT INTO friendof VALUES ((SELECT $NODE_ID FROM person WHERE ID = 4), (SELECT $NODE_ID FROM person WHERE ID = 2));
116-
INSERT INTO friendof VALUES ((SELECT $NODE_ID FROM person WHERE ID = 5), (SELECT $NODE_ID FROM person WHERE ID = 4));
85+
INSERT INTO likes VALUES ((SELECT $node_id FROM Person WHERE ID = 1),
86+
(SELECT $node_id FROM Restaurant WHERE ID = 1),9);
87+
INSERT INTO likes VALUES ((SELECT $node_id FROM Person WHERE ID = 2),
88+
(SELECT $node_id FROM Restaurant WHERE ID = 2),9);
89+
INSERT INTO likes VALUES ((SELECT $node_id FROM Person WHERE ID = 3),
90+
(SELECT $node_id FROM Restaurant WHERE ID = 3),9);
91+
INSERT INTO likes VALUES ((SELECT $node_id FROM Person WHERE ID = 4),
92+
(SELECT $node_id FROM Restaurant WHERE ID = 3),9);
93+
INSERT INTO likes VALUES ((SELECT $node_id FROM Person WHERE ID = 5),
94+
(SELECT $node_id FROM Restaurant WHERE ID = 3),9);
95+
96+
INSERT INTO livesIn VALUES ((SELECT $node_id FROM Person WHERE ID = 1),
97+
(SELECT $node_id FROM City WHERE ID = 1));
98+
INSERT INTO livesIn VALUES ((SELECT $node_id FROM Person WHERE ID = 2),
99+
(SELECT $node_id FROM City WHERE ID = 2));
100+
INSERT INTO livesIn VALUES ((SELECT $node_id FROM Person WHERE ID = 3),
101+
(SELECT $node_id FROM City WHERE ID = 3));
102+
INSERT INTO livesIn VALUES ((SELECT $node_id FROM Person WHERE ID = 4),
103+
(SELECT $node_id FROM City WHERE ID = 3));
104+
INSERT INTO livesIn VALUES ((SELECT $node_id FROM Person WHERE ID = 5),
105+
(SELECT $node_id FROM City WHERE ID = 1));
106+
107+
INSERT INTO locatedIn VALUES ((SELECT $node_id FROM Restaurant WHERE ID = 1),
108+
(SELECT $node_id FROM City WHERE ID =1));
109+
INSERT INTO locatedIn VALUES ((SELECT $node_id FROM Restaurant WHERE ID = 2),
110+
(SELECT $node_id FROM City WHERE ID =2));
111+
INSERT INTO locatedIn VALUES ((SELECT $node_id FROM Restaurant WHERE ID = 3),
112+
(SELECT $node_id FROM City WHERE ID =3));
113+
114+
-- Insert data into the friendOf edge.
115+
INSERT INTO friendOf VALUES ((SELECT $NODE_ID FROM Person WHERE ID = 1), (SELECT $NODE_ID FROM Person WHERE ID = 2));
116+
INSERT INTO friendOf VALUES ((SELECT $NODE_ID FROM Person WHERE ID = 2), (SELECT $NODE_ID FROM Person WHERE ID = 3));
117+
INSERT INTO friendOf VALUES ((SELECT $NODE_ID FROM Person WHERE ID = 3), (SELECT $NODE_ID FROM Person WHERE ID = 1));
118+
INSERT INTO friendOf VALUES ((SELECT $NODE_ID FROM Person WHERE ID = 4), (SELECT $NODE_ID FROM Person WHERE ID = 2));
119+
INSERT INTO friendOf VALUES ((SELECT $NODE_ID FROM Person WHERE ID = 5), (SELECT $NODE_ID FROM Person WHERE ID = 4));
117120
118121
119122
-- Find Restaurants that John likes
@@ -132,11 +135,11 @@ AND person1.name='John';
132135
SELECT Person.name
133136
FROM Person, likes, Restaurant, livesIn, City, locatedIn
134137
WHERE MATCH (Person-(likes)->Restaurant-(locatedIn)->City AND Person-(livesIn)->City);
135-
136138
```
137139

138140
## Clean Up
139141
Clean up the schema and database created for the sample.
142+
140143
```
141144
USE graphdemo;
142145
go
@@ -153,8 +156,6 @@ USE master;
153156
go
154157
DROP DATABASE graphdemo;
155158
go
156-
157-
158159
```
159160

160161
## Script explanation

0 commit comments

Comments
 (0)