16. 학사관리 DB 관련 종합연습문제

● PRACTICE 연습문제

    - 학사관리 관련 종합연습문제 (테이블 조인)

    Q1) 디자인 교과목 중에서 학점이 제일 많은 교과목을 수강신청한 명단을 조회하시오 
          (학번, 이름, 과목코드)

-- 방법1

-- 0. 
select * from tb_sugang order by gcode;

-- 1. 디자인 교과목 학점별 내림차순 정렬
select gcode, gname, ghakjum
from tb_gwamok
where gcode like 'd%'
order by ghakjum desc;

-- 2. 1의 결과에 줄번호 추가
select gcode, gname, ghakjum, rownum as rnum
from (    
        select gcode, gname, ghakjum
        from tb_gwamok
        where gcode like 'd%'
        order by ghakjum desc
      );

-- 3. 2의 결과를 AA로 두고, 수강테이블과 AA테이블을 조인
select SU.gcode, hakno
from tb_sugang SU join (
                        select gcode, gname, ghakjum, rownum as rnum
                        from (    
                                select gcode, gname, ghakjum
                                from tb_gwamok
                                where gcode like 'd%'
                                order by ghakjum desc
                              )
                        )AA
on SU.gcode=AA.gcode;

-- 4. 3에서 학점이 가장 높은 수업에 대해서만 조회
select SU.gcode, hakno
from tb_sugang SU join (
                        select gcode, gname, ghakjum, rownum as rnum
                        from (    
                                select gcode, gname, ghakjum
                                from tb_gwamok
                                where gcode like 'd%'
                                order by ghakjum desc
                              )
                        )AA
on SU.gcode=AA.gcode
where rnum=1;

-- 5. 4에서 학생테이블까지 3개의 테이블을 조인해서 최종답 출력 
select ST.hakno, ST.uname, SU.gcode 
from tb_sugang SU join (
                        select gcode, gname, ghakjum, rownum as rnum
                        from (    
                                select gcode, gname, ghakjum
                                from tb_gwamok
                                where gcode like 'd%'
                                order by ghakjum desc
                              )
                        )AA
on SU.gcode=AA.gcode join tb_student ST
on SU.hakno=ST.hakno
where rnum=1;


-- 방법2

-- 1. 학생, 수강, 과목 테이블 조인
select ST.*, SU.*, GW.*
from tb_student ST left join tb_sugang SU
on ST.hakno=SU.hakno left join tb_gwamok GW
on SU.gcode=GW.gcode;

-- 2. 1번을 과목별 학점으로 내림차순 정렬(1차 정렬)하고, 과목코드별로 2차 정렬
select ST.*, SU.*, GW.*
from tb_student ST left join tb_sugang SU
on ST.hakno=SU.hakno left join tb_gwamok GW
on SU.gcode=GW.gcode
order by nvl(ghakjum, 0) desc, SU.gcode;

-- 3. 2번에서 과목코드를 이용해서 디자인 과목만 조회(학점별 내림차순)
select ST.*, SU.*, GW.*
from tb_student ST left join tb_sugang SU
on ST.hakno=SU.hakno left join tb_gwamok GW
on SU.gcode=GW.gcode
where GW.gcode like 'd%'
order by nvl(ghakjum, 0) desc, SU.gcode;

-- 4. 학점이 제일 높은 과목이 rownum=1임을 이용해서 최종답 구하기(학번, 이름, 과목코드)
select hakno, uname, gcode
from(
    select ST.hakno, ST.uname, SU.gcode, GW.ghakjum
    from tb_student ST left join tb_sugang SU
    on ST.hakno=SU.hakno left join tb_gwamok GW
    on SU.gcode=GW.gcode
    where GW.gcode like 'd%'
    order by nvl(ghakjum, 0) desc, SU.gcode
    )
