DATABASE 16장 연습문제
문제1] 분석함수 중에서 rank(), dense_rank()의 차이점을 쓰시오.
rank() 함수는 데이터의 순위가 동일 한 경우 다음 순위를 건너뛰지만
dense_rank() 함수는 다음 순위를 연속적으로 출력한다
문제2] 학년별로 학생들의 생년월일을 기준으로 생일이 빠른 (내림차순) 학생부터 순위를 부여하여
출력하시오.
출력)
name grade birthdate birthdate_rank
---------------------------------------------
서재진 1 85/11/29 1
박동진 1 85/11/24 2
하나리 1 84/12/18 3
조명훈 1 84/09/16 4
박미경 1 84/05/16 5
이동훈 1 83/12/10 6
김진경 2 83/02/28 1
임유진 2 83/01/21 2
김진영 2 82/06/06 3
류민정 2 81/08/19 4
지은경 2 80/04/12 5
김영균 3 81/01/11 1
윤진욱 3 79/04/02 2
이광훈 4 81/10/13 1
전인하 4 79/07/02 2
오유석 4 77/10/12 3
select name, grade, birthdate,
rank() over (partition by grade order by birthdate desc) birthdate_rank
from student;
문제3] 학과별로 생일이 가장 빠른 상위 2명의 학과번호, 이름
생년월일을 출력하시오.
출력)
deptno name birthdate birthdate_rank
-------------------------------------------
101 서재진 85/11/29 1
101 박미경 84/05/16 2
102 하나리 84/12/18 1
102 김진영 82/06/06 2
201 박동진 85/11/24 1
201 조명훈 84/09/16 2
select deptno, name, birthdate, birthdate_rank
from (select deptno, name, birthdate,
rank() over (partition by deptno order by birthdate desc) as birthdate_rank
from student)
where birthdate_rank <=2;
문제4] 학과별로 몸무게가 가장 무거운 상위 2명의 학생 이름, 몸무게
키를 출력하시오.
deptno name weight height weight_rank
-------------------------------------------
101 이광훈 92 175 1
101 김영균 88 170 2
102 오유석 92 177 1
102 윤진욱 70 171 2
201 박동진 70 182 1
201 이동훈 64 172 2
select deptno, name, weight, height, weight_rank
from (select deptno, name, weight, height,
row_number() over (partition by deptno order by weight desc) as weight_rank
from student)
where weight_rank <=2;
문제5] 몸무게를 기준으로 3개의 그룹으로 나누어
그룹이 1이면 ‘heavy_weight',
2이면 ’normal_weight',
나머지 그룹이면 ‘light_weight'로 분류하여 출력하시오.(CASE WHEN,ntile 이용하시오)
출력)
name weight weight_group weight_ntile
-------------------------------------
이광훈 92 heavy_weight 1
오유석 92 heavy_weight 1
김영균 88 heavy_weight 1
서재진 72 heavy_weight 1
류민정 72 heavy_weight 1
전인하 72 heavy_weight 1
윤진욱 70 normal_weight 2
박동진 70 normal_weight 2
하나리 68 normal_weight 2
이동훈 64 normal_weight 2
조명훈 62 normal_weight 2
임유진 54 light_weight 3
박미경 52 light_weight 3
김진경 51 light_weight 3
김진영 48 light_weight 3
지은경 42 light_weight 3
select name, weight, weight_group, weight_ntile
from (select name, weight,
case when weight>70 then 'heavy_weight'
when weight>60 then 'normal_weight'
else 'light_weight'
end as weight_group,
ntile(3) over (order by weight desc) weight_ntile
from student)
order by weight desc;
문제6] 공과대학에 재학중인 모든 학생들의 이름과
학과 이름을 출력하시오.
출력)
name dname
--------------------
전인하 컴퓨터공학과
이동훈 전자공학과
박미경 컴퓨터공학과
김영균 컴퓨터공학과
박동진 전자공학과
김진영 멀티미디어학과
지은경 컴퓨터공학과
오유석 멀티미디어학과
하나리 멀티미디어학과
임유진 컴퓨터공학과
서재진 컴퓨터공학과
윤진욱 멀티미디어학과
이광훈 컴퓨터공학과
김진경 전자공학과
조명훈 전자공학과
류민정 컴퓨터공학과
SELECT s.name, d.dname
FROM student s, (SELECT deptno, dname
FROM department
START WITH dname = '공과대학'
CONNECT BY PRIOR deptno=college) d
WHERE d.deptno= s.deptno;
문제7] 1. 교수 급여순으로 정렬하여 이전 1명의 교수와 급여 합계를 출력하시오.
출력)
name sal sal_sum
-------------------------
김도훈 400 400
이재우 400 800
성연희 400 800
염일웅 400 800
최윤식 400 800
이만식 400 800
전은지 400 800
남은혁 400 800
권혁일 400 800
SELECT name, sal,
SUM(sal) OVER(ORDER BY sal
ROWS 1 PRECEDING) AS sal_sum
FROM professor;
문제 8] 부서 테이블에서 부서간의 소속 관계를 top-down형식의 계층 구조로 출력하시오.
출력형식)
학과번호 학과이름 상위부서 학과위치
---------------------------------------------
10 공과대학
100 정보미디어학부 10
101 컴퓨터공학과 100 1호관
102 멀티미디어학과 100 2호관
200 메카트로닉스학부 10
201 전자공학과 200 3호관
202 기계공학과 200 4호관
SELECT deptno 학과번호, dname 학과이름, college 상위부서, loc 학과위치
FROM department
START WITH deptno = '10'
CONNECT BY PRIOR deptno = college;