-
Notifications
You must be signed in to change notification settings - Fork 0
/
Chapter 11 - SQL Graph - Exercises.sql
152 lines (130 loc) · 4.03 KB
/
Chapter 11 - SQL Graph - Exercises.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
---------------------------------------------------------------------
-- T-SQL Fundamentals Fourth Edition
-- Chapter 11 - SQL Graph
-- Exercises
-- © Itzik Ben-Gan
---------------------------------------------------------------------
-- 1-1
-- Write a query that identifies who follows Stav
-- Tables involved: TSQLV6 database, Graph.Account and Graph.Follows tables
--Desired output
accountname
--------------
Alma
Lilach
(2 rows affected)
-- 1-2
-- Write a query that identifies who follows Stav or Yatzek or both
-- Tables involved: TSQLV6 database, Graph.Account and Graph.Follows tables
--Desired output
accountname follows
-------------- ----------
Miko Yatzek
Alma Stav
Omer Yatzek
Mitzi Yatzek
Lilach Stav
Lilach Yatzek
(6 rows affected)
-- 1-3
-- Write a query that identifies who follows both Stav and Yatzek
-- Tables involved: TSQLV6 database, Graph.Account and Graph.Follows tables
--Desired output
accountname
--------------
Lilach
-- 1-4
-- Write a query that identifies who follows Stav but not Yatzek
-- Tables involved: TSQLV6 database, Graph.Account and Graph.Follows tables
--Desired output
accountname
--------------
Alma
-- 2-1
-- Write a query that returns relationships where the first account
-- is either a friend of or follows the second account or both
-- Tables involved: TSQLV6 database,
-- Graph.Account, Graph.IsFriendOf and Graph.Follows tables
--Desired output
actid1 act1 actid2 act2
------- ------- ------- -------
2 Orli 379 Tami
2 Orli 641 Inka
2 Orli 727 Mitzi
71 Miko 199 Lilach
71 Miko 379 Tami
71 Miko 661 Alma
71 Miko 883 Yatzek
71 Miko 953 Omer
199 Lilach 71 Miko
199 Lilach 661 Alma
199 Lilach 883 Yatzek
199 Lilach 941 Stav
199 Lilach 953 Omer
379 Tami 2 Orli
379 Tami 71 Miko
379 Tami 421 Buzi
379 Tami 641 Inka
421 Buzi 379 Tami
421 Buzi 661 Alma
421 Buzi 727 Mitzi
641 Inka 2 Orli
641 Inka 379 Tami
641 Inka 727 Mitzi
661 Alma 71 Miko
661 Alma 199 Lilach
661 Alma 421 Buzi
661 Alma 883 Yatzek
661 Alma 941 Stav
727 Mitzi 2 Orli
727 Mitzi 421 Buzi
727 Mitzi 641 Inka
727 Mitzi 883 Yatzek
883 Yatzek 71 Miko
883 Yatzek 199 Lilach
883 Yatzek 661 Alma
883 Yatzek 727 Mitzi
883 Yatzek 953 Omer
941 Stav 199 Lilach
941 Stav 661 Alma
953 Omer 71 Miko
953 Omer 199 Lilach
953 Omer 883 Yatzek
(42 rows affected)
-- 2-2
-- Write a query that returns relationships where the first account
-- is a friend of but doesn't follow the second account
-- Tables involved: TSQLV6 database,
-- Graph.Account, Graph.IsFriendOf and Graph.Follows tables
--Desired output
actid1 act1 actid2 act2
------- ------- ------- -------
2 Orli 641 Inka
71 Miko 199 Lilach
199 Lilach 661 Alma
379 Tami 421 Buzi
421 Buzi 727 Mitzi
661 Alma 199 Lilach
661 Alma 883 Yatzek
727 Mitzi 641 Inka
883 Yatzek 71 Miko
883 Yatzek 661 Alma
883 Yatzek 727 Mitzi
883 Yatzek 953 Omer
(12 rows affected)
-- 3
-- Given an input post ID, possibly representing a reply to another post,
-- return the chain of posts leading to the input one
-- Use a recursive query
-- Tables involved: TSQLV6 database, Graph.Post and Graph.IsReplyTo tables
--Desired output for input post ID 1187 as an example
postid posttext
------- -------------------------------------------------
13 Got a new kitten. Any suggestions for a name?
449 Maybe Pickle?
1031 How about Gherkin?
1061 I love Gherkin!
1187 So you don't like Pickle!? I'M UNFRIENDING YOU!!!
(5 rows affected)
-- 4
-- Solve exercise 3 again, only this time using the SHORTEST_PATH option