[SQL] 학사프로그램 inner join 활용

[inner join]

⊙ 교집합을 찾는 조인 (inner 생략가능)

⊙ inner join을 활용

select SU.hakno, STU.uname, GW.gcode, GW.gname,   GW.ghakjum  from tb_sugang SU inner join tb_student STU   on SU.hakno=STU.hakno inner join tb_gwamok GW  on SU.gcode=GW.gcode  order by SU.hakno;

 

 

 

문1) 위 결과를 바탕으로 학번별로 수강신청과목의 총 학점을 조회하시오 

방법 1> inner join

select SU.hakno, STU.uname, sum(GW. ghakjum)     from tb_sugang SU inner join tb_student STU     on SU.hakno=STU.hakno inner join tb_gwamok GW     on SU.gcode=GW.gcode     group by SU.hakno, STU.uname     order by SU.hakno;

 

방법2> 전체 AA 이용

select AA.hakno, AA.uname, sum(AA.ghakjum)  from(      select SU.hakno, STU.uname, GW.gcode, GW.gname,             GW.ghakjum      from tb_sugang SU inner join tb_student STU      on SU.hakno=STU.hakno inner join tb_gwamok GW      on SU.gcode=GW.gcode      order by SU.hakno          ) AA  group by AA.hakno, AA.uname  order by AA.hakno;

 

문2) 위 결과에서 학번별로 디자인 교과목 수강신청과목의 총 학점을 조회하시오

① gcode 에 d가 들어가는 것이 '디자인 교과목'

select hakno, gcode from tb_sugang where gcode like 'd%';

 

 

② ①을 이용하여 d만 골라내어 총학점의 합을 찾아냄

select BB.hakno, STU.uname, sum(BB.ghakjum) from(         select AA.hakno, AA.gcode, GW.ghakjum         from (                   select hakno, gcode                   from tb_sugang                   where gcode like 'd%'                 ) AA join tb_gwamok GW         on AA.gcode=GW.gcode         ) BB join tb_student STU on BB.hakno=STU.hakno group by BB>hakno, STU.uname;

 

 

문3) 과목코드 p001을 신청한 학생들의 명단을 조회

 

 

 

방법 1>	방법2>
select  SU.gcode, GW.gname, SU.hakno, STU.uname  from tb_student STU join tb_sugang SU  on STU.hakno=SU.hakno join tb_gwamok GW  on SU.gcode=GW.gcode  where SU.gcode='p001';	select AA.gcode, GW.gname, AA.hakno, AA.uname   from (         select SU.gcode,STU.hakno, STU.uname         from tb_student STU join tb_sugang SU         on STU.hakno=SU.hakno         where SU.gcode='p001'  ) AA join tb_gwamok GW  on AA.gcode=GW.gcode;

 

문4) 프로그램 교과목 중에서 학점이 제일 많은 과목을 신청한 학생들 명단을 조회하시오

방법1>

select gcode  from tb_gwamok  where ghakjum=(select max(ghakjum) from tb_gwamok                         where gcode like 'p%') and gcode like 'p%'  select tb_sugang.gcode, tb_sugang.hakno, tb_student.uname  from tb_sugang join tb_student  on tb_sugang.hakno=tb_student.hakno   where gcode in (select gcode                  from tb_gwamok                  where ghakjum=( select max(ghakjum)                                   from tb_gwamok                                  where gcode like 'p%')                  and gcode like 'p%'                  );

 

 

<진행 과정 / 방법2>

 

① 프로그램 교과목 조회

select * from tb_gwamok  where gcode like 'p%';

 

 

② 프로그램 교과목중에서 가장 많은 학점 조회

select max(ghakjum)  from tb_gwamok  where gcode like 'p%';

 

③ 프로그램 교과목 중에서 가장 많은 학점을 가진 교과목들 조회

select gcode, ghakjum  from tb_gwamok  where ghakjum=( select max(ghakjum)                          from tb_gwamok                          where gcode like 'p%') and gcode like 'p%'

  

 <방법 2>

select AA.gcode, AA.hakno, STU.uname   from(         select tb_sugang.gcode, tb_sugang.hakno         from tb_sugang          where gcode in (select gcode                         from tb_gwamok                         where ghakjum=( select max(ghakjum)                                          from tb_gwamok                                         where gcode like 'p%')                         and gcode like 'p%'                         )     )AA join tb_student STU  on AA.hakno=STU.hakno;

 

 

 문5) 수강신청을 하지 않은 학생들의 명단을 조회하시오

select hakno, uname   from tb_student   where hakno not in ( select hakno from tb_sugang                             group by hakno);

 

<진행 과정 / 방법2>

 

① 수강신청을 한 학번들

select hakno  from tb_sugang  group by hakno;

 

 

② 수강신청을 한 학생들

select hakno, uname  from tb_student  where hakno in ( select hakno from tb_sugang                         group by hakno);

 

 

③ 수강신청을 하지 않은 학생들 (where ~ not in)

select hakno, uname  from tb_student  wher hakno not in ( select hakno from tb_sugang                             group by hakno);