13. 학사관리 DB, 테이블 조인

○ 테이블 조인

    - 두 개 이상의 테이블을 결합하여 데이터를 추출하는 기법
    - 두 테이블의 공통값을 이용하여 컬럼을 조합하는 수단

    - 형식

select 칼럼명
from 테이블1 join 테이블2
on 조건절;                     -- ANSI(표준) SQL문

select 칼럼명
from 테이블1, 테이블2
on 조건절;                     -- Oracle SQL문


select T1.*, T2.*               -- T1.모든칼럼, T2.모든칼럼
from T1 join T2
on T1.x=T2.x                    -- 테이블명.칼럼명 ( . : ~에 속해있는)

select T1.*, T2.*, T3.*               
from T1 join T2
on T1.x=T2.x join T3            -- 3개 테이블 조인
on T1.y=T3.y

select T1.*, T2.*, T3.*               
from T1 join T2
on T1.x=T2.x join T3            -- 3개 테이블 조인
on T1.y=T3.y join T4            -- 4개 테이블 조인
on T1.z=T4.z

 

○ 물리적 테이블, 논리적 테이블

    - 물리적 테이블 : 실제 create table 한 테이블
    - 논리적 테이블 : SQL 문에 의해 가공된 테이블
    - 물리적 테이블과 논리적 테이블은 서로 동등한 관계이다
    - 물리적 테이블과 논리적 테이블을 join 하는 것도 가능

 

학사관리 관련 테이블(tb_student, tb_gwamok, tb_sugang) 및 입력 데이터

 

● PRACTICE 연습문제

    - 학사관리 관련 테이블을 이용한 테이블 조인 연습문제

 

    Q1) 수강신청을 한 학생들 중에서 '제주'에 사는 학생들만 학번, 이름, 주소를 조회하시오

--Q1

-- 수강테이블 + 학생테이블
select SU.hakno, uname, address
from tb_sugang SU join tb_student ST
on SU.hakno=ST.hakno;

-- 제주에 사는 학생만 조회
select SU.hakno, ST.uname, ST.address  --ST. 생략가능
from tb_sugang SU join tb_student ST
on SU.hakno=ST.hakno
where address='제주';

-- 가공된 논리적 테이블의 이름을 지정하고 다시 재가공할 수 있다
select AA.hakno, AA.gcode, AA.uname, AA.address
from (
      select SU.hakno, SU.gcode, uname, address
      from tb_sugang SU join tb_student ST
      on SU.hakno=ST.hakno
     ) AA -- 논리적 테이블의 이름을 AA라 지정
where AA.address='제주';

-- 어느 테이블에 있는 칼럼인지 확실한 경우는 AA생략가능
select hakno, gcode, uname, address
from (
      select SU.hakno, SU.gcode, uname, address
      from tb_sugang SU join tb_student ST
      on SU.hakno=ST.hakno
     ) AA                                                           
where AA.address='제주';

    Q2) 과목별 수강 신청 인원수, 과목코드, 과목명을 조회하시오

--Q2

-- 과목별 수강 신청 인원수
select gcode, count(*)
from tb_sugang
group by gcode;

-- 과목별 수강 신청 인원수, 과목코드, 과목명을 조회
select SUCNT.gcode, GW.gname, SUCNT.cnt
from(select gcode, count(*) as cnt
    from tb_sugang
    group by gcode) SUCNT join tb_gwamok GW
on SUCNT.gcode=GW.gcode;

-- 과목코드별 정렬, alias부여
select SUCNT.gcode as 과목코드
      ,GW.gname    as 과목명
      ,SUCNT.cnt   as 수강신청인원수
from(select gcode, count(*) as cnt
    from tb_sugang
    group by gcode) SUCNT join tb_gwamok GW
on SUCNT.gcode=GW.gcode
order by SUCNT.gcode;


--강사님코드-------------------------------
    select AA.gcode, GW.gname, concat(AA.cnt, '명')
    from (
          select gcode, count(*) as cnt from tb_sugang group by gcode 
         ) AA join tb_gwamok GW
    on AA.gcode=GW.gcode
    order by AA.gcode;

    Q3) 지역별로 수강신청 인원수를 조회하시오 ( 예. 서울 2명, 제주 1명)

--Q3

-- 지역별 학생수
select address, count(*)
from tb_student
group by address

-- 수강신청 학생목록
select hakno from tb_sugang group by hakno;

-- 지역별로 묶기
select ST.address, concat(count(*), '명') as cnt
from (select hakno from tb_sugang group by hakno) AA join tb_student ST
on AA.hakno=ST.hakno
group by ST.address;


