from spj except

select distinct jno from spj

where spj.sno in ( )

select distinct sno from s

where s.city='天津'

5.查询这样的工程：供给该工程的零件P1的平均供应量大于供给工程J1的任何一种零件的最大供应量。

select jno from spj where pno='p1' group by jno having avg(qty)>

(select max(qty) from spj

where spj.jno='j1')

6． 针对实验一创建的Student数据库进行下面的数据查询(10分，每小题5分)

(1) 求不选修C语言课程的学生学号。

select sno from s except select sno from sc where cno=

(select cno from c

where cname='c语言')

(2) 求这样的学生姓名：该学生选修了全部课程并且其中一门课在90分以上。

17

select sname from s where sno in (select sno from sc where sno in (

select sno from s

where not exists (select * from c

where not exists (select * from sc

where sno=s.sno and cno=c.cno)

)

)

group by sno

having max(grade)>90 )

4. 实验3:视图操作和安全性控制

4.1. 目的和要求

1． 掌握使用图形用户界面和SQL语言创建，操作和删除视图的方法。

2． 掌握SQL Server中的安全性相关的登录名，角色以及用户的创建以及使用方法。3． 学会使用T-SQL语句对数据库和表操作的灵活控制功能。

4.2. 实验准备

1． 了解与视图相关的各种SQL语句。

18

2． 了解登录名，角色以及用户的创建以及使用方法。

3． 了解T-SQL语句在对数据库和表的控制权限相关命令(GRANT/REVOKE)的用法。

4.3. 实验内容

1． 在Student数据库中，利用图形用户界面，创建一个选修了数据库课程并且是1986年

出生的学生的视图，视图中包括学号，性别，成绩这三个信息。(5分)

操作工程：右击“视图”，将相关的表添加进去，然后指定视图所依附的表间的连接关系，最后指出视图的输出（即视图的各个属性列），操作界面如下图所示：

2． 用两种不同的SQL语句创建课本128页第11题中要求的视图（视图名：V_SPJ）（6分，

每种方法3分)。

方法一：create view V_SP as

select sno,pno,qty from spj

where spj.jno in as

(select jno from j

where j.jname='三建')

方法二：create view V_SPJ

19

select sno,pno,qty from spj,j

where j.jno=spj.jno and

j.jname='三建'

3． 用SQL语句完成课本128页第11题中对视图V_SPJ的查询（4分，每小题2分)。

语句：select pno,sum(qty) 总量 from V_SPJ group by pno 运行界面：

语句：

select * from V_SPJ where sno='s1' 运行界面：

4． 用T-SQL语句操作视图的数据。(15分，每题5分)

（1） 给视图V_SPJ中增加一条数据(基本表中有插入的数据即可)。

insert into V_SPJ

values('s5','j3',900)

说明：必须将主键约束去掉并且允许为空值以后才可插入

（2） 修改视图V_SPJ中的任意一条数据的供应数量。

update V_SPJ set qty=111

20