어제 마지막 문제
문제 348. (오늘의 마지막 문제) 다음과 같이 결과를 출력하세요
직업별 부서번호별 토탈월급들을 출력하는데,
맨 옆과 맨 아래에 집계값이 각각 나오게 출력하시오
-- sum + decode
select nvl(job,'토탈') as 직업 ,
sum(decode(deptno,10,sal,null)) as "10",
sum(decode(deptno,20,sal,null)) as "20",
sum(decode(deptno,30,sal,null)) as "30",sum(sal)as 토탈
from emp
group by rollup (job);-- pivot (서브쿼리랑 pivot 사용하는 방법 -> 굳이 안써도됨)
select nvl(job,'토탈')as 직업 , sum("10") as "10" , sum("20") as "20" , sum("30") as "30", sum(토탈) as 토탈
from (select job, deptno, sal, sum(sal) over (partition by job) as 토탈
from emp )
pivot (sum(sal) for deptno in(10,20,30))
group by rollup (job)
order by job asc;※ 어차피 pivot 을 사용해도, 오라클이 sum+decode 로 바꿔서 수행함
-> Query transfromation
SQL -> parsing -> Query transfromation -> 실행계획 생성
(기계어로 변환) (오라클이 SQL을 재작성)
(오타 등 체크) (쿼리변화)
full outer join -> union all 로 쿼리변화됨
insert into emp(empno,ename,sal)
values (1234, 'JACK', 70);
문제 349. (SQL 튜닝 방법)
아래의 SQL을 집합 연산자 (union, union all) 와 right outer join 과 left outer join 으로 수행하시오select e.ename, d.loc from emp e full outer join dept d on (e.deptno = d.deptno);select e.ename, d.loc from emp e left outer join dept d on (e.deptno = d.deptno) union select e.ename, d.loc from emp e right outer join dept d on (e.deptno (+) = d.deptno);rowid 써서 정렬 맞춰주는 방법이 있음!!
rowid 번호를 from 절에 서브쿼리로 넣은 다음에 정렬해주고,
select 절에는 rowid 를 안보이게 하면 정렬이 됨!
=> 요런 과정을 튜닝 이라고 함
rowid
: 테이블의 행의 논리적 주소 (file번호# + block번호# + row번호#)
데이터를 insert 하면 생김. (책의 페이지 번호 같은!)
새로 추가하면 마지막에 생김
SQL 튜닝 (실제 실행계획 보기)
무조건 짧은 sql이 좋은것 아님.
메모리 block의 갯수가 적은 sql이 좋음
/+ gather_plan_statistics
-> 지금 db의 성능을 느리게하는 sql이 뭔지 고민해보기전 쓰는거
실제 실행 계획 : SQL을 실제로 실행하고 실행된 SQL의 실행계획 확인
이 힌트는 현 SQL에 대한 실행계획에 대한 통계정보를 볼 때 사용하는 힌트입니다.
select /*+ gather_plan_statistics */ ename, sal
from emp
where ename='SCOTT';
SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); 
buffers 의 숫자가 작을수록 좋은 sql이다!
튜닝전
select /*+ gather_plan_statistics */ ename, loc
from (
select e.rowid as rw, e.ename, d.loc
from emp e left outer join dept d
on ( e.deptno = d.deptno )
union
select e.rowid as rw, e.ename, d.loc
from emp e right outer join dept d
on ( e.deptno = d.deptno )
order by rw asc
); 
튜닝후
select /*+ gather_plan_statistics */ e.ename, d.loc
from emp e full outer join dept d
on ( e.deptno = d.deptno ); 
069. 집합연산자로 데이터의 교집합을 출력하기 (INTERSECT) (교집합)
- union all
- union
- intersect : 교집합
- minus
예제.
select deptno, sum(sal)
from emp
where deptno in (10,20)
group by deptno
intersect
select deptno, sum(sal)
from emp
where deptno in (20,30)
group by deptno;
- 운영 DB, 테스트 DB (운영DB를 넣고 테스트해보는 DB)가 있는데
운영DB와 테스트DB를 비슷하게 맞추기위해 계속 테스트 db에 insert 시킨다.
이 때, 운영db와 테스트db의 차이를 보기 위해서 intersect가 쓰인다.
문제 350.
market_2017 과 market_2022 의 건수를 각각 count 하시오select count(*) from market_2017; -> 316078 select count(*) from market_2022; -> 312811
문제 351.
market_2017 테이블에서 상호명이 카페베네를 포함하고 있는 상가업소번호, 상호명을 출력하시오select 상가업소번호, 상호명 from market_2017 where 상호명 like '%카페베네%';-> 72개
문제 352.
market_2022 테이블에서 상호명이 카페베네를 포함하고 있는 상가업소번호, 상호명을 출력하시오select 상가업소번호, 상호명 from market_2022 where 상호명 like '%카페베네%';-> 51개
문제 353.
2022년도에 있는 상호명 카페베네가 2017년도에도 있었던 상가업소번호와 상호명을 출력하시오 (두개의 교집합 구하는)select 상가업소번호, 상호명 from market_2017 where 상호명 like '%카페베네%' intersect select 상가업소번호, 상호명 from market_2022 where 상호명 like '%카페베네%';A 테이블에 있는 데이터중에 B 테이블에도 있는 데이터 구하는거!
070. 집합연산자로 데이터의 차이를 출력하기 (MINUS) (차집합)
A -> 10,20,30
B -> 20,30,40
A-B = 10 만 나온다 (40은 X)
오라클이 차집합을 구할 때는 내부적으로 정렬을 하고 차집합을 구한다.
내부적으로 정렬하는 집합연산자는, 'union', 'intersect', 'minus'
(union all 빼고 다)
문제 354.
2017년도에는 존재했는데 2022년도에 사라진 카페베네 매장은 총 몇개인가?select count(*) from ( select 상호명 from market_2017 where 상호명 like '%카페베네%' minus select 상호명 from market_2022 where 상호명 like '%카페베네%' );from 절에 있는 결과를 count 해라!
(서브쿼리사용)
문제 355.
이번에는 스타벅스도 사라진 매장이 몇개인지 확인하시오select count(*) from ( select 상가업소번호, 상호명 from market_2017 where 상호명 like '%스타벅스%' minus select 상가업소번호, 상호명 from market_2022 where 상호명 like '%스타벅스%' );상가업소번호 넣고 안넣고에 따라 왜 결과가 달라지늬???
DB링크 만들기 (다른 db접속)
문제 356.
짝꿍의 데이터베이스의 emp 테이블을 조회하기 위해서
짝꿍의 아이피 주소를 알아내시오내 pc의 ip주소 알아내는 방법 : 도스창 열고 ipconfig
192.168.19.27
짝꿍 : 192.168.19.24
문제 357.
자기 자신의 아이피주소로 자기 자신의 DB에 접속이 되는지를 먼저 확인합니다.
-> 도스창을 열고 아래와 같이 접속합니다.
sqlplus c##scott/tiger@192.168.19.27:1521/xe
문제 358.
짝꿍 데이터베이스로 접속되는지 확인
(SQL> exit) 한다음 ip 다시 입력
sqlplus c##scott/tiger@192.168.19.24:1521/xe
문제 359.
내 sqldeveloper 에서 아래와 같이 짝꿍 db의 테이블들을 조회할 수 있는 db링크 를 생성합니다.create public database link dblink21 connect to C##scott identified by tiger using '192.168.19.24:1521/xe' ;Database link DBLINK21이(가) 생성되었습니다.
※ 조회하는 법 : select * from emp@dblink21;
※ 내 DB링크가 기억이 안난다면? (db link 이름 확인하는법)
: select * from dba_db_links;
문제 360.
내 emp 테이블에 랜덤으로 데이터를 한건 입력하시오insert into emp(empno, ename, sal, job, deptno) values (1234, 'suzy', 6000, 'singer', '75'); commit;
문제 361.
짝꿍의 emp 테이블과 나의 emp테이블의 데이터의 차이가 뭐가 있는지 조회하시오select * from emp@dblink21 minus select * from emp;
071. 서브쿼리 사용하기 1 (단일행 서브쿼리)
예제. JONES 보다 더 많은 월급을 받는 사원들의 이름과 월급을 출력하시오
select ename, sal
from emp
where sal > 'JONES'; 로 하면 안됨
JONES 의 월급을 먼저 구해야함!
select sal
from emp
where ename='JONES';select ename, sal from emp where sal > ( select sal from emp where ename='JONES' );
메인쿼리 = outer 쿼리
서브쿼리 = inner 쿼리
문제 362. scott 과 같은 월급을 받는 사원들의 이름과 월급을 출력하시오
select ename, sal from emp where sal = ( select sal from emp where ename='SCOTT' );
문제 363. 위의 결과를 다시 출력하는데 SCOTT 은 제외하고 출력하시오
select ename, sal from emp where sal = ( select sal from emp where ename='SCOTT' ) and ename != 'SCOTT';
문제 364.
ALLEN 보다 늦게 입사한 사원들의 이름과 입사일을 출력하시오select ename, hiredate from emp where hiredate > ( select hiredate from emp where ename = 'ALLEN' );
문제 365.
서울시 물가 데이터 테이블을 구성하시오
https://cafe.daum.net/oracleoracle/SDMs/1
문제 366.
price 테이블에서 a_price 의 최대값을 출력하시오select max(a_price) from price;
문제 368.
서울시 물가 데이터중에 가장 가격(a_price) 이 비싼
생필품명(a_name), 그 가격(a_price) 를 출력하시오select a_name, a_price from price where a_price = ( select max(a_price) from price);
▣ (7월 4일 점심시간 문제) 아래의 SQL의 결과를 집합 연산자(union all 이나 union) 로 구현하시오.
select nvl( to_char( empno), '전체집계:' ) as empno, ename, sum(sal) as sal from emp group by grouping sets( (empno, ename), () );select to_char( empno), ename, sum(sal) as sal from emp group by empno, ename union select nvl( to_char( null), '전체집계:' ) as empno, to_char(null) as ename , sum(sal) from emp;
다시 서브쿼리
문제 369.
직업이 SALESMAN 인 사원들중에 최대월급을 받는 사원의 이름과 월급을 출력하시오select ename, sal from emp where sal = ( select max (sal) from emp where job='SALESMAN' );이름은 여러개 출력되려고 하고, 최대월급은 한개로 나오려고 하니까 오류가 나기때문에 서브쿼리를 사용해서 먼저 가장 많은 월급을 구해야함!
select ename, sal from emp where sal = ( select max (sal) from emp where job='SALESMAN' ) and job='SALESMAN' ;로 하면 정확함
문제 370.
위의 결과를 서브쿼리 이용하지 말고 order by .. fetch row 를 이용해서 수행하시오select ename, sal from emp where job='SALESMAN' order by sal desc fetch first 1 rows only;
문제 371. dba는 SQL을 볼때 성능을 생각하면서 봐야하므로 위의 2개의 SQL의 buffer 의 갯수를 확인하시오
select /*+ gather_plan_statistics */ ename, sal from emp where sal = ( select max (sal) from emp where job='SALESMAN' ) and job='SALESMAN' ;
select /*+ gather_plan_statistics */ ename, sal from emp where job='SALESMAN' order by sal desc fetch first 1 rows only;
위의 테이블은 emp 테이블을 2번이나 선택해서 읽고있기 때문에 느림!
문제 372.
DALLAS 의 부서번호를 출력하시오select deptno from dept where loc = 'DALLAS';
문제 373.
DALLAS 의 부서번호에서 근무하는 사원들의 이름과 월급을 출력하시오select ename, sal from emp where deptno = (select deptno from dept where loc = 'DALLAS' );서브쿼리 사용하면 조인 안해도됨?..
문제 374.
위의 결과를 서브쿼리 쓰지말고 조인으로 수행하시오select e.ename, e.sal from emp e, dept d where e.deptno = d.deptno and loc = 'DALLAS';
문제 375. (난이도 중)
KING 에게 보고하는 사원들의 이름을 출력하시오
(mgr 번호에 KING 의 사원번호가 있는 사원들)select ename from emp where mgr = (select empno from emp where ename = 'KING') ;
072. 서브쿼리 사용하기 2 (다중행 서브쿼리)
예제. 사원번호가 7788, 7902, 7369 번인 사원의 사원번호, 사원이름을 출력하시오.
select empno, ename
from emp
where empno in (7788, 7902, 7369);
여러개 비교할 때 'in' 사용
예제. 이름이 SCOTT 인 사원과 월급이 같은 사원들의 이름과 월급을 출력하시오
select ename, sal
from emp
where sal = (select sal
from emp
where ename = 'SCOTT');예제. 직업이 SALESMAN 인 사원들과 월급이 같은 사원들의 이름과 월급을 출력하시오
select ename, sal
from emp
where sal = (select sal
from emp
where job='SALESMAN') ;
- 하위 질의 : 서브쿼리
직업이 salesman 인 사람들의 월급이 두개 이상이기 때문에 리턴되었다??
= 대신에in써줘야함select ename, sal from emp where sal in (select sal from emp where job='SALESMAN') ;서브쿼리의 종류 2가지
단일행 서브쿼리: 서브쿼리에서 메인쿼리로 하나의 값이 리턴되는 경우
-> 연산자 : =, >, <, >=, <=, !=, <>, ^=다중행 서브쿼리: 서브쿼리에서 메인쿼리로 여러개의 값이 리턴되는 경우
-> 연산자 : in, not in, >all, >all, >any, < any
단일행 서브쿼리 자리에 in 등의 다중행 서브쿼리 써도 결과값 나오긴하나,
오라클이 내부적으로 단일행 서브쿼리로 변경하여 수행하는 것이다.
문제 376.
우리반 테이블에서 통신사가 kt인 학생들과 나이가 같은 학생들의 이름과 나이를 출력하시오select ename, age from emp17 where age in (select age from emp17 where telecom = 'kt') ;= 말고 in 써야함 (통신사가 kt인 학생들이 많기 때문에)
073. 서브쿼리 사용하기 3 (NOT IN) ★★★★
not in 쓸 때 null 값 처리 방법
문제 377.
위의 결과를 다시 출력하는데 이번에는 통신사가 kt인 학생들과 나이가 같지 않은 학생들의 이름과 나이를 출력하시오select ename, age from emp17 where age not in (select age from emp17 where telecom = 'kt') ;
not in사용
예제. 관리자인 사원들의 이름을 출력하시오
(job이 manager가 아니라, 사원번호가 자기 부하직원들의 mgr 번호인 사원들을 출력하는 것 -> 직속부하가 하년ㅇㄹ한ㄴ명이ㅏ라도 있는 사람들
select ename
from emp
where empno in (select mgr
from emp); select ename
from emp
where empno not in (select mgr
from emp);안나옴
※ 왜 선택된 레코드가 없다고 나오냐면,
바로 null 값때문이다. mgr중에 null값이 있어서
NULL 값은 알수없는 값 (트루도 될수 있고 폴스도 될수있음)
select ename
from emp
where empno != 7788 and empno !=7902 and empno !=null; 
- and 는 true 만 있어야함! 근데 null은 알수없기 때문에 오류!
그래서 위의 예제 답을
null 이 리턴되지 않게 해야함!!
select ename
from emp
where empno not in (select mgr
from emp
where mgr is not null );is not null 을 사용하거나, 또는
select ename
from emp
where empno not in (select nvl(mgr, -1)
from emp ); nvl 사용해 null 값을 -1 로 출력되게 해서 null 값을 리턴되지 않게 만드는것
※ 다중행 서브쿼리문 작성시, not in 을 사용하면 서브쿼리에 null 처리를
잘 해줘서 null 값이 리턴되지 않도록 해줘야 합니다.
오라클의 서브쿼리의 종류 3가지
- single row subquery : 서브쿼리에서 메인쿼리로 하나의 값(행)이 리턴되는 경우
- multiple row subquery : 서브쿼리에서 메인쿼리로 여러개의 값(행)이 리턴되는 경우
- multiple column subquery : 서브쿼리에서 메인쿼리로 여러개의 컬럼값이 리턴되는 경우
추가. 서브쿼리에서 여러개의 컬럼값 리턴시키기
서브쿼리 non pair wise 방식
문제 378.
직업이 SALESMAN 인 사원들과 월급이 같은 사원들의 이름과 월급과 커미션을 출력하시오select ename, sal, comm, job from emp where sal in ( select sal from emp where job = 'SALESMAN' );
문제 379.
직업이 SALESMAN 인 사원들과 월급이 같고 그리고 커미션도 같은 사원들의 이름, 월급, 커미션, 직업을 출력하시오.select ename, sal, comm, job from emp where sal in ( select sal from emp where job = 'SALESMAN' ) and comm in ( select comm from emp where job = 'SALESMAN' );
and 사용해서 여러개 컬럼값 작성
=> 요게 non pair wise 방식
서브쿼리 pair wise 방식
pair wise 방식 은,
select ename, sal, comm, job
from emp
where ( sal, comm) in (select sal, comm
from emp
where job ='SALESMAN'); -> 오라클에서만 사용 가능함 ONLY ORACLE!!
출력되는 정렬만 다르지 데이터는 똑같이 출력된다.
꼭 짧아져서 좋아지는게 아니라, 대용량에서는 두개가 결과가 다르게 나올수 있음!
데이터를 검색하는 방법이 서로 다르다.
update emp
set sal = 1500
where ename='KING';
update emp
set comm = 1400
where ename='KING';
commit;
non pair wise 방식에서는 직업이 salesman 이 아닌 사람도 나왔음 (KING)
※ pairwise 방식보다 non pairwise 방식이 데이터를 더 많이 검색한다.
non pair wise vs pair wise
sal comm sal comm
1250 1400 1250 1400
1250 500 1250 500
1600 300 1600 300
1500 0 1500 0
1250 300
1250 0
1600 1400
1600 500
1600 0
1500 1400
1500 500
1500 300다른 직업이라 하더라도, sal, comm 둘중 하나만 같아도 검색하는게 non pair wise
pair wise는 딱 그거에 해당되는 데이터만 검색함
문제 380.
최신 mySQL 버젼에서는 pair wise 방식이 지원되는지 확인해보시오.
답: mySQL 은 둘다 지원됩니다.
문제 381.
우리반에서 통신사가 kt인 학생들과 나이가 같고 성별이 같은 학생들의 이름, 나이, 성별, 통신사를 출력하시오
(non pair wise 방식으로 수행해주세요)select ename, age, gender, telecom from emp17 where age in ( select age from emp17 where telecom = 'kt' ) and gender in ( select gender from emp17 where telecom = 'kt' );select ename, age, gender, telecom from emp17 where ( age, gender) in (select age, gender from emp17 where telecom ='kt');두개 답이 다르게 나왔음 (두명 생략됨 희림, 기찬)
pairwise 방식에서는 검색이 안되었다.
문제 382.
우리반 테이블에서 통신사가 lg인 학생들과 나이가 같은 학생들의 이름과 나이와 통신사를 출력하시오select ename, age, telecom from emp17 where telecom in ( select telecom from emp17 where telecom = 'lg') and age in ( select age from emp17 where telecom = 'lg');
delete from emp where hiredate is null;
commit;
문제 383. (집합 연산자) 아래의 SQL 을 튜닝하시오
튜닝전
select deptno, to_char(null) as job, sum(sal) from emp group by deptno union select to_number(null) as deptno, job, sum(sal) from emp group by job;emp 테이블을 한번만 select 되게 sql 튜닝해라
튜닝후select deptno, job, sum(sal) from emp group by grouping sets ( (deptno) , (job) ) order by deptno, job;
문제 384.
아래의 SQL의 튜닝 후 SQL을 작성하시오select deptno, to_char(null) as job, sum(sal) from emp group by deptno union select to_number(null) as deptno, job, sum(sal) from emp group by job union select null, null, sum(sal) from emp;튜닝후
select deptno, job, sum(sal) from emp group by grouping sets ( (deptno) , (job), () ) order by deptno, job;
grouping set3개 괄호로 -> deptno 별 집계, job별 집계, 전체 집계 된것!
문제 385. (오늘의 마지막 문제)
아래의 SQL의 튜닝 전 SQL 을 작성하시오튜닝 전? : ?
select gender, to_char(null) as telecom, round(avg(age)) from emp17 group by gender union select to_char(null) as gender, telecom, round(avg(age)) from emp17 group by telecom union select null, null, round(avg(age)) from emp17;튜닝 후
select gender, telecom, round(avg(age)) from emp17 group by grouping sets ( (gender) , (telecom) , () ) order by gender, telecom;
