[Oracle] Paging까지 배운 후 연습 문제

1. 테이블 생성

1. 테이블 생성
----------------------------- 학생테이블
create table tb_student(
   hakno    char(5)     not null            -- 학번
  ,uname    varchar(20) not null            -- 이름
  ,email    varchar(20) unique              -- 이메일
  ,address  varchar(20) not null            -- 주소
  ,phone    varchar(20)                     -- 전화번호
  ,regdt    date        default sysdate     -- 등록일
  ,primary key(hakno)                       -- 기본키
);

drop table tb_student;
delete table tb_student;

----------------------------- 과목테이블
create table tb_gwamok(
   gcode    char(4)      not null           -- 과목코드 (p:프로그램교과목, d:디자인교과목)
  ,gname    varchar(20)  not null           -- 과목이름
  ,ghakjum  number(2)    default 1          -- 학점
  ,regdt    date         default sysdate    -- 등록일    
  ,primary key(gcode)                       -- 기본키
);

drop table tb_gwamok;

----------------------------- 수강테이블
create table tb_sugang(
   sno    number(3)  not null               -- 일련번호
  ,hakno  char(5)    not null               -- 학번
  ,gcode  char(4)    not null               -- 과목코드
  ,primary key(sno)                         -- 기본키
);

drop table tb_sugang;


2. 수강테이블 시퀀스 생성
create sequence sugang_seq;

drop sequence sugang_seq;


3. 행추가

----------------------------- tb_student테이블에 행 추가하기
insert into tb_student(hakno,uname,address,phone,email)
values('g1001','홍길동','서울','111-5558','11@naver.com');

insert into tb_student(hakno,uname,address,phone,email)
values('g1002','홍길동','제주','787-8877','33@daum.net');

insert into tb_student(hakno,uname,address,phone,email)
values('g1003','개나리','서울','554-9632','77@naver.com');

insert into tb_student(hakno,uname,address,phone,email)
values('g1004','홍길동','부산','555-8844','88@daum.net');

insert into tb_student(hakno,uname,address,phone,email)
values('g1005','진달래','서울','544-6996','33@nate.com');

insert into tb_student(hakno,uname,address,phone,email)
values('g1006','개나리','제주','777-1000','66@naver.com');


----------------------------- tb_gwamok테이블에 행 추가하기
insert into tb_gwamok(gcode,gname,ghakjum) values('p001','JAVA',3);
insert into tb_gwamok(gcode,gname,ghakjum) values('p002','Oracle',3);
insert into tb_gwamok(gcode,gname,ghakjum) values('p003','JSP',2);
insert into tb_gwamok(gcode,gname,ghakjum) values('d001','HTML',1);
insert into tb_gwamok(gcode,gname,ghakjum) values('d002','포토샵',5);
insert into tb_gwamok(gcode,gname,ghakjum) values('d003','일러스트',3);
insert into tb_gwamok(gcode,gname,ghakjum) values('d004','CSS',1);
insert into tb_gwamok(gcode,gname,ghakjum) values('p004','Python',3);
insert into tb_gwamok(gcode,gname,ghakjum) values('p005','AJAX',2);


----------------------------- 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,'g1002','p003');
insert into tb_sugang(sno,hakno,gcode) values(sugang_seq.nextval,'g1001','p003');
insert into tb_sugang(sno,hakno,gcode) values(sugang_seq.nextval,'g1001','p004');
insert into tb_sugang(sno,hakno,gcode) values(sugang_seq.nextval,'g1005','p001');
insert into tb_sugang(sno,hakno,gcode) values(sugang_seq.nextval,'g1005','d001');
insert into tb_sugang(sno,hakno,gcode) values(sugang_seq.nextval,'g1005','d002');
insert into tb_sugang(sno,hakno,gcode) values(sugang_seq.nextval,'g1005','d003');
insert into tb_sugang(sno,hakno,gcode) values(sugang_seq.nextval,'g1001','d001');
insert into tb_sugang(sno,hakno,gcode) values(sugang_seq.nextval,'g1001','p002');
insert into tb_sugang(sno,hakno,gcode) values(sugang_seq.nextval,'g1006','p001');
insert into tb_sugang(sno,hakno,gcode) values(sugang_seq.nextval,'g1001','p005');
/////////////////////////////////////////////////////////////////////////////////

commit;

-- tb_student 테이블 전체 레코드 갯수
select count(*) from tb_student;    -- 6

-- tb_gwamok 테이블 전체 레코드 갯수
select count(*) from tb_gwamok;    -- 9

-- tb_sugang 테이블 전체 레코드 갯수
select count(*) from tb_sugang;    -- 14
/////////////////////////////////////////////////////////////////////////////////

 

 

2. 문제

 

문1) 디자인 교과목중에서 학점이 제일 많은 교과목을 수강신청한 명단을 조회하시오
    (학번, 이름, 과목코드)
    

    g1005 진달래 d002

 

 

 

