create or replace view winloss as
  (select when, home tname, 'W' result
   from   results
   where  hruns is not null and vruns is not null and hruns>vruns)
  union
  (select when, visitor tname, 'W' result
   from   results
   where  hruns is not null and vruns is not null and vruns>hruns)
  union
  (select when, home tname, 'L'  result
   from   results
   where  hruns is not null and vruns is not null and hruns<vruns)
  union
  (select when, visitor tname, 'L' result
   from   results
   where  hruns is not null and vruns is not null and vruns<hruns)
  union
  (select when, home tname, 'T' result
   from   results
   where  hruns is not null and vruns is not null and hruns=vruns)
  union
  (select when, visitor tname, 'T' result
   from   results
   where  hruns is not null and vruns is not null and vruns=hruns);
--
create or replace view wins as
 (select tname, count(*) nwins
  from   winloss
  where  result='W'
  group by tname)
  union
 (select tname, 0 nwins
  from   teams
  where  tname not in 
          (select tname from winloss where result='W')
 );
--
create or replace view losses as
 (select tname, count(*) nlosses
  from   winloss
  where  result='L'
  group by tname)
  union
 (select tname, 0 nlosses
  from   teams
  where  tname not in 
          (select tname from winloss where result='L')
 );
--
create or replace view ties as
 (select tname, count(*) nties
  from   winloss
  where  result='T'
  group by tname)
  union
 (select tname, 0 nties
  from   teams
  where  tname not in 
          (select tname from winloss where result='T')
 );
--
create or replace view standings as
  select t.tname TEAM, t.nwins WINS, l.nlosses LOSSES, e.nties TIES, 
         to_char(round((nwins+0.5*nties)/(nwins+nlosses+nties),3),'9.999') PERCENTAGE
  from   wins t, losses l, ties e
  where  t.tname=l.tname and l.tname=e.tname
  order by percentage desc, t.tname desc;
