2019/07/29 rownum

[전 수업] inner join 과 left outer join, right outer join 을 배워 보았다

[본 수업] rownum을 이용해 행번호에 맞게 데이터를 추출해보자

---

•  rownum  행번호  : 입력한 데이터들의 행번호를 입력해준다
•  rowid   행의 주소: 입력한 데이터들의 행의 주소 값

1. rownum과 rowid를 select를 이용해서 조회해 보자

select rowid,rownum,hakno,uname   from tb_student;

rownum은  조회의 순번을 매겨주고,  rowid는 테이블에 있는 해당 로우를 찾기위해 사용되는     논리적인 정보이다

 

2. rownum 에러

select address,rownum  from tb_student group by address;

다른 칼럼과 마찬가지로 group by 절로 address를 설정을 해 놓으면 rownum을 칼럼으로 설정 할 수가 없다.

 

3. 줄 번호 1~3사이를 조회 

select rownum,hakno,uname  from tb_student  where rownum>=1 and rownum<=3;

rownum을 칼럼으로 잡아두고, where 조건문에 rownum칼럼의 조건을 걸어 사용할 수있다.

 

4. 줄 번호 4~6사이를 조회

rownum이 order by가 같이 SQL문으로 쓰이면 순서가 뒤바뀌기 때문에 사이에 끼어있는 데이터를 찾을 수 없다

 

5. 셀프조인후 행번호 추가

select addr,rownum from(                                   select address as addr                                   from tb_student                                   group by address ) AA;

group by 로 인해 안에 있는 쿼리문에서는 rownum을 사용하지 못한다 ※셀프 조인 통해 rownum을 사용

 

6. 줄번호 4~6사이를 구하는 방법

--1

select hakno,uname,address  from tb_student  order by hakno;

tb_student 테이블의 학번,이름,주소를 hakno순으로 조회함

 

--2

select rownum rnum,hakno,uname,address  from(   select hakno,uname,address   from tb_student  order by hakno);

위에서 조회한 논리적 테이블을 셀프조인 후 rownum을 건다

 

--3 

select rnum,hakno,uname,address from (     select rownum rnum,hakno,uname,address     from (             select hakno,uname,address             from tb_student             order by hakno)             )             where rnum>=4 and rnum<=6;

모조 칼럼 rownum을 실제 칼럼으로 인식시킨 후  다른 명령어와 병행해서 사용한다

 

 

7. 문) 학번별 수강신청 총학점을 구하고
        총학점순으로 정렬후 위에서 부터 3건만 조회하시오

select SU.hakno,SU.gcode,GM.ghakjum    from tb_sugang SU join tb_gwamok GM  on SU.gcode=GM.gcode;

1. 과목 코드별 학점을 가져온다

 

select SU.hakno,SUM(GM.ghakjum) as 총학점  from tb_sugang SU join tb_gwamok GM  on SU.gcode=GM.gcode  group by SU.hakno;

2. 학번별로 총학점을 구하기 (학번별로 그룹을 묶어주고 그룹을 묵어 주면 묶어준 데이터들을 SUM으로 합계를 구한다)

 

 

select SU.hakno,SUM(GM.ghakjum) as 총학점  from tb_sugang SU join tb_gwamok GM  on SU.gcode=GM.gcode  group by SU.hakno  order by SUM(GM.ghakjum) desc;

3. 총 학점순으로 정렬하여 조회하기 (보여지는 테이블의 데이터는 같다)

 

select AA.hakno,AA.총학점,STU.uname,rownum  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  order by SUM(GM.ghakjum) desc  )AA join tb_student STU  on AA.hakno=STU.hakno;

4. 학생 테이블을 join 을 해서 위에 총학점에 이름을 넣자

 

select AA.hakno,AA.총학점,STU.uname,rownum  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  order by SUM(GM.ghakjum) desc  )AA join tb_student STU  on AA.hakno=STU.hakno  where rownum>=1 and rownum<=3;

조건문 where절을 rownum을 걸어 조건에 맞게 조회

 

※--행번호 4~6 출력하기(조회안됨)

rownum은 모조칼럼이므로 조건절에 직접 사용하지 말고 실제칼럼으로 인식후 사용할 것을 추천

select hakno,총학점,uname,rnum  from(          select AA.hakno,AA.총학점,STU.uname,rownum as rnum         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                 order by SUM(GM.ghakjum) desc                 )AA join tb_student STU                 on AA.hakno=STU.hakno         )BB where BB.rnum>=4 and BB.rnum<=6;

 

 

8. 문)학번별 수강신청한 총학점을 조회하시오
  (단,수강신청하지 않은 학생의 총학점도 0으로 표시)
--    g001 홍길동 8
--    g002 무궁화 6
--    g003 진달래 0
--    수강/학생/과목

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

1. 과목 테이블에 수강신청 테이블을 left join해준다

 

select STU.uname,SU.hakno,SU.gcode,nvl(GM.ghakjum,0) 학점  from tb_student STU left outer join tb_sugang SU  on STU.hakno=SU.hakno left outer join tb_gwamok GM  on SU.gcode=GM.gcode  order by STU.uname;

2. 학생 테이블에 수강테이블을 left join을 해주고 다시 한번 과목테이블을 left join해줘 이름순으로 정렬해 주었다

 

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

3.BB로 테이블 지정후 STU 테이블로 다시 join하여 데이터를 검색한다

 

※강사님 풀이

select tb_sugang.gcode,sum(tb_gwamok.ghakjum) as 총학점  from tb_sugang inner join tb_gwamok  on tb_sugang.gcode=tb_gwamok.gcode  group by tb_sugang.gcode;

수강테이블과 과목테이블을 조인시킨후 gcode별로 그룹화 한다 그 후 학점의 총학점을 조회

 

select STU.hakno, AA.총학점, nvl(AA.총학점,0) from tb_student STU left join (         select tb_sugang.hakno,sum(tb_gwamok.ghakjum) 총학점         from tb_sugang inner join tb_gwamok         on tb_sugang.gcode=tb_gwamok.gcode         group by tb_sugang.hakno) AA      on STU.hakno=AA.hakno;

위에서 조회한 테이블을 AA 로 alias값을 잡고 학생테이블과  join을 해주어 총학점을 구해준다

 

 

9.문) 학생테이블에서 학번순으로 정렬후 
   행번호를 아래와 같이 붙여서 조회하시오
--rownum  이름  이메일  주소  전화번호

select rnum,hakno,uname,email,address,phone     from(     select rownum rnum,hakno,uname,email,address,phone     from tb_student)AA     order by AA.rnum desc;