웹개발 교육/Database
[24일] Database (11) - 종합연습문제
ewok
2022. 8. 29. 15:51
문제 1
문1) 디자인 교과목중에서 학점이 제일 많은 교과목을 수강신청한 명단을 조회하시오
(학번, 이름, 과목코드)
d002 g1005 진달래
--디자인교과목의 학점 조회하기
select * from tb_gwamok where gcode like 'd%' order by ghakjum desc;
--1) 디자인 교과목 중에서 학점이 제일 많은 교과목 조회
select max(ghakjum) --5
from tb_gwamok
where gcode like 'd%';
--2) 1) 결과에서 나온 학점(5)과 동일한 학점을 갖고 있는 행에서 과목코드 선택
-- 즉, 디자인 교과목 중에서 학점이 제일 많은 과목코드(단, 중복된 학점이 없다는 가정하에)
select gcode
from tb_gwamok
where ghakjum=(select max(ghakjum) from tb_gwamok where gcode like 'd%')
and gcode like 'd%'; --d002
--3) 2)에서 나온 과목코드(d002)를 수강신청한 명단을 조회
select gcode, hakno from tb_sugang where gcode=('d002');
select gcode, hakno
from tb_sugang
where gcode=(
select gcode
from tb_gwamok
where ghakjum=(select max(ghakjum) from tb_gwamok where gcode like 'd%')
and gcode like 'd%'
);
--4) 3)의 결과를 AA테이블로 만든 후, 학생테이블을 조인해서 이름 가져오기
select AA.gcode, AA.hakno, ST.uname
from (
select gcode, hakno
from tb_sugang
where gcode=(
select gcode
from tb_gwamok
where ghakjum=(select max(ghakjum) from tb_gwamok where gcode like 'd%')
and gcode like 'd%'
)
) AA join tb_student st
on AA.hakno=st.hakno;
--------------------------------------------------
-- 내가 푼 방법
select gcode, gname, ghakjum
from tb_gwamok
where gcode like 'd%'
order by ghakjum desc;
-- 디자인 교과목 중 학점이 제일 많은 교과목
select gcode, gname, ghakjum, rownum
from (
select gcode, gname, ghakjum
from tb_gwamok
where gcode like 'd%'
order by ghakjum desc
) aa
where rownum=1;
--
select aa.gcode, su.hakno
from (
select gcode, gname, ghakjum, rownum
from (
select gcode, gname, ghakjum
from tb_gwamok
where gcode like 'd%'
order by ghakjum desc
)
where rownum=1
) aa join tb_sugang su
on aa.gcode=su.gcode;
--
select st.hakno, uname, bb.gcode
from (
select aa.gcode, su.hakno
from (
select gcode, gname, ghakjum, rownum
from (
select gcode, gname, ghakjum
from tb_gwamok
where gcode like 'd%'
order by ghakjum desc
)
where rownum=1
) aa join tb_sugang su
on aa.gcode=su.gcode
) bb join tb_student st
on bb.hakno=st.hakno;
-----------------------------------------------------
--한번에
select st.hakno, uname, su.gcode, gw.ghakjum
from tb_student st join tb_sugang su
on st.hakno=su.hakno join tb_gwamok gw
on su.gcode=gw.gcode
where gw.gcode like 'd%'
order by ghakjum desc;
select aa.hakno, uname, aa.gcode
from (
select st.hakno, uname, su.gcode, gw.ghakjum
from tb_student st join tb_sugang su
on st.hakno=su.hakno join tb_gwamok gw
on su.gcode=gw.gcode
where gw.gcode like 'd%'
order by ghakjum desc
) aa
where rownum=1;
문제 2
문2) 학번별 수강신청한 총학점을 구하고 학번 순으로 정렬해서 줄번호 4~6행 조회하시오
(단, 수강신청하지 않은 학생의 총학점도 0으로 표시)
g1004 0 4
g1005 12 5
g1006 3 6
--학생테이블 조회
select hakno, uname from tb_student order by hakno;
--1) 수강신청한 과목의 학점 가져오기
select su.hakno, su.gcode, gw.ghakjum
from tb_sugang su join tb_gwamok gw
on su.gcode=gw.gcode;
--2) 학번 별로 총 학점 구하기
select su.hakno, sum(gw.ghakjum) as 총학점
from tb_sugang su join tb_gwamok gw
on su.gcode=gw.gcode
group by su.hakno;
--3) 수강신청하지 않은 학생들도 가져올 수 있도록 학생테이블 left join하고,
-- 2) 결과(AA테이블)를 붙임
select ST.hakno, ST.uname, AA.hakno, AA.총학점
from tb_student ST left join (
select su.hakno, sum(gw.ghakjum) as 총학점
from tb_sugang su join tb_gwamok gw
on su.gcode=gw.gcode
group by su.hakno
) AA
on ST.hakno=AA.hakno;
--4) 총학점이 null이면 0으로 바꾸고, 학번 순으로 조회
select ST.hakno, ST.uname, nvl(AA.총학점, 0)
from tb_student ST left join (
select su.hakno, sum(gw.ghakjum) as 총학점
from tb_sugang su join tb_gwamok gw
on su.gcode=gw.gcode
group by su.hakno
) AA
on ST.hakno=AA.hakno
order by ST.hakno;
--5) 줄번호 추가 (줄번호가 있는 상태에서 정렬됨)
select ST.hakno, ST.uname, nvl(AA.총학점, 0), rownum
from tb_student ST left join (
select su.hakno, sum(gw.ghakjum) as 총학점
from tb_sugang su join tb_gwamok gw
on su.gcode=gw.gcode
group by su.hakno
) AA
on ST.hakno=AA.hakno
order by ST.hakno;
--6) 5)의 결과를 셀프조인하고 나서, 줄번호 추가하기
select BB.hakno, BB.uname, BB.총학점2, rownum as rnum
from (
select ST.hakno, ST.uname, nvl(AA.총학점, 0) as 총학점2
from tb_student ST left join (
select su.hakno, sum(gw.ghakjum) as 총학점
from tb_sugang su join tb_gwamok gw
on su.gcode=gw.gcode
group by su.hakno
) AA
on ST.hakno=AA.hakno
order by ST.hakno
) BB;
--7) 6)의 결과를 셀프조인(CC)하고 줄번호(rnum) 4~6 조회
select CC.hakno, CC.총학점2, rownum as rnum
from (
select BB.hakno, BB.uname, BB.총학점2, rownum as rnum
from (
select ST.hakno, ST.uname, nvl(AA.총학점, 0) as 총학점2
from tb_student ST left join (
select su.hakno, sum(gw.ghakjum) as 총학점
from tb_sugang su join tb_gwamok gw
on su.gcode=gw.gcode
group by su.hakno
) AA
on ST.hakno=AA.hakno
order by ST.hakno
) BB
) CC
where rnum>=4 and rnum<=6;
-------------------------------------------------------
-- 내가 푼 방법
select st.hakno, su.gcode
from tb_student st left join tb_sugang su
on st.hakno=su.hakno;
--
select aa.hakno, sum(nvl(gw.ghakjum, 0)) 총학점
from (
select st.hakno, su.gcode
from tb_student st left join tb_sugang su
on st.hakno=su.hakno
) aa left join tb_gwamok gw
on aa.gcode=gw.gcode
group by aa.hakno
order by aa.hakno;
--줄번호 부여
select bb.hakno, 총학점, rownum
from (
select aa.hakno, sum(nvl(gw.ghakjum, 0)) 총학점
from (
select st.hakno, su.gcode
from tb_student st left join tb_sugang su
on st.hakno=su.hakno
) aa left join tb_gwamok gw
on aa.gcode=gw.gcode
group by aa.hakno
order by aa.hakno
) bb;
-- 4~6조회
select cc.hakno, 총학점, rnum
from (
select bb.hakno, 총학점, rownum rnum
from (
select aa.hakno, sum(nvl(gw.ghakjum, 0)) 총학점
from (
select st.hakno, su.gcode
from tb_student st left join tb_sugang su
on st.hakno=su.hakno
) aa left join tb_gwamok gw
on aa.gcode=gw.gcode
group by aa.hakno
order by aa.hakno
) bb
) cc
where rnum>=4 and rnum<=6;
문제 3
문3) 학번별로 수강신청 총학점을 구하고, 총학점순으로 내림차순 정렬후
위에서 부터 1건만 조회하시오 (학번, 이름, 총학점)
-- 수강테이블에 행추가 해주세요
-- (총학점이 다 같은 값이여서 결과확인하기가 조금 애매 합니다)
insert into tb_sugang(sno,hakno,gcode) values(sugang_seq.nextval,'g1001','p005');
commit;
g1001 14 홍길동 1
--1) 과목코드가 일치하는 학점 가져오기
select SU.hakno, SU.gcode, GW.ghakjum
from tb_sugang SU join tb_gwamok GW
on SU.gcode=GW.gcode;
--2) 학번 별로 총학점 구하고, 총학점 순으로 내림차순 정렬하기
select SU.hakno, sum(GW.ghakjum) as 총학점
from tb_sugang SU join tb_gwamok GW
on SU.gcode=GW.gcode
group by SU.hakno
order by sum(GW.ghakjum) desc;
--3) 2)의 결과를 AA테이블로 만들고, 학생테이블 조인해서 이름 가져오기
select aa.hakno, aa.총학점, ST.uname,rownum as rnum
from (
select SU.hakno, sum(GW.ghakjum) as 총학점
from tb_sugang SU join tb_gwamok GW
on SU.gcode=GW.gcode
group by SU.hakno
order by sum(GW.ghakjum) desc
) AA join tb_student st
on aa.hakno=st.hakno;
--4) 3)의 결과를 셀프조인하고 줄번호를 이용해서 위에서부터 1건만 조회하기
select hakno, 총학점, uname, rnum
from (
select aa.hakno, aa.총학점, ST.uname,rownum as rnum
from (
select SU.hakno, sum(GW.ghakjum) as 총학점
from tb_sugang SU join tb_gwamok GW
on SU.gcode=GW.gcode
group by SU.hakno
order by sum(GW.ghakjum) desc
) AA join tb_student st
on aa.hakno=st.hakno
)
where rnum=1;
-----------------------------------------------
-- 내가 푼 방법
--학번별 총학점 내림차순 정렬
select su.hakno, sum(gw.ghakjum) 총학점
from tb_sugang su join tb_gwamok gw
on su.gcode=gw.gcode
group by su.hakno
order by 총학점 desc;
--student 테이블 조인해서 이름 붙이고 rownum
select aa.hakno, uname, 총학점, rownum
from (
select su.hakno, sum(gw.ghakjum) 총학점
from tb_sugang su join tb_gwamok gw
on su.gcode=gw.gcode
group by su.hakno
order by 총학점 desc
) aa join tb_student st
on aa.hakno=st.hakno;
--위에서 1건 조회
select bb.hakno, uname, 총학점, rnum
from (
select aa.hakno, uname, 총학점, rownum rnum
from (
select su.hakno, sum(gw.ghakjum) 총학점
from tb_sugang su join tb_gwamok gw
on su.gcode=gw.gcode
group by su.hakno
order by 총학점 desc
) aa join tb_student st
on aa.hakno=st.hakno
) bb
where rnum=1;