SQL VIEW 생성하기

view 의 종류 2가지 

	단순뷰	복합뷰
테이블의 갯수	1개	2개이상
함수포함여부	포함안함	포함
데이터 갱신여부	갱신됨	갱신안될 수도 있다.

VIEW 의 장점

1. 보안상 민감한 데이터를 노출 시키고 싶지 않을때
2. 복잡한 쿼리문을 간단하게 쿼리하고 싶을때

---

emp57 emp 테이블에서 사원번호, 이름, 월급을 가져온 데이터로 view 생성하기

create or replace view emp57
as
select empno, ename, sal
from emp ;

emp57 view 의 데이터를 이름이 scott 인 사원의 월급을 0 로 수정

update emp57
set sal = 0
where ename = 'SCOTT';

 

*****view 를 수정하게 되면 table 도 수정이 된다.

(단순 view)

직업이 salesmane 인 사원들 이름, 월급, 직업, 부서번호를 view 로 생성하시오

create or replace view emp442
as
select ename, sal, job, deptno
from emp
where job = 'SALESMAN';

 

(복합 view , 함수 이용(0) )

emp 테이블에서 DEPTNO, ename, sal , 부서번호별로 월급이 높은순으로 정렬된 순위를 뽑은 데이터를 VIEW로 생성 하시오 

create or replace view emp498
as
select deptno, ename , sal, rank() over ( partition by deptno
                                          order by sal desc )ranking
from emp;

 

rank 데이터 분석 함수가 포함 되어있으므로 → 복합 view 이다.
view를 만들때 함수가 있다면 반드시 컬럼 별칭을써주어야 한다.

---

만든 view  (emp498) 에서 순위가 1위인사원들만 출력

select *
from emp498
where ranking = 1;

 

 

문제 market 테이블에서 시군구명을 출력하시오 (중복제거)

select distinct(시군구명)
from market;

문제 시군구명,  시군구명별 스타벅스 매장갯수 출력( 제일 많은 순대로 ) 

select 시군구명, count(*)
from market
where 상호명 like '%스타벅스%'
group by 시군구명
order by 2 desc;

문제  위의 쿼리를 이용하여 서울시의 카페매장의 상호명, 상호명별 건수를 출력하는데 건수가 높은 순으로 출력 하시오

select 상호명, count(*)
from (
          select *
          from market
         where 상권업종중분류명 like '%카페%'
     )
group by 상호명
order by 2 desc ;

 

문제 504 강남구의 가장많은 업종이 무엇인지 출력하시오
(시군구명, 상권업종소분류명, 시군구명별 , 상권업종분류명별 건수 출력) 건수 높은것부터

select 시군구명, 상권업종소분류명, count(*)
from market
where 시군구명 = '강남구'
group by 시군구명, 상권업종소분류명
order by 3 desc ;

 

 

(복합 view , table 갯수 이용 하여 만들기)

문제 506 사원이름, 부서위치를 출력하는 view506 생성하시오 (두테이블 조인 하여 VIEW ) 

create or replace view emp506
as
select e.ename, d.loc
from emp e, dept d
where e.deptno = d.deptno;

 

→ 테이블의 갯수가 2개 이므로 복합 view 입니다.

 

만든 emp506 번 view 로 부서위치가 dallas 인 사원들 이름, 위치를 출력하시오

select ename,loc
from emp506
where loc = 'DALLAS';

emp506 의 데이터를 수정

update emp506
set loc = 'CHICAHO'
where ename = 'KING';

 

→ 키- 보존된것이 나닌 테이블로 대응한 열을 수정할 수 없다 → 라는 오류 메세지가 뜬다. (18c)

그러나 오라클  21 c 에서는 수행이 된다~ 18 c 에서는 안됨 

다만 수행 된다는것은 조인된 deptno 를 기준으로 king 이 deptno 가 10 번 이기때문에

king 만 변경되는것이 아닌 deptno= 10 번인 loc 가 모두 newyork → chicago 로 변경된다는 의미이다.

---

직업과, 직업별 총월급을 출력하시오 

select job, sum(sal)as 토탈
from emp
group by job ;

위의 결과를 view 생성하시오

create or replace view emp510
as
select job, sum(sal)as 토탈
from emp
group by job ;

 

→ 이 또한 복합 view

emp510 을 수정

update emp510
set 토탈 = 9000
where job = 'SALESMAN';

 

오류메세지 : VIEW 에대한 데이터 조작이 부적합 → 변경 할 수가 없다.

 

내가 그동안 만든 VIEW 가 무엇이 있는지 조회

SELECT * 
FROM USER_VIEWS;

방금 만든 emp510 VIEW 삭제하는 방법

drop view emp510;

 

table 은 그대로이고 view 만 삭제 된다.

---

emp 에서 deptno, deptno 별 평균월급 데이터로 뷰를 만드시오 

create or replace view emp_avg_sal
as
select deptno, avg(sal) as 평균월급
from emp
group by deptno ;

 

부서번호, 이름, 월급, 자기가 속한 부서번호의 평균월급 출력 (튜닝전)

select e.deptno, e.ename, e.sal, round(v.평균월급) as 평균월급
from emp e, emp_avg_sal v
where e.deptno = v.deptno;

-> 테이블과 뷰를 조인하여 출력하였다. 

문제 515 (튜닝후) 데이터 분석함수 이용

select deptno, ename, sal, round(avg(sal) over (partition by deptno)) as 부서평균
from emp;

 

데이터 분석함수인 avg(sal) over ( partition by deptno) 를 사용하여 table 을 emp 하나만 이용하였다.

 

-> 조인이나 서브쿼리보다는 먼저 데이터 분석함수를 이용 해 보아야한다.