create table teams
( country varchar2(3)
, group1 varchar2(1)   
, seq_in_group number(1)
);

insert into teams (country, group1, seq_in_group)
values ('BRA','A',1);
insert into teams (country, group1, seq_in_group)
values ('CRO','A',2);
insert into teams (country, group1, seq_in_group)
values ('MEX','A',3);
insert into teams (country, group1, seq_in_group)
values ('CMR','A',4);
insert into teams (country, group1, seq_in_group)
values ('ESP','B',1);
insert into teams (country, group1, seq_in_group)
values ('NLD','B',2);
insert into teams (country, group1, seq_in_group)
values ('CHL','B',3);
insert into teams (country, group1, seq_in_group)
values ('AUS','B',4);
insert into teams (country, group1, seq_in_group)
values ('COL','C',1);
insert into teams (country, group1, seq_in_group)
values ('GRC','C',2);
insert into teams (country, group1, seq_in_group)
values ('CIV','C',3);
insert into teams (country, group1, seq_in_group)
values ('JPN','C',4);

create table match_schedule
( seq  number(1)
, home number(1)
, away number(1)
);

insert into match_schedule (seq, home, away) values (1,1,2);
insert into match_schedule (seq, home, away) values (2,3,4);
insert into match_schedule (seq, home, away) values (3,1,3);
insert into match_schedule (seq, home, away) values (4,4,2);
insert into match_schedule (seq, home, away) values (5,4,1);
insert into match_schedule (seq, home, away) values (6,2,3);


-- query the games to be played

select ms.seq
,      t1.group1
,      t1.country
,      t2.country
from   match_schedule ms
       join
       teams t1
       on (ms.home = t1.seq_in_group)
       join
       teams t2
       on ( ms.away= t2.seq_in_group
            and
            t1.group1 = t2.group1
          )
order
by     group1
,      seq

	
create table match_results
( group1 varchar2(1)
, home   number(1)
, away   number(1)
, home_goals number(2)
, away_goals number(2)
);

insert into match_results ( group1 , home, away, home_goals, away_goals) values ('A',1,2,2,1);
insert into match_results ( group1 , home, away, home_goals, away_goals) values ('A',3,4,1,3);
insert into match_results ( group1 , home, away, home_goals, away_goals) values ('A',1,3,0,0);
insert into match_results ( group1 , home, away, home_goals, away_goals) values ('A',4,2,3,1);

insert into match_results ( group1 , home, away, home_goals, away_goals) values ('B',1,2,1,1);
insert into match_results ( group1 , home, away, home_goals, away_goals) values ('B',3,4,2,0);
insert into match_results ( group1 , home, away, home_goals, away_goals) values ('B',1,3,3,1);
insert into match_results ( group1 , home, away, home_goals, away_goals) values ('B',4,2,0,3);


-- results per group, ordered by play-sequence
select mr.group1 "Group" 
,      t1.country||'-'||t2.country match
,      mr.home_goals||'-'||away_goals score
from   match_results mr
       join
       teams t1
       on (mr.home = t1.seq_in_group
           and 
           mr.group1 = t1.group1)
       join
       teams t2
       on ( mr.away= t2.seq_in_group
            and
            t1.group1 = t2.group1
          )
       join
       match_schedule ms
       on (ms.home = mr.home
           and
           ms.away = mr.away
           )
order 
by    mr.group1
,     ms.seq


Group MATCH   SCORE                                                                           
----- ------- ---------------------------------------------------------------------------------
A     BRA-CRO 2-1                                                                               
A     MEX-CMR 1-3                                                                               
A     BRA-MEX 0-0                                                                               
A     CMR-CRO 3-1                                                                               
B     ESP-NLD 1-1                                                                               
B     CHL-AUS 2-0                                                                               
B     ESP-CHL 3-1                                                                               
B     AUS-NLD 0-3     


aggregate match results per team:

with results as 
( select mr.group1 
,      mr.home
,      mr.home_goals
,      mr.away 
,      mr.away_goals
from   match_results mr
union all
select mr.group1 
,      mr.away
,      mr.away_goals
,      mr.home 
,      mr.home_goals
from   match_results mr
)
select group1
,      home
,      sum( case
            when home_goals > away_goals then 3
            when away_goals > home_goals then 0
            else 1 end
          ) over (partition by group1, home) points
,      sum( home_goals) over (partition by group1, home) goals_scored
,      sum( away_goals) over (partition by group1, home) goals_conceded
from  results
order
by    group1
,     points

