■SUBQUERY 활용
■ subquery의 종류 3가지
1. single row subquery : 서브쿼리에서 메인쿼리로
하나의 값이 리턴 되는 경우
2. multiple row subquery: 서브쿼리에서 메인쿼리로 여러개의 값이 리턴 되는 경우
3. multiple column subquery: 서브쿼리에서 메인쿼리로 여러개의 컬럼값들이 리턴 되는 경우
-SINGLE ROW SUBQUERY
문제238.
SMITH 보다 높은 월급을 받는 사원들의
이름과 월급을 출력하는데
월급이 높은 사원부터 출력하시오
select ename, sal from emp
where sal>(select sal from emp
where ename='SMITH' )
order by sal desc;
문제239.
KING 에게 보고 하는 사원들의 이름을
출력하시오
select ename from emp
where mgr=(select EMPNO
from emp where ename='KING');
**EMP 테이블에서 유일하게 밸류가 일치하는 컬럼
문제240.
DALLAS 에 있는 부서번호가 무엇인지
출력하시오(같은 테이블임 dept)
SELECT deptno from dept
where loc='DALLAS';
문제241.
DALLAS 에 있는 부서번호에서
근무하는 사원들의
이름과 월급을 출력하시오
조인으로 한거
select E.ename, E.sal
from emp E, dept D
where E.deptno = D.deptno
and
D.loc = 'DALLAS';
서브쿼리로 한거
select ename, sal
from emp
where deptno = (select deptno
from dept
where loc= 'DALLAS');
-MULTIPLE ROW SUBQUERY
단일행 연산자 외에 다행 연산자 값을 출력하기 위해 사용하는 쿼리로서
IN, ANY, ALL 을 활용한다.
문제242.
직업이 SALESMAN 인 사원들과
월급이 같은 사원들의
이름과 월급을 출력하시오
select ename, sal
from emp
where job='SALESMAN'
왜 어렵게 뽑지?
select ename, sal from emp
where SAL=(SELECT sal from emp
where job= 'SALESMAN');
에러뜸
직업이 SALESMAN 인 사람의 월급이
4개나 되서 에러남.
단일행 = 로는 안됨.
답
select ename, sal from emp
where SAL IN(SELECT sal from emp
where job= 'SALESMAN');
여러개 출력하기 위해
IN 쓰면 된당.
이게
multiple row subquery 다.
문제243.
월급이 1000 에서 2000 사이인 사원들과
같은 직업을 갖는 사원들의
이름과 직업과 월급을
출력하시오
select ename, job, sal from emp
where job in(select job from emp
where
sal between 1000 and 2000) ;
■subquery 의 종류 3가지의 사용 연산자
1. single row subquery:
= ,> ,< , >=, <=, !=, ^=, <>
2. multiple row subquery:
in, not in, >all, <all, >any, <any
3. multiple colmn subquery:
in, not in
■
문제244.
직업이 SALESMAN 인 사원들과
월급이 같지 않은 사원들의
이름과 월급을 출력하시오
select ename, sal from emp
where sal not in
(select sal from emp
where job='SALESMAN');
**서브가 여러개값이니까 not in 으로.
Not in 쓸 때는 nvl 잊지 마시오!!!!!
문제245.
관리자인 사원들의 이름을 출력하시오
(자기 밑에 직속부하가 한명이라도 있는
사원들)
select ename from emp
where empno in
( select mgr from emp);
------------
select 관리자.ename from emp 관리자
where 관리자.empno in (select 사원.mgr from emp 사원
where 관리자.empno = 사원.mgr);
****empno 랑 mgr 이 호환되지 않으면
두번째 것 처럼 서브로 해야 한다
문제246.
관리자가 아닌 사원들의 이름을 출력하시오
오답
select ename
from emp
where ename not in( select ename from emp
where empno = mgr);
>>>왜 이것이 오답일까?
select ename
from emp
where empno = mgr
Empno = mgr 로 하면
한 행에서 사원번호랑 매너저넘버가 같은
사원의 이름을 출력하라는 뜻이 된다.
사원번호와 mgr 번호가 일치하는 사람은 없다
select ename from emp
where empno in (select mgr from emp);
이게 관리자인 사원이름을 출력하는 쿼리다.
학생값(설명듣기전)
SELECT ename
FROM emp
WHERE ename NOT IN
(SELECT ename
FROM EMP
WHERE empno IN
(SELECT mgr FROM emp));
***윗 문제 답이 관리자인 사원이름 이니까
그걸 그대로 끌고 와서
not in 으로 엮어 버림;;;;;;;
****이름에는 null 값이 없으니까 에러안뜬거임
오답
select ename from emp
where empno not in
( select mgr from emp);
왜?
null 값이 있어서 (=KING)
null 때문에 값이 없다고 나옴.
※설명:
in 은 '=any'(같다.어떤것과) 라는 뜻이다
any 는 or 연산자
예:
select ename from emp
where empno =any(select mgr from emp);
| |
wehre empno=7788 or empno=7566 or empno=null
true true null
**true or true = true
false = false
null
**true and true = true
true and fals = fals
true and null = null
select ename from emp
where empno not in
( select mgr from emp);
| |
where empno != 7566 and empno !=7839 and
empno != null ;
※설명:
not in = '!=all'
all 이라서 and 연산자
그래서 null값을 가진 KING 때문에
true and null 이 되버림. 그래서 에러.
null 처리가 필요하다 !!!!!!!!!!!!!!!!!!!!nvl
그래서~~~
select ename from emp
where empno not in
( select nvl(mgr, 0) from emp);
*****************************
※서브쿼리문에서 not in 을 사용할 때는
nvl 처리를 반 드 시 해야 한다.
******************************
문제247.
커미션이 null 인 사원들과 같은 사원들의
이름과 직업을 출력하시오
select ename, job from emp
where comm is null;
select ename, job from emp
where job in(select job from emp
where comm is null);
문제248.
직업이 ANALYST 인 사원들의 커미션과
같은 커미션을 받는 사원들의
이름과 직업과 커미션을 출력하시오
(where, select 의 이해가 필요한 문제였다;;;;)
오답
select ename, job, comm
from emp
where to_char(nvl(comm, '모름')) in(select nvl(to_char(comm),'모름') from emp)
and
where job= 'ANALYST');
>>>>>>>>>>>커미션은 숫자값이다.
근데 애널리스트의 널값만 문자값으로 지정하니까
애널리스트랑 같은 널값을 받는 직업군은
출력이 안된다.
숫자는 숫자로 하는게 좋다.
안그럼 못쓰는 자료가 됨.
select ename, job, comm
from emp
where nvl(comm, -1) in
(select nvl(comm,-1) from emp)
and
job= 'ANALYST';
화면 캡처: 2018-10-23 오후 12:59
*******왜 2개가 나오냐면
답
select ename, job, comm
from emp
where nvl(comm, -1) in
(select nvl(comm, -1) from emp
where job='ANALYST');
***
널값 -1 은 서브쿼리에서 지정을 위한 값.
(널 값 카운트 하려고)
**nvl(comm, -1) 이
select 절에 있으면
Null 값이 -1 로 출력 될 것이다.
근데 이 문제는
Where 절에 nvl(comm, -1) 이라고 했고
수행을 위한 조건으로 인식될 뿐,
출력될 때 표현되지 않는다.
(where 절 이니까 !!!!!!!!!!)
Null 을 카운트 시키기 위한 조건일 뿐이다.
그래서 메인쿼리에서 뽑을 때
영향 안 받고 그대로 null 로 뽑힘.
0은 안됨. 널값이 0인 사원도 있으니까.
문제249.
직업이 SALESMAN 인 사원들의 월급중에서
가장 많은 월급보다
더 많은 월급을 받는 사원들의
이름과 월급을 출력하시오
select ename, sal from emp
where sal > (select max(sal)
from emp where job='SALESMAN');
※설명
만약 max(sal) 이 아니라
그냥 sal 는
단일연산자 '> ' 가 안먹힌다.
(세일즈맨 월급이 여러개임)
그냥 sal을 먹히게 하려면
'>all' 연산자 쓰면 됨(여러행 연산자)
'>all' 은
ALL 이하 모든값보다 큰 거를 찾으라는 것.
select ename, sal from emp
where sal > (select max(sal)
from emp where job='SALESMAN');
▪'>all' 은 메인쿼리부터 수행해서
하나씩 다 비교하는 연산자
▪ '>' 은 서브쿼리부터 수행해서
한번에 척 비교연산 하는거다.
▪ 그래서 '>all' 을 잘 안 쓴다.(느림)
문제250.
부서번호가 30번인 사원들 중에서의
가장 작은 월급보다
더 많은 월급을 받는 사원들의
이름과 월급을 출력하시오
select ename, sal , deptno
from emp
where sal >(
select min(sal) from emp
where deptno=30);
또는
select ename, sal , deptno
from emp
where sal >any(
select sal from emp
where deptno=30);
'>any' 는
sal 전체값인 950, 1250, 1250, 1500, 1600, 2850
중에서
이 들 중에 어느것 보다 크기만 하면 된다.
(즉, 가장작은 값보다 크면 된다)
라는 명령어 이므로
알아서 최소값을 뽑는다.
*서브쿼리에서 뽑아주는게 sal 인데
조건이 부서번호 30 이라해도
결국 sal 이 나온다.
문제251.(점심시간 문제)
DALLAS 에서 월급이 2등인 사원과 같은
월급을 받는 사원의
이름과 월급을 출력하시오
답안나옴 왜?
select E.ename, E.sal
from emp E, dept D
where sal in
(
select E.ename, E.sal,
dense_rank()over(order by E.sal desc) as "순위"
from emp E, dept D
where D.loc = 'DALLAS'
)
and
where 순위 ='2';
***메인where 절의 sal in 에 해당하는
서브쿼리 값이
이름까지 들어가서 안됨.
이름이랑 이것저것 다 넣으려면
from 절에서 서브열어야함.
**참고로
순위는 함수값이고
해당 숫자는 문자임
-------------------------------------
오답2
select *
from
(
select e.ename, e.sal,
dense_rank()over(order by sal) as 순위
from emp e, dept d
where e.deptno = d.deptno)
where d.loc='DALLAS'
and 순위 = 2;
서브쿼리 안에 내용을 그려보면
이름 월급 순위
KING 5000 1
. . .
. . .
지역이 없는 테이블에서
메인쿼리에서 DALLAS 뽑으라고 하니까
못 뽑는거다.
---------------------------------------------
답
select ENAME, SAL
from
(
select E.ename, E.sal,
dense_rank()over(partition by D.loc order by E.sal desc) as "순위"
from emp E, dept D
where E.deptno = D.deptno
and
D.loc = 'DALLAS'
)
where 순위 = 2;
***********같은 월급을 받는 직원을
못 뽑으니 다시 수정********
******메인쿼리 ENAME, SAL 을
E.ename, E.sal 로 하면 안되는 이유가
서브쿼리의 E.ename 을 뽑으라는 건지
서브쿼리의 from emp E 에서 갖고 오라는건지
알 수 없기 때문이다.
*****메인쿼리에 그냥 * 때려도 됨
----------------------------------------------------
7. select ENAME, SAL
6. from emp
5. where sal=(
select sal from
(
3. select E.ename, E.sal,
dense_rank()over(partition by D.loc order by E.sal desc) as "순위"
1. from emp E, dept D
2. where E.deptno = D.deptno and D.loc = 'DALLAS'
)
4. where 순위 = 2
);
해설:
1.emp, dept 테이블 에서
2.두 테이블의 넘버가 같고, 지역이 달라스인
3.이름, 월급, 지역별 월급순위를
출력한 테이블에서
4.순위=2 인
5.월급을 지정하여
6. 위 지정값을 만족하는 것을 emp테이블에서
7.이름, 월급을 출력하라
■multiple column subquery
WHERE 절에 2개 이상의 컬럼을 입력한다.
그 값을 출력하는데
NON PAIR WISE,
PAIR WISE
2가지 방법을 활용한다.
문제252.
직업이 SALESMAN 인 사원들과 월급이 같고
커미션도 같은 사원들의
이름과 월급과 커미션을 출력하시오
select ename, sal, comm
from emp
where (sal, comm) in(select sal, comm
from emp
where job = 'SALESMAN');
------------------------------
-NON PAIR WISE
WHERE 절의 서브쿼리에 2개의 컬럼을
연결연산자로 각각 연결 해주는 방법이다.
이 방법의 단점은 각 컬럼을 각각 출력하기 때문에
2개 컬럼 모두 공통점을 갖지 않은 값도 출력한다.
답 1. non pair wise 방식
select ename, sal, comm
from emp
where sal in (select sal
from emp
where job = 'SALESMAN')
and nvl(comm, -1) in(select nvl(comm, -1)
from emp
where job='SALESMAN');
****오류가 발생하는 이유*****
우리가 원하는 건 세일즈맨의 월급과, 커미션을 동시에 만족하는 사원을 구하는 것이다.
근데
non pair 로 하면 월급이 1250,1600,1500,1250 해당되는 사람 다 불러오고
커미션이 1400, 300, 0, 500 에 해당되는 사람 다 불러오게 된다.
---------------------------------------
-PAIR WISE
WHERE 절에 2개의 컬럼을 서브쿼리 할 때
괄호로 묶어서 한 번에 처리하는 방법이다.
이 방법은 2 컬럼의 공통값을 출력하고자 할 때
유용하다.
답2. pair wise 방식
select ename, sal, comm
from emp
where (sal, nvl(comm,-1)) in
(select sal, nvl(comm, -1)
from emp
where job='SALESMAN');
값은 똑같음. 순서만 다름.
데이터 처리
non pair wise 가 더 많이 검색됨
세일즈맨은 이렇게 4명이 있다.
월급 커미션
1250 500
1250 1400
1600 300
1500 0
이걸 non pair wise 로 하면
1250 1400
1250 300
1250 0
.
.
.
이런식으로 non pair wise 방식은
같은 값이 있으면
세일즈맨이 아니더라도
다 나온다.
만약
update emp
set sal = 1250,
comm = 300
where ename = 'KING';
commit;
이렇게 고치고
위 2개를 다시 출력하면
non pair wise
select ename, sal, comm , job
from emp
where sal in (select sal
from emp
where job = 'SALESMAN')
and nvl(comm, -1) in(select nvl(comm, -1)
from emp
where job='SALESMAN');
pair wise
select ename, sal, comm, job
from emp
where (sal, nvl(comm,-1)) in
(select sal, nvl(comm, -1)
from emp
where job='SALESMAN');
위와 같이 non pair wise 는
세일즈맨과 같은 값을 가지는
킹도 뽑아낸다.
문제253.
통계학과인 학생들과 나이가 같고
통신사가 같은 학생들의
이름과 나이와 전공과 통신사를
출력하시오
pair wise 방법
select ename, age, major, lower(telecom)
from emp2
where(age, lower(telecom)) in
(select age, lower(telecom)
from emp2
where major like '%통계%');
non pair wise 방법
select ename, age, major, lower(telecom)
from emp2
where age in (select age
from emp2
where major like '%통계%')
and lower(telecom) in (select lower(telecom)
from emp2
where major like '%통계%');
■ subquery의 종류 3가지
1. single row subquery :
서브쿼리에서 메인쿼리로
하나의 값이 리턴 되는 경우
2. multiple row subquery:
서브쿼리에서 메인쿼리로 여러개의 값이 리턴 되는 경우
3. multiple column subquery:
서브쿼리에서 메인쿼리로 여러개의 컬럼값들이 리턴 되는 경우
select 문에서 subquery 를 쓸 수 있는 절
select -----subquery 가능(scalar subquery)
from -----subquery 가능(in line view)
where ----subquery 가능
group by------- X
having ----subquery 가능
order by --subquery 가능(scalar subquery)
group by 만 빼고 subquery 가능
문제254.
사원 테이블의 토탈월급을 출력하시오
select sum(sal) from emp;
문제255. 이름, 월급, 사원 테이블의 토탈월급을
출력하라
(분석함수 이용하지 마시오)
튜닝전 : select 의 서브쿼리!!!!!!!!!!!
select ename, sal, (select sum(sal) from emp)
as "토탈월급"
from emp;
튜닝후
select ename, sal, sum(sal)over()토탈월급
from emp;
문제256.
이름, 월급, 사원 테이블 전체의 토탈월급,
사원 테이블 전체의 최대월급,
사원 테이블 전체의 최소월급,
사원 테이블 전체의 평균월급을
출력하시오
튜닝전
select ename, sal,
(select sum(sal) from emp)토탈,
(select max(sal) from emp) 최대,
(select min(sal) from emp) 최소,
(select avg(sal) from emp) 평균
from EMP;
튜닝후
select ename, sal, sum(sal)over()토탈월급,
max(sal)over()최대월급,
min(sal)over()최소월급,
avg(sal)over()평균월급
from emp;
**실행통계 및 자동추적으로
튜닝후 값이 더 작아진걸 확인할 수 있다
문제257.
부서번호, 이름, 월급, 자기가 속한
부서번호의 토탈월급을 출력하시오
(튜닝전이 중요한 문제)
튜닝전
select deptno, ename, sal,
(select sum(e2.sal)
from emp e2
where e2.deptno=e1.deptno)부서토탈
from emp e1;
*********그룹으로 묶는 대신에
where 절을 이용하여 부서번호로 묶어준다.
**consitent gets 53개
튜닝후
select deptno, ename, sal,
sum(sal) over (partition by deptno) 부서토탈
from emp;
**consitent gets 41개
문제258.
사원번호, 이름, 월급, 월급의 누적치를
출력하시오
( 함수 안쓰고 하시오)
***inline view 안 될거다.
튜닝전:(스칼라 서브쿼리문으로 수행)
SELECT empno, ename, sal,
( SELECT SUM(sal)
FROM EMP
WHERE empno
BETWEEN (select MIN(empno)
FROM EMP )
AND e.empno ) 누계
FROM EMP e
ORDER BY empno;
튜닝후:
select empno, ename, sal,
sum(sal) over (order by empno rows
between unbounded preceding
and
current row)누적치
from emp;
---------------------------------------------
****위에 튜닝전 한 친구가 똑똑한게
between 으로 group 에러 안생긴다는걸
알고 했다는게 똑똑한거임.
문제259.
부서번호, 이름, 월급, 순위를 출력하는데
순위가
부서번호별로 각각 월급이 높은 사원에 대한
순위임?
(아직도 랭크가 단일행인지 복문행인지
감을 못잡으면 못 품)
select deptno, ename, sal,
(dense_rank()over(partition by deptno
order by sal desc)) 순위
from emp;
문제260.
위의 결과를 다시 출력하는데
각 부서번호별로
순위가 1위인 사원들만 출력하시오
select *
from
(select
deptno, ename, sal,
(dense_rank()over(partition by deptno
order by sal desc)) 순위
from emp
)
where 순위 = '1';
문제261.
부서번호, 이름, 월급, 자기가 속한 부서번호의
평균월급을 출력하시오
select e1.deptno, e1.ename, e1.sal,
(select round(avg(e2.sal)) from emp e2
where e2.deptno = e1.deptno)평균월급
from emp e1;
선생님답>>> 튜닝한거임
select deptno, ename, sal,
round( avg(sal) over(partition by deptno))부서평균
from emp;
문제262.
위의 결과를 다시 출력하는데
자기의 월급이 자기 부서의 평균월급보다
더 큰 사원들만 출력하시오
select *
from
(select
deptno, ename, sal,
round(avg(sal) over(partition by deptno))부서평균
from emp
)
where sal > 부서평균;
--------
****지금 내가 헷갈리는게
서브쿼리의 별칭지정.
-------
소진답
SELECT *
FROM (
SELECT deptno , ename , sal ,
(SELECT ROUND(AVG(sal))
FROM emp e2
WHERE e2.deptno = e1.deptno) 평균
FROM emp e1
)
WHERE sal > 평균 ;
****메인쿼리에 e1.depno 안해도 되는이유가
메인쿼리 from 절에 테이블 한개잖아.
원래 별칭써서 나누는건
테이블 조인했을 때.
*****돌아가는 순서
FROM (
FROM emp e1
FROM emp e2
--------
오답 왜?
select e1.deptno, e1.ename, e1.sal,
(select round(avg(sal))
from emp e2
where e1.deptno = e2.deptno)평균월급
from emp e1
where e1.sal > 평균월급;
*******from 에서 서브쿼리 안넣고
메인 select 에다가 서브쿼리 넣은걸로
where 값 뽑으려니까 답 안나옴
문제263.
261.부서번호, 이름, 월급, 자기가 속한 부서번호의
평균월급을 출력하시오
위의 결과를 다시 출력하는데
자기의 월급이 자기 부서의 평균월급보다
더 큰 사원들만 출력하시오
위의 결과를 분석함수를 이용하지 않고
수행하시오
답 소현이가 푼건데
테이블 조인 시킨거. 기발해서 봐둘 것
select e1.deptno, e1.ename, e1.sal, e2.부서평균
from (
select deptno, round(avg(sal)) 부서평균
from emp
group by deptno
)e2, emp e1
where e1.deptno = e2.deptno
and e1.sal > e2.부서평균;
------------------------------------------ㅊ
SELECT *
FROM (
SELECT deptno , ename , sal ,
(SELECT ROUND(AVG(sal))
FROM emp e2
WHERE e2.deptno = e1.deptno) 평균
FROM emp e1
)
WHERE sal > 평균 ;
------------------------------------
select *
from (
select e.deptno, e.ename, e.sal as ES,
(select round(avg(sal))
from emp
where deptno = e.deptno)부서평균
from emp e
)
where ES > 부서평균
order by deptno;
****e.sal 로 썼다가 계속 틀렸음.
안에 있는 e.sal 을 갖고오는건지
E테이블에서 갖고 오는건지
헷갈려 할 수 있음.********
ALIAS 지정을 생명같이
여겨야 겠구만.
--------------------------------------
select e.deptno, e.ename, e.sal, 부서평균
from (select deptno, round(avg(sal))부서평균
from emp
group by deptno) ee, emp e
where e.deptno = ee.deptno
and
e.sal > 부서평균
order by deptno asc;
***11/7 다시 풀려고 도전했는데
안되서 슬펐음
문제264.
직업, 직업별 토탈월급을 출력하시오
select job, sum(sal)
from emp
group by job;
문제265.
위의 결과를 다시 출력하는데
직업이 SALESMAN 의
토탈월급보다 더 큰것만 출력하시오
select job, sum(sal)
from emp
group by job
having sum(sal) > (select sum(sal)
from emp
where job='SALESMAN');
******이건 너무 깊게 생각하지마
Having 절의 sum(sal) 은
이미 직업_그룹화를 거쳐 나온 값임.
문제266.
직업, 직업별 토탈월급을 출력하는데
직업별 토탈월급들의 평균값보다 더 큰것 만
출력하시오
select job, sum(sal)
from emp
group by job
having sum(sal)>
(select avg(sum(sal) )
from emp
group by job) ;
order by 는 거의 서브쿼리 안해서 패스
문제267.
이름, 월급,
직속상사(관리자)이름, 직속상사의 월급을
출력하시오(self join 쓰세옹)
select 사원.ename, 사원.sal,
관리자.ename, 관리자.sal
from emp 사원, emp 관리자
where 사원.mgr = 관리자.empno;
문제268.
위의 결과를 다시 출력하는데
자기관리자보다
더 많은 월급을 받는 사원들만
출력하시오
select 사원.ename, 사원.sal,
관리자.ename, 관리자.sal
from emp 사원, emp 관리자
where 사원.mgr = 관리자.empno
and
사원.sal> 관리자.sal;
문제269.(오늘의 마지막 문제, 쉽게내주심ㅜㅜ)
이름, 입사일,
자기의 관리자(직속상사)의 이름,
자기의 관리자(직속상사)의 입사일 을
출력하는데
자기의 직속상사보다 먼저 입사한 사원들만
출력하시오
select 사원.ename, 사원.hiredate,
관리자.ename, 관리자.hiredate
from emp 사원, emp 관리자
where 사원.mgr = 관리자.empno
and
사원.hiredate < 관리자.hiredate;
'sql' 카테고리의 다른 글
| 11. 데이터 조작 언어(DML문) (0) | 2019.03.30 |
|---|---|
| 10. 집합연산자 (0) | 2019.03.30 |
| 8. JOIN (0) | 2019.03.30 |
| 7. DATA 분석함수(rank, dens_rank, listagg, ntile, lead, lag) (0) | 2019.03.30 |
| 6. 그룹함수 (0) | 2019.03.30 |
