Day 9:(9)视图实训_参考答案

-- 视图实训_参考答案




-- 1. 创建emp61表（eid int,ename varchar(10),salary decimal(10,2)）, 插入数据


create table emp61(eid int,ename char(10),salary decimal(10,2))
insert into emp61 values(1,'aaa1',1900)
insert into emp61 values(2,'aaa2',2500)
insert into emp61 values(3,'aaa3',3600)
insert into emp61 values(4,'aaa4',4800)
insert into emp61 values(5,'aaa5',1000)
select * from emp61




-- 2. 创建视图v_sal，使插入或修改的工资要大于2000,并验证 
create view v_sal as select * from emp61 where salary>2000 with check option
insert into v_sal values(6,'aaa6',1800)
update v_sal set salary=1800 where eid=2
select * from v_sal




-- 3. 修改视图v_sal，使输入的工资大于平均工资
alter view v_sal as select * from emp61 where (salary>(select avg(salary) from emp61)) with check option
select avg(salary) from emp61
insert into v_sal values(6,'aaa6',6800)


-- 4. 查看视图v_sal的信息、视图定义、视图相关性，对视图v_salary进行定义加密，再次查看视图v_sal的信息、视图定义、视图相关性
alter view v_sal with encryption as select * from emp61 
where (salary>(select avg(salary) from emp61)) with check option
sp_help v_sal
sp_helptext v_sal
sp_depends v_sal


-- 5. 在northwind数据库里，创建视图v_ps查询显示产品名称和供应商名称
create view v_ps as select products.productname,suppliers.companyname from products,suppliers 
where products.supplierid=suppliers.supplierid


-- 6. 修改视图v_ps 查询显示国家为'USA'的供应商名称、所在国家和产品名称
alter view v_ps as select products.productname,suppliers.companyname,suppliers.country from products,suppliers
where products.supplierid=suppliers.supplierid and suppliers.country='USA'
select * from v_ps


-- 7. 删除视图v_ps
drop view v_ps
select * from v_ps


-- 8. 修改视图v_sal为v_salary
sp_rename v_sal,v_salary 
select * from v_salary


-- 9. 查看视图v_salary的信息、视图定义、视图相关性
sp_help v_salary
sp_helptext v_salary
sp_depends v_salary