GROUP1       HOME     POINTS GOALS_SCORED GOALS_CONCEDED
------ ---------- ---------- ------------ --------------
A               2          0            2              5 
A               2          0            2              5 
A               3          1            1              3 
A               3          1            1              3 
A               1          4            2              1 
A               1          4            2              1 
A               4          6            6              2 
A               4          6            6              2 
B               4          0            0              5 
B               4          0            0              5 
B               3          3            3              3 
B               3          3            3              3 
B               1          4            4              2 
B               1          4            4              2 
B               2          4            4              1 
B               2          4            4              1 

 16 rows selected 


with results as 
( select mr.group1 
,      mr.home
,      mr.home_goals
,      mr.away 
,      mr.away_goals
from   match_results mr
union all
select mr.group1 
,      mr.away
,      mr.away_goals
,      mr.home 
,      mr.home_goals
from   match_results mr
)
, team_results as
( select group1
,      home team
,      count(*) over (partition by group1, home) games_played
,      sum( case
            when home_goals > away_goals then 3
            when away_goals > home_goals then 0
            else 1 end
          ) over (partition by group1, home) points
,      sum( home_goals) over (partition by group1, home) goals_scored
,      sum( away_goals) over (partition by group1, home) goals_conceded
from  results
)
select distinct
       tr.group1
,      t.country
,      games_played
,      points
,      goals_scored-goals_conceded goal_difference
,      goals_scored
from   team_results tr
       join
       teams t
       on (tr.team = t.seq_in_group
           and
           tr.group1 = t.group1
           )
order
by     tr.group1
,      points desc
,      goal_difference desc
,      goals_scored desc

GROUP  COUNTRY GAMES_PLAYED     POINTS GOAL_DIFFERENCE GOALS_SCORED
------ ------- ------------ ---------- --------------- ------------
A      CMR                2          6               4            6 
A      BRA                2          4               1            2 
A      MEX                2          1              -2            1 
A      CRO                2          0              -3            2 

B      NLD                2          4               3            4 
B      ESP                2          4               2            4 
B      CHL                2          3               0            3 
B      AUS                2          0              -5            0 

derived from these results:

Group MATCH   SCORE                                                                           
----- ------- ---------------------------------------------------------------------------------
A     BRA-CRO 2-1                                                                               
A     MEX-CMR 1-3                                                                               
A     BRA-MEX 0-0                                                                               
A     CMR-CRO 3-1                                                                               
B     ESP-NLD 1-1                                                                               
B     CHL-AUS 2-0                                                                               
B     ESP-CHL 3-1                                                                               
B     AUS-NLD 0-3     


create table match
( group1 varchar2(1)
, home   varchar2(2)
, away   varchar2(3)
, home_goals number(2)
, away_goals number(2)
);


insert into matches
( group1, home, away)
select t1.group1
,      t1.country
,      t2.country
from   match_schedule ms
       join
       teams t1
       on (ms.home = t1.seq_in_group)
       join
       teams t2
       on ( ms.away= t2.seq_in_group
            and
            t1.group1 = t2.group1
          )
)

with results as 
( select m.group1 
,      m.home
,      m.home_goals
,      m.away 
,      m.away_goals
from   match m
union all
select m.group1 
,      m.away
,      m.away_goals
,      m.home 
,      m.home_goals
from   match m
)
, team_results as
( select group1
,      home team
,      count(*) over (partition by group1, home) games_played
,      sum( case
            when home_goals > away_goals then 3
            when away_goals > home_goals then 0
            else 1 end
          ) over (partition by group1, home) points
,      sum( home_goals) over (partition by group1, home) goals_scored
,      sum( away_goals) over (partition by group1, home) goals_conceded
from  results
)
select distinct
       tr.group1
,      tr.team
,      games_played
,      points
,      goals_scored-goals_conceded goal_difference
,      goals_scored
from   team_results tr
order
by     tr.group1
,      points desc
,      goal_difference desc
,      goals_scored desc


-- 2nd round and beyond matches

A1-B2 - C1 - D2
E1-F2 - G1 - H2

A2-B1 - C2 - D1
E2-F1 - G2 - H1


-- still to be played
select ms.seq
,      t1.group1
,      t1.country
,      t2.country
from   match_schedule ms
       join
       teams t1
       on (ms.home = t1.seq_in_group)
       join
       teams t2
       on ( ms.away= t2.seq_in_group
            and
            t1.group1 = t2.group1
          )
minus 
select ms.seq
,      t1.group1
,      t1.country
,      t2.country
from   match_results mr
       join
       teams t1
       on (mr.home = t1.seq_in_group
           and 
           mr.group1 = t1.group1)
       join
       teams t2
       on ( mr.away= t2.seq_in_group
            and
            t1.group1 = t2.group1
          )
       join
       match_schedule ms
       on (ms.home = mr.home
           and
           ms.away = mr.away
           )