5장. 다른 시간대에서의 데이터 관리
SQL 심화2017. 9. 7. 03:18
■ 지난 금요일에 배운 내용 정리
- merge 문
튜닝전 : 상호관련 서브 쿼리를 이용한 update문장
튜닝후 :1. merge
2.updatable join view
- Query도 많이 하지만
- 오라클 --------------> 하둡 이관 프로젝트가 많아
데이터 이행
예전 버전 오라클 ----------> 새로운 버전 오라클
데이터이행
- 상호관련 서브 쿼리
- 다른 시간대에서의 데이터 관리
---------------------
↑
우리나라와 시간대가 다른 나라에도 DB가 있고 거기에 데이터를 입력하고 불러와서 봐야할 때 알아야하는 기능
- 5장 목차
- 날짜를 확인하는 오라클 키워드
- 날짜형 데이터 타입
- 날짜형 함수
- 날짜를 확인하는 오라클 키워드
우리가 여태 알았던 것은 SLECT SYSDATE FROM DUAL ;
SELECT CURRENT_DATE FROM DUAL;
- 오늘날짜 + 현재시간 확인하는 방법
SELECT SYSTIMESTAMP FROM DUAL;
2017/03/20 13:53:12 +09:00
영국의 그리니치 천문대가 0시라고하면 우리나라는 아침 9시 인것이다
- 우리나라 서울의 시간대를 확인하려면
select distinct tzame
from v$timezone_names
where lower(tzname) like '%seoul%';
Asia/Seoul
- 영국 런던의 시간대를 알아내기 위한 나라 도시명 확인
select distinct tzname
from v$timezone_names
where lower(tzname) like '%london%';
Europe/London
- 영국이 지금 몇시인지 확인하고 싶다면?
select localtimestamp from dual;
문제489. 현재 세션의 시간대를 다시 영국/런던으로 변경하고
아래 두개의 쿼리 결과를 확인하시오
select systimestamp from dual; -------> 시스템의 시간
select localtimestamp from dual; ---------> 데이터 베이스의 시간
5.2 날짜형 data type
- date
- timestamp
- timestamp with timezone
- timestamp with local time zone
cretae table date_table
(
hiredate1 date,
hiredate2 timestamp,
hiredate3 timestamp with time zone
hiredate4 timestamp with local time zone );
insert into date_table
values (sysdate, systimestamp, localtimestamp, localtimestamp);
select * from date_table;
- months_between
- add_months
- next_day
- last_day
- extract
- to_yminterval
- to_dsinterval
- to_timestamp
select extract( year from sysdate) from dual;
문제490. 이름, 입사한 년도를 출력하시오 !
select ename, extract( year from hiredate) from emp;
문제491. 입사한 년도, 입사한 년도별 토탈 월급을 출력하시오 !
select extract( year from hiredate), sum(sal)
from EMP
group by extract( year from hiredate);
문제492. ALLEN이 입사한 달은 출력하시오
select extract( MONTH from hiredate)
FROM EMP
WHERE ENAME = 'ALLEN';
문제493. 11일에 입사한 사원들의 이름과 입사일을 출력하시오
select ENAME , from hiredate
FROM EMP
WHERE extract( DAY from hiredate) = 11;
문제494. 오늘부터 100달 뒤의 날짜를 출력하시오!
SELECT ADD_MONTHS(SYSDATE,100)
FROM DUAL;
문제495. 오늘부터 1년 2개월 후의 날짜를 출력하시오 !
SELECT ADD_MONTHS(SYSDATE,14)
FROM DUAL;
SELECT SYSDATE + TO_YMINTERVAL('01-02')
FROM DUAL;
문제496. 오늘부터 100일 10시간이 지난후의 날짜를 출력하시오 !
SELECT SYSDATE + TO_DSINTERVAL('100 10:00:00')
FROM DUAL;
5.3 날짜형 함수
- months_between
- add_months
- next_day
- last_day
- extract
- to_yminterval
- to_dsinterval
- to_timestamp
2017-03-20 오후 2:49
문제497. 아까 2시 30분쯩에 emp 테이블의 king의 데이터가 무엇이었는지 확인하시오
select * from emp as of timestamp to_timestamp('2017/03/20 14:30:00', 'RRRR/MM/DD HH24:MI:SS')
WHERE ENAME = 'KING';
■ 6장. 서브쿼리에서의 데이터 검색
- 6장 목차
- MULTIPLE COLUMN SUBQUERY
- SCALAR SUBQUERY
- 상호관련 서브쿼리 - SELECT 문
- 상호관련 서브쿼리 DML 문
- EXISTS 문 NOT EXISTS 문
- WITH 절
6.1 MULTIPEL COLUMN SUBQUERY
- SUBQUERY 의 종류 3가지
- SINGLE ROW SUBQUERY
- MULTIPEL ROW SUBQUERY
- MUTIPEL COLUMN SUBQUERY
- PAIR WISE 방식
- NON PAIR WISE 방식
문제498 . 직업이 SALESMAN 인사원들과 월급이 같은 사원들의 이름과 월급을 출력하시오 !
SELECT ENAME, SAL
FROM EMP
WHERE SAL IN ( SELECT SAL
FROM EMP
WHERE JOB = 'SALESMAN');
문제499. 직업이 SALESMAN 인 사원들과 월급도 같고 그리고 커미션도 같은 사원들의 이름과 월급과 커미션을 출력하시오
UPDATE EMP
SET SAL = 2500,
WHERE ENAME = 'SMITH';
UPDATE EMP
SET COMM = 1400
WHERE ENAME = 'SMITH';
[ NON PAIR WISE]
SELECT ENAME, SAL
FROM EMP
WHERE SAL IN ( SELECT SAL
FROM EMP
WHERE JOB = 'SALESMAN')
AND COMM IN ( SELECT COMM
FROM EMP
WHERE JOB = 'SALESMAN');
[PAIR WISE]
SELECE ENAME, SAL ,COMM
FROM EMP
WHERE (SAL, COMM) IN (SELECT SAL,COMM
FROM EMP
WHERE JOB = 'SALESMAN');
설명 : PAIRWISE 방식은 그 월급에 그 커미션이어야 데이터가 검색이 되는데
NON PAIR WISE 방식은 직업이 SALESMAN 인 사원들의 월급중에서 하나만 같기만 함녀 되고 커미션하나만 같아도
출력되는 방식이다.
PAIR WISE
3200 300
2500 500
2500 1400
3000
PAIR WISE 방식 VS NON PAIR WISE 방식
(1000, 250) (1000, 250)
(2000, 100) (1000, 100)
(4000, 300) (1000, 300)
(2400, 150) (1000, 150)
(1000, 250)
(1000, 100)
(1000, 100)
(1000, 300)
:
:
문제500. 나이가 30대인 학생들과 전공이 같고 통신사가 같은 학생들의 이름과 나이와 전공을 출력하시오
[NON PAIR WISE]
SELECT ENAME,AGE,MAJOR
FROM EMP2
WHERE TELECOM IN (SELECT TELECOM
FROM EMP2
WHERE AGE >=30)
AND MAJOR IN ( SELECT MAJOR
FROM EMP2
WHERE AGE >=30);
[PAIR WISE] (...... , .....) 이로케 세트로 같아야 해영!!!
SELECT ENAME,AGE,MAJOR
FROM EMP2
WHERE (TELECOM,MAJOR) IN (SELECT TELECOM ,MAJOR
FROM EMP2
WHERE AGE >=30);
6.1 SCALAR SUBQUERY
- SELECT 절에서 SUBQUERY를 쓸 수 있는 절
SELECT -- SCALAR SUBQUERY
FROM -- IN LINE VIEW
WHERE -- SUBQUERY
GROUP BY --X
HAVING ---SUBQUIERY
ORDER BY --- SCALAR SUBQUERY
- SCALAR SUBQUERY의 특징 ? 딱 하나의 값만 리턴한다
문제501. 사원이름, 월급, 사원 테이블의 최대월급,
사원 테이블의 최소월급,
사원 테이블의 토탈 월급을 출력하시오.
- 분석함수 사용하지 않은 SQL
SELECT ENAME, SAL, (SELECT MAX(SAL) FROM EMP ) 최대,
(SELECT MIN(SAL) FROM EMP ) 최소,
(SELECT SUM(SAL) FROM EMP ) 토탈
FROM EMP; --29개 블락읽음
- 분석함수 사용한 SQL
SELECT ENAME, SAL , MAX(SAL) OVER (),
MIN(SAL) OVER (),
SUM(SAL) OVER()
FROM EMP; -- 7개 블락 읽음
읽은 블락 확인 TRACE METHOD
PLAN 페이지 열기
OPTION -> TRACE METHOD -> LOCAL FILE (CHECK)
하나의 SQL만 넣고 F9 하하호호
EMP테이블 4번셀렉트
SQL 튜닝
SELECT ENAME, SAL , (SELECT MAX(SAL),MIN(SAL),SUM(SAL) FROM EMP)
FROM EMP;
SELECT ENAME,SAL (SELECT MAX(SAL)||MIN(SAL)||SUM(SAL) FROM EMP)
FROM EMP;
SELECT ENAME, SUBSTR(TOTAL,1,4) AS "MAX(SAL)" , SUBSTR(TOTAL,5,7) AS "MIN(SAL)", SUBSTR(TOTAL,8) AS "SUM(SAL)"
FROM (
SELECT ENAME,SAL, (SELECT MAX(SAL)||MIN(SAL)||SUM(SAL) FROM EMP) AS TOTAL
FROM EMP);
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.000 0.000 0 15 0 14
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.000 0.000 0 15 0 14
문제502. 아래의 결과에서 최대월급, 최소월급 , 토탈월급 만 살라대려면 어떻게 해야하는가?
SELECT ENAME,
SUBSTR(TOTAL, 1, 4) AS "MAX(SAL)" ,
SUBSTR(TOTAL, 5, 3) AS "MIN(SAL)",
SUBSTR(TOTAL, 8) AS "SUM(SAL)"
FROM (SELECT ENAME,
SAL,
(SELECT MAX(SAL)||MIN(SAL)||SUM(SAL)
FROM EMP) AS TOTAL
FROM EMP); --15개 블락 읽음
LPAD를 이용해서 좀더 쉽게 잘라보자!
select ename,
substr(total, 1, 10) as 최대,
substr(total, 11, 10) as 최소,
substr(total, 21, 10) as 토탈
from(select ename,
sal,
(select LPAD(max(sal), 10) || LPAD(min(sal), 10) || LPAD(sum(sal), 10)
from emp) as total
from emp); -- 15개 블락 읽음
문제503. 이름,나이, 우리반 최대나이,
우리반 최소나이,
우리반 평균나이를 출력하시오!
SELECT ENAME, AGE, MAX(AGE) OVER (),
MIN(AGE) OVER (),
AVG(AGE) OVER ()
FROM EMP2; ---3블락
select ename,
substr(total, 1, 10) as 최대,
substr(total, 11, 10) as 최소,
substr(total, 21, 10) as 토탈
from(select ename,
AGE,
(select LPAD(max(AGE), 10) || LPAD(min(AGE), 10) || LPAD(sum(AGE), 10)
from emp2) as total
from emp2); --7 블락
■ 6장. 서브쿼리에서의 데이터 검색
- 6장 목차
- MULTIPLE COLUMN SUBQUERY
- SCALAR SUBQUERY
- 상호관련 서브쿼리 - SELECT 문
- 상호관련 서브쿼리 DML 문
- EXISTS 문 NOT EXISTS 문
- WITH 절
6.3 상호관련 서브쿼리 SELECT 문장
문제504. 이름, 월급, 부서번호, 부서위치를 출력하시오
(조인으로 수행)
SELECT E.ENAME, E.SAL, E.DEPTNO, D.LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;
문제505. 위의 결과를 SCALAR SUBQUERY 를 써서 수행하시오!
SELECT ENAME, SAL, DEPTNO, (SELECT LOC
FROM DEPT
WHERE DEPTNO = E.DEPTNO ) LOC
FROM EMP E; -- 11블락
-- 스칼라 서브 쿼리
SELECT ENAME, SAL, DEPTNO, (SELECT LOC
FROM DEPT
WHERE DEPTNO = E.DEPTNO ) LOC
FROM EMP E; --13블락
문제506. 아래의 SQL을 분석함수를 사용하지 않고 SCALAR SUBQUERY 로 튜닝하시오
SELECT ENAME,SAL,DEPTNO, (SELECT MAX(SAL) FROM EMP WHERE DEPTNO=E.DEPTNO ) 최대,
(SELECT MIN(SAL) FROM EMP WHERE DEPTNO=E.DEPTNO ) 최소,
(SELECT SUM(SAL) FROM EMP WHERE DEPTNO=E.DEPTNO ) 토탈,
ROUND((SELECT AVG(SAL) FROM EMP WHERE DEPTNO=E.DEPTNO ),2)평균
FROM EMP E
ORDER BY DEPTNO; --91블락
-- 분석함수에서 못하는 where절을 쓸 수있어
SELECT e.ename,
sal,
e.deptno,
substr(ap,1,10) "max(sal)",
substr(ap,11,10) "min(sal),",
substr(ap,21,10) "sum(sal)",
substr(ap,31,10) "avg(sal)"
FROM emp e,
(SELECT deptno,
(SELECT lpad(max(sal), 10)|| lpad(min(sal), 10)|| lpad(sum(sal), 10)|| lpad(avg(sal), 10)
FROM emp d
WHERE d.deptno = s.deptno
GROUP BY deptno
) ap
FROM emp s
GROUP BY deptno
) a
WHERE e.deptno = a.deptno
ORDER BY deptno ; -- 35블락 -- 35블락
SELECT ENAME,SAL,DEPTNO, MAX(SAL) over (partition by deptno) 최대,
MIN(SAL) over (partition by deptno) 최소,
SUM(SAL) over (partition by deptno) 토탈,
round(AVG(SAL) over (partition by deptno),2)평균
FROM EMP E
ORDER BY DEPTNO; --7블락
SQL 튜너 -- 굵직한 사례들을 암기
■ 어제까지 배운 내용 복습
- flashback version Query
"특정 테이블의 data가 어떻게 변경되어왔는 이력 정보 "
- merge문으로 새로운 data를 입력하는 문자
- 날짜형 데이터 타입
- date
- timestamp
- timestamp with timezone
- timestamp with local time zone
5.3 날짜형 함수
- months_between
- add_months
- next_day
- last_day
- extract
- to_yminterval
- to_dsinterval
- to_timestamp
■ 6장. 서브쿼리에서의 데이터 검색
- 6장 목차
- MULTIPLE COLUMN SUBQUERY
- SUBQUERY 의 종류 3가지
- SINGLE ROW SUBQUERY
- MULTIPEL ROW SUBQUERY
- MUTIPEL COLUMN SUBQUERY
- - PAIR WISE 방식 ===> 오라클에 지원하는 방식
- - NON PAIR WISE 방식 ===> 오라클 , non 오라클 db지원
- SCALAR SUBQUERY
- 상호관련 서브쿼리 - SELECT 문
- 상호관련 서브쿼리 DML 문
- EXISTS 문 NOT EXISTS 문
- WITH 절
'SQL 심화' 카테고리의 다른 글
| 6장. 서브쿼리에서의 데이터 검색 (0) | 2017.09.07 |
|---|---|
| 4장 대형 데이터 집합 조작 (0) | 2017.09.07 |
| 3장 데이터 딕셔너리를 통해서 객체 관리 (0) | 2017.09.07 |
| 2장 스키마 객체 관리 (0) | 2017.09.07 |
| 1장 유저 엑세스 제어 (0) | 2017.09.07 |
