MS-SQL Veiw

begin tran
--view
/*
보다, 실제테이블은 아니며, 실제 테이블의 특정 부분을 보기 위한
구문을 담고있는 객체.
보안 , 편리성
view를수정할경우 기본값도 수정됨
*/

create view view01
as
select empno, ename, sal
from emp

select * from view01

create view view02
as
select empno, emp.deptno, dname
from emp, dept
where emp.deptno = dept.deptno
 and emp.deptno in(10,20)
 
select* from view02

update view01
set sal = 9999
where ename = 'smith'

select * from emp

insert view01 values(8888,'tom',500)

alter view view01 with encryption
as
select empno, ename, sal
from emp

/*
view03
직업별 평균 월급을 담는 뷰 생성.
*/

alter view view01
as
select job , avg(sal) as [avgsal]
from emp
group by job

update view01
set avgsal = 3000
where job = 'clerk'

/*
view04
사원이름, 월급을 담는 뷰 생성 단, 월급이 높은 사람부터 출력(내림차순)
*/

--order by는 바로 view에 담길수없다.
alter view view02
as
select top 50 ename, sal
from emp
order by sal desc

/*
view05
사원번호, 이름, 월급을 담는 뷰, 단 월급이 2000이상
*/

alter view view02
as
select empno,ename,sal
from emp
where sal > 2000

insert view02 values(7777,'tom',2500)
insert view02 values(8888,'kim',1500)
insert view02 values(9999,'lee',500)

select * from emp

select * from view02

delete from emp
where empno in(7777,8888,9999)