문2) 학번별 수강신청한 총학점을 구하고 학번별 정렬해서 줄번호 4~6행 조회하시오
    - 단, 수강신청하지 않은 학생의 총학점도 0으로 표시

        g1004 0 4
        g1005 12 5
        g1006 3 6

 

 

문3) 학번별로 수강신청 총학점을 구하고, 총학점순으로 내림차순 정렬후
     위에서 부터 1건만 조회하시오 (학번, 이름, 총학점)

 

        g1001 홍길동 14 1

 

 

 

3. 풀이

 

눌러서 확인하세요 

문1) 디자인 교과목중에서 학점이 제일 많은 교과목을 수강신청한 명단을 조회하시오
    (학번, 이름, 과목코드)
    
    g1005	진달래	d002
    ;

-- 1) 디자인 교과목 신청명단
select SU.hakno, uname, SU.gcode, gname, ghakjum
from tb_sugang SU join tb_student ST
on SU.hakno = ST.hakno join tb_gwamok GW
on SU.gcode = GW.gcode
where SU.gcode like 'd%';

-- 1)에 조건주기
select *
from(
    select SU.hakno, uname, SU.gcode, ghakjum
    from tb_sugang SU join tb_student ST
    on SU.hakno = ST.hakno join tb_gwamok GW
    on SU.gcode = GW.gcode
    where SU.gcode like 'd%'
    ) AA
where ghakjum = (
                select max(ghakjum)
                from tb_gwamok
                where gcode like 'd%'
                );

-- 1)하고 2)를 합칠 수도 있구나~
select SU.hakno, uname, SU.gcode
from tb_sugang SU join tb_student ST
on SU.hakno = ST.hakno join tb_gwamok GW
on SU.gcode = GW.gcode
where SU.gcode like 'd%' and ghakjum = (
                                        select max(ghakjum)
                                        from tb_gwamok
                                        where gcode like 'd%'
                                        );


문2) 학번별 수강신청한 총학점을 구하고 학번별 정렬해서 줄번호 4~6행 조회하시오
    - 단, 수강신청하지 않은 학생의 총학점도 0으로 표시

        g1004	0	4
        g1005	12	5
        g1006	3	6

-- 1) 전체 학생들의 총학점 구하기
select ST.hakno, sum(nvl(GW.ghakjum, 0))
from tb_student ST left join tb_sugang SU
on ST.hakno = SU.hakno left join tb_gwamok GW
on SU.gcode = GW.gcode
group by ST.hakno
order by ST.hakno;


-- 2) AA 테이블로 만들어서 rownum로 줄번호 붙이기
select hakno, sumhak, rownum
from (
    select ST.hakno as hakno, sum(nvl(GW.ghakjum, 0)) as sumhak
    from tb_student ST left join tb_sugang SU
    on ST.hakno = SU.hakno left join tb_gwamok GW
    on SU.gcode = GW.gcode
    group by ST.hakno
    order by ST.hakno
    ) AA;
    
    
select *
from tb_student ST left join tb_sugang SU
on ST.hakno = SU.hakno;

-- 3) 2)의 결과에 붙여진 줄번호에 조건절로 4~6행만 조회하기
select *
from (
    select hakno, sumhak, rownum as rnum
    from (
        select ST.hakno as hakno, sum(nvl(GW.ghakjum, 0)) as sumhak
        from tb_student ST left join tb_sugang SU
        on ST.hakno = SU.hakno left join tb_gwamok GW
        on SU.gcode = GW.gcode
        group by ST.hakno
        order by ST.hakno
        ) AA
    ) BB
where rnum between 4 and 6;


문3) 학번별로 수강신청 총학점을 구하고, 총학점순으로 내림차순 정렬후
     위에서 부터 1건만 조회하시오 (학번, 이름, 총학점) 
-- 수강테이블에 행추가 해주세요
-- (총학점이 다 같은 값이여서 결과확인하기가 조금 애매 합니다)
insert into tb_sugang(sno,hakno,gcode) values(sugang_seq.nextval,'g1001','p005');
commit;


-- 1) 학번별 수강신청 총학점 구한 뒤 총학점 순으로 내림차순 정렬
select SU.hakno, uname, sum(ghakjum)
from tb_sugang SU join tb_student ST
on SU.hakno = ST.hakno join tb_gwamok GW
on SU.gcode = GW.gcode
group by SU.hakno, uname
order by sum(ghakjum) desc;

-- 2) 1)의 결과를 AA 테이블로 만든 뒤 rownum을 붙이고 1건만 조회
select hakno, uname, sum_hak, rownum
from (
    select SU.hakno, uname, sum(ghakjum) as sum_hak
    from tb_sugang SU join tb_student ST
    on SU.hakno = ST.hakno join tb_gwamok GW
    on SU.gcode = GW.gcode
    group by SU.hakno, uname
    order by sum(ghakjum) desc
    ) AA
where rownum = 1;