--강사님코드-------------------------------
select address, count(*) || '명'
from (
      select AA.hakno, address
      from(
          select hakno from tb_sugang group by hakno
          )AA join tb_student ST
      on AA.hakno=ST.hakno
    )BB
group by address;

    Q4) 학번별 수강신청과목의 총학점을 학번별 순으로 조회하시오
          G1001 홍길동 9학점
          G1002 홍길동 6학점
          G1005 진달래 9학점

--Q4

-- 학생들의 학번 조회
select uname, hakno
from tb_student;

-- 학번별 수강 과목 조회
select hakno, gcode 
from tb_sugang
order by hakno;

select hakno, count(*) 
from tb_sugang
group by hakno
order by hakno;

-- 수강+과목: 학생별 학점계산
select SU.hakno, sum(GW.ghakjum)
from tb_sugang SU join tb_gwamok GW
on SU.gcode=GW.gcode
group by SU.hakno;

-- 학번, 이름, 수강학점 출력
select AA.hakno, ST.uname, concat(AA.tothakjum, '학점') as 총학점 
from (
     select SU.hakno, sum(GW.ghakjum) as tothakjum
    from tb_sugang SU join tb_gwamok GW
    on SU.gcode=GW.gcode
    group by SU.hakno
     ) AA join tb_student ST
on AA.hakno=ST.hakno;


--강사님코드-------------------------------

--방법1
select SU.hakno, ST.uname, sum(GW.ghakjum) || '학점' as hap
from tb_sugang SU join tb_student ST
on SU.hakno=ST.hakno join tb_gwamok GW
on SU.gcode=GW.gcode
group by SU.hakno, ST.uname
order by SU.hakno;

--방법2
select BB.hakno, ST.uname, BB.hap ||'학점'
from(
     select hakno, sum(ghakjum) as hap
     from(
         select SU.hakno, Gw.ghakjum
         from tb_sugang SU join tb_gwamok GW
         on SU.gcode=GW.gcode
         )AA
     group by hakno
    )BB join tb_student ST
on BB.hakno=ST.hakno;

    Q5) 학번 g1001이 수강신청한 과목을 과목코드별로 조회하시오

          G1001    p001    OOP
          G1002    p003    JSP
          G1005   d001    HTML

--Q5

-- 1. 수강테이블에서 학번이 g1001인 학생이 수강한 과목코드 조회 
select hakno, gcode
from tb_sugang
where hakno = 'g1001';

-- 2. 1의 결과를 AA로 두고, 과목테이블과 조인해서 과목명까지 조회
select AA.hakno, AA.gcode, GW.gname
from(
    select hakno, gcode from tb_sugang where hakno = 'g1001'
    )AA join tb_gwamok GW
on AA.gcode=GW.gcode
order by AA.gcode;


--강사님코드-------------------------------
select SU.hakno, SU.gcode, GW.gname
from tb_sugang SU join tb_gwamok GW
on SU.gcode=GW.gcode
where SU.hakno='g1001'
order by SU.gcode;

    Q6) 수강신청을 한 학생들의 학번, 이름 조회

--Q6

-- 1. 수강테이블과 학생테이블을 조인해서 수강신청한 학생의 학번과 이름 조회
select ST.hakno, uname
from tb_student ST join tb_sugang SU
on ST.hakno=SU.hakno;

-- 2. 1에서 학번으로 그룹화해서 겹치는 학번은 1번만 출력    
select ST.hakno
from tb_student ST join tb_sugang SU
on ST.hakno=SU.hakno
group by ST.hakno;

-- 3. 2를 AA로 두고 다시한번 학생테이블과 조인해서 수강신청한 학생의 학번과 이름 출력
select AA.hakno, uname
from (
    select ST.hakno
    from tb_student ST join tb_sugang SU
    on ST.hakno=SU.hakno
    group by ST.hakno
    )AA join tb_student ST2
on AA.hakno=ST2.hakno;

--강사님코드-------------------------------
select hakno, uname
from tb_student
where hakno in (select distinct(hakno) from tb_sugang);

    Q7) 지역별로 수강신청 인원수를 조회하시오 ( 예. 서울 2명, 제주 1명)

--Q7

-- 1. 학생테이블과 수강테이블 조인해서 수강신청한 학생들의 학번출력
select ST.hakno
from tb_student ST join tb_sugang SU
on ST.hakno=SU.hakno
group by ST.hakno;

-- 2. 1을 이용해서 수강하지 않은 학생들의 학번, 이름 조회
select hakno, uname
from tb_student
where hakno not in(
                    select ST.hakno
                    from tb_student ST join tb_sugang SU
                    on ST.hakno=SU.hakno
                    group by ST.hakno
                   );


--강사님코드-------------------------------
select hakno, uname
from tb_student
where hakno not in (select distinct(hakno) from tb_sugang);