SQL 실습자료 #3 사용자 scott의 K-리그 DB(TEAM, PLAYER, STADIUM, SCHEDULE 테이블)에서 SQL을 구하시오.

  1. 본인이 좋아하는 팀의 스타디움에 임의의 일정(예:2014-09-05)을 갖는 SCHEDULE 테이블에 등록하는 SQL문을 작성하시오. 어웨이팀과 스코어를 임의의 값으로 입력하시오.

    insert into stadium values('B05', '20130915', 'Y', 'K09', 'K05', 3, 2);

  2. ‘서울’에 속한 선수 중에서 키가 180과 190사이의 선수들을 조회하되, 운동장명, 창단연도, 선수이름,키를 별명으로 하여 키 순으로 내림차순 정렬하여 출력하시오. ( 3개의 테이블이 관련됨 )

    select st.stadium_name 운동장명, 
				t.orig_yyyy 창단연도, 
				p.player_name 선수이름, 
				p.height 키
from stadium st, team t, player p
where (st.stadium_id = t.stadium_id and t.team_id = p.team_id)
and t.region_name = '서울'
and (p.height >= 180 and p.height <= 190)
order by 키 desc;

  3. 각 구단별 홈 경기장의 좌석수를 내림차순으로 출력하시오. ( 연고지명, 팀명, 운동장명, 좌석수 출력)

    select t.region_name 연고지명, 
        t.team_name 팀명, 
        st.stadium_name 운동장명, 
        st.seat_count 좌석수
from stadium st,team t
where st.stadium_id = t.stadium_id
order by st.seat_count desc;

  4. 각 경기장별로 진행된 게임에서 홈스코어와 어웨이 스코어의 합을 구하여 출력하시오. ( 운동장명, 홈스코어의 합, 어웨이스코어의 합 출력 )

    select st.stadium_name 운동장명, 
        sum(sc.home_score) as "홈스코어의 합", 
        sum(sc.away_score) as "어웨이스코어의 합"
from stadium st, schedule sc
where st.stadium_id = sc.stadium_id
group by st.stadium_name;

  5. 각 소속팀별 입단년도가 2011년인 축구선수 수를 검색하되 몸무게의 평균을 구하여 소수점 1자리에서 반올림하여 출력하세요. ( 팀명, 축구선수수, 평균몸무게 순서로 출력 )

    select t.team_name 팀명, 
        count(*) 축구선수수, 
        ROUND(avg(p.weight)) 평균몸무게
from team t, player p
where t.team_id = p.team_id
group by t.team_name;

  6. 홈스코어와 어웨이 스코어의 차가 3점 이상인 경기를 조회하여 다음의 순으로 출력하시오. ( 홈팀명, 어웨이팀명, 홈스코어, 어웨이스코어, 스코어의 차 출력 )

    select t1.team_name 홈팀명,
        t2.team_name 어웨이팀명,
        sc.home_score 홈스코어,
        sc.away_score 어웨이스코어,
        abs(sc.home_score - sc.away_score) "스코어의 차"
from schedule sc 
join team t1 on sc.hometeam_id = t1.team_id
join team t2 on sc.awayteam_id = t2.team_id
where abs(sc.home_score - sc.away_score) >= 3;

  7. ‘전남’ 선수 중에서 선수명, 입단년도, 백넘버를 출력하되 백넘버 오름차순으로 출력하시오.

    select p.player_name 선수명,
        p.join_yyyy 입단년도,
        p.back_no 백넘버
from player p
join team t on p.team_id = t.team_id
where t.region_name = '전남'
order by p.back_no;

select p.player_name 선수명,
        p.join_yyyy 입단년도,
        p.back_no 백넘버
from player p, team t
where p.team_id = t.team_id
and t.region_name = '전남'
order by 백넘버;

  8. 팀별 축구선수 수를 출력하시오. ( 팀명, 축구선수수 출력 )

    select t.team_name 팀명,
        count(*) 축수선수수
from team t
join player p on t.team_id = p.team_id
group by t.team_name;

select t.team_name 팀명,
        count(*) 축구선수수
from team t, player p
where t.team_id = p.team_id
group by t.team_name;

  9. 일정 및 스코어 테이블을 참조하여 스코어차이가 0인 즉 비긴 게임을 검색하여 언제 어떤 경기장에서 였는지를 출력하시오. ( 경기일자, 경기장명, 좌석수, 홈팀명, 어웨이팀명, 홈스코어 출력)

    select sc.sche_date 경기일자,
        st.stadium_name 경기장명,
        st.seat_count 좌석수,
        t1.team_name 홈팀명,
        t2.team_name 어웨이팀명,
        sc.home_score 홈스코어
from schedule sc
join stadium st on st.stadium_id = sc.stadium_id
join team t1 on sc.hometeam_id = t1.team_id
join team t2 on sc.awayteam_id = t2.team_id
where abs(sc.home_score - sc.away_score) = 0;

select sc.sche_date 경기일자,
        st.stadium_name 경기장명,
        st.seat_count 좌석수,
        t1.team_name 홈팀명,
        t2.team_name 어웨이팀명,
        sc.home_score 홈스코어
from schedule sc, stadium st, team t1, team t2
where sc.stadium_id = st.stadium_id
and sc.hometeam_id = t1.team_id
and sc.awayteam_id = t2.team_id
and abs(sc.home_score - sc.away_score) = 0;

  10. 포지션별 선수들의 팀명, 선수명, 국적 을 출력하되, 팀명 순으로 오름차순, 같은 팀에서는 선수명을 내림차순으로 정렬하여 출력하시오. ( 국적이 null 값이거나 공백이면 ‘한국’으로 출력한다.

    select t.team_name 팀명,
        p.player_name 선수명,
        case when p.nation is null then '한국' 
             when p.nation = '' then '한국'
             else p.nation 
        end 
from team t
join player p on t.team_id = p.team_id
order by 팀명, 선수명 desc;

select t.team_name 팀명,
        p.player_name 선수명,
        case when p.nation is null then '한국'
             when p.nation = '' then '한국'
             else p.nation
        end
from team t, player p
where t.team_id = p.team_id
order by 팀명, 선수명 desc;