where rownum=1; 

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

    -- 1) 디자인 교과목에서 학점이 제일 많은 교과목의 과목코드 조회
    select max(ghakjum) from tb_gwamok where gcode like 'd%';

    -- 2) 1)에서 나온 학점(5학점)과 동일한 학점을 가지고 있는 행에서 과목코드 선택
    --    즉, 디자인 교과목 중에서 학점이 제일 많은 과목의 과목코드
    select gcode 
    from tb_gwamok 
    where ghakjum=(select max(ghakjum) from tb_gwamok where gcode like 'd%')
          and gcode like 'd%';

    -- 3) 2)에서 나온 과목코드(d002)를 수강신청한 명단을 수강테이블에서 확인
    select gcode, hakno
    from tb_sugang
    where gcode=(select gcode 
                 from tb_gwamok 
                 where ghakjum=(select max(ghakjum) from tb_gwamok where gcode like 'd%')
                       and gcode like 'd%');

    -- 4) 3의 결과를 AA테이블로 만들고, 학생 테이블을 조인해서 이름 가져오기
    select AA.gcode, AA.hakno, ST.uname
    from (
            select gcode, hakno
            from tb_sugang
            where gcode=(select gcode 
                         from tb_gwamok 
                         where ghakjum=(select max(ghakjum) from tb_gwamok where gcode like 'd%')
                               and gcode like 'd%')
         ) AA join tb_student ST
    on AA.hakno=ST.hakno;

    -- 4-1) 다른방법
    select ST.hakno, ST.uname, SU.gcode
    from tb_sugang SU join tb_student ST
    on SU.hakno=ST.hakno
    where gcode=(select gcode 
                 from tb_gwamok 
                 where ghakjum=(select max(ghakjum) from tb_gwamok where gcode like 'd%')
                       and gcode like 'd%');

    Q2) 학번별 수강신청한 총학점을 구하고 학번별 정렬해서 줄번호 4~6만 조회하시오
         (단, 수강신청하지 않은 학생의 총학점도 0으로 표시)

-- 방법1

-- 0. 학생, 수강, 과목 테이블을 left join해서 자료 살펴보기
select ST.*, SU.*, GW.*
from tb_student ST left join tb_sugang SU
on ST.hakno=SU.hakno left join tb_gwamok GW
on SU.gcode=GW.gcode;

-- 1. 학생, 수강, 과목 테이블을 left join해서 필요한 정보 조회    
select ST.hakno, ST.uname, SU.gcode, GW.ghakjum
from tb_student ST left join tb_sugang SU
on ST.hakno=SU.hakno left join tb_gwamok GW
on SU.gcode=GW.gcode;

-- 2. 1의 결과를 학번별로 그룹화해서 학점합 구하기(null은 0으로), 학번순으로 정렬
select hakno, nvl(sum(ghakjum),0)
from (
        select ST.hakno, ST.uname, SU.gcode, GW.ghakjum
        from tb_student ST left join tb_sugang SU
        on ST.hakno=SU.hakno left join tb_gwamok GW
        on SU.gcode=GW.gcode
      )
group by hakno
order by hakno;

    -- 2. (2에서 이름까지 출력하고 싶다면) 
    --   1의 결과를 1차 학번별, 2차 이름별로 그룹화해서 학점합 구하기 (null은 0으로), 
    --   학번순으로 정렬
    select hakno, uname, nvl(sum(ghakjum),0)
    from (
            select ST.hakno, ST.uname, SU.gcode, GW.ghakjum
            from tb_student ST left join tb_sugang SU
            on ST.hakno=SU.hakno left join tb_gwamok GW
            on SU.gcode=GW.gcode
          )
    group by hakno, uname
    order by hakno;


-- 3. 2의 결과에 줄번호 붙여주기
select hakno, tothakjum, rownum as rnum
from (
        select hakno, nvl(sum(ghakjum),0) as tothakjum
        from (
                select ST.hakno, ST.uname, SU.gcode, GW.ghakjum
                from tb_student ST left join tb_sugang SU
                on ST.hakno=SU.hakno left join tb_gwamok GW
                on SU.gcode=GW.gcode
              )
        group by hakno
        order by hakno
     ); 

-- 4. 4의 결과에서 줄번호 4~6만 조회 
select rnum, hakno, tothakjum
from (
        select hakno, tothakjum, rownum as rnum
        from (
                select hakno, nvl(sum(ghakjum),0) as tothakjum
                from (
                        select ST.hakno, ST.uname, SU.gcode, GW.ghakjum
                        from tb_student ST left join tb_sugang SU
                        on ST.hakno=SU.hakno left join tb_gwamok GW
                        on SU.gcode=GW.gcode
                      )
                group by hakno
                order by hakno
             )
     )
where rnum>=4 and rnum<=6;


-- 방법2

-- 1. 학생, 수강, 과목 테이블 조인
select ST.*, SU.*, GW.*
from tb_student ST left join tb_sugang SU
on ST.hakno=SU.hakno left join tb_gwamok GW
on SU.gcode=GW.gcode;

