Surrogate key for countries

create table countries ( 
  country_id integer 
    generated by default as identity 
    not null  
    primary key, 
  country_iso_code varchar2(2) 
    not null 
    unique, 
  country_name varchar2(256) 
    not null, 
  home_timezone varchar2(256) 
    not null 
)

begin 
  insert into countries ( country_iso_code, country_name, home_timezone )  
     values ( 'GB', 'United Kingdom', 'Europe/London' ); 
  insert into countries ( country_iso_code, country_name, home_timezone )  
    values ( 'US', 'USA', 'America/Los_Angeles' ); 
  insert into countries ( country_iso_code, country_name, home_timezone )  
     values ( 'DE', 'Germany', 'Europe/Berlin' ); 
  insert into countries ( country_iso_code, country_name, home_timezone )  
     values ( 'IN', 'India', 'Asia/Kolkata' ); 
  commit; 
end; 

Lookup table for game statuses

create table game_statuses ( 
  game_status_id varchar2(10) 
    not null 
    primary key, 
  description varchar2(100) 
    not null 
)

insert into game_statuses values ( 'SCHEDULED', 'The game is planned' )

insert into game_statuses values ( 'COMPLETED', 'The game has finished' )

insert into game_statuses values ( 'CANCELLED', 'The game was called off' )

commit

Columns for home/away countries

create table games ( 
  game_id integer 
    generated by default as identity 
    not null 
    primary key 
, game_time timestamp  
    with time zone 
    not null 
, game_status varchar2(10) 
    references game_statuses 
    not null 
, home_country 
    references countries 
, away_country 
    references countries 
, home_score integer 
, away_score integer 
, check ( home_country <> away_country ) 
/**/ 
)

create or replace function get_country_id ( iso_code varchar2 )  
  return varchar2 as 
  country_id varchar2(2); 
begin 
  select country_id into country_id  
  from   countries 
  where  country_iso_code = iso_code; 
  return nvl ( country_id, iso_code ); 
end; 

declare 
  use_columns  boolean := true; 
  store_points boolean := false; 
  use_utc      boolean := false; 
  procedure ins_game (  
    game_status  varchar2, game_time timestamp with time zone, 
    home_country varchar2, away_country varchar2,  
    home_score   int,      away_score int,  
    home_points  int,      away_points int 
  ) as 
    stmt clob; 
  begin 
    stmt := 'insert into games ( game_status, game_time ' || 
    case when use_columns then  
    ', home_country, away_country, home_score, away_score ' 
    end || 
    case when use_columns and store_points then  
    ', home_points, away_points ' 
    end || 
    ') values ( :game_status, :game_time ' || 
    case when use_utc then 
    q'[ at time zone 'UTC' ]' 
    end ||  
    case when use_columns then  
    ', get_country_id ( :home_country ), get_country_id ( :away_country )  
    , :home_score, :away_score ' 
    end || 
    case when use_columns and store_points then  
    ', :home_points, :away_points ' 
    end ||  
    ')'; 
    dbms_output.put_line ( stmt ); 
    if use_columns and store_points then 
      execute immediate stmt 
      using game_status, game_time 
        , home_country, away_country, home_score, away_score  
        , home_points, away_points; 
    elsif use_columns then  
      execute immediate stmt 
      using game_status, game_time 
        , home_country, away_country, home_score, away_score; 
    else 
      execute immediate stmt 
      using game_status, game_time; 
    end if; 
  end ins_game; 
 
begin 
  ins_game (  
    'COMPLETED', timestamp'2022-11-05 15:00:00 Europe/London', 'GB', 'US', 3, 1, 3, 0  
  ); 
  ins_game (  
    'COMPLETED', timestamp'2022-11-05 15:00:00 Europe/Berlin', 'DE', 'IN', 2, 2, 1, 1 
  ); 
  ins_game (  
    'COMPLETED', timestamp'2022-11-12 15:00:00 Asia/Kolkata', 'IN', 'GB', 0, 0, 1, 1 
  ); 
  ins_game (  
    'COMPLETED', timestamp'2022-11-12 15:00:00 America/Los_Angeles', 'US', 'DE', 1, 4, 0, 3 
  ); 
  ins_game (  
    'SCHEDULED', timestamp'2022-11-19 15:00:00 America/Los_Angeles', 'US', 'IN', null, null, null, null 
  ); 
  ins_game (  
    'SCHEDULED', timestamp'2022-11-19 15:00:00 Europe/Berlin', 'DE', 'GB', null, null, null, null 
  ); 
  ins_game (  
    'SCHEDULED', timestamp'2022-11-26 15:00:00 America/Los_Angeles', 'US', 'GB', null, null, null, null 
  ); 
  ins_game (  
    'SCHEDULED', timestamp'2022-11-26 15:00:00 Asia/Kolkata', 'IN', 'DE', null, null, null, null 
  ); 
