- not <> 인덱스 x
- not empno < 7900 -> empno >= 7899로 바뀜
- between 인덱스 o , not between 인덱스 o
-
17) ename 첫번째 글자가 A인 사원만 이름 급여 조회
17-1) FULL SCAN . where절에 인덱스만들어진 컬럼에 함수나 계산식 사용되는 순간
인덱스 사용안함.
select ename, sal
from emp
where SUBSTR(ename,1,1) = 'A';
17-2) INDEX SCAN. LIKE 인덱스 사용함.
문자%만 되요
select ename, sal
from emp
where ename LIKE 'A%';
17-3) FULL SCAN.
select ename, sal
from emp
where ename LIKE '_A%';
17-4) FULL SCAN.
empno type이 뭐에요-> 숫자 -> LIKE '' 문자로 찾아야해요 -> TO_CHAR("EMPNO") LIKE '77%'로 바뀌어서 찾아요
그래서 인덱스 사용이 안돼요. 문자%만 되요
select empno, ename
from emp
where empno LIKE '77%';
18) FULL SCAN.
null값 찾는것은 풀스캔.
select ename, sal
from emp
where ename IS null;
19) FULL SCAN.
where절 컬럼에 무엇을 사용하면 인덱스 안쓸거야 하는 의미.
select ename, sal
from emp
where empno + 0 = 7788;
20) job + mgr 결합 인덱스
20-1) INDEX SCAN.e
select ename, job, mgr
from emp
where job > 'MANAGER';
20-2) FULL SCAN.
select ename, job, mgr
from emp
where mgr = 7788;
20-3) FULL SCAN.
select ename, job, mgr
from emp
where job <> 'SALESMAN' and mgr = 7788;
20-4) INDEX SCAN
select ename, job, mgr
from emp
where job = 'SALESMAN' and mgr = 7788;
alter session set optimizer_mode=all_rows;
1) SORT ORDER BY 됨
select ename, sal
from emp
where ename > 'BB'
order by sal;
2) SORT ORDER BY 안함.
INDEX이기때문에. 어차피 인덱스 정렬순서로 나오기 때문에.
select ename, sal
from emp
where ename > 'BB'
order by ename;
3) INDEX SCAN DESCENDING
인덱스 역순으로도 가능해.
select ename, sal
from emp
where ename > 'BB'
order by ename desc;
4) INDEX SCAN
필요한 컬럼만 쓰면 인덱스로 찾아서 검색.
select ename
from emp
where ename > 'BB'
order by ename desc;
-----------------------------------
set autut on exp
alter session set optimizer_mode=all_rows;
alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
문제1) FULL SCAN만 나와. 이거 인덱스 사용하는걸로 바꿔보기.
select ename, hiredate
from emp
where TO_CHAR(hiredate,'yyyy') = '1981';
정답1-1)
select ename, hiredate
from emp
where hiredate >= TO_DATE('19810101','yyyymmdd')
and hiredate < TO_DATE('19820101', 'yyyymmdd');
정답1-2) between은 양 괄호에 =이 붙어서 19820101의 자정 날짜도 나오므로.
select ename, hiredate
from emp
where hiredate between TO_DATE('19810101','yyyymmdd') and TO_DATE('19820101', 'yyyymmdd') - 1/24/60/60;
문제2) FULL SCAN. 제일처음 마지막에 입사한 날짜. 인덱스 사용하는걸로 바꿔보기.
select min(hiredate), max(hiredate)
from emp;
정답2-1) min, max 따로 select하면 min/max index scan 함.
따라서 min따로 max따로 해서 합쳐주기.
select min(hiredate) from emp
union all
select max(hiredate) from emp;
정답2-2)
select min(d1), max(d1)
from ( select min(hiredate) d1 from emp
union all
select max(hiredate) from emp) A;
문제3)
select ename, job, mgr
from emp
where job || mgr in ('CLERK7788', 'DA7902', 'MANAGER7839');
정답3-1)
select ename, job, mgr
from emp
where job = 'CLERK' and mgr =7788
or job = 'DA' and mgr = 7902
or job = 'MANAGER' and mgr = 7839;
정답3-2)
select ename, job, mgr from emp
where (job, mgr) in ( ('CLERK',7788), ('DA', 7902), ('MANAGER', 7839) );
----------------------------------------------------------------------
오라클db 자동변환 우선순위
- 문자 vs 숫자 : 문자를 숫자로 변경
- 문자 vs 날짜 : 문자를 날짜로 변경
- 날짜 vs 숫자 : 자동변경 불가 에러 발생
예외>> like 사용시 / 문자함수(length, substr, instr, lpad...0 사용시에만 숫자/날짜를 문자로 변경함
ex1) 우편번호 00123 char로.
- number(5) -> 123
- varchar2(5) -> 00123
- char(5) -> 00123
- varchar2(5) 에 1234 입력하면 1234만 입력됨. 가변길이 4자리수잡음.
- char(5) 1234공백 입력되고 고정길이5자리수 잡음.
where 우편번호 = 12345; -> 인덱스 사용 불가.
문자 숫자
===> where to_number(우편번호) = 12345
where 우편번호 = '12345'; -> 인덱스사용가능
ex2) 사번 : number(4);
where 사번 = 7788;
where 사번 = '7788' -> where 사번 to_number('7788');
인덱스 문제는 없지만 컬럼을바꾸지않기떄문에. 타입을 맞춰주기.
where 사번 like '77%'
===> where to_char(사번) like '77%'
ex3) 등록일자 : date
whee 등록일자 like '2023%';
===> where to_char(등록일자) like '2023%'
----------------------------------------------------------------------------
인덱스 스캔방식
poin조건 : = in
line 조건 : < > between like
1)
select ename, deptno, sal
from emp
where deptno between 10 and 30
and sal = 2700;
2)
select ename, deptno, sal
from emp
where deptno in (10, 20, 30)
and sal = 2700;
in이좋니 between이 좋니 -> 인덱스없으면 같고
인덱스있으면 in은 확인하고 빠져나온다고?
-----------------------------------------------------------------------------
작업량 확인
start c:\sql\xplan
create index sale_idx1 on sales( sale_dt, empno);
1) 결합 index. sale_dt between보다 in이 유리.
select sale_dt, empno, target
from sales
where sale_dt between '20201201' and '20201231'
and empno=1234;
-> in으로 변환하려면.. 아래꺼..
[
select level*10 from dual
connect by level < 11; -> 10 20 30 40 50 ??
select ascii('A') from dual;
select chr(70) from dual;
select chr(level + 64) from dual connect by level <27;
select chr(level + 64) || '01' from dual connect by level <27;
-> where code between 'A01' AND 'Z01'
and sal=2500 일때 in으로 작업하면 편하니까
위에 level 써가지고 in으로 하면 편하대.
ex)
select sale_dt, empno, target
from sales
where sale_dt between '20201201' and '20201231'
and empno=1234;
=====> 바꿔보자
select sale_dt, empno, target
from sales
where sale_dt in (select '202012' || lpad(level,2,'0') from dual connect by level < 32)
and empno=1234;
-------------------------------------------------------------------------------------
조인문장, where절 select문은 변환을 시킨다.
1) dept 부서중 급여 1000보다 많이받는 부서원이 있는 부서만 조회.
select distinct d.deptno, d.dname
from dept d, emp e
where d.deptno = e.deptno
and e.sal > 1000;
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 |00:00:00.01 | 14 | | | |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
| 1 | HASH UNIQUE | | 1 | 3 |00:00:00.01 | 14 | 1096K| 1096K| 716K (0)|
|* 2 | HASH JOIN | | 1 | 12 |00:00:00.01 | 14 | 1134K| 1134K| 721K (0)|
| 3 | TABLE ACCESS FULL| DEPT | 1 | 4 |00:00:00.01 | 7 | | | |
|* 4 | TABLE ACCESS FULL| EMP | 1 | 12 |00:00:00.01 | 7 | | | |
--------------------------------------------------------------------------------------------------------
결합이 많이져. 그래서 where 절의 select절 이용해서.
select d.deptno, d.dname
from dept d
where d.deptno in (select e.deptno from emp e where e.sal > 1000);
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 |00:00:00.01 | 15 | | | |
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN SEMI | | 1 | 3 |00:00:00.01 | 15 | 1134K| 1134K| 754K (0)|
| 2 | TABLE ACCESS FULL| DEPT | 1 | 4 |00:00:00.01 | 7 | | | |
|* 3 | TABLE ACCESS FULL| EMP | 1 | 12 |00:00:00.01 | 8 | | | |
-------------------------------------------------------------------------------------------------------
select d.deptno, d.dname
from dept d
where exists (select 1 from emp e where e.deptno = d.deptno and e.sal > 1000);
- 조인-> where절 select문 형태 (semi join수행가능)
조건 만족하는 자료 존재여부 확인.
ex) 고객 중 2021년 주문한 고객 명단 조회
- where절 서브쿼리문장 -> 조인문장으로 변경 DB에서 자동 수행. 사용자 변경 필요 x
- 조인문장 -> select절 서브쿼리는 자동변경하지 않고 그대로 냅둠. (상황에 따라 사용자 sql문 재작성)
- select절 서브쿼리 -> 조인문장형태 자동변경 안함.(상황에 따라 사용자 sql문 재작성)
- 실행계획 select 밑에 큰박스 하나면 select절 서브쿼리 없는거, 두개이상이면 select절 서브쿼리가 있는것.
-
select ename
, (select dname from dept where deptno = e.deptno) d1
, (select loc from dept where deptno = e.deptno) d2
from emp e;
----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 |00:00:00.01 | 8 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 3 | 3 |00:00:00.01 | 5 |
|* 2 | INDEX UNIQUE SCAN | PK_DEPT | 3 | 3 |00:00:00.01 | 2 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 3 | 3 |00:00:00.01 | 5 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 3 | 3 |00:00:00.01 | 2 |
| 5 | TABLE ACCESS FULL | EMP | 1 | 14 |00:00:00.01 | 8 |
----------------------------------------------------------------------------------------
5가 amin쿼리.
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
alter session set statistics_level = all;
1) select절 조인으로 변경. left outer join 필수.
select count(t1.c1), count(t1.c2), count(t1.c3),
count((select t2.c3 from sc_t2 t2 where t2.c1 = t1.c1)) as t2_c3,
count((select t3.c3 from sc_t3 t3 where t3.c1 = t1.c1)) as t3_c3
from sc_t1 t1
order by t1.c1, t1.c2;
->
select count(t1.c1), count(t1.c2), count(t1.c3), count(t2.c3), count(t3.c3)
from sc_t1 t1 left outer join sc_t2 t2 on t1.c1 = t2.c1
left outer join sc_t3 t3 on t1.c1 = t3.c1
order by t1.c1, t1.c2;
2) 조인문장으로 변경. select절 서브쿼리는 outer join . 뒤 테이블 기준.
select d.deptno, d.dname,
(select sum(sal) from emp where deptno = d.deptno) s1
from dept d;
->
select d.deptno, d.dname, sum(sal)
from dept d left outer join emp e on d.deptno = e.deptno
group by d.deptno, d.dname;
3)
select count(comm), count(mgr), count(*)
from emp;
4) nvl은 각각 다 확인하면서 null값을 확인해서 바꿔주기 때문에, 안해도됨 결과는 같음.
그룹함수는 null값을 자동으로 뺀다.
select ename, comm, comm+500, nvl(comm,0)+500
from emp;
select sum(comm), sum(nvl(comm,0))
from emp;
select avg(comm), avg(nvl(comm,0))
from emp;
5) rollup은 소계. 기준은 rollup 왼쪾꺼부터.
select deptno, sum(sal), count(*)
from emp
group by deptno;
select deptno, sum(sal), count(*)
from emp
group by ROLLUP(deptno);
6)
select deptno job, sum(sal), count(*)
from emp
group by ROLLUP(deptno, job);
select deptno job, sum(sal), count(*)
from emp
group by ROLLUP( job , deptno);
7)
select deptno job, sum(sal), count(*)
from emp
group by CUBE( job , deptno);
8) 그루핑 ㅇ소계를 지정.
select 지역 성별 연령대 count(*)인원
from 고객
group by GROUPING SETS( (지역,성별,연령대), (지역));
select deptno, job, count(*), sum(sal)
from emp
group by grouping sets((deptno, job), (deptno));
-----------------------------------------------------------------------
1) 각 mgr 사원이 담당하는 인원 조회
select mgr, count(*)
from emp
group by mgr;
2) 총인원도구해주세요
select mgr, count(*)
from emp
group by ROLLUP(mgr);
MGR COUNT(*)
---------- ----------
7566 2
7698 5
7782 1
7788 1
7839 3
7902 1
1
14
3) 소계에 mgr 총인원 찍어주고시퍼요
select NVL(mgr, '총인원'), count(*)
from emp
group by ROLLUP(mgr);
-> 에러. mgr 숫자타입이라서. 총인원을 숫자타입으로 변환하지 못해요
select NVL(to_char(mgr), '총인원') as mgr, count(*)
from emp
group by ROLLUP(mgr);
-> mgr없는 인원까지 총인원으로 나와버림
select mgr, count(*), grouping(mgr)
from emp
group by ROLLUP(mgr);
MGR COUNT(*) GROUPING(MGR)
---------- ---------- -------------
7566 2 0
7698 5 0
7782 1 0
7788 1 0
7839 3 0
7902 1 0
1 0
14 1
-> grouping 함수는 group by 수행한 절은 1. 아닌건 0으로 나옴
select case when grouping(mgr) = 1 then '총인원'
else to_char(mgr) end 관리자
, count(*) 인원
from emp
group by ROLLUP(mgr);
문제) 구분해준대. 똑같은 기준으로 여러번 사용되었으면
deptno가 같은기준으로 그루핑 여러번되었으니까.
select deptno, empno, ename, sum(sal), group_id()
from emp
group by GROUPING SETS( (deptno, empno, ename), (deptno), (deptno))
order by deptno, empno;
=>
select deptno
, empno
, case when group_id() = 1 then '평균'
else NVL(ename, '합계')
end ename
, case when group_id() = 1 then avg(sal)
else sum(sal)
end sal
, group_id()
from emp
group by GROUPING SETS( (deptno, empno, ename), (deptno), (deptno))
order by deptno, group_ID(), empno;
--------------------------------------------------------------------------------------------------
문제) 다음 결과를 B처럼 나타내기. B는 성능에 좋지 않음.
A) dl
select deptno, sum(sal) from emp group by ROLLUP(deptno);
DEPTNO SUM(SAL)
---------- ----------
10 8750
20 10875
30 9400
29025
B)
select
(select sum(sal) from emp where deptno=10) 부서10,
(select sum(sal) from emp where deptno=20) 부서20,
(select sum(sal) from emp where deptno=30) 부서30,
(select sum(sal) from emp) 전체
from dual;
부서10 부서20 부서30 전체
---------- ---------- ---------- ----------
8750 10875 9400 29025
답1)내가한건 피벗;;
select *
from ( select deptno, sal, grouping(sal) from emp group by ROLLUP(sal))
PIVOT (
sum(sal)
for deptno
in ('10', '20', '30', 'all)')
);
답2) 디코드이용;;;
select ename, deptno, sal, decode(deptno, 10, sal), decode(deptno, 20, sal), decode(deptno, 30, sal)
from emp;
-> 다음단게
select sum( decode(deptno, 10, sal) ) 부서10,
sum( decode(deptno, 20, sal) ) 부서20,
sum( decode(deptno, 30, sal) ) 부서30,
sum(sal) 전체
from emp;
뒤에 job으로 그룹바이하면표처럼.
잡으로 나눈거중에 10번부서인애들은 합이 몇 이렇게 나타냄.
case + group 함수로
select job
, sum( decode(deptno, 10, sal) ) 부서10,
sum( decode(deptno, 20, sal) ) 부서20,
sum( decode(deptno, 30, sal) ) 부서30,
sum(sal) 전체
from emp
group by job;
문제2)
각 부서 입사년도별 입사인원 조회
부서 입사1931 입사1980
10 ?? ??
20 ?? ??
30 ?? ??
----->
select deptno
, count( decode(to_char(hiredate,'yyyy'), '1981', empno) ) 입사1981
, count( decode(to_char(hiredate,'yyyy'), '1980', empno) ) 입사1980
from emp
group by deptno;
문제3)
select ename from emp;
-> 한줄에 사원이름 2명씩 표시하기.
select rownum, ename from emp;
select round(rownum/2)
, max(decode( mod(rownum,2), 1, ename)) 이름1
, max(decode( mod(rownum,2), 0, ename)) 이름2
from emp
group by round(rownum/2)
order by round(rownum/2);
------------------------------------------------------------------------------------
1)
select ename, sal, SUM(sal) from emp;
->
select ename, sal, SUM(sal) from emp group by ename, sal;
2)
select ename, sal, ( select sum(sal) from emp) 합계
from emp;
select ename, sal, (select count(*) from emp where sal > 4000) 인원
from emp;
select ename, sal, (select count(*) from emp where sal > e.sal) 인원
from emp e;
-> 순위가 되네; 나보다 급여 많이받는사람 몇명? -> 이게 순위
select ename, sal, (select count(*) from emp where sal > e.sal)+1 인원
from emp e
order by 인원;
윈도우함수 :
select 문 수행 후 결과ROW를 대상으로 함수 적용함
계산은 그룹함수처럼 묶어서, 표시는 row마다.
함수이름(인자) OVER ( partition by ~ order by ~ ROWS ~)
인자의 개수는 함수에따라 다름.
over은 필수.
partition by는 파티션기준마다 함수 초기화. ex) 서울 대전 경기 따로. 10번부서1등누구 20번1등누구 등 안쓰면 통으로 처리해 라는 의미
order by 는 분석함수적용순서. 일부함수(rank, dense_rank, row_number, lead, lag ntile)에서는 필수.
rows 계산범위 제한. 단독사용x 사용시 order by 지정해야함
위는 순서대로 써야함.
1.
select ename, deptno, sal
, RANK() OVER( order by sal desc) 등수
from emp;
각 부서별로 partition by 이용. 각 부서별 1등은?
select ename, deptno, sal
, RANK() OVER( order by sal desc) 등수
, RANK() OVER( partition by deptno order by sal desc) 등수2
from emp;
2. rank, dense_rank, row_number 비교
select ename, sal
, RANK() OVER( order by sal desc) r1
, DENSE_RANK() OVER( order by sal desc) r2
, ROW_NUMBER() OVER( order by sal desc) r3
from emp
row_number은 전부다 다른 번호.
rank는 2등이 2명이면 그다음은 4등
dense_rank는 2등이2명이어도 그다음음3등.
select ename,deptno, sal
, SUM(sal) OVER() 전체합계
, SUM(sal) OVER( partition by deptno ) 부서합계
from emp;
3. rows 동작
select ename, deptno, sal
, SUM(sal) OVER( ROWS between 1 preceding and 1 following) 결과
from emp;
-> ROWS는 ORDER BY와 하몎 써야함. 오류뜸.
select ename, deptno, sal
, SUM(sal) OVER( ORDER BY ename ROWS between 1 preceding and 1 following) 결과
from emp;
ENAME DEPTNO SAL 결과
-------------------- ---------- ---------- ----------
ADAMS 20 1100 2700
ALLEN 30 1600 5550
BLAKE 30 2850 6900
CLARK 10 2450 8300
FORD 20 3000 6400
JAMES 30 950 6925
JONES 20 2975 8925
KING 10 5000 9225
MARTIN 30 1250 7550
MILLER 10 1300 5550
SCOTT 20 3000 5100
SMITH 20 800 5300
TURNER 30 1500 3550
WARD 30 1250 2750
-> 1preceding 1following 앞에한줄 뒤에한줄 이라는 말.
select ename, deptno, sal
, SUM(sal) OVER( ORDER BY ename ROWS between 1 preceding and current row) 결과
from emp;
ENAME DEPTNO SAL 결과
-------------------- ---------- ---------- ----------
ADAMS 20 1100 1100
ALLEN 30 1600 2700
BLAKE 30 2850 4450
CLARK 10 2450 5300
FORD 20 3000 5450
JAMES 30 950 3950
JONES 20 2975 3925
KING 10 5000 7975
MARTIN 30 1250 6250
MILLER 10 1300 2550
SCOTT 20 3000 4300
SMITH 20 800 3800
TURNER 30 1500 2300
WARD 30 1250 2750
-> 앞에거 하나만 더하세요. 임
4. 앞에날짜 모두다 더하세요하면 다음과같이. 일자별누적합계같은건 다음과 같이.
select ename, deptno, sal
, SUM(sal) OVER( ORDER BY ename ROWS UNBOUNDED PRECEDING) 결과
from emp;
5. 부서별로 따로따로 누적해주세요..
select ename, deptno, sal
, SUM(sal) OVER( PARTITION BY deptno ORDER BY ename ROWS UNBOUNDED PRECEDING) 결과
from emp;
5. 구분. 고객을 3분류로 나눠보죠 나눴을때 남으면 1그룹에 더해줘요.
select ename, sal, ntile(3) OVER( order by sal)
from emp;
ENAME SAL NTILE(3)OVER(ORDERBYSAL)
-------------------- ---------- ------------------------
SMITH 800 1
JAMES 950 1
ADAMS 1100 1
WARD 1250 1
MARTIN 1250 1
MILLER 1300 2
TURNER 1500 2
ALLEN 1600 2
CLARK 2450 2
BLAKE 2850 2
JONES 2975 3
SCOTT 3000 3
FORD 3000 3
KING 5000 3
6. 현재 ROW 기준 앞줄(LAG / 뒷줄(LEAD) 컬럼 가져오기
select ename, sal, LEAD( ename) OVER (order by sal) 뒷줄이름
from emp;
ENAME SAL 뒷줄이름
-------------------- ---------- --------------------
SMITH 800 JAMES
JAMES 950 ADAMS
ADAMS 1100 WARD
WARD 1250 MARTIN
MARTIN 1250 MILLER
MILLER 1300 TURNER
TURNER 1500 ALLEN
ALLEN 1600 CLARK
CLARK 2450 BLAKE
BLAKE 2850 JONES
JONES 2975 SCOTT
SCOTT 3000 FORD
FORD 3000 KING
KING 5000
select ename, sal, LAG( ename) OVER (order by sal) 앞줄이름
from emp;
ENAME SAL 앞줄이름
-------------------- ---------- --------------------
SMITH 800
JAMES 950 SMITH
ADAMS 1100 JAMES
WARD 1250 ADAMS
MARTIN 1250 WARD
MILLER 1300 MARTIN
TURNER 1500 MILLER
ALLEN 1600 TURNER
CLARK 2450 ALLEN
BLAKE 2850 CLARK
JONES 2975 BLAKE
SCOTT 3000 JONES
FORD 3000 SCOTT
KING 5000 FORD
문제1) emp이용 사원이름, 급여, 부서번호, 본인부서 최대급여와 차액조회.
결과 차액 많은 사원부터나오도록 정렬
내가한답)
select ename, sal, deptno, maxsal, maxsal-sal as dif
from (
select ename, sal, deptno, MAX(sal) OVER( partition by deptno) maxsal
from emp
)
order by dif desc;
강사답)
select ename, sal, deptno, MAX(sal) OVER( partition by deptno) - sal 차액
from emp
order by 차액 desc;
문제2) 윈도우함수는 select, orderby절에서만 사용 가능 .
emp 각 부서에서 최소급여받는 사원의 이름, 급여, 부서번호 조회.
내가한답)
select ename, sal, deptno
from (
select ename, sal, deptno, MIN(sal) OVER( partition by deptno) as minsal
from emp
)
where sal = minsal;
강사답)
select *
from (
select ename, sal, deptno, rank() OVER( partition by deptno order by sal) rk
from emp
)
where rk = 1;
문제3)
select ename, hiredate --본인다음입사한 사원과 입사간격 조회
from emp
where deptno = 30
order by hiredate;
ex) 대출상환정보 테이블
계좌번호 상환일자 금액
100 2023-04-05 100만
100 2023-04-15 200만
100 2023-04-19 100만
내가한답)
select ename, hiredate, hiredate-leadate as difdate
from (
select ename, hiredate, lead(hiredate) OVER( order by hiredate) leadate
from emp
);
강사답)
select ename, hiredate
, lead(hiredate) OVER( order by hiredate) 후임입사일자
, lead(hiredate) OVER( order by hiredate) - hiredate 입사간격
from emp
--------------------------------------------------------------------------
SQL> select * from 분류;
메뉴번호 메뉴명 상위메뉴
---------- ------------------------ ----------
1 도서
2 국내도서 1
3 해외도서 1
4 교육 2
5 자격증 2
6 문학 2
7 의학 3
8 컴퓨터 3
9 학습서 4
10 IT 5
11 부동산 5
12 소설 6
13 수필 6
14 장편소설 12
15 단편소설 12
1) 메뉴번호1번부터 시작 (START WITH) , 메뉴번호 먼저(prior)보고 상위메뉴랑 비교연결(CONNECT BY).
select level, LPAD('*', level*4, '*') || 메뉴명 as 결과
from 분류
START WITH 메뉴번호 = 1
CONNECT BY prior 메뉴번호 = 상위메뉴;
LEVEL 결과
---------- ----------------------------------------
1 ****도서
2 ********국내도서
3 ************교육
4 ****************학습서
3 ************자격증
4 ****************IT
4 ****************부동산
3 ************문학
4 ****************소설
5 ********************장편소설
5 ********************단편소설
4 ****************수필
2 ********해외도서
3 ************의학
3 ************컴퓨터
2)
select level, sys_connect_by_path(메뉴명,'/') as 결과
from 분류
START WITH 메뉴번호 = 1
CONNECT BY prior 메뉴번호 = 상위메뉴;
LEVEL 결과
---------- ----------------------------------------
1 /도서
2 /도서/국내도서
3 /도서/국내도서/교육
4 /도서/국내도서/교육/학습서
3 /도서/국내도서/자격증
4 /도서/국내도서/자격증/IT
4 /도서/국내도서/자격증/부동산
3 /도서/국내도서/문학
4 /도서/국내도서/문학/소설
5 /도서/국내도서/문학/소설/장편소설
5 /도서/국내도서/문학/소설/단편소설
4 /도서/국내도서/문학/수필
2 /도서/해외도서
3 /도서/해외도서/의학
3 /도서/해외도서/컴퓨터
3) 전개방향 변겨해보기
1. 위에서 아래로 오는 방향.
select level, LPAD('*', level*4, '*') || 메뉴명 as 결과
from 분류
START WITH 메뉴번호 = 4
CONNECT BY prior 메뉴번호 = 상위메뉴;
pk
LEVEL 결과
---------- ----------------------------------------
1 ****교육
2 ********학습서
2. 밑에서 위로 올라가는 방향
select level, LPAD('*', level*4, '*') || 메뉴명 as 결과
from 분류
START WITH 메뉴번호 = 4
CONNECT BY 메뉴번호 = prior 상위메뉴;
fk
LEVEL 결과
---------- ----------------------------------------
1 ****교육
2 ********국내도서
3 ************도서
4) 조건지정위치에 따라 결과 달라짐.
1. where 절 조건 사용한 경우.
2레벨있던 국내조직은 날라가고, 하위 조직은 살아남음
select level, sys_connect_by_path(메뉴명,'/') as 결과
from 분류
where 메뉴명 <> '국내도서'
START WITH 메뉴번호 = 1
CONNECT BY prior 메뉴번호 = 상위메뉴;
LEVEL 결과
---------- ----------------------------------------
1 /도서
3 /도서/국내도서/교육
4 /도서/국내도서/교육/학습서
3 /도서/국내도서/자격증
4 /도서/국내도서/자격증/IT
4 /도서/국내도서/자격증/부동산
3 /도서/국내도서/문학
4 /도서/국내도서/문학/소설
5 /도서/국내도서/문학/소설/장편소설
5 /도서/국내도서/문학/소설/단편소설
4 /도서/국내도서/문학/수필
2 /도서/해외도서
3 /도서/해외도서/의학
3 /도서/해외도서/컴퓨터
2. CONNECT BY절 조건 지정한 경우
가지제거. 하위도 날라감. 다같이날라감.
국내도서 가지 날라가고 해외도서만 남음.
select level, sys_connect_by_path(메뉴명,'/') as 결과
from 분류
START WITH 메뉴번호 = 1
CONNECT BY prior 메뉴번호 = 상위메뉴
and 메뉴명 <> '국내도서';
LEVEL 결과
---------- ----------------------------------------
1 /도서
2 /도서/해외도서
3 /도서/해외도서/의학
3 /도서/해외도서/컴퓨터
----------------------------------------------------------------------------
1. 7,8월 인원 빠져있는데 그냥 다 나오게 하기.
select to_char(hiredate, 'mm') 입사월, count(*) 인원
from emp
group by to_char(hiredate,'mm');
입사 DLSDNJS
---- ----------
04 1
09 2
12 3
11 1
01 1
02 2
05 2
03 1
06 1
select LPAD ( level, 2, 0) mon
from dual
connect by level < 13;
이용
select b.mon, nvl(a.인원,0)
from (
select LPAD ( level, 2, 0) mon
from dual
connect by level < 13 ) B LEFT JOIN
( select to_char(hiredate, 'mm') 입사월, count(*) 인원
from emp
group by to_char(hiredate, 'mm') ) A
ON (a.입사월 = b.mon)
order by b.mon;
MON NVL(A.인원,0)
---------------- -------------
01 1
02 2
03 1
04 1
05 2
06 1
07 0
08 0
09 2
10 0
11 1
12 3
--------------------------------------------------------------------------------------
문제1)
sales 자료 중 salecnt값이 가장 큰 5개의 자료만 sale_dt, empno, salecnt 조회하기
sale
1) X
select ename, sal
from (select ename, sal from emp order by sal desc, empno ) A
where rownum >= 4 and rownum < 7;
2)
select rownum no, ename, sal
from (select ename, sal from emp order by sal desc, empno) A;
3)
select *
from (select rownum no, ename, sal
from (select ename, sal from emp order by sal desc, empno) A) B
where no >= 4 and no <= 6;
답)
select sale_dt, empno, salecnt
from ( select sale_dt, empno, salecnt
from sales
order by salecnt DESC, empno) A
where rownum < 6;
문제2)
5개 자료를 1page에 표시한다고 했을 떄
2번째 페이지에 해당되는 자료를 조회.
select *
from ( select rownum no, sale_dt, empno, salecnt
from ( select sale_dt, empno, salecnt
from sales
order by salecnt DESC, empno) A
) B
where b.no >= 6 and b.no <= 10;
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Tim
e |
--------------------------------------------------------------------------------
-------
| 0 | SELECT STATEMENT | | 957K| 52M| | 7368 (1)| 00:
01:29 |
|* 1 | VIEW | | 957K| 52M| | 7368 (1)| 00:
01:29 |
| 2 | COUNT | | | | | |
|
| 3 | VIEW | | 957K| 40M| | 7368 (1)| 00:
01:29 |
| 4 | SORT ORDER BY | | 957K| 16M| 25M| 7368 (1)| 00:
01:29 |
| 5 | TABLE ACCESS FULL| SALES | 957K| 16M| | 1918 (1)| 00:
00:24 |
--------------------------------------------------------------------------------
-> 작업량 많으니 where절로 제한
select *
from ( select rownum no, sale_dt, empno, salecnt
from ( select sale_dt, empno, salecnt
from sales
order by salecnt DESC, empno) A
where rownum < 11
) B
where b.no >= 6 and b.no <= 10;
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
----------
| 0 | SELECT STATEMENT | | 10 | 570 | | 7368 (1)|
00:01:29 |
|* 1 | VIEW | | 10 | 570 | | 7368 (1)|
00:01:29 |
|* 2 | COUNT STOPKEY | | | | | |
|
| 3 | VIEW | | 957K| 40M| | 7368 (1)|
00:01:29 |
|* 4 | SORT ORDER BY STOPKEY| | 957K| 16M| 25M| 7368 (1)|
00:01:29 |
| 5 | TABLE ACCESS FULL | SALES | 957K| 16M| | 1918 (1)|
00:00:24 |
--------------------------------------------------------------------------------
sort order by VS sort order by stopkey
rownum으로 결과제한?
SAP CO