l3_dbd

insert into results values(1,2,2,0);
insert into results values(1,3,2,1);
insert into results values(2,1,1,1);
insert into results values(2,3,1,0);
insert into results values(3,1,0,1);
insert into results values(3,2,1,1);


create view MatchWon (team_id) as
select team1_id from results
where goals_team1>goals_team2
union all
select team2_id from results
where goals_team1<goals_team2;

create view MatchEqual (team_id) as
select team1_id from results
where goals_team1=goals_team2
union all
select team2_id from results
where goals_team1=goals_team2;

create view PointsWon(team_id, points) as
select t.team_id, (count(w.team_id)*3) as points from teams t, matchwon w
where t.team_id=w.team_id(+)
group by t.team_id;

create view PointsEqual(team_id, points) as
select t.team_id, count(e.team_id) as points from teams t, matchequal e
where t.team_id=e.team_id(+)
group by t.team_id;

create view PointsInit (team_id, won, equal) as
select t.team_id, sum(w.points), sum(e.points) from teams t, pointswon w, pointsequal e
where t.team_id=w.team_id and t.team_id=e.team_id
group by t.team_id;

create view Rank(team_id, points) as
select t.team_id, p.won+p.equal as points from teams t, pointsinit p
where t.team_id=p.team_id
order by points desc;

create view RankTeams (team_id, points) as
select team_id, sum(points) as points from
(
  select team1_id as team_id, decode(sign(goals_team1-goals_team2),1,3,0,1,-1,0) as points from results
  union all
  select team2_id as team_id, decode(sign(goals_team2-goals_team1),1,3,0,1,-1,0) as points from results
) pointsMatch
group by team_id
order by points desc;