19.10.08. (TOP_N질의/치환변수/RANK/DENSE_RANK/ROW_NUMBER/PARTITION BY/ER 다이어그램/PK vs FK)

(1) TOP_N 질의 활용

​

문1 emp 테이블에서, 급여를 가장 많이 받는 3명을 출력하라.

​

select a.*

from (select *

from emp

order by sal desc) a

where rownum <=3;

​

문2 사원의 사번, 이름, 급여, 커미션포함급여, 부서이름, 도시를 출력하라.

-- 급여를 가장 많이 받는 순으로 정렬

-- 부서이름이 null이면 '대기발령중'으로 출력

-- 한 화면당 5명씩 출력

-- 2번째 화면의 사원번호를 출력

​

(틀림.... 어렵다...)

select e.empno,e.ename,e.sal,e.sal+nvl(comm,0),nvl2(dname,dname,'대기발령중'),d.loc

from emp e, dept d, (select empno,ename,sal,sal+nvl(comm,0),nvl2(dname,dname,'대기발령중'),loc

from emp natural join dept

order by 3 desc)

where e.deptno=d.deptno and rownum <= 10

order by 3 desc;

​

(정답 ==> 한번에 다 하려고 하지 말고, 천천히 하나하나씩 풀어가기)

( ① => ② => ③ 풀이과정 )

① select empno,ename,sal,sal+nvl(comm,0),nvl(dname,'대기발령'), d.loc

from emp e, dept d

where e.deptno = d.deptno(+) (* emp 테이블에 사원번호가 null 값이니까 출력하려면 left outer join 사용)

order by 3 desc;

​

② select rownum, a.*

from (select empno,ename,sal,sal+nvl(comm,0),nvl(dname,'대기발령'), d.loc

from emp e, dept d

where e.deptno = d.deptno(+)

order by 3 desc) a

where rownum <= 10; (* 1번째 화면은 1등~5등 출력되니까 2번째 화면은 6등~10등까지 총 10명 출력)

​

cf) 출력순서

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY

​

③select b.*

from (select rownum rn, a.*

from (select empno,ename,sal,sal+nvl(comm,0),nvl(dname,'대기발령'), d.loc

from emp e, dept d

where e.deptno = d.deptno(+)

order by 3 desc) a

where rownum <= &pg*5) b (* 한 화면당 5명씩 출력)

where b.rn > &pg * 5-5; (* pg에 대한 값 입력 : 2)

​

(2) 치환변수 : sql문에서 '&'치환변수를 사용하면 값을 입력하도록 요구할 수 있다.

: 동일한 컬럼을 데이터만 바꿔서 검색하거나 입력하고자 할 때 사용

: 문자 or 날짜를 입력할 때는 작은따옴표(' ')를 사용

​

(3) RANK( ) OVER 함수 : 서브쿼리 안써도 되는!!

: rownum과 같지만 rownum은 무조건 1부터 순번을 매긴다면,

rank( )는 순위가 같을 경우 같은 순위로 처리된다.

​

select rank( ) over(order by sal desc) rn, ename, sal s

from emp;

​

select empno, sum(sal), rank() over(order by sum(sal) desc) as rank

from emp

group by empno;

​

(* 동점일 경우 같은 순위로 처리되기 때문에 만약 2등이 2명이라면 그 후 순위은 3등이 아닌 4등이 된다.)

이렇게 나타나는 것이 싫다면? DENSE_RANK() OVER() 함수 사용! 2명의 2등 뒤에 그 후 순위는 3등으로 출력~

​

select empno, sum(sal), dense_rank() over(order by sum(sal) desc) as rank

from emp

group by empno;

​

(* 동점일 경우라도 같은 등수로 처리되는 것이 싫다면? ROW_NUMEBER() 사용! )

select empno, sum(sal), row_number() over(order by sum(sal) desc) as rank

from emp

group by empno;

​

(* 전체가 아닌 부서번호별로 등수를 매기고 싶다면? rank함수 or row_number 안에 partition by 컬럼명)

select deptno,ename,job,sum(sal),rank() over(partition by deptno order by sum(sal) desc) as rank

from emp

group by deptno,ename,job;

​

(* 1위만 출력하고 싶다면? select문으로 한 번 더 감싸주기)

select * from

(select deptno,ename,job,sum(sal),rank() over(partition by deptno order by sum(sal) desc) as rank

from emp

group by deptno,ename,job)

where rank = 1;

​

(4) Entity Relationship Diagram = ERD

​

- 객체(Entity), 속성(Attribute), 관계(Relationship)로 구성

- 점선은 선택(may be)를 의미하고, 실선은 필수(must be)를 의미

- 양쪽을 모두 고려해야 한다. 우선, 왼쪽에서 오른쪽으로! 그후, 오른쪽에서 왼쪽 관계 따져보기!

​

ex) 사원과 부서 객체가 있다고 가정해보자. (M : 1 관계)

사원 입장에서 부서와의 관계 : 사원은 어느 부서든 하나의 부서에 반드시 소속되기 때문에 실선

부서 입장에서 사원과의 관계 : 신생부서일 경우 아직 사원이 없을 수도 있기 때문에 점선 or 각 부서는

여러 명의 사원을 배치

​

(5) PRIMARY KEY 기본키 vs FOREIGN KEY 외래키

​

PRIMARY KEY : UIQUE, NOT NULL

: 학번, 주민번호, 사원번호 등등

 

FOREIGN KEY : 외부 테이블의 PRIMARY KEY를 참조한다는 의미에서 외래키

: NULL값 가능

​

ex) EMP 테이블과 DEPT 테이블

DEPT 테이블이 부모 테이블이며, DEPTNO가 기본키

EMP 테이블이 자식 테이블이며, DEPTNO가 외래키

- 부모 테이블에 없는 값이 자식 테이블에 입력될 수 없다.

- 자식 테이블이 참조 하고 있는 값은 부모 테이블에서 삭제할 수 없다.