北理工-SQLserver作业题及答案

答： SELECT Sbirth,Sname FROM Student where Sbrith < (SELECT Min(Sbirth) FROM Student WHERE Sdept=’信息系’) AND Sdept <>’信息系’; 9） 删除 “选课表”中的所有记录。 答： Delete From 选课表; 10） 查询所有课程的任课教师，没有教师教授的课程对应的教师为空。 答： Select * from Course a left join 教师授课表 b on a.Cno = b.Cno; 11） 按授课教师分组查询“高等数学”课程的最低分、最高分和平均分。 答： Select Tno,min(Grade),max(Grade),avg(Grade) from 学生选课 a left join Course b on a.Cno=bCno Where b.Cname=’高等数学’ group by Tno; 12） 建立如下约束：选修课程的总学分不到 40 分的学生不能选修“综合实践”课程。 （方法不限） 答：
alter table 学生选课
WITH NOCHECK
add constraint Grade40
check
(课程号 = '综合实践' AND Sno in (select Sno from 学生选课 WEHRE Sum(Grade) > 40)); 13） 创建如下函数：输入教师工号 Tno，课程号 Cno，输出选修了教师 Tno 所授课程 Cno 的学生信息，包括姓名，性别，学号，所在院系。 答：
3、什么是连接？连接分为哪几种？ 答：连接是通过连接运算符可以实现多个表查询。连接是关系数据库模型的主要特点， 也是它区别于其它类型数据库管理系统的一个标志。 分为：内连接、外连接、交叉连接。 4. 创建视图需要注意哪些原则？ 答： 1.必须拥有创建视图的权限 2.只能在当前数据库中创建视图 3.一个视图最多引用 1024 列 4.视图名不能与表重名 5.视图中列的名称一般继承其基表的列的名称 6.可以将视图创建在其它视图上 7.不能在视图上创建全文索引 8.不能在临时表上创建视图，也不能创建临时视图 9.定义视图的查询语句不能包含 order by 和 into 的关键字 5. 简述创建索引的时候，在哪些列上可以创建索引，在哪些列上不能创建索引。 答： 什么字段应该建立索引
在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构； 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度； 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的； 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序 查询时间； 在经常使用在 WHERE 子句中的列上面创建索引，加快条件的判断速度。
大作业 3 北京理工大学继续教育学院夜大
2014 年下半年
专业层次： 计算机科学与技术 高起本
《SQLServer》期末大作业（A 卷）
（卷面满分 100 分）
班级：
姓名：
学号：
成绩
一、简答（每题 5 分） 1. 怎样结束事物？
答：用户发出 Commit 或 Rollback 操作
2. 根据处理特性，SQL Server 将游标分为哪几种？特点是什么？
) ON [PRIMARY]
课程表： CREATE TABLE [dbo].[Course]( [Cno] [nchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL, [CName] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL, [Cpno] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL, [Ccredit] [numeric](18, 2) NULL CONSTRAINT [DF_Course_Course_Score] DEFAULT ((6)), CONSTRAINT [PK_Course] PRIMARY KEY CLUSTERED ( [Course_Code] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED]
INDEX 课程号_Index
ON 课程表(课程号)
[WITH FILLFACTOR=x] 7） 创建视图“view_1”,该视图包括计算机系教师的信息。
答： CREATE VIEW [dbo]. view_1 AS SELECT * FROM 教师表 WHERE EXISTS(SELECT * FROM Orders WHERE Tdept = ‘计算机系’) 8） 查询比信息系的学生年龄都小的其他系的学生年龄和姓名。
GO 5)、建立一个触发器，当删除学生表的记录时，删除该学生选修的所有课程。 答：
create trigger Studentdelete
on Student
after deleFra Baidu biblioteke
as
delete from 学生选课表
where Sno in
(select Sno from deleted) 6） 在“课程表”的“课程号”列上创建唯一聚集索引。 答：
什么字段不适合建立索引
在查询中很少使用或者参考的列 只有很少数据值的列 ，例如性别 更新频繁，检索不频繁的列 大数据对象类型的列
二、操作题（每题 5 分） 有一个数据库，数据库中包括如下表： (a) “学生”表 Student 由学号（Sno）、姓名（Sname）、性别（Ssex）、年龄（Sbirth）、 所在系（Sdept）五个属性组成，记为：Student(Sno,Sname,Ssex,Sage,Sdept) ，Sno 为关键字。 (b) “课程”表 Course 由课程号（Cno）、课程名（Cname）、先修课号（Cpno）、 学 分（Ccredit）四个属性组成，可记为： Course(Cno,Cname,Cpno,Ccredit) Cno 为关键字。 (c)“学生选课”表 SC 由学号（Sno）、课程号（Cno）、工号（Tno）、学期（semester）、 成绩（Grade）、年份（year），可以记为 SC（Sno、Cno、Tno、semester、Grade、year） ，其中(Sno,Cno) 为关键字。 （d）教师表工号（Tno）、姓名（Tname）、性别（Tsex）、年龄（Tbirth）、所在系 （Tdept） （e）教师授课表：工号（Tno）、课程号（Cno） 完成下列操作： 1） 创建教师表、课程表和教师授课表。 答： 学生表：
CREATE TABLE [dbo].[ Student]( [Sno] [nchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL, [Sname] [nchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL, [Ssex] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL, [Sbirth] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL, [InDate] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL, [Sdept] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL, CONSTRAINT [PK_Studen] PRIMARY KEY CLUSTERED ( [Sno] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
AS DECLARE @Avg float SELECT @Avg=AVG(a.成绩) FROM 学生选课表 a left join
学生表 b on a.Sno = b.Sno WHERE a.Sdept = '计算机系' print '计算机系学生的平均分：'+convert(varchar(5),@Avg)
删除外键：
alter table [dbo].[ 教师授课表] drop constraint fk_fID1 alter table [dbo].[ 教师授课表] drop constraint fk_fID2
3）建立约束：课程表中学分介于 2 和 4 之间。 答： alter table [dbo].[课程表] add constraint CK_Ccredit check(Ccredit between 2 and 4) 4)创建存储过程，查询某个系所有学生的平均成绩。 答： CREATE PROCEDURE proc_stu_avg
答：SQL Server 将游标分为 Transact_SQL 游标、API 游标、客户游标。
（1）Transact_SQL 游标
Transact_SQL 游标是由 DECLARE CURSOR 语法定义、主要用在 Transact_SQL 脚本、存储过程和触 发器中。Transact_SQL 游标主要用在服务器上，由从客户端发送给服务器的 Transact_SQL 语句 或是批处理、存储过程、触发器中的 Transact_SQL 进行管理。 Transact_SQL 游标不支持提取数 据块或多行数据。
（3）客户游标
客户游标主要是当在客户机上缓存结果集时才使用。在客户游标中，有一个缺省的结果集被用来在 客户机上缓存整个结果集。客户游标仅支持静态游标而非动态游标。由于服务器游标并不支持所有 的 Transact-SQL 语句或批处理，所以客户游标常常仅被用作服务器游标的辅助。因为在一般情况
下，服务器游标能支持绝大多数的游标操作。由于 API 游标和 Transact-SQL 游标使用在服务器端， 所以被称为服务器游标，也被称为后台游标，而客户端游标被称为前台游标。在本章中我们主要讲 述服务器（后台）游标
2)在教师表、课程表和教师授课表上建立主外键约束，然后删除外键约束。
教师表 ：
添加主键
alter table [dbo].[教师表] add constraint pk_id primary key (Tno)
课程表 ：
添加主键
alter table [dbo].[课程表] add constraint pk_id primary key (Cno)
（2）API 游标
API 游标支持在 OLE DB， ODBC 以及 DB_library 中使用游标函数，主要用在服务器上。每一次客 户端应用程序调用 API 游标函数，MS SQL SEVER 的 OLE DB 提供者、ODBC 驱动器或 DB_library 的 动态链接库（DLL） 都会将这些客户请求传送给服务器以对 API 游标进行处理。
教师授课表： CREATE TABLE [dbo].[TeacherCourse]( [Tno] [nchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL, [Cno] [nchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL, CONSTRAINT [PK_TC] PRIMARY KEY CLUSTERED ( [Tno] ASC, [Cno] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
教师授课表 ：
添加外键
alter table [dbo].[ 教师授课 表] add constraint fk_fID1 foreign key (Tno) references [dbo]. [教师表]([Tno]) alter table [dbo].[ 教师授课 表] add constraint fk_fID2 foreign key (Cno) references [dbo]. [课程表]([Cno])