-
Notifications
You must be signed in to change notification settings - Fork 0
/
retrieve.sql
163 lines (157 loc) · 4.48 KB
/
retrieve.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
152
153
154
155
156
157
158
159
160
161
162
163
-- 1
/* a
SELECT persons.id, persons.firstName, persons.lastName, persons.username, persons.password, persons.email
FROM persons
INNER JOIN developers ON persons.id = developers.id
*/
/* b
SELECT persons.id, persons.firstName, persons.lastName, persons.username, persons.password, persons.email
FROM persons
INNER JOIN developers ON persons.id = developers.id
WHERE developers.id = 34
*/
/* c
SELECT persons.id, persons.firstName, persons.lastName, persons.username, persons.password, persons.email
FROM persons
INNER JOIN website_roles ON persons.id = website_roles.developer_id
INNER JOIN websites ON websites.id = website_roles.website_id
WHERE websites.name= "Twitter"
AND (website_roles.role = "owner" OR website_roles.role = "admin")
*/
/* d
SELECT persons.id, persons.firstName, persons.lastName, persons.username, persons.password, persons.email, pages.title
FROM pages, persons
INNER JOIN page_role ON persons.id = page_role.developer_id
WHERE page_role.role = "reviewer"
AND pages.id = page_role.page_id
AND pages.visits < 300000
*/
/* e
SELECT persons.id, persons.firstName, persons.lastName, persons.username, persons.password, persons.email
FROM pages, persons
INNER JOIN page_role ON persons.id = page_role.developer_id
WHERE page_role.role = "writer"
AND pages.title = "Home"
AND pages.id = page_role.page_id
*/
-- 2
/* a
SELECT websites.id, websites.name, websites.description, websites.updated, min(websites.visits)
FROM websites
*/
/* b
SELECT websites.name
FROM websites
WHERE websites.id = 678
*/
/* c
SELECT websites.name, widgets.dtype
FROM persons
INNER JOIN page_role ON persons.id = page_role.developer_id
INNER JOIN pages ON page_role.page_id = pages.id
INNER JOIN websites on pages.website_id = websites.id
INNER JOIN widgets ON pages.id = widgets.page_id
WHERE widgets.dtype = "youtube"
AND page_role.role = "reviewer"
AND persons.firstName = "Bob"
*/
/* d
SELECT persons.firstName, websites.name, website_roles.role
FROM persons
INNER JOIN website_roles ON persons.id = website_roles.developer_id
INNER JOIN websites on website_roles.website_id = websites.id
WHERE persons.firstName = "alice"
AND website_roles.role = "Owner"
*/
/* e
SELECT websites.name
FROM persons
INNER JOIN website_roles ON persons.id = website_roles.developer_id
INNER JOIN websites ON website_roles.website_id = websites.id
WHERE website_roles.role = "admin"
AND persons.firstName = "charlie"
AND websites.visits > 6000000
*/
-- 3
/* a
SELECT pages.title, max(pages.visits) as "page visits"
FROM pages
WHERE pages.visits = (select max(pages.visits) from pages)
*/
/* b
SELECT pages.title
FROM pages
WHERE pages.id = 234
*/
/* c
SELECT persons.firstName, pages.title, page_role.role
FROM persons
INNER JOIN page_role ON persons.id = page_role.developer_id
INNER JOIN pages ON page_role.page_id = pages.id
WHERE persons.firstName = "alice"
AND page_role.role = "Editor"
*/
/* d
SELECT sum(pages.visits), websites.name
FROM Websites
INNER JOIN pages ON websites.id = pages.website_id
WHERE websites.name = "CNET"
*/
/* e
SELECT avg(pages.visits), websites.name
FROM Websites
INNER JOIN pages ON websites.id = pages.website_id
WHERE websites.name = "Wikipedia"
*/
-- 4
/* a
SELECT widgets.name
FROM pages
INNER JOIN websites ON pages.website_id = websites.id
INNER JOIN widgets on pages.id = widgets.page_id
WHERE pages.title = "Home"
AND websites.name = "CNET"
*/
/* b
SELECT widgets.name, widgets.dtype
FROM pages
INNER JOIN websites ON pages.website_id = websites.id
INNER JOIN widgets on pages.id = widgets.page_id
WHERE widgets.dtype = "youtube"
AND websites.name = "CNN"
*/
/* c
SELECT widgets.name, widgets.dtype
FROM Persons, pages
INNER JOIN widgets ON pages.id = widgets.page_id
INNER JOIN page_role ON pages.id = page_role.page_id
WHERE widgets.dtype = "image"
AND persons.firstName = "Alice"
AND page_role.role = "reviewer"
*/
/* d
SELECT widgets.name
FROM pages
INNER JOIN widgets ON pages.id = widgets.page_id
INNER JOIN websites ON pages.website_id = websites.id
WHERE websites.name = "Wikipedia"
*/
-- 5
/* a
SELECT websites.name
FROM persons
INNER JOIN website_priviledges ON persons.id = website_priviledges.developer_id
INNER JOIN websites ON website_priviledges.website_id = websites.id
WHERE persons.firstName = "Bob"
AND website_priviledges.priviledge = "delete"
group by websites.name
*/
/* b
SELECT pages.title
FROM persons
INNER JOIN page_priviledges ON persons.id = page_priviledges.developer_id
INNER JOIN pages ON page_priviledges.page_id = pages.id
WHERE persons.firstName = "Charlie"
AND page_priviledges.priviledge = "create"
group by pages.title
*/