end; 

alter session set nls_timestamp_format = ' DD Mon HH24:MI '

alter session set nls_timestamp_tz_format = ' DD Mon HH24:MI TZR'

select * from countries

select * from games

commit

All games for a team

select  
       game_time at time zone team.home_timezone game_time 
     , game_status, home_score, away_score 
     , case 
         when home_country = team.country_id then team.country_iso_code  
         else oppo.country_iso_code  
       end home_country 
     , case 
         when away_country = team.country_id then team.country_iso_code  
         else oppo.country_iso_code  
       end away_country 
from   games 
join   countries team 
on     team.country_id in ( home_country, away_country ) 
join   countries oppo 
on     oppo.country_id in ( home_country, away_country )/**/ 
where  team.country_iso_code = 'US' 
and    team.country_iso_code <> oppo.country_iso_code 

SQL macro for country games

create or replace function country_games (  
  country varchar2 
) return clob sql_macro as 
begin 
  return q'! 
select  
       game_time at time zone team.home_timezone game_time 
     , game_status, home_score, away_score 
     , case 
         when home_country = team.country_id then team.country_iso_code  
         else oppo.country_iso_code  
       end home_country 
     , case 
         when away_country = team.country_id then team.country_iso_code  
         else oppo.country_iso_code  
       end away_country /**/ 
from   games 
join   countries team 
on     team.country_id in ( home_country, away_country ) 
join   countries oppo 
on     oppo.country_id in ( home_country, away_country ) 
where  team.country_iso_code = country 
and    team.country_iso_code <> oppo.country_iso_code 
!'; 
end country_games; 

select * from country_games ( 'IN' )

select * from country_games ( 'DE' )

All games for GB including outcome for completed games

select  
       game_time 
     , home_country, away_country 
     , home_score, away_score 
     , case  
         when game_status <> 'COMPLETED' then 'TBC' 
         when home_score = away_score then 'D' 
         when home_score > away_score and home_country = 'GB' then 'W' 
         when home_score < away_score and away_country = 'GB' then 'W' 
         else 'L' 
       end result 
from   country_games ( 'GB' ) 
order  by game_time

List all fixtures

select game_time at local game_time 
     , home_country, away_country 
from   games

create or replace function fixtures  
  return clob sql_macro as 
begin 
  return q'!  
select  
       game_time at local game_time 
     , home_country, away_country 
from   games!'; 
end fixtures; 

Fixtures for next 7 days in session time zone

select game_time 
     , home.country_iso_code home_country, away.country_iso_code away_country 
from   table ( fixtures ) 
join   countries home 
on     home_country = home.country_id 
join   countries away 
on     away_country = away.country_id 
where  game_time >= timestamp'2022-11-16 00:00:00 UTC' 
and    game_time < timestamp'2022-11-23 00:00:00 UTC' 
order  by game_time

alter session set time_zone = 'Europe/Berlin'

select game_time 
     , home.country_iso_code home_country, away.country_iso_code away_country 
from   table ( fixtures ) 
join   countries home 
on     home_country = home.country_id 
join   countries away 
on     away_country = away.country_id 
where  game_time >= timestamp'2022-11-16 00:00:00 UTC' 
and    game_time < timestamp'2022-11-23 00:00:00 UTC' 
order  by game_time

alter session set time_zone = 'Asia/Kolkata'

select game_time 
     , home.country_iso_code home_country, away.country_iso_code away_country 
from   table ( fixtures ) 
join   countries home 
on     home_country = home.country_id 
join   countries away 
on     away_country = away.country_id 
where  game_time >= timestamp'2022-11-16 00:00:00 UTC' 
and    game_time < timestamp'2022-11-23 00:00:00 UTC' 
order  by game_time

