lab3_dbd(done)

 DROP table teams;
 DROP table results;

 CREATE TABLE teams
 (
	team_id int primary key,
	team_name varchar2(20)
 );

 CREATE TABLE results
 (
	team1_id int,
	team2_id int,
	CONSTRAINT fk_team1_id FOREIGN KEY(team1_id) REFERENCES teams(team_id),
	CONSTRAINT fk_team2_id FOREIGN KEY(team2_id) REFERENCES teams(team_id),
	goals_team1 int,
	goals_team2 int,
	Unique(team1_id, team2_id)
 );

insert into teams values(1,'Poli');
insert into teams values(2,'MancesterUnited');
insert into teams values(3,'FCBarselona');

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


--Create a View generating the Overall Team Ranking (Point Based)
-- A team receives 3 points / match won
-- A team receives 1 point for a draw
-- Two teams can play each other two times (maximum) per season (one home game and one away)


CREATE VIEW wonMatch_view(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 drawMatch_view(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 wonMatchPoints_view(team_id, team_points) AS
SELECT t.team_id, (COUNT(m.team_id)*3) AS team_points
FROM teams t, wonMatch_view m
WHERE t.team_id = m.team_id
GROUP BY t.team_id;


CREATE VIEW drawMatchPoints_view(team_id, team_points) AS
SELECT t.team_id, COUNT(d.team_id) AS team_points
FROM teams t, drawMatch_view d
WHERE t.team_id = d.team_id
GROUP BY t.team_id;


CREATE VIEW ranking_view (team_id, team_name, total_points) AS
SELECT t.team_id, t.team_name, SUM(w.team_points + e.team_points) AS pts
FROM teams t, wonMatchPoints_view w, drawMatchPoints_view e
WHERE t.team_id=w.team_id AND t.team_id=e.team_id
GROUP BY t.team_id, t.team_name
ORDER BY pts DESC;