오라클 기초 실습내용 3/3
기본 sql 사용과 해당문법의 설명을 확인하면서 기초를 다시 확인할수있을듯함..
워낙 오래전 자료다 보니... 지우는것보단...이렇게라도 .... 가끔보게되면
뭔가 새로운 기분이 들지않을까..해서......
UNIX(r) System V Release 4.0 (xx)
login: team4
Password:
Sun Microsystems Inc. SunOS 5.5.1 Generic May 1996
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
공지사항 : Oracle Login 변경(oracle실습은 Team계정으로 login 하여 사용하세요)
- from : tschang (1999/06/14)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL*Plus: Release 8.0.6.0.0 - Production on Fri Feb 18 09:47:54 2000
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to:
Oracle8 Enterprise Edition Release 8.0.6.0.0 - Production
With the Objects option
PL/SQL Release 8.0.6.0.0 - Production
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
ATTEND TABLE
BONUS TABLE
DEPT TABLE
DUMMY TABLE
HYO_S TABLE
IMSIC TABLE
MAST TABLE
MASTER36 TABLE
MASTERC TABLE
MP TABLE
M_4 TABLE
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
OOK TABLE
OPENC TABLE
P TABLE
PAL TABLE
S TABLE
S2 TABLE
SALGRADE TABLE
SLAVE36 TABLE
SP TABLE
SS TABLE
SS2 TABLE
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
SSS TABLE
STUDENTC TABLE
S_IN TABLE
VIEWPC VIEW
WANP TABLE
WANS TABLE
WANSP TABLE
설강 TABLE
설강36 TABLE
수강 TABLE
수강36 TABLE
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
학생 TABLE
학생36 TABLE
35 rows selected.
SQL> select * from sp;
S_ P_ QTY
-- -- ----------
S1 P1 300
S1 P2 200
S1 P3 400
S1 P4 200
S1 P5 100
S1 P6 100
S2 P1 300
S2 P2 400
S3 P2 200
S4 P2 200
S4 P4 300
S_ P_ QTY
-- -- ----------
S4 P5 400
12 rows selected.
SQL> select p_id
2 from sp
3 group by p_id
4 having count(*) >1; // 테이블sp에서 p_id 그룹으로하고 조건은 p_id의 합이 1보다 큰것을 출력.
// group by ... + having(조건) ,count(*) :
P_
--
P1
P2
P4
P5
SQL> select sname
2 from s, sp
3 where s.s_id = sp.s_id and sp.p_id = 'P2';
SNAME
--------------------
홍길동
손오공
박문수
신수동
SQL> select sname
2 from s
3 where s_id in
4 (select s_id
5 from sp
6 where sp.p_id = 'P2');
SNAME
--------------------
홍길동
손오공
박문수
신수동
SQL> select sname
2 from s
3 where exists
4 (select *
5 from sp
6 where s.s_id=sp.s_id and sp.p_id='P2');
SNAME
--------------------
홍길동
손오공
박문수
신수동
SQL> select sname
2 from s
3 where s_id not in
4 (select s_id
5 from sp
6 where sp.p_id='P2');
SNAME
--------------------
반월동
SQL> select p_id
2 from p
3 where weight >16
4 union //합집합
5 select p_id
6 from sp
7 where p_id='S2';
P_
--
P2
P3
P6
SQL> l // l =list =엘.
1 select p_id
2 from p
3 where weight >16
4 union
5 select p_id
6 from sp
7* where p_id='S2'
SQL> l 4
4* union
SQL> c /union/union all //문자바꿈. union 과 union all의 차이
4* union all
SQL> / //바로앞의 실행
P_
--
P2
P3
P6
SQL> l
1 select p_id
2 from p
3 where weight >16
4 union all
5 select p_id
6 from sp
7* where p_id='S2'
SQL> l 7
7* where p_id='S2' //문장이 제대로 수행안된 이유 s_id 를 p_id로 잘못사용.....
SQL> c /p/s
7* where s_id='S2'
SQL> /
P_ //union all..................................
--
P2
P3
P6
P1
P2
SQL> l
1 select p_id
2 from p
3 where weight >16
4 union all
5 select p_id
6 from sp
7* where s_id='S2'
SQL> l 4
4* union all
SQL> c /union all/union
4* union
SQL> /
P_
-- //union.........................................union all과의 차이점 ?????
P1
P2
P3
P6
SQL> select s_id
2 from s
3 where city =
4 (select city
5 from s
6 where s_id='S1');
S_
--
S1
S4
SQL> select s_id // s_id를 테이블 s에서 가져와 출력을 하는데....
2 from s
3 where city in // city가 다음과 같아야한다.=........
4 (select city // city를 s테이블에서 가져오는데 조건은 s_id가 's1'이어야한다.
5 from s // ( => s_id가 's1'인 것의 city를 검색한다)
6 where s_id='S1');
S_
--
S1
S4
SQL> select * from p;
P_ PNAME COLOR WEIGHT CITY
-- -------------------- ---------- ---------- ----------
P1 너트 빨강 12 서울
P2 볼트 초록 17 부산
P3 스크류 파랑 17 광주
P4 스크류 빨강 14 서울
P5 캠 파랑 12 부산
P6 콕 빨강 19 서울
6 rows selected.
SQL> update p
2 set color='노랑',
3 weight=weight+5,
4 city=NULL
5 where p_id='P2';
update p
*
ERROR at line 1:
ORA-01407: cannot update ("TEAM4"."P"."CITY") to NULL
SQL> desc p;
Name Null? Type
------------------------------- -------- ----
P_ID NOT NULL CHAR(2)
PNAME NOT NULL VARCHAR2(20)
COLOR VARCHAR2(10)
WEIGHT NOT NULL NUMBER(3)
CITY NOT NULL VARCHAR2(10)
SQL> desc s;
Name Null? Type
------------------------------- -------- ----
S_ID NOT NULL CHAR(2)
SNAME NOT NULL VARCHAR2(20)
STATUS NOT NULL NUMBER(3)
CITY NOT NULL VARCHAR2(10)
SQL> desc sp;
Name Null? Type
------------------------------- -------- ----
S_ID NOT NULL CHAR(2)
P_ID NOT NULL CHAR(2)
QTY NOT NULL NUMBER(3)
SQL> select * from sp;
S_ P_ QTY
-- -- ----------
S1 P1 300
S1 P2 200
S1 P3 400
S1 P4 200
S1 P5 100
S1 P6 100
S2 P1 300
S2 P2 400
S3 P2 200
S4 P2 200
S4 P4 300
S_ P_ QTY
-- -- ----------
S4 P5 400
12 rows selected.
SQL> select * from s;
S_ SNAME STATUS CITY
-- -------------------- ---------- ----------
S1 홍길동 20 서울
S2 손오공 10 부산
S3 박문수 30 부산
S4 신수동 20 서울
S5 반월동 30 경주
SQL> select * from pp;
P_ PNAME COLOR WEIGHT CITY
-- ---------- ----- ---------- ----------
p1 너트 빨강 12 서울
p2 볼트 초록 17 부산
p3 스크류 파랑 17 광주
p4 스크류 빨강 14 서울
p5 캠 파랑 12 부산
p6 콕 빨강 19 서울
6 rows selected.
SQL> desc pp;
Name Null? Type
------------------------------- -------- ----
P_ID NOT NULL CHAR(2)
PNAME NOT NULL VARCHAR2(10)
COLOR NOT NULL VARCHAR2(5)
WEIGHT NOT NULL NUMBER(3)
CITY VARCHAR2(10)
SQL>select sp * from sspp;
S_ P_ QTY
-- -- ----------
s1 p1 300
s1 p2 200
s1 p3 400
s1 p4 200
s1 p5 100
s1 p6 100
s2 p1 300
s2 p2 400
s3 p2 200
s4 p2 200
s4 p4 300
S_ P_ QTY
-- -- ----------
s4 p5 400
12 rows selected.
SQL> desc sspp;
Name Null? Type
------------------------------- -------- ----
S_ID NOT NULL CHAR(2)
P_ID NOT NULL CHAR(2)
QTY NOT NULL NUMBER(3)
SQL> connect scott/tiger
Connected.
SQL> selcect * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL
---------- ---------- -------------------- ---------- --------- ----------
COMM DEPTNO
---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800
20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600
300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250
500 30
EMPNO ENAME JOB MGR HIREDATE SAL
---------- ---------- -------------------- ---------- --------- ----------
COMM DEPTNO
---------- ----------
7566 JONES MANAGER 7839 02-APR-81 2975
20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250
1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30
EMPNO ENAME JOB MGR HIREDATE SAL
---------- ---------- -------------------- ---------- --------- ----------
COMM DEPTNO
---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
7788 SCOTT ANALYST 7566 09-DEC-82 3000
20
7839 KING PRESIDENT 17-NOV-81 5000
10
EMPNO ENAME JOB MGR HIREDATE SAL
---------- ---------- -------------------- ---------- --------- ----------
COMM DEPTNO
---------- ----------
7844 TURNER SALESMAN 7698 08-SEP-81 1500
0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100
20
7900 JAMES CLERK 7698 03-DEC-81 950
30
EMPNO ENAME JOB MGR HIREDATE SAL
---------- ---------- -------------------- ---------- --------- ----------
COMM DEPTNO
---------- ----------
7902 FORD ANALYST 7566 03-DEC-81 3000
20
7934 MILLER CLERK 7782 23-JAN-82 1300
10
7035 smith timer
15 rows selected.
SQL> select empno, ename, sal, //마지막의 ,는 있어도 되고 없어도 되고,,,,,,,
2 from emp
3 where deptno=&deptno; //조건을 deptno로 주는데 deptno를 변수로 설정해서 입력값을 받아서 조건 결정
Enter value for deptno: 10
old 3: where deptno=&deptno
new 3: where deptno=10
EMPNO ENAME SAL
---------- ---------- ----------
7782 CLARK 2450
7839 KING 5000
7934 MILLER 1300
SQL> select empno,ename , sal,deptno //deptno를 추가
2 from emp
3 where deptno=&deptno;
Enter value for deptno: 30
old 3: where deptno=&deptno
new 3: where deptno=30
//-----생략----//
SQL> / //재실행
Enter value for deptno: 10
old 3: where deptno=&deptno
new 3: where deptno=10
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
7782 CLARK 2450 10
7839 KING 5000 10
7934 MILLER 1300 10
SQL> /
Enter value for deptno: 20
old 3: where deptno=&deptno
new 3: where deptno=20
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
7369 SMITH 800 20
7566 JONES 2975 20
7788 SCOTT 3000 20
7876 ADAMS 1100 20
7902 FORD 3000 20
SQL> /
Enter value for deptno: 30
old 3: where deptno=&deptno
new 3: where deptno=30
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
7499 ALLEN 1600 30
7521 WARD 1250 30
7654 MARTIN 1250 30
7698 BLAKE 2850 30
7844 TURNER 1500 30
7900 JAMES 950 30
6 rows selected.
SQL> select * from ss;
S_ SNAME STATUS CITY
-- ---------- ---------- ----------
s1 홍길동 20 서울
s2 손오공 10 부산
s3 박문수 30 부산
s4 신수동 20 서울
s5 반월성 30 경주
SQL> select * from sspp
2 ;
S_ P_ QTY
-- -- ----------
s1 p1 300
s1 p2 200
s1 p3 400
s1 p4 200
s1 p5 100
s1 p6 100
s2 p1 300
s2 p2 400
s3 p2 200
s4 p2 200
s4 p4 300
S_ P_ QTY
-- -- ----------
s4 p5 400
12 rows selected.
SQL> select * fromm tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
A11 TABLE
B11 TABLE
C11 TABLE
MAST TABLE
PP TABLE
SLAVE1 TABLE
SS TABLE
SSPP TABLE
8 rows selected.
SQL> column sal format $99,999 //칼럼 표현형식 => I-26 page
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL
---------- ---------- -------------------- ---------- --------- --------
COMM DEPTNO
---------- ----------
7566 JONES MANAGER 7839 02-APR-81 $2,975
20
7654 MARTIN SALESMAN 7698 28-SEP-81 $1,250
1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 $2,850
..................이하생략!!
15 rows selected.
SQL> column sal clear //칼럼형식 해제.
SQL> slelect * from enp mp; //엘리어스
EMPNO ENAME JOB MGR HIREDATE SAL
---------- ---------- -------------------- ---------- --------- ----------
COMM DEPTNO
---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800
20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600
300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250
...........이하생략
15 rows selected.
SQL> select &1, &&2 from emp //SQL변수(& , &&)입력!
2 where &&2 =&3;
Enter value for 1: SAL //1의값 입력
Enter value for 2: JOB //2의값 입력
old 1: select &1, &&2 from emp
new 1: select SAL, JOB from emp
Enter value for 3: 'SALESMAN'
old 2: where &&2 =&3
new 2: where JOB ='SALESMAN'
SAL JOB
---------- --------------------
800 SALESMAN
1100 SALESMAN
950 SALESMAN
1300 SALESMAN
SQL> /
Enter value for 1: SAL //1의값 입력
old 1: select &1, &&2 from emp //2의값은 앞에서 입력한값을 그대로 사용
new 1: select SAL, JOB from emp //2=JOB
Enter value for 3: 'MANAGER'
old 2: where &&2 =&3
new 2: where JOB ='MANAGER'
SAL JOB
---------- --------------------
2975 MANAGER
2850 MANAGER
2450 MANAGER
SQL> /
Enter value for 1: saL
old 1: select &1, &&2 from emp
new 1: select saL, JOB from emp
Enter value for 3: 'SALESMAN'
old 2: where &&2 =&3
new 2: where JOB ='SALESMAN'
SAL JOB
---------- --------------------
1600 SALESMAN
1250 SALESMAN
1250 SALESMAN
1500 SALESMAN
SQL> select ename , sal, sal/22, round(sal/22.0), trunc(sal/22.2) //SQL함수 I-29 page 책이 조금 틀림
2 from emp
3 where deptno=30;
ENAME SAL SAL/22 ROUND(SAL/22.0) TRUNC(SAL/22.2)
---------- ---------- ---------- --------------- ---------------
ALLEN 1600 72.7272727 73 72
WARD 1250 56.8181818 57 56
MARTIN 1250 56.8181818 57 56
BLAKE 2850 129.545455 130 128
TURNER 1500 68.1818182 68 67
JAMES 950 43.1818182 43 42
6 rows selected.
SQL> select ename, sum(comm)
2 from emp
3 group by ename;
ENAME SUM(COMM)
---------- ----------
ADAMS
ALLEN 300
BLAKE
CLARK
FORD
JAMES
JONES
KING
MARTIN 1400
MILLER
SCOTT
ENAME SUM(COMM)
---------- ----------
SMITH
TURNER 0
WARD 500
smith
15 rows selected.
SQL> select avg(sal), max(sal), min(comm)*100, count(comm), sum(sal)
2 from emp
3 where job='SALESMAN';
AVG(SAL) MAX(SAL) MIN(COMM)*100 COUNT(COMM) SUM(SAL)
---------- ---------- ------------- ----------- ----------
1400 1600 0 4 5600
SQL> select ename, sum(comm)
2 from emp
3 group by ename
4 order by ename desc;
ENAME SUM(COMM)
---------- ----------
smith
WARD 500
TURNER 0
SMITH
SCOTT
MILLER
MARTIN 1400
KING
JONES
JAMES
FORD
ENAME SUM(COMM)
---------- ----------
CLARK
BLAKE
ALLEN 300
ADAMS
15 rows selected.
SQL> select deptno, sum(comm)
2 from emp
3 group by deptno;
DEPTNO SUM(COMM)
---------- ----------
10
20
30 2200
SQL> select count(job)
2 from emp;
COUNT(JOB) // 칼럼 job 이 15개가 있다. 중복과는 상관없다.
----------
15
SQL> select count(distinct job) //distinct:중복되 칼럼을 간단하게
2 from emp;
COUNT(DISTINCTJOB)
------------------
6
SQL> select deptno, job, count(*)
2 from emp
3 where deptno in(10,20) //in = or
4 group by deptno, job; //deptno와 job의 순서로...group 만약deptno하나에 대해서만 group by해주면 에러
DEPTNO JOB COUNT(*)
---------- -------------------- ----------
10 CLERK 1
10 MANAGER 1
10 PRESIDENT 1
20 ANALYST 2
20 CLERK 2
20 MANAGER 1
6 rows selected.
SQL> select deptno from emp;
DEPTNO
----------
20
30
30
20
30
30
10
20
10
30
20
DEPTNO
----------
30
20
10
15 rows selected.
SQL> select deptno, job
2 from emp;
DEPTNO JOB
---------- --------------------
20 CLERK
30 SALESMAN
30 SALESMAN
20 MANAGER
30 SALESMAN
30 MANAGER
10 MANAGER
20 ANALYST
10 PRESIDENT
30 SALESMAN
20 CLERK
DEPTNO JOB
---------- --------------------
30 CLERK
20 ANALYST
10 CLERK
timer
15 rows selected.
SQL> select deptno, sum(sal), avg(sal) //sal의 합계와 평균.
2 from emp //
3 where job <> 'MANAGER' // job != 'MANAGER'
4 group by deptno //
5 having avg(sal)> 1500; //그룹화하는 조건으로 sal의 평균이 1500이상
DEPTNO SUM(SAL) AVG(SAL)
---------- ---------- ----------
10 6300 3150
20 7900 1975
SQL> select deptno, sal
2 from emp;
DEPTNO SAL
---------- ----------
20 800
30 1600
30 1250
20 2975
30 1250
30 2850
10 2450
20 3000
10 5000
30 1500
20 1100
DEPTNO SAL
---------- ----------
30 950
20 3000
10 1300
15 rows selected.
SQL> select deptno , sum(sal) //1.job이 manager나 president가 아닌 조건을
2 from emp 만족하는
3 where job not in('MANAGER','PRESIDENT') .....
4 group by deptno //
5 having count(*)>2 and sum(sal) >16000 //그룹화하는 조건을 나열
6 order by sum(sal) desc; //정렬하는 칼럼과 방식.내림차순
DEPTNO SUM(SAL)
---------- ----------
20 7900
30 6550
SQL> select initcap(ename) //출력시 ename의 첫글자를 대문자로한다.
2 from emp;
INITCAP(EN
----------
Smith
Allen
Ward
Jones
Martin
Blake
Clark
Scott
King
Turner
Adams
INITCAP(EN
----------
James
Ford
Miller
Smith
15 rows selected.
===>여기서는 모르겠다....... 책의 실습 I-34 page
SQL> select inse lect * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select instr(loc, 'A Y\ ^H X')
2 from dept;
INSTR(LOC,'X')
--------------
0
0
0
0
SQL> select inse rt tr(loc, C 'C')
2 for rom dept;
INSTR(LOC,'C')
--------------
0
0
1
0
SQL> select instr(loc, '" L')
2 from m dept;
INSTR(LOC,'L')
--------------
0
3
0
0
SQL> select length(eanme name)
2 from emp;
LENGTH(ENAME)
-------------
5
5
4
5
6
5
5
5
4
6
5
LENGTH(ENAME)
-------------
5
4
6
5
15 rows selected.
SQL> select lower(eanme name)
2 from emp;
LOWER(ENAM
----------
smith
allen
ward
jones
martin
blake
clark
scott
king
turner
adams
LOWER(ENAM
----------
james
ford
miller
smith
15 rows selected.
SQL> su select substr(grade, rde ade select substr(grade, 1,2)
2 from frmoo om emp;
select substr(grade, 1,2)
*
ERROR at line 1:
ORA-00904: invalid column name
SQL> l 1
1* select substr(grade, 1,2)
SQL> c grad /gradd e/ename
1* select substr(ename, 1,2)
SQL> /
SUBS
----
SM
AL
WA
JO
MA
BL
CL
SC
KI
TU
AD
SUBS
----
JA
FO
MI
sm
15 rows selected.
SQL> select ename, job
2 (decodd e, (job, 'CLERK', 0.2, 'MANAGER', 0.5 15, 'SALESMAN', 0.3, 0.2)*SAL)
3 from emp
4 where deptno=10;
(decode, (job, 'CLERK', 0.2, 'MANAGER', 0.15, 'SALESMAN', 0.3, 0.2)*SAL)
*
ERROR at line 2:
ORA-00907: missing right parenthesis
SQL> l 2
2* (decode, (job, 'CLERK', 0.2, 'MANAGER', 0.15, 'SALESMAN', 0.3, 0.2)*SAL)
SQL> c /decode,/decode
2* (decode (job, 'CLERK', 0.2, 'MANAGER', 0.15, 'SALESMAN', 0.3, 0.2)*SAL)
SQL> /
select ename, job
*
ERROR at line 1:
ORA-00904: invalid column name
SQL> l
1 select ename, job
2 (decode (job, 'CLERK', 0.2, 'MANAGER', 0.15, 'SALESMAN', 0.3, 0.2)*SAL)
3 from emp
4* where deptno=10
SQL> l 2
2* (decode (job, 'CLERK', 0.2, 'MANAGER', 0.15, 'SALESMAN', 0.3, 0.2)*SAL)
SQL> c /sla SAL/bonus
2* (decode (job, 'CLERK', 0.2, 'MANAGER', 0.15, 'bonusESMAN', 0.3, 0.2)*SAL)
SQL> roo llback
2 ;
Rollback complete.
SQL> l
1 rollback
2*
SQL> seec lect enma^? select ename, job
2 (decoed de(job, 'CLERK', 0.2, 'MANAGER', 0.15, 'SLAESMAN',0.3,0.2)*SAL) BONUS
3 FLR from emp
4 where deptno =10;
select ename, job
*
ERROR at line 1:
ORA-00904: invalid column name
SQL> l 1
1* select ename, job
SQL> c /job/job,
1* select ename, job,
SQL> /
ENAME JOB BONUS
---------- -------------------- ----------
CLARK MANAGER 490
KING PRESIDENT 1000
MILLER CLERK 260
SQL>
'IT TIP > Oracle' 카테고리의 다른 글
| Oracle dict 활용 & Compile ,Drop 쿼리 (0) | 2019.06.24 |
|---|---|
| 테스트용 실습 문제와 풀이 60문제 (0) | 2017.04.13 |
| 오라클 테스트를 위한 자료테이블 DESC (0) | 2017.04.13 |
| sqlplus - 기초실습2 (0) | 2017.04.13 |
| sqlplus - 기초실습1 (0) | 2017.04.13 |
| 사용자정의 SPLIT함수. (0) | 2017.04.06 |
| REGEXP_SUBSTR함수로.SPLIT하기 (0) | 2017.04.06 |
| 계층구조 START WITH...CONNECT BY (0) | 2017.04.06 |
| 프로시저내 다이나믹 SQL 예제1 (0) | 2017.04.05 |
| 그룹내 순위결정하는 함수 - ROW_NUMBER() ,RANK() ,DENSE_RANK() (0) | 2017.04.05 |