alter session set time_zone = 'Europe/London'

Summary of results per country

with rws as ( 
  select home_country, away_country 
    , ( home_score - away_score ) as home_diff 
    , ( away_score - home_score ) as away_diff 
    , case  
        when home_score > away_score then 'W'  
        when home_score = away_score then 'D'  
        when home_score < away_score then 'L' 
      end home_wdl 
    , case  
        when home_score > away_score then 'L'  
        when home_score = away_score then 'D'  
        when home_score < away_score then 'W' 
      end away_wdl 
    , case  
        when home_score > away_score then 3  
        when home_score = away_score then 1  
        when home_score < away_score then 0  
      end home_points 
    , case  
        when home_score > away_score then 0 
        when home_score = away_score then 1  
        when home_score < away_score then 3  
      end away_points /**/ 
  from   games 
  where  game_status = 'COMPLETED' 
) 
select country 
     , sum ( points ), listagg ( wdl ) games, sum ( score_diff ) score_diff 
from   rws 
unpivot ( 
  ( country, points, wdl, score_diff ) for home_away in (  
    ( home_country, home_points, home_wdl, home_diff ) as 'HOME', 
    ( away_country, away_points, away_wdl, away_diff ) as 'AWAY' 
  ) 
) 
group  by country

SQL macro of country summary

create or replace function country_results  
  return clob sql_macro as 
begin 
  return q'!   
with rws as ( 
  select home_country, away_country 
    , ( home_score - away_score ) as home_diff 
    , ( away_score - home_score ) as away_diff 
    , case  
        when home_score > away_score then 'W'  
        when home_score = away_score then 'D'  
        when home_score < away_score then 'L' 
      end home_wdl 
    , case  
        when home_score > away_score then 'L'  
        when home_score = away_score then 'D'  
        when home_score < away_score then 'W' 
      end away_wdl 
    , case  
        when home_score > away_score then 3  
        when home_score = away_score then 1  
        when home_score < away_score then 0  
      end home_points 
    , case  
        when home_score > away_score then 0 
        when home_score = away_score then 1  
        when home_score < away_score then 3  
      end away_points  
  from   games 
  where  game_status = 'COMPLETED' 
) 
select country 
     , points, wdl, score_diff 
from   rws 
unpivot ( 
  ( country, points, wdl, score_diff ) for home_away in (  
    ( home_country, home_points, home_wdl, home_diff ) as 'HOME', 
    ( away_country, away_points, away_wdl, away_diff ) as 'AWAY' 
  ) 
)!'; 
end country_results; 

select * from table ( country_results )

select * from table ( country_results ) 
pivot ( 
  count(*) c 
, sum ( score_diff ) diff 
, sum ( points ) points  
  for wdl in ( 'W' W, 'D' D, 'L' L ) 
)

create or replace function league  
  return clob sql_macro as 
begin 
  return q'!  
select * from table ( country_results ) 
pivot ( 
  count(*) c 
, sum ( score_diff ) diff 
, sum ( points ) points  
  for wdl in ( 'W' W, 'D' D, 'L' L ) 
)   
!'; 
end league; 

Final league

select country_name, w_c w, d_c d, l_c l,  
       nvl ( w_points, 0 ) + nvl ( d_points, 0 ) points, 
       nvl ( w_diff, 0 ) + nvl ( d_diff, 0 ) + nvl ( l_diff, 0 ) score_diff 
from   table ( league ) 
join   countries  
on     country = country_id 
order  by points desc, score_diff desc

update games 
set    game_status = 'ABANDONED' 
where  game_id = 4

Add new game status

insert into game_statuses  
  values ( 'ABANDONED', 'The game was stopped' )

update games 
set    game_status = 'ABANDONED' 
where  game_id = 4

commit

alter table games 
  add constraint game_statuses_c 
  check (  
    game_status in ( 'SCHEDULED', 'COMPLETED', 'CANCELLED', 'ABANDONED' )  
  )

select /*+ gather_plan_statistics */* from games 
where  game_status = 'JUNK'

Check constraints can give better optimizations

select *  
from   table(dbms_xplan.display_cursor(:LIVESQL_LAST_SQL_ID, null, 'ALLSTATS LAST'))