교육 20일차

권재현·2021년 4월 12일

교육 데이터베이스

교육

목록 보기

14/49

1.순차함수 - 지정된 순서대로 데이터로 나열.

select 에서만 올 수 있음

row_number - 정렬 후 줄 번호 할당.
rank - 순위를 지정. 단, 동률이 있는 경우 동률 개수만큼 건너 뛰어 번호 할당.
dense_rank - 순위를 지정. 단, 동률이 있는 경우 다음에 숫자를 이어서 번호할당.

기본형태

순차함수명( ) over( partition by 기준 값,.....

order by 값 정렬 기준,.....)

partition by : 기준값을 기준으로 데이터를 구분 짓는다.,중복가능
- ex) --별 순위, 순서 -> partition by

  SELECT ENAME, SAL,JOB,
    ROW_NUMBER() OVER(ORDER BY SAL DESC) AS RNUM,
      RANK() OVER(ORDER BY SAL DESC) AS RNK,
       DENSE_RANK() OVER(ORDER BY SAL DESC) AS DRNK
FROM EMP
;

ROWNUM

--ORACLE의 ROWNUM : 데이터 취득 순서대로 줄번호를 할당한다.
-- 조회된 인덱스 순서
SELECT ENAME, SAL, ROWNUM
FROM EMP
ORDER BY SAL DESC(내림차순)
;

문제 1.업무별 급여 순위를 구하시오.

--업무별 급여 순위를 구하시오.
SELECT  ENAME,SAL, DEPTNO,
   RANK() OVER(PARTITION BY JOB ORDER BY SAL DESC) AS RNK
FROM EMP
;

PARTITION을 통해 업무끼리 분리 후 그다음 급여 순위를 별로 내림차순을 한다.

순차 함수는 SELECT에서만 사용 가능하다.

문제 2.부서별 급여순위 1위인 사원을 구하시오

SELECT ENAME,SAL,DEPTNO
FROM(SELECT ENAME,SAL,DEPTNO,
   RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC)AS RNK
FROM EMP)
WHERE RNK = 1
;

일단 처음 부서별 급여 1순위를 뽑기 위해서 부서별 급여순위를 뽑아야 하기 때문에, 서브쿼리를 사용했다. 서브쿼리를 살펴보면 순차함수 RANK을 사용해서 가로안에 파티션을 부서번호인 DEPTNO를 기준으로 나누고 SAL을 통해 내림차순을 했다. 그다음 1등인 사원만 구하면 되기에 WHERE 에서 RNK =1 이 들어갔다.

문제 3.가장 많은 급여를 받는 사원의 부서명을 구하시오

-- HINT SUBQUERY -> WHERE
-- HINT SUBQUERY * 2
--사용된 테이블 : EMP, DEPT
SELECT DEPTNO
FROM
    (SELECT ENAME,DEPTNO, SAL,
        RANK() OVER(ORDER BY SAL DESC) AS RNK
    FROM EMP)
WHERE RNK =1
;
--출력: DEPTNO : 10

SELECT DNAME
FROM DEPT
WHERE DEPTNO = (SELECT DEPTNO 
        FROM  
        (SELECT ENAME,DEPTNO, SAL,
        RANK() OVER(ORDER BY SAL DESC) AS RNK
        FROM EMP)
    WHERE RNK =2) 
;
--출력 DNAME : ACCOUNTING

이 문제는 데이터를 두개를 활용해서 풀 수 있는 문제인데, 바로 공통되게 들어가 있는 부서 숫자인 DEPTNO을 활용해야한다.

처음에 사원 중에 가장 많은 급여를 받는 사람을 구해야 하기에 바로 순차함수 RANK 안에 ORDER BY SAL(급여) DESC(내림차순)을 하면 바로 가장 위에 있는 ROW가 많은 급여을 받는 사람이다. 그다음 그사람 필요하기에 WHERE에서 별칭 RNK =1 적었고, 그다음 여기가 핵심이다. 다른테이블인 DEPT 에서 사용하기 위해서 최종 SELECT에 DEPTNO를 적는다.

그런 다음 FROM에서 테이블 DEPT를 갖고오고, SELECT에서 부서명만 필하니 DNAME을 적는다

마지막으로 WHERE 에 부서번호를 받기 위해 DEPTNO 에 서브쿼리를 붙이는 것이다.

2. 관계파악

primary key: 주키
- 데이터를 특정 짓는 데이터 컬럼, 고유 값
foreign key : 외래키, PK에서 가져와서 사용한다.
- 특정 pk에 관계를 가진 컬럼 , 종속적
그림의 동그라미 의미 : 없을 수 도 있다 라는 의미

A.직접참조 : 1: N, DEPTNO==DEPTNO

B. 자가참조 :범위 EMPNO=MGR(상사EMPNO)

많이 사용은 안함

C. 간접참조 : 한테이블 SAL BETWEEN LOSAL AND HISAL

3. Join- 데이터 + 데이터, FROM에서만 사용

A inner join B
- on 조건
A left outer join B
- on 조건
A right outer join B
- on 조건

별칭- 별칭 사용 습관을 들이자

--별칭 : E,D
--ASC는 오름차순
SELECT *
FROM EMP E INNER JOIN DEPT D
                         ON E.DEPTNO = D.DEPTNO
ORDER BY E.EMPNO ASC, D.DEPTNO ASC
;

SAL 범위 간접참조 사용

--SAL 범위
 --간접참조사용 SAL
 SELECT * 
 
 FROM EMP E INNER JOIN SALGRADE SG
         ON E.SAL BETWEEN SG.LOSAL AND SG. HISAL
 ;

자가참조 사용

 --자가참조
SELECT E1.ENAME AS 부하직원, E1.MGR, E2.EMPNO, E2.ENAME AS 상급자
FROM EMP E1 INNER JOIN EMP E2
                      ON E1.MGR = E2.EMPNO
                     
;

직접참조 와 간접 참조 사용

---사원의 부서명과 급여등급을 출력하시오.
--직접참조사용
SELECT E.ENAME,D.DNAME,SG.GRADE
FROM EMP E INNER JOIN DEPT D
                    ON E.DEPTNO = D.DEPTNO
            INNER JOIN SALGRADE SG
                ON E.SAL BETWEEN SG.LOSAL AND SG.HISAL
;

문제4. 시카고에 근무하는 사원들의 급여순위를 구하시오

--문제
--시카고에 근무하는 사원들의 / 급여순위를 구하시오.
SELECT E.ENAME,E.SAL, D.LOC,
    RANK() OVER (ORDER BY SAL  DESC) AS RNK
FROM EMP E INNER JOIN DEPT D
                        ON E.DEPTNO = D.DEPTNO
                        AND D.LOC= 'CHICAGO'
;