hfztDi0t

1drop table if exists matches;
2drop table if exists tournaments;
3drop table if exists ttypes;
4drop table if exists courts;
5drop table if exists players;
6
7create table players(
8	id serial primary key,
9	name text,
10	rank int,
11	country text
12	);
13
14create table ttypes(
15	id serial primary key,
16	name text
17	);
18
19create table courts(
20	id serial primary key,
21	name text
22	);
23
24create table tournaments(
25	id serial primary key,
26	name text,
27	city text,
28	ttype_id int references ttypes(id),
29	court_id int references courts(id)
30	);
31
32create table matches(
33	id serial primary key,
34	tournament_id int references tournaments(id),
35	player_id int references players(id),
36	won int,
37	yr int
38	);
39	
40
41insert into players(name, rank, country) values	('Garbine Muguruza', 5, 'IDK'),
42	('Serena Williams', 2, 'USA'),
43	('Simona Halep', 1, 'Romania');
44
45insert into ttypes(name) values
46	('Grand Slam'), ('Premier Mandatory'), ('Premier 5');
47
48insert into courts(name) values
49	('clay'), ('grass'), ('hard');
50
51insert into tournaments(name, city, ttype_id, court_id) values
52	('Roland Garos', 'Paris', 1, 1),
53	('Tournament_1', 'Bucharest', 1, 1),
54	('Tournament_2', 'Blabla', 2, 1),
55	('Tournament_3', 'Blabla', 2, 2),
56	('Tournament_4', 'Blabla', 3, 1),
57	('Tournament_5', 'Blabla', 3, 2),
58	('Tournament_6', 'Blabla', 3, 3),
59	('Tournament_7', 'Blabla', 1, 3);
60
61insert into matches(tournament_id, player_id, yr, won) values
62	(1, 1, 2016, 7),
63	(1, 2, 2016, 6),
64	(1, 3, 2016, 3),
65	(1, 3, 2015, 8),
66	(4, 2, 2016, 5)
67	;
68
69
70---------------- i -------------------
71select distinct t.name, m.yr from tournaments t
72 inner join matches m on m.tournament_id = t.id
73 inner join players p on m.player_id = p.id
74 inner join ttypes tt on tt.id = t.ttype_id
75 where tt.name = 'Grand Slam' and p.rank > 0 and m.won > 2;
76
77
78 -------------- ii --------------------
79 select p.name, p.country from players p
80 inner join matches m on m.player_id = p.id
81 inner join tournaments t on m.tournament_id = t.id
82 inner join courts c on c.id = t.court_id
83 where m.yr = 2016 and m.won > 0 and c.name = 'clay'
84 and p.id not in (
85	select m1.player_id from matches m1
86	inner join tournaments t1 on m1.tournament_id = t1.id
87	inner join courts c1 on c1.id = t1.court_id
88	where c1.name = 'grass'
89	);
90
91--------------- iii ---------------------
92select p.name, p.country from players p
93inner join matches m on m.player_id = p.id
94group by p.name, p.country
95having sum(m.won) = (
96	select sum(m1.won) as swon from matches m1
97	inner join players p1 on p1.id = m1.player_id
98	group by p1.id
99	order by swon desc
100	limit 1)