07월 29일 월 | SQL활용 08 - SQL 연습 Ⅱ

SQL join

학사관리 시스템

지난 번에 이어서 학사관리 시스템을 토대로 SQL학습을 진행한다.

 

문제 1) 학번별로 수강신청과목의 총 학점을 조회하시오. 
-- 김연아의 g1008, g1016 김나라 g1002 각각 총학점을 조회

 

select AA.hakno, AA.총학점, STU.uname from (         select SU.hakno, sum(SUB.ghakjum) as 총학점         from tb_sugang SU join tb_subject SUB         on SU.gcode = SUB.gcode         group by SU.hakno having SU.hakno in ('g1002','g1008','g1016')         ) AA join tb_student STU         on AA.hakno=STU.hakno          order by AA.hakno;

 

 

문제 2) 디자인과목 수강하는 학생들만 구하기.  

내 풀이

select AA.hakno, STU.uname, AA.gname, AA.총학점 from (         select SU.hakno,SUB.gname,sum(SUB.ghakjum) as 총학점         from tb_sugang SU join tb_subject SUB         on SU.gcode = SUB.gcode         group by SU.hakno,SUB.gname,SUB.gcode having SUB.gcode like 'd%'         ) AA join tb_student STU         on AA.hakno=STU.hakno          order by AA.hakno;

 

 

다른 방식

select BB.hakno, sum(BB.ghakjum) 총학점 from( select AA.hakno, AA.gcode, SUB.ghakjum from(     select hakno, gcode     from tb_sugang     where gcode like 'd%' ) AA join tb_subject SUB on AA.gcode = SUB.gcode )BB group by BB.hakno;

select AA.hakno, sum(AA.ghakjum) 총학점 from (select SU.hakno, SU.gcode, SUB.ghakjum from tb_sugang SU join tb_subject SUB on SU.gcode=SUB.gcode where SU.gcode like'd%') AA join tb_student STU on AA.hakno = STU.hakno group by AA.hakno;

 

 

문제 3) 과목코드가 p001을 신청한 학생들의 명단을 조회하시오.

-- p001, 교과목명, 학생들이름, 학번

select AA.gcode, AA.gname, STU.uname, AA.hakno  from (         select SU.hakno,SUB.gcode,SUB.gname,sum(SUB.ghakjum) as 총학점         from tb_sugang SU join tb_subject SUB         on SU.gcode = SUB.gcode         group by SU.hakno,SUB.gname,SUB.gcode having SUB.gcode = 'p001'         ) AA join tb_student STU         on AA.hakno=STU.hakno          order by AA.hakno;

 

선생님 풀이

select SU.gcode, SUB.gname, SU.hakno, STU.uname from tb_sugang SU join tb_student STU on SU.hakno = STU.hakno join tb_subject SUB on SU.gcode = SUB.gcode where SU.gcode = 'p001';

 

 

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

 

select STU.uname, SU.gcode, SUB.ghakjum from tb_subject SUB join tb_sugang SU on SUB.gcode = SU.gcode join tb_student STU on SU.hakno = STU.hakno where SUB.ghakjum = (select max(ghakjum) from tb_subject) and SUB.gcode like 'p%' order by SUB.gcode;

 

선생님 풀이

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

 

 

 

 

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

select tb_student.uname from tb_student where tb_student.uname not in  (select STU.uname from tb_sugang SU join tb_student STU on SU.hakno = STU.hakno);

 

 

선생님 풀이

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

이게 더 정확한듯...

 

 

 

 

 

조인의 종류

출처: NCS 학습모듈

 

 

1) 내부조인 (inner join)
두 테이블에 공통으로 존재하는 칼럼을 이용하는 방식

 

2) 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;

 

 

 

3) 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;

 

 

문제 6) 수강신청을 하지 않은 과목들을 조회

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

모든 과목을 하나씩은 수강신청해놨어서 새 데이터 d004, XD, 3학점짜리 과목을 추가하였음

 

 

 

선생님 풀이

select gcode from tb_subject where gcode not in ( select gcode from tb_sugang group by gcode );

 

 

select SUB.gcode, SUB.gname, SUB.ghakjum, SU.gcode from tb_subject SUB left join tb_sugang SU on SUB.gcode = SU.gcode;

저 노란 부분만 추출하면 된다.

SU.gcode is Null로  추출한다

 

select SUB.gcode, SUB.gname, SUB.ghakjum, SU.gcode from tb_subject SUB left join tb_sugang SU on SUB.gcode = SU.gcode where SU.gcode is null;