-- 2. 1에서 학번별로 그룹화해서 총학점 구하기
select ST.hakno, sum(gw.ghakjum)
from tb_student ST left join tb_sugang SU
on ST.hakno=SU.hakno left join tb_gwamok GW
on SU.gcode=GW.gcode
group by ST.hakno;

-- 3. 2에서 총학점이 null인 곳은 0으로 출력하고, 학번별로 정렬
select ST.hakno, nvl(sum(gw.ghakjum),0) haksum
from tb_student ST left join tb_sugang SU
on ST.hakno=SU.hakno left join tb_gwamok GW
on SU.gcode=GW.gcode
group by ST.hakno
order by ST.hakno;

-- 4. 3에 줄번호 부여
select hakno, haksum, rownum as rnum
from (
        select ST.hakno, nvl(sum(gw.ghakjum),0) haksum
        from tb_student ST left join tb_sugang SU
        on ST.hakno=SU.hakno left join tb_gwamok GW
        on SU.gcode=GW.gcode
        group by ST.hakno
        order by ST.hakno
     );

-- 5. 4에서 줄번호 4~6만 출력
select hakno, haksum, rnum
from(
    select hakno, haksum, rownum as rnum
    from (
            select ST.hakno, nvl(sum(gw.ghakjum),0) haksum
            from tb_student ST left join tb_sugang SU
            on ST.hakno=SU.hakno left join tb_gwamok GW
            on SU.gcode=GW.gcode
            group by ST.hakno
            order by ST.hakno
         )
    )
where rnum>=4 and rnum<=6;

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

    -- 1) 수강 신청한 과목의 학점 가져오기
    select SU.hakno, SU.gcode, GW.ghakjum
    from tb_sugang SU join tb_gwamok GW
    on SU.gcode=GW.gcode;

    -- 2) 학번별로 총 학점 구하기
    select SU.hakno, SUM(GW.ghakjum) as 총학점
    from tb_sugang SU join tb_gwamok GW
    on SU.gcode=GW.gcode
    group by SU.hakno;

    -- 3) 수강신청하지 않은 학생들도 가져올 수 있도록 학생테이블을 left join으로
    --    2)의 결과(AA)와 붙이기
    select ST.hakno,ST.uname, AA.총학점 
    from tb_student ST left join(
                                select SU.hakno, SUM(GW.ghakjum) as 총학점
                                from tb_sugang SU join tb_gwamok GW
                                on SU.gcode=GW.gcode
                                group by SU.hakno
                                 ) AA
    on ST.hakno=AA.hakno; 

    -- 4) 총학점이 null이면 0으로 바꾸고 학번별로 조회하기
    select ST.hakno,ST.uname, nvl(AA.총학점,0) 
    from tb_student ST left join(
                                select SU.hakno, SUM(GW.ghakjum) as 총학점
                                from tb_sugang SU join tb_gwamok GW
                                on SU.gcode=GW.gcode
                                group by SU.hakno
                                 ) AA
    on ST.hakno=AA.hakno
    order by ST.hakno; 

    -- 5) 줄번호 추가 (줄번호가 있는 상태에서 정렬됨)
    select ST.hakno,ST.uname, nvl(AA.총학점,0), rownum as rnum 
    from tb_student ST left join(
                                select SU.hakno, SUM(GW.ghakjum) as 총학점
                                from tb_sugang SU join tb_gwamok GW
                                on SU.gcode=GW.gcode
                                group by SU.hakno
                                 ) AA
    on ST.hakno=AA.hakno
    order by ST.hakno; 

    --6) 5)의 결과를 셀프조인하고 나서 줄번호 추가하기
    select hakno, uname, 총학점, rownum as rnum
    from (
            select ST.hakno, ST.uname, nvl(AA.총학점, 0) as 총학점  
            from tb_student ST left join (     
                                            select SU.hakno, sum(GW.ghakjum) as 총학점
                                            from tb_sugang SU join tb_gwamok GW
                                            on SU.gcode=GW.gcode
                                            group by SU.hakno
                                         ) AA
            on ST.hakno=AA.hakno
            order by ST.hakno
    )BB;

    --7) 6)의 결과를 셀프조인하고 줄번호(rnum)4~6 조회하기
    select hakno, 총학점, rnum
    from (
            select hakno, uname, 총학점, rownum as rnum
            from (
                    select ST.hakno, ST.uname, nvl(AA.총학점, 0) as 총학점  
                    from tb_student ST left join (     
                                                    select SU.hakno, sum(GW.ghakjum) as 총학점
                                                    from tb_sugang SU join tb_gwamok GW
                                                    on SU.gcode=GW.gcode
                                                    group by SU.hakno
                                                 ) AA
                    on ST.hakno=AA.hakno
                    order by ST.hakno
                 )
    )
    where rnum>=4 and rnum<=6;

    Q3) 학번별로 수강신청 총학점을 구하고, 총학점 순으로 내림차순 정렬 후,
          위에서부터 1건만 조회하시오 (학번, 이름, 총학점)

