[07] 오라클 조인

-- tb_student테이블에 레코드 추가하기

insert into tb_student(hakno,uname,address,phone)
values('g1001','홍길동','서울','111-5558')

insert into tb_student(hakno,uname,address,phone)
values('g1002','홍길동','안양','787-8877') 

insert into tb_student(hakno,uname,address,phone)
values('g1003','개나리','서울',554-9632)

insert into tb_student(hakno,uname,address,phone)
values('g1004','홍길동','부평','555-8844')

insert into tb_student(hakno,uname,address,phone)
values('g1005','진달래','서울','544-6996')

insert into tb_student(hakno,uname,address,phone)
values('g1006','개나리','안양','777-1000')

 

-- tb_gwamok테이블 행 추가하기
insert into tb_gwamok(gcode,gname,ghakjum)
values('p001','OOP',3)

insert into tb_gwamok(gcode,gname,ghakjum)
values('p002','Oracle',3)

insert into tb_gwamok(gcode,gname,ghakjum)
values('p003','MVC1',2)

insert into tb_gwamok(gcode,gname,ghakjum)
values('d001','웹표준',2)

insert into tb_gwamok(gcode,gname,ghakjum)
values('d002','포토샵',5)

insert into tb_gwamok(gcode,gname,ghakjum)
values('p004','MVC2',3)

문)각 주소별로 학생이 몇명인지 갯수를 구하시오

select address,count(*)
from tb_student
group by address
order by count(*) desc

 

문) 각 주소에 3명이하만 살고 있는 학생이 누구인지?

select address,uname
from tb_student
where address in (
                         select address
                         from tb_student
                         group by address
                         having count(*)<3
                        )

---------------------
-- tb_sugang 행 추가하기

insert into tb_sugang(sno,hakno,gcode)
values(sugang_seq.nextval,'g1001','p001')

insert into tb_sugang(sno,hakno,gcode)
values(sugang_seq.nextval,'g1002','p002')

insert into tb_sugang(sno,hakno,gcode)
values(sugang_seq.nextval,'g1002','p001')

insert into tb_sugang(sno,hakno,gcode)
values(sugang_seq.nextval,'g1001','p003')

-----------------------

 

[JOIN]
- 테이블과 다른 테이블을 조합하는 것
- 테이블명 join 테이블명 on 조건

 

select * from tb_student join tb_sugang
on tb_sugang.hakno=tb_student.hakno

select * from tb_gwamok join tb_sugang
on tb_sugang.gcode=tb_gwamok.gcode

-- 별칭 사용
select * from tb_student AA join tb_sugang BB
on AA.hakno=BB.hakno

select CC.*
from (
         select * from tb_student AA join tb_sugang BB
         on AA.hakno=BB.hakno
        ) CC
where CC.address='안양'

select CC.*
from (
         select AA.hakno,AA.uname,AA.address,BB.gcode from tb_student AA join tb_sugang BB
         on AA.hakno=BB.hakno
        ) CC
where CC.address='안양'

 

-- (1)
select DD.uname,CC.*
from (
        select AA.gname,BB.* from tb_gwamok AA join tb_sugang BB
        on AA.gcode=BB.gcode
        where BB.hakno='g1002'
       ) CC join tb_student DD
on CC.hakno=DD.hakno

-- (2)
select * from tb_gwamok AA join tb_sugang BB
on AA.gcode=BB.gcode join tb_student CC
on BB.hakno=CC.hakno
where BB.hakno='g1002'

----------

 

문) 각 학생별로 수강신청한 총학점, 이름, 학번을 검색

(1)
select CC.hakno,sum(CC.ghakjum) as gsum
from (
        select BB.hakno,AA.ghakjum from tb_gwamok AA join tb_sugang BB
        on AA.gcode=BB.gcode
       ) CC
group by CC.hakno

(2)
select DD.*,ST.uname
from
(
  select CC.hakno,sum(CC.ghakjum) as gsum
  from (
          select BB.hakno,AA.ghakjum from tb_gwamok AA join tb_sugang BB
          on AA.gcode=BB.gcode
         ) CC
  group by CC.hakno
) DD join tb_student ST
on DD.hakno=ST.hakno

 

(3)학번 'g1002'
select DD.*,ST.uname
from
(
  select CC.hakno,sum(CC.ghakjum) as gsum
  from (
          select BB.hakno,AA.ghakjum from tb_gwamok AA join tb_sugang BB
          on AA.gcode=BB.gcode
         ) CC
  group by CC.hakno
) DD join tb_student ST
on DD.hakno=ST.hakno
where DD.hakno='g1002'