SQL활용_#4_답.pdf

SQL 활용 실습자료 #4 << 내부조인(INNER JOIN), 자연조인(NATURAL JOIN) 익히기>>

1. SELECT * FROM DEPT INNER JOIN EMP E ON DEPT.DEPTNO = E.DEPTNO; 와 SELECT * FROM EMP E INNER JOIN DEPT ON DEPT.DEPTNO = E.DEPTNO; 와의 차이점은?

   • 출력

2. 1)을 NATURAL JOIN하시오. 1)의 결과와 어떤 차이가 있는가?

   • 출력

3. SELECT t.team_id, t.region_name, p.player_name FROM team t join player p ON (t.team_id = p.team_id);을 실행하여 보자. 위의 컬럼들을 출력하는데, 연고지가 ‘강원’인 선수들만 검색하시오.

   • 출력

4. p.321 예제에서 두 SQL의 실행결과는 차이가 있는가? 실행결과를 이용하여 홈팀점수와 어웨이팀 점수의 최대점수를 구하시오.(홈최대접수, 어웨이최대점수 출력)

   -- [예제]
   -- 홈팀이 3점 이상 차이로 승리한 경기의 경기장 이름, 경기장번호, 경기일정, 홈팀이름, 어웨이팀이름, 홈팀스코어,어웨이팀스코어 정보 출력
   select st.stadium_name 경기장이름,
           st.stadium_id 경기장번호,
           sc.sche_date 경기일정, 
           t1.team_name 홈팀이름,
           t2.team_name 어웨이팀이름,
           sc.home_score 홈스코어,
           sc.away_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 sc.home_score - sc.away_score >= 3;
   
   select st.stadium_name 경기장이름,
           st.stadium_id 경기장번호,
           sc.sche_date 경기일정,
           t1.team_name 홈팀이름,
           t2.team_name 어웨이팀이름,
           sc.home_score 홈스코어,
           sc.away_score 어웨이스코어
   from schedule sc, stadium st, team t1, team t2
   where sc.stadium_id = st.stadium_id
   and t1.team_id = sc.hometeam_id
   and t2.team_id = sc.awayteam_id
   and sc.home_score - sc.away_score >= 3;
   

   • 출력

5. p.325-328 예제로 left out join, right out join, full out join을 익히시오. p.330 집합연산자

   • 출력

6. ‘전남’에 속한 선수 중에서 포지션이 ‘GK’인 선수들과 ‘서울’에 속한 선수 중에서 몸무게가 65이상인 선수들을 합집합하시오. ( 연고지명, 선수명, 포지션, 몸무게 순으로 (선수명, 포지션)순으로 오름차순으로 출력하시오 ), 가) 합집합 대신에 조건절에 이용한 일반 SQL문으로 변경하여 결과를 비교하시오.

   select t.region_name 연고지명,
           p.player_name 선수명,
           p.position 포지션,
           p.weight 몸무게
   from team t
   join player p on t.team_id = p.team_id
   where t.region_name = '전남'
   and p.position = 'GK'
   UNION
   select t2.region_name 연고지명,
           p2.player_name 선수명,
           p2.position 포지션,
           p2.weight 몸무게
   from team t2
   join player p2 on t2.team_id = p2.team_id
   where t2.region_name = '서울'
   and p2.weight >= 65
   order by 선수명, 포지션;
   
   select t.region_name 연고지명,
           p.player_name 선수명,
           p.position 포지션,
           p.weight 몸무게
   from team t
   join player p on t.team_id = p.team_id
   where ( t.region_name = '전남' and p.position = 'GK' )
   or ( t.region_name = '서울' and p.weight >= 65 )
   order by 선수명, 포지션;
   

   • 출력

p.339번 예제 4)번에서 미더필더(MF)가 아닌 선수들의 정보를 보고 싶다로 수정요망.

1. ‘전남’에 속한 선수 중에서 포지션이 ‘GK’가 아닌 선수들을 차집합(MINUS)f으로 구하시오. ( 연고지명, 선수명, 포지션, 몸무게 순으로 (선수명, 포지션)순으로 오름차순으로 출력하시오 ), 가) 차집합 대신에 조건절에 이용한 일반 SQL문으로 변경하여 결과를 비교하시오.

   select t.region_name 연고지명,
           p.player_name 선수명,
           p.position 포지션,
           p.weight 몸무게
   from team t
   join player p on t.team_id = p.team_id
   where t.region_name = '전남'
   MINUS
   select t2.region_name 연고지명,
           p2.player_name 선수명,
           p2.position 포지션,
           p2.weight 몸무게
   from team t2
   join player p2 on t2.team_id = p2.team_id
   where p2.position = 'GK'
   order by 선수명, 포지션;
   --
   select t.region_name 연고지명,
           p.player_name 선수명,
           p.position 포지션,
           p.weight 몸무게
   from team t
   join player p on t.team_id = p.team_id
   where t.region_name = '전남'
   and p.position != 'GK'
   order by 선수명, 포지션;
   

   • 출력

2. ‘전남’에 속한 선수 중에서 포지션이 ‘GK’가 아닌 선수들 교집합(intersect)으로 구하시오. ( 연고지명, 선수명, 포지션, 몸무게 순으로 (선수명, 포지션)순으로 오름차순으로 출력하시오 ), 가) 교집합 대신에 조건절에 이용한 일반 SQL문으로 변경하여 결과를 비교하시오.

   select t.region_name 연고지명,
           p.player_name 선수명,
           p.position 포지션,
           p.weight 몸무게
   from team t
   join player p on t.team_id = p.team_id
   where t.region_name = '전남'
   INTERSECT
   select t2.region_name 연고지명,
           p2.player_name 선수명,
           p2.position 포지션,
           p2.weight 몸무게
   from team t2
   join player p2 on t2.team_id = p2.team_id
   where p2.position != 'GK'
   order by 선수명, 포지션;
   

   • 출력

제4절 Subquery

1. 포지션이 ‘GK’인 선수들의 평균몸무게(subquery)보다 크거나 같은 선수들을 단일행 서브쿼리로 구하시오. ( 선수명, 포지션, 몸무게 순으로 선수명순으로 내림차순으로 출력하시오 ),

   select p.player_name 선수명,
           p.position 포지션,
           p.weight 몸무게
   from player p
   where p.weight >= ( select avg(p2.weight)
                       from player p2 
                       where p2.position = 'GK')
   order by 선수명 desc;
   

   • 출력

2. 선수들 중에서 ‘정진우’가 소속된 팀정보를 서브쿼리로 구하시오. ( 연고지명, 팀명, 영문팀명 순으로 출력하시오 ), 가) 일반 SQL문으로 변경하여 결과를 비교하시오.

   select t.region_name 연고지명,
           t.team_name 팀명,
           t.e_team_name 영문팀명
   from team t
   where t.team_id in ( select p2.team_id
                       from player p2
                       where p2.player_name = '정진우' );
   
   select t.region_name 연고지명,
           t.team_name 팀명,
           t.e_team_name 영문팀명
   from team t
   join player p on t.team_id = p.team_id
   where p.player_name = '정진우';
   

   • 출력