索引和视图实验报告

7-14 通过v_final和v_course视图查询所有学生的学号、姓名和已修必修课的总学分。

USE teaching

SELECT studentno AS '学号',sname AS '姓名',SUM(credit) AS '必修课总学分' FROM v_final,v_course

WHERE v_final.courseno=v_course.courseno GROUP BY studentno,sname

7-15 利用sysobjects和syscomments两个系统表查看v_avg视图的名称、ID和定义视图的文本信息。

USE teaching

SELECT sysobjects.name,sysobjects.id,syscomments.text FROM sysobjects,syscomments

WHERE sysobjects.name='v_avg' AND sysobjects.type='V' AND sysobjects.id=syscomments.id

7-18 通过视图V_course向基表course中插入数据（‘c05129’，‘数据库编程’，‘必修’，64,4）。

Use teaching

Go

INSERT INTO v_course

VALUES('c05129','数据库编程','必修',64,4)

Go

SELECT *FROM course

7-21 通过视图V_course将基表course课程号为c05129的课程名称修改为‘数据库应用与开发’。

USE teaching GO

UPDATE v_course

SET cname='数据库应用与开发' WHERE courseno='c05129' GO

SELECT * FROM course

7-23 通过视图v_final将基本表student和score中学号为'0925121107'，选修的课程号为'c05129'的学生姓名修改为'李静'，期末成绩修改为分。

USE teaching GO

UPDATE v_final

SET sname='李静',final=60

WHERE studentno='0925121107' AND courseno='c05109'

7-24 通过视图v_sex删除基表student中学号为0938211038的学生记录。

USE teaching

Go

Delect from v_sex

Where studentno='0938211038'

GO

SELECT * FROM student

7-25 通过视图v_course删除基表course中课程号为c05109的课程记录

USE teaching

GO

DELECT FROM v_course WHERE courseno='c05109'