Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
select * from teams;
TEAM_ID TEAM_NAME LIG DIVISION
---------- --------- --- --------
1 Yankees A E
2 Red Sox A E
3 Giants N W
3 rows selected.
select * from games;
WINNER_TEAM_ID LOOSER_TEAM_ID GAME_ID START_DATE_ID FINISH_DATE_ID
-------------- -------------- ---------- -------------- --------------
2 1 2786 14-AUG-04 14-AUG-04
1 3 2795 16-AUG-04 16-AUG-04
1 3 2803 17-AUG-04 17-AUG-04
1 2 2773 13-AUG-04 13-AUG-04
4 rows selected.
break on a
col a heading "Team|Name" format a10
col b heading "Won/|Lost" format a4
col c heading "Game|ID" format 9999
col d heading "Game|Start|Date" format a9
select team_name a, 'W' b, game_id c, start_date_id d
from games, teams
where winner_team_id = team_id
union
select team_name a, 'L' b, game_id c, start_date_id d
from games, teams
where looser_team_id = team_id
order by a, d
/
Game
Team Won/ Game Start
Name Lost ID Date
---------- ---- ----- ---------
Giants L 2795 16-AUG-04
L 2803 17-AUG-04
Red Sox L 2773 13-AUG-04
W 2786 14-AUG-04
Yankees W 2773 13-AUG-04
L 2786 14-AUG-04
W 2795 16-AUG-04
W 2803 17-AUG-04
8 rows selected.
col e heading "Wins"
col f heading "Losses"
select a, sum(nvl(e,0)) e, sum(nvl(f,0)) f
from
(select team_name a, count(*) e, null f
from teams, games
where team_id = winner_team_id
group by team_name
union
select team_name a, null e, count(*) f
from teams, games
where team_id = looser_team_id
group by team_name)
group by a;
Game
Team Won/ Game Start
Name Lost ID Date
---------- ---- ----- ---------
Giants L 2795 16-AUG-04
L 2803 17-AUG-04
Red Sox L 2773 13-AUG-04
W 2786 14-AUG-04
Yankees W 2773 13-AUG-04
L 2786 14-AUG-04
W 2795 16-AUG-04
W 2803 17-AUG-04
8 rows selected.
col e heading "Wins"
col f heading "Losses"
select a, sum(nvl(e,0)) e, sum(nvl(f,0)) f
from
(select team_name a, count(*) e, null f
from teams, games
where team_id = winner_team_id
group by team_name
union
select team_name a, null e, count(*) f
from teams, games
where team_id = looser_team_id
group by team_name)
group by a;
Team
Name Wins Losses
---------- ---------- ----------
Giants 0 2
Red Sox 1 1
Yankees 3 1