SQLServer数据库应用与开发：第8章上机

代码及解释

8.1

create nonclustered index idx_cname on course(cname)

8.2

if exists(select name from sysindexes where name='uq_stu')
	drop index student.uq_stu
go
create unique index uq_stu on student(studentno,classno)

8.3

alter index uq_stu on student
rebuild
with(ignore_dup_key=on,fillfactor=80)

8.4:

if exists(select name from sys.views where name='v_avgstu')
	drop view v_avgstu
go
create view v_avgstu
as
select top 10 student.studentno,sname,avg(final) as '平均分'
from student,score
where student.studentno=score.studentno
group by student.studentno,sname
order by '平均分' desc

go
select *
from v_avgstu

解释：
1.不建议在视图中使用排序，要用排序必须有top语句，并且top语句不能使用100%查询（排序无效）。所以建议直接在使用视图的时候排序

8.5

create view v_teacher
as
select *
from teacher
go
alter view v_teacher
as
select *
from teacher
with check option

8.6

insert into v_teacher
values('to5039','赵欣悦','计算机应用','讲师','计算机学院')
insert into v_teacher
values('t06018','李承','机械制造','副教授','机械学院')
go

select *
from teacher

8.7

update v_teacher
set prof='副教授'
where teacherno='to5039'
go
select *
from teacher