기말고사 프로젝트 - 4 SQL시나리오 10개

//팀승률
select team_name,
       to_char(team_win/(team_win+team_lose)*100,'99.00')lank 
       from team
       order by lank desc;

//입력받은 맵에서 각 종족당 총승률
select map_name,
       to_char((map_zvp_zwin/(map_zvp_zwin+map_zvp_pwin)*100
           +map_tvz_zwin/(map_tvz_twin+map_tvz_zwin)*100)/2,'99.00') AS zerg,
       to_char((map_pvt_pwin/(map_pvt_pwin+map_pvt_twin)*100
           +map_zvp_pwin/(map_zvp_zwin+map_zvp_pwin)*100)/2,'99.00') AS protoss,
       to_char((map_tvz_twin/(map_tvz_twin+map_tvz_zwin)*100
           +map_pvt_twin/(map_pvt_pwin+map_pvt_twin)*100)/2,'99.00') AS terran
       from map
       where map_name='&맵이름';

//다승 순위
select gamer_name,gamer_id, gamer_zwin+gamer_twin+gamer_pwin total
       from progamer
       order by total desc;

//입력받은 프로게이머의 각 종족당 승률
select gamer_name,
       to_char(gamer_zwin/(gamer_zwin+gamer_zlose)*100,'99.00')저그전,
       to_char(gamer_twin/(gamer_twin+gamer_tlose)*100,'99.00')테란전,
       to_char(gamer_pwin/(gamer_pwin+gamer_plose)*100,'99.00')프로전
        from progamer
       where gamer_name='&프로게이머이름';

//종족당 게이머수
select count(*)
       from progamer
       where gamer_main='&종족';

//대회명 입력시 우승자정보
select '&리그이름' league_name,gamer_name,gamer_id,gamer_age,team_name
       from progamer
       where gamer_name=
       (select league_final from league
               where league_name='&리그이름'
                     and league_type='개인전');

//우승상금 많이 받은 선수
select league_final,(sum(league_final_money)+
      sum(league_semifinal_money)) total_money
      from league where league_type='&league_type'
      group by league_final
      order by total_money desc

//팀이름입력시 JOIN을 사용한 팀원개인정보
select p.gamer_name, p.gamer_id,p.gamer_main,t.team_name
       from progamer p join team t
       on p.team_name=t.team_name
          and t.team_name='&팀이름'

//팀창단일로부터 지난 시간들
select team_name,team_date,
       SYSDATE-team_date 지난일,
       (SYSDATE-team_date)/7 지난주,
       (SYSDATE-team_date)/30 지난달,
       (SYSDATE-team_date)/365 지난년
       from team

//팀전 총 포인트
set feedback off
ttitle '플레이오프 진출 유력팀'
btitle 'total point'
select team_name,team_win,team_lose,team_foul,
       nvl2(team_foul,team_win-team_lose-team_foul/2
            ,team_win-team_lose) total_point
       from team