※ 서브쿼리 사용시 not in 사용하면 서브쿼리에서 메인쿼리로 null 값이
리턴되지 않도록 주의해야합니다.
▣ 074 서브 쿼리 사용하기 4(EXISTS와 NOT EXISTS)
짝꿍의 emp 테이블에는 존재하는데 나의 emp 테이블에는 존재하지 않는
데이터가 무엇인지 확인하고 싶다면 ?
1. minus
2. exists 와 not exists예제. 부서테이블의 부서번호를 출력하시오 !
select deptno
from dept;
예제. minus 를 이용해서 부서 테이블에는 존재하는데 사원 테이블에는
존재하지 않는 부서번호를 출력하시오 !
dept - emp = 40
10 10
20 20
30 30
40select deptno from dept
minus
select deptno from emp;
※ minus 는 위와 같이 두 테이블의 데이터의 차이를 확인하고 싶을 때 사용
운영 db 테스트 db (준혁)
192.168.19.29
emp 테이블 -----------------------> emp 테이블
↑
실시간 입력 ↓
↓
insert into emp(empno, ename, sal, job, deptno) select * from emp@dblink29;
values( 3984, 'JONES2', 4500, 'dba', 80 );
commit; db link 이름을 확인:
**select * from dba_db_links;**문제386. ( 테스트 db쪽에서) 운영 db 의 테이블을 엑세스 하기 위한
db 링크를 생성하시오 !
create public database link dblink_yys
connect to c##scott
identified by tiger
using '192.168.19.29:1521/xe' ; <--- 운영db의 아이피주소:포트번호/db이름
select * from tab@dblink_yys;
문제387. (테스트 db쪽 에서 수행) 운영 db 쪽에 있는 emp 테이블을 테스트 db
에 다음과 같이 생성하시오 !
create table emp_yys
as
select * from emp@dblink_yys;
select * from emp_yys;
문제388. (테스트 db 쪽에서 수행) 테스트 db 의 emp 테이블과
운영 db 의 emp 테이블의 차이를 확인하시오 !
( 운영 db 에는 존재하는 데이터인데 test db 에는 존재하지 않는
데이터를 검색하시오 )
select from emp@dblink_yys
minus
select from emp;
문제389. (테스트 db 에서 수행) 운영 db 에 emp에 존재하는데 테스트 db 에
emp에는 존재하지 않는 데이터를 운영 db 에 emp 테이블에 입력하시오 !
insert into emp
select from emp@dblink_yys
minus
select from emp;
select * from emp;
commit;
문제390. (테스트 db에서 수행) 테스트 db 의 emp 테이블을 truncate 하세요.
( truncate 를 수행하면 데이터는 다 지워지고 테이블 구조만 남습니다.)
truncate table emp ;
select * from emp;
문제391. (테스트 db 에서 수행) 운영 db의 emp 테이블을 테스트 db 의 emp
테이블에 입력하시오 !
insert into emp
select *
from emp@dblink_yys;
select * from emp;
commit;
문제392. dept 테이블에서 부서번호를 출력하는데 emp 테이블에 존재하는
부서번호들만 출력하시오
select deptno
from dept d
where exists ( select 'X'
from emp e
where e.deptno = d.deptno) ;
※설명: 위와 같이 **exists** 문을 사용하게 되면 **메인쿼리의 테이블부터 먼저 엑세스를**
하면서 부서번호가 10번이 emp 테이블에 존재하는지 찾아보게 되는데 존재하면 더이상 스캔하지 않고 멈춥니다. 20번과 30번, 40번도 마찬가지로 존재하면 멈춥니다.
**그래서 검색 성능이 좋은데 그냥 다 좋은건 아니고
메인쿼리의 데이터의 건수가 몇건 되지않을때 좋은 검색속도를 보입니다.**
위와 같이 exist 문은 테이블 별칭을 사용하고 있고 그리고 **메인쿼리의 컬럼이 서브쿼리에 들어가고 있습니다.**문제393. telecom_table 에서 통신사를 출력하는데 우리반 emp17 테이블에
존재하는 통신사만 출력하시오 !
SELECT * from telecom_table;
select telecom
from telecom_table t
where exists ( select *
from emp17 e
where e.telecom = t.telecom );
문제394. telecom_table 에는 존재하는 통신사인데 emp17에는 존재하지 않는
통신사를 출력하시오 !
select telecom
from telecom_table t
where not exists ( select *
from emp17 e
where e.telecom = t.telecom );
문제395. 운영 db 의 emp 테이블에는 존재하는 사원번호인데
테스트 db 의 emp 테이블에는 존재하지 않는 사원번호가 무엇인가 ?
운영db> select empno
from emp e
where not exists ( select *
from emp@dblink21 t
where t.empno = e.empno );
테스트db> select empno
from emp@dblink_yys e
where not exists ( select *
from emp t
where t.empno = e.empno );
문제396. (운영db에서 수행) 운영 db 의 emp 테이블에 아래의 데이터를
입력하시오 !
insert into emp(empno, ename, sal, deptno )
values( 8888, 'AAA', 3000, 30 );
insert into emp(empno, ename, sal, deptno )
values( 9999, 'BBB', 4000, 20 );
commit;
※ 서브쿼리 사용시 not in 사용하면 서브쿼리에서 메인쿼리로 null 값이
리턴되지 않도록 주의해야합니다.
문제397. (OCP 시험용 테이블) HR 계정에 departments 테이블에는 있는
department_id 인데 employees 테이블에는 존재하지 않는
department_id 를 출력하시오 !
select department_id
from departments d
where not exists ( select *
from employees e
where e.department_id = d.department_id );
문제398. dept 테이블에서 부서번호를 출력하는데 emp 테이블에 있는 부서번호만
출력하시오 ! ( exists 를 이용해서 수행, in 을 이용해서 수행 )
- exists 를 이용해서
select deptno
from dept d
where exists ( select *
from emp e
where e.deptno = d.deptno ); - in 을 이용해서
select deptno
from deptwhere deptno in ( select deptno from emp );
문제399. dept 테이블에서 deptno 를 출력하는데
dept 테이블에는 존재하는데 emp 테이블에는 존재하지 않는
deptno 를 출력하시오 ! ( not exists 와 not in 으로 각각 수행하시오)
- not exists
select deptno
from dept d
where not exists ( select *
from emp e
where e.deptno=d.deptno ); - not in
select deptno
from dept
where deptno not in ( select deptno
from emp
where deptno is not null );
※ (dba 관점에서 SQL을 사용) 두 테이블 사이에 차이가 나는 데이터가 무엇인지
확인하고 싶을 때 사용하는 SQL ?
1. exists 와 not exists
2. minusselect deptno from dept
minus
select deptno from emp;
문제400. emp 테이블을 emp_backup 으로 백업하시오 !
create table emp_backup
as
select *
from emp;
select * from emp_backup;
문제401. emp 테이블의 데이터중 부서번호가 20번인 사원들을 지우고
commit 하세요.
delete from emp
where deptno = 20;
commit;
select * from emp;
문제402. emp 테이블과 emp_backup 의 데이터의 차이를 확인하시오 !
**※ 두 테이블의 사이의 데이터의 차이를 확인하는 SQL ?**
1. minus
2. not existsselect from emp_backup
minus
select from emp;
select from emp
minus
select from emp_backup;
복구해줍니다.
insert into emp
select from emp_backup
minus
select from emp;
commit;
[7월 5일 점심시간 문제] 우리반 테이블에서 남양주에서 사는 학생들과
나이가 같고 통신사가 같은 학생들의 이름과 나이와 통신사를 출력하시오.
(출력하고 결과가 같은지 짝꿍과 비교해보세요)
1. non pairwise 방식
select ename, age, telecom
from emp17
where age in (select age
from emp17
where address like '%남양주%')
and telecom in (select telecom
from emp17
where address like '%남양주%');
2. pairwise 방식
select ename, age, telecom
from emp17
where (age, telecom) in (select age, telecom
from emp17
where address like '%남양주%');▣ 075 서브 쿼리 사용하기 5(HAVING절의 서브 쿼리)
- select 문의 6가지절에서 서브쿼리를 쓸 수 있는 절은 ? select 서브쿼리 사용 가능 ( scalar subquery )
from 서브쿼리 사용 가능 ( in line view )
where 서브쿼리 사용 가능
group by X
having 서브쿼리 사용 가능 ( scalar subquery )
order by 서브쿼리 사용 가능 ( scalar subquery )
문제403. 직업, 직업별 토탈월급을 출력하시오 !
delete from emp
where job is null;
select job, sum(sal)
from emp
group by job;
문제404. 직업과 직업별 토탈월급을 출력하는데 직업이 SALESMAN 인 사원들의
토탈월급보다 더 큰 사원들만 출력하시오 !
select job, sum(sal)
from emp
where sum(sal) > ( select sum(sal)
from emp
where job='SALESMAN' )
group by job;
ORA-00934: 그룹 함수는 허가되지 않습니다.
where 절에는 그룹함수로 검색조건을 줄 수 없고 having 절에 줘야합니다.
select job, sum(sal)
from emp
group by job
having sum(sal) > ( select sum(sal)
from emp
where job='SALESMAN' );
문제405. 통신사, 통신사별 인원수를 출력하는데 통신사별 인원수가 lg 의
인원수보다 큰것만 출력하시오 !
select telecom, count()
from emp17
group by telecom
having count() > ( select count(*)
from emp17
where telecom ='lg' );
문제406.(복습문제) 직업, 직업별 최대월급, 직업별 최소월급, 직업별 토탈월급,
직업별 인원수를 출력하시오 !
select job, max(sal), min(sal), sum(sal), count(*)
from emp
group by job;
▣ 076 서브 쿼리 사용하기 6(FROM절의 서브 쿼리)
- select 문의 6가지절에서 서브쿼리를 쓸 수 있는 절은 ?
select 서브쿼리 사용 가능 ( scalar subquery )
from 서브쿼리 사용 가능 ( in line view )
where 서브쿼리 사용 가능
group by X
having 서브쿼리 사용 가능 ( scalar subquery )
order by 서브쿼리 사용 가능 ( scalar subquery )문제407. (복습문제) 이름, 월급, 월급에 대한 순위를 출력하시오 !
select ename, sal, dense_rank() over ( order by sal desc ) as 순위
from emp;
문제408. 위의 결과에서 순위가 3등과 5등을 출력하시오 !
select ename, sal, dense_rank() over ( order by sal desc ) as 순위
from emp
where 순위 in ( 3, 5 );
ORA-00904: "순위": 부적합한 식별자
select *
from (
select ename, sal, dense_rank() over ( order by sal desc ) as 순위
from emp
)
where 순위 in ( 3, 5 );
※ from 절의 서브쿼리를 써야 위의 결과를 볼 수 있습니다.
문제409. 직업, 이름, 월급, 순위를 출력하는데 순위가 직업별로 각각 월급이
높은 순서대로 순위를 부여하시오 !
select job, ename, sal, dense_rank() over ( partition by job
order by sal desc ) 순위
from emp;
문제410. 위의 결과를 다시 출력하는데 순위가 1등인 사원들만 출력하시오 !
select *
from (
select job, ename, sal, dense_rank() over ( partition by job
order by sal desc ) 순위
from emp
)
where 순위 = 1;
문제411. (복습문제) 사원 테이블의 평균월급을 출력하시오 !
select avg(sal)
from emp;
문제412. 사원 테이블에서 이름, 월급, 사원 테이블의 평균월급을 출력하시오 !
select ename, sal, avg(sal) over () 평균월급
from emp;
문제413. 위의 결과를 다시 출력하는데 자기의 월급이 사원 테이블의 평균월급
보다 더 큰 사원들만 출력하시오 !
select ename, sal, 평균월급
from (
select ename, sal, avg(sal) over () 평균월급
from emp )
where sal > 평균월급 ;
문제414. (복습문제) 부서번호, 부서번호별 평균월급을 출력하시오 !
select deptno, avg(sal)
from emp
group by deptno;
문제415. 부서번호, 이름, 월급, 자기가 속한 부서번호의 평균월급을 출력하시오
select deptno, ename, sal, avg(sal) over ( partition by deptno ) 부서평균
from emp;
문제416. 위의 결과를 다시 출력하는데 자기의 월급이 자기의 부서번호의 평균월급
보다 더 큰 사원들만 출력하시오 !
select *
from (
select deptno, ename, sal, avg(sal) over ( partition by deptno ) 부서평균
from emp
)
where sal > 부서평균;
문제417. 위의 결과를 보기 위해서 다른 튜닝전 SQL로 출력하시오 !
select e.deptno, e.ename, e.sal, v.부서평균
from emp e, ( select deptno, avg(sal) 부서평균
from emp
group by deptno ) v
where e.deptno = v.deptno and e.sal > v.부서평균 ;
▣ 077 서브 쿼리 사용하기 7(SELECT절의 서브 쿼리)
- select 문의 6가지절에서 서브쿼리를 쓸 수 있는 절은 ? select 서브쿼리 사용 가능 ( scalar subquery )
from 서브쿼리 사용 가능 ( in line view )
where 서브쿼리 사용 가능
group by X
having 서브쿼리 사용 가능 ( scalar subquery )
order by 서브쿼리 사용 가능 ( scalar subquery )
문제418. 토탈월급을 출력하시오 !
select sum(sal)
from emp;
문제419. 이름, 월급, 토탈월급을 출력하시오 !
select ename, sal, sum(sal) over ()
from emp;
문제420. 이름, 월급, 토탈월급을 출력하는데 select 절의 서브쿼리인
스칼라 서브쿼리를 이용해서 출력하시오!
select ename, sal, ( select sum(sal) from emp ) as 토탈값
from emp; → 14건
※ 설명 : select 절의 서브쿼리인 스칼라 서브쿼리는 emp 테이블의 건수만큼
반복되면 수행 됩니다.
select ename, sal, ( select sum(sal) from emp ) as 토탈값
from emp
where job='SALESMAN'; → 4
아래의 2개의 SQL의 버퍼의 갯수를 각각 확인하시오 !
튜닝전:
select /+ gather_plan_statistics / ename, sal,
( select sum(sal) from emp ) as 토탈값
from emp;
SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
버퍼의 갯수 : 6개
튜닝후:
select /+ gather_plan_statistics / ename, sal,
sum(sal) over () as 토탈값
from emp;
SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
버퍼의 갯수 : 6개
select ename, sal, ( select sum(sal) from emp ) as 토탈값
from emp;
select ename, sal, sum(sal) over () as 토탈값
from emp;
문제421. 직업이 SALESMAN 인 사원들의 토탈월급을 출력하시오 !
select sum(sal)
from emp
where job='SALESMAN';
문제422. 이름, 월급, 직업이 SALESMAN 인 사원들의 토탈월급을 출력하시오
select ename, sal, ( select sum(sal)
from emp
where job='SALESMAN' ) as "SALES토탈"from emp;
※ select 절의 scalar 서브쿼리는 SQL 튜닝을 잘 해야 합니다.
문제423. 이름, 월급, 직업이 SALESMAN 인 사원들의 토탈월급,
직업이 SALESMAN 인 사원들의 최대월급,
직업이 SALESMAN 인 사원들의 최소월급을 출력하시오!
select ename, sal, ( select sum(sal) from emp where job='SALESMAN' ),
( select max(sal) from emp where job='SALESMAN' ),
( select min(sal) from emp where job='SALESMAN' )
from emp;
위의 SQL의 문제점은 emp 테이블의 건수만큼 스칼라 서브쿼리의 select 문장이 반복된다는것 입니다. 그런데 emp 테이블을 각각 3번을 엑세스를 하고 있습니다.
select sum(sal), max(sal), min(sal)
from emp
where job='SALESMAN';
위에 SQL처럼 emp 테이블을 3번 엑세스해서 데이터를 조회하지말고
다음과 같이 1번만 엑세스해서 데이터를 조회하시오
select ename, sal, ( select sum(sal), max(sal), min(sal)
from emp
where job='SALESMAN' )
from emp;
ORA-00913: 값의 수가 너무 많습니다
**SQLP 주관식 )** 이름, 월급, 직업이 SALESMAN인 사원들의 토탈월급,
직업이 SALESMAN인 사원들의 최대월급,
직업이 SALESMAN인 사원들의 최소월급,
을 출력하시오!
튜닝전: select ename, sal, ( select sum(sal) from emp where job='SALESMAN' ) 토탈월급,
( select max(sal) from emp where job='SALESMAN' ) 최대월급,
( select min(sal) from emp where job='SALESMAN' ) 최소월급
from emp;
튜닝후 : ??? -> 게시판 활용※ 스칼라 서브쿼리의 특징 ? 하나의 값만 출력할 수 있습니다.
select ename, sal, ( select sum(sal) || max(sal) || min(sal)
from emp
where job='SALESMAN' )
from emp;
문제424. 이름, 성별, 나이, 남자 나이의 평균값, 여자 나이의 평균값을
출력하시오 !
select ename, gender, age,
( select avg(age) from emp17 where gender='남' ) 남자평균,
( select avg(age) from emp17 where gender='여' ) 여자평균
from emp17;
문제425. 이름, 주소, 나이, 서울에서 사는 학생들의 평균나이,
서울이 아닌곳에서 사는 학생들의 평균나이를
출력하시오 !
select ename, address, age,
( select avg(age) from emp17 where address like '%서울%' ) 서울평균,
( select avg(age) from emp17 where address not like '%서울%' ) 서울외평균
from emp17;
문제426. 이름, 월급, 부서위치를 출력하시오 !
select ename, sal, ( select loc
from dept d
where d.deptno = e.deptno ) 부서위치
from emp e ;
JOIN 사용
select e.ename, e.sal, d.loc
from emp e, dept d
where e.deptno = d.deptno;
문제427. 부서번호, 이름, 월급, 자기가 속한 부서번호의 평균월급을 출력하시오!
- 튜닝전
select deptno, ename, sal, ( select avg(sal)
from emp s
where s.deptno = e.deptno ) 부서평균
from emp e ;
- 튜닝후
select deptno, ename, sal, avg(sal) over (partition by deptno)
from emp;
문제428. 통신사, 이름, 나이, 자기가 속한 통신사의 평균나이를 출력하시오 !
( 스칼라 서브쿼리로 수행하세요 )
튜닝전:
**SET AUTOT ON**
select telecom, ename, age, ( select avg(age)
from emp17 s
where s.telecom = e.telecom ) 통신사평균
from emp17 e ;
**20 consistent gets** <--- 메모리에서 읽은 버퍼의 갯수문제429. 위의 SQL을 스칼라 서브쿼리 이용하지 말고 데이터 분석함수로
수행하시오 !
**SET AUTOT ON**select telecom , ename, age, avg(age) over (partition by telecom) 통신사평균
from emp17 ;
11 consistent gets <--- 메모리에서 읽은 버퍼의 갯수
문제430. (오늘의 마지막 문제) 우리반 테이블에서 이름, 나이, 통신사, 성별, 자기가 속한 통신사의 평균나이, 자기의 성별의 평균나이를 출력하시오 (스칼라 서브쿼리로 하세요)
select ename, age, telecom, gender,
(select round(avg(age)) from emp17 s where s.telecom = e.telecom) as 통신사평균,
(select round(avg(age)) from emp17 s where s.gender = e.gender) as 나이평균
from emp17 e;
select ename, age, telecom, gender,
round(avg(age) over (partition by telecom)) as 통신사평균,
round(avg(age) over (partition by gender)) as 나이평균
from emp17;