-
Notifications
You must be signed in to change notification settings - Fork 0
/
tournament.sql
41 lines (38 loc) · 1.54 KB
/
tournament.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
--Recreate database and drop previous if exists
drop database if exists tournament;
create database tournament;
\c tournament
-- Table definitions for the tournament project.
----------------------------------------------------------------------------------------------------------------------
-- involve information about each registered player
create table Players(
id serial primary key,
name text
);
----------------------------------------------------------------------------------------------------------------------
-- involve record of each match
create table Matches(
match_id serial primary key,
winner integer references Players(id),
loser integer references Players(id)
);
----------------------------------------------------------------------------------------------------------------------
-- keeps record of each player
create view total_wins as
select Players.id as player, count(Matches.winner) as wins
from Players left join Matches
on Players.id = Matches.winner
group by Players.id, Matches.winner
order by Players.id;
create view total_loses as
select Players.id as player, count(Matches.loser) as losses
from Players left join Matches
on Players.id = Matches.loser
group by Players.id, Matches.loser
order by Players.id;
create view matches_played as
select Players.id as player, count(Matches) as matches
from Players left join Matches
on(Players.id=Matches.winner) or(Players.id=Matches.loser)
group by Players.id
order by Players.id asc;