2019/07/29 join한 내용을 가지고 문제 풀이

[전 수업] 3개의 테이블을 이용해 조인해보고 문제를 풀어보았다

[본 수업] 테이블을 이용해 조회하는 능력을 기르자

---

1.inner join(생략가능)

수강테이블 학생테이블 과목테이블 세개를 조인하여 논리테이블을 생성  / 수강테이블의 학번으로 오름차순으로 정열하였다                                                                                                     

 

2. 문) 학번별 수강 신청과목의 총학점을 조회

총학점을 count()함수로 잘못 조회하였다

---

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

※서브쿼리를 이용하지 않아도 조회 할 수 있다

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

 

▶구하는 또 다른 방법

 

select AA.hakno,AA.총학점,STU.uname
    from(
        select SU.hakno,sum(GM.ghakjum) as 총학점
        from tb_sugang SU join tb_gwamok GM
        on SU.gcode=GM.gcode
        group by SU.hakno
        ) AA join tb_student STU
        on AA.hakno=STU.hakno
        order by 총학점;

 

3. 디자인 교과목 대상으로 학번별 수강신청과목의 총학점을 조회(단,디자인교과목만)

select AA.hakno,AA.uname,sum(aa.ghakjum)
        from(   select su.hakno,stu.uname,su.gcode,GM.gname,GM.ghakjum
from tb_sugang SU join tb_student STU
on SU.hakno=STU.hakno join tb_gwamok GM
on SU.gcode=GM.gcode
where su.gcode like 'd%') AA
        group by AA.hakno,AA.uname;

 

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

 

select SU.gcode,GM.gname,SU.hakno,STU.uname
from tb_sugang SU inner join  tb_student STU
on SU.hakno=STU.hakno inner join tb_gwamok GM
on SU.gcode=GM.gcode
where SU.gcode='p001';

 

 

5. 프로그램 교과목중에서 학점이 제일 많은 과목을 신청한 학생들 명단을 조회

 

 

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

 

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

 

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

 

 

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

 

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

 

4) 프로그램교과목중에서 학점이 가장 많은 교과목을 신청한 학번

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%'
            );

 

5) 프로그램교과목중에서 학점이 가장 많은 교과목을 신청한 학생의 명단

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;

 

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

 

1.수강신청 한 학번

select hakno
  from tb_sugang
  group by hakno;

 

2.수강신청 한 학생들

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

 

3.수강신청을 하지않은 학생들

 

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

 

 

---

7. 내부조인(inner join)
두 테이블에 공통으로 존재하는 칼럼을 이용하는 방식 (생략가능)
  
  select STU.hakno,STU.uname,STU.phone,SU.gcode
  from tb_student STU inner join tb_sugang SU
  on STU.hakno=SU.hakno;

8. left join
 왼쪽 테이블의 모든 데이터와 오른쪽 테이블의 동일한 데이터를 추출

※수강신청을 한 학생, 하지 않은 학생들 모두 조회
    select STU.hakno,STU.uname,STU.phone,SU.gcode
    from tb_student STU left outer join tb_sugang SU
    on STU.hakno=SU.hakno;                                 --outer 생략가능

9. right outer join
 오른쪽 테이블의 모든 데이터와 왼쪽 테이블의 동일 데이터를 추출
    select STU.hakno,STU.uname,STU.phone,SU.gcode
    from tb_sugang SU right outer join tb_student STU
    on STU.hakno=SU.hakno;

---

10. 수강신청을 하지 않은 과목들을 조회

1)

select gcode,gname
    from tb_gwamok
    where gcode not in(
select gcode from tb_sugang group by gcode );

2) left join 을 이용해서 수강신청되지 않은 hakno를 null값으로 구하기

select GM.gcode,GM.gname,SU.hakno
                 from tb_gwamok GM left outer join tb_sugang SU
    on GM.gcode=SU.gcode;

3) hakno가 null인걸 이용하여 수강신청되지 않은 과목 조회

select GM.gcode,GM.gname,SU.hakno
    from tb_gwamok GM left outer join tb_sugang SU
    on GM.gcode=SU.gcode 
    where SU.gcode is null;