-- 1. 문2-2에서 학번별로 학점 총합을 구한것을 총학점 순으로 내림차순 정렬
select hakno, nvl(sum(ghakjum),0) as tothakjum
from (
        select ST.hakno, ST.uname, SU.gcode, GW.ghakjum
        from tb_student ST left join tb_sugang SU
        on ST.hakno=SU.hakno left join tb_gwamok GW
        on SU.gcode=GW.gcode
      )
group by hakno
order by tothakjum desc;

-- 2. 1의 결과에 줄번호 붙여주기
select hakno, tothakjum, rownum
from (
        select hakno, nvl(sum(ghakjum),0) as tothakjum
        from (
                select ST.hakno, ST.uname, SU.gcode, GW.ghakjum
                from tb_student ST left join tb_sugang SU
                on ST.hakno=SU.hakno left join tb_gwamok GW
                on SU.gcode=GW.gcode
              )
        group by hakno
        order by tothakjum desc
     );

-- 3. 2의 결과에서 위의 1건만 조회
select hakno, tothakjum, rownum
from (
        select hakno, nvl(sum(ghakjum),0) as tothakjum
        from (
                select ST.hakno, ST.uname, SU.gcode, GW.ghakjum
                from tb_student ST left join tb_sugang SU
                on ST.hakno=SU.hakno left join tb_gwamok GW
                on SU.gcode=GW.gcode
              )
        group by hakno
        order by tothakjum desc
     )
where rownum=1;


-- 방법2)

-- 1) 3개 테이블 조인 후, 학번과 이름으로 그룹화 하여 총학점 구하기
select ST.hakno, ST.uname, nvl(sum(ghakjum),0) as 총학점
from tb_student ST left join tb_sugang SU
on ST.hakno=SU.hakno left join tb_gwamok GW
on SU.gcode=GW.gcode
group by ST.hakno, ST.uname
order by 총학점 desc;

-- 2) 1)을 셀프조인하고 rownum이용해서 윗줄만 출력
select hakno, uname, 총학점
from (
        select ST.hakno, ST.uname, nvl(sum(ghakjum),0) as 총학점
        from tb_student ST left join tb_sugang SU
        on ST.hakno=SU.hakno left join tb_gwamok GW
        on SU.gcode=GW.gcode
        group by ST.hakno, ST.uname
        order by 총학점 desc
     )
where rownum=1;



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

    --1) 과목코드가 일치하는 학점 가져오기
    select SU.hakno, SU.gcode, GW.ghakjum
    from tb_sugang SU join tb_gwamok GW
    on SU.gcode=GW.gcode;

    --2) 학번별로 총학점 구하고, 총학점순으로 내림차순 정렬하기
    select SU.hakno, sum(GW.ghakjum) as 총학점
    from tb_sugang SU join tb_gwamok GW
    on SU.gcode=GW.gcode
    group by SU.hakno
    order by sum(GW.ghakjum) desc;

    --3) 2)의 결과를 AA테이블로 만들고, 학생테이블 조인해서 이름 가져오기
    select AA.hakno, AA.총학점, ST.uname, rownum as rnum
    from (
            select SU.hakno, sum(GW.ghakjum) as 총학점
            from tb_sugang SU join tb_gwamok GW
            on SU.gcode=GW.gcode
            group by SU.hakno
            order by sum(GW.ghakjum) desc
          ) AA join tb_student ST
    on AA.hakno=ST.hakno;

    --4) 3)의 결과를 셀프조인하고 줄번호(rnum)를 이용해서 위에서부터 1건만 조회하기
    select hakno, uname, 총학점
    from (
            select AA.hakno, AA.총학점, ST.uname, rownum as rnum
            from (
                    select SU.hakno, sum(GW.ghakjum) as 총학점
                    from tb_sugang SU join tb_gwamok GW
                    on SU.gcode=GW.gcode
                    group by SU.hakno
                    order by sum(GW.ghakjum) desc
                  ) AA join tb_student ST
            on AA.hakno=ST.hakno
         )
    where rnum=1;