-
Notifications
You must be signed in to change notification settings - Fork 0
/
a2.sql
230 lines (189 loc) · 4.82 KB
/
a2.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
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
SET search_path TO A2;
--If you define any views for a question (you are encouraged to), you must drop them
--after you have populated the answer table for that question.
--Good Luck!
--Query 1
INSERT INTO query1(
SELECT pname, cname, tname
FROM player, country, champion, tournament
WHERE player.pid = champion.pid AND champion.tid = tournament.tid AND tournament.cid = country.cid AND country.cid = player.cid
ORDER BY pname
);
--Query 2
CREATE VIEW total AS
SELECT tid, SUM(capacity) AS totalCapacity
FROM court
GROUP BY tid;
INSERT INTO query2(
SELECT tname, totalCapacity
FROM total, tournament
WHERE total.tid = tournament.tid AND totalCapacity >= ALL
(
SELECT totalCapacity
FROM total
)
ORDER BY tname
);
DROP VIEW total;
--Query 3
CREATE VIEW pair AS
(
SELECT winid AS pid, lossid AS opponent
FROM event)
UNION
(
SELECT lossid AS pid, winid AS opponent
FROM event);
CREATE VIEW HighestRank AS
SELECT pair.pid, min(globalRank) AS rank
FROM pair, player
WHERE pair.opponent = player.pid
GROUP BY pair.pid;
INSERT INTO query3(
SELECT pair.pid AS p1id, p.pname AS p1name, pair.opponent AS p2id, o.pname AS p2name
FROM pair, HighestRank, player p, player o
WHERE pair.pid = p.pid AND pair.opponent = o.pid AND pair.pid = HighestRank.pid AND o.globalRank = HighestRank.rank
ORDER BY p1name
);
DROP VIEW pair;
DROP VIEW HighestRank;
--Query 4
INSERT INTO query4(
SELECT pid, pname
FROM player
WHERE NOT EXISTS
(
(
SELECT DISTINCT tid
FROM tournament
)
EXCEPT
(
SELECT DISTINCT tid
FROM champion
WHERE champion.pid = player.pid
)
)
ORDER BY pname
);
--Query 5
CREATE VIEW average AS
SELECT pid, avg(wins) AS avgwins
FROM record
WHERE year >= 2011 AND year <= 2014
GROUP BY pid
ORDER BY avgwins DESC;
INSERT INTO query5(
SELECT average.pid, pname, avgwins
FROM player, average
WHERE player.pid = average.pid
ORDER BY avgwins DESC
LIMIT 10
);
DROP VIEW average;
--Query 6
CREATE VIEW win2011 AS
SELECT pid, wins
FROM record
WHERE year = 2011;
CREATE VIEW win2012 AS
SELECT pid, wins
FROM record
WHERE year = 2012;
CREATE VIEW win2013 AS
SELECT pid, wins
FROM record
WHERE year = 2013;
CREATE VIEW win2014 AS
SELECT pid, wins
FROM record
WHERE year = 2014;
INSERT INTO query6(
SELECT player.pid, pname
FROM player, win2011, win2012, win2013, win2014
WHERE player.pid = win2011.pid AND player.pid = win2012.pid AND player.pid = win2013.pid AND player.pid = win2014.pid AND win2011.wins < win2012.wins AND win2012.wins < win2013.wins AND win2013.wins < win2014.wins
ORDER BY pname
);
DROP VIEW win2011;
DROP VIEW win2012;
DROP VIEW win2013;
DROP VIEW win2014;
--Query 7
INSERT INTO query7(
SELECT p1.pname, c1.year
FROM champion c1, champion c2, player p1, player p2
WHERE c1.pid = p1.pid AND c2.pid = p2.pid AND c1.pid = c2.pid AND c1.year = c2.year AND c1.tid < c2.tid
ORDER BY pname DESC, year DESC
);
--Query 8
CREATE VIEW winlosspair AS
(SELECT winid AS p1id, lossid AS p2id
FROM event)
UNION
(SELECT lossid AS p1id, winid AS p2id
FROM event);
INSERT INTO query8(
SELECT p1.pname AS p1name, p2.pname AS p2name, cname
FROM winlosspair, player p1, player p2, country
WHERE winlosspair.p1id = p1.pid AND winlosspair.p2id = p2.pid AND p1.cid = p2.cid AND p1.cid = country.cid
ORDER BY cname, p1name DESC
);
DROP VIEW winlosspair;
--Query 9
CREATE VIEW cityCampion AS
SELECT country.cid, count(*) AS champions
FROM champion, player, country
WHERE champion.pid = player.pid AND player.cid = country.cid
GROUP BY country.cid;
INSERT INTO query9(
SELECT cname, champions
FROM cityCampion, country
WHERE cityCampion.cid = country.cid AND champions >= ALL
(
SELECT champions
FROM cityCampion
)
ORDER BY cname DESC
);
DROP VIEW cityCampion;
--Query 10
CREATE VIEW morewin AS
(
SELECT winid AS pid
FROM event
WHERE year = 2014
)
EXCEPT ALL
(
SELECT lossid AS pid
FROM event
WHERE year = 2014
);
CREATE VIEW gametime AS
(
SELECT winid AS pid, duration
FROM event
WHERE winid <> lossid
)
UNION ALL
(
SELECT lossid AS pid, duration
FROM event
);
INSERT INTO query10(
(
SELECT DISTINCT pname
FROM player, morewin
WHERE player.pid = morewin.pid
)
INTERSECT
(
SELECT pname
FROM player, gametime
WHERE gametime.pid = player.pid
GROUP BY player.pid, pname
HAVING AVG(duration) > 200
)
);
DROP VIEW morewin;
DROP VIEW gametime;