-
Notifications
You must be signed in to change notification settings - Fork 0
/
query-examples.txt
50 lines (46 loc) · 1.76 KB
/
query-examples.txt
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
-- List of all finals with Ukrainian teams
SELECT
t.name
, r.season
, r.cup
, r.pos
FROM intcup_playoffranking r
INNER JOIN intcup_teams t ON t.team_id = r.team_id
INNER JOIN intcup_countries c ON c.country_id = t.country_id
WHERE r.sport = 'hockey' AND c.name = 'Ukraine'
AND r.pos < 17
ORDER BY r.pos ASC, r.season DESC
-- Ukrainian presence in top-16 of international cups in hockey
SELECT
t.name
, r.season
, r.cup
, r.pos
FROM intcup_playoffranking r
INNER JOIN intcup_teams t ON t.team_id = r.team_id
INNER JOIN intcup_countries c ON c.country_id = t.country_id
WHERE r.sport = 'hockey' AND c.name = 'Ukraine'
AND r.pos < 17
ORDER BY r.pos ASC, r.season DESC
-- Ranking of countries by titles
SELECT
c.name country
, COUNT(*) titles
, SUM(IF(r.sport = 'hockey', 1, 0)) titles_hockey
, SUM(IF(r.sport = 'soccer', 1, 0)) titles_soccer
, SUM(IF(r.sport = 'handball', 1, 0)) titles_handball
, SUM(IF(r.sport = 'basketball', 1, 0)) titles_basketball
, SUM(IF(r.sport = 'hockey' AND r.cup = 1, 1, 0)) titles_hockey_cl
, SUM(IF(r.sport = 'soccer' AND r.cup = 1, 1, 0)) titles_soccer_cl
, SUM(IF(r.sport = 'handball' AND r.cup = 1, 1, 0)) titles_handball_cl
, SUM(IF(r.sport = 'basketball' AND r.cup = 1, 1, 0)) titles_basketball_cl
, SUM(IF(r.sport = 'hockey' AND r.cup = 2, 1, 0)) titles_hockey_cwc
, SUM(IF(r.sport = 'soccer' AND r.cup = 2, 1, 0)) titles_soccer_cwc
, SUM(IF(r.sport = 'handball' AND r.cup = 2, 1, 0)) titles_handball_cwc
, SUM(IF(r.sport = 'basketball' AND r.cup = 2, 1, 0)) titles_basketball_cwc
FROM intcup_playoffranking r
INNER JOIN intcup_teams t ON t.team_id = r.team_id
INNER JOIN intcup_countries c ON c.country_id = t.country_id
WHERE r.pos = 1
GROUP BY country
ORDER BY titles DESC, country ASC