SQL SERVER 2000实用教程习题及实验参考答案

习题参考答案

第一章

1、SQL Server 2000有七种不同的可用版本：企业版、企业评估版、标准版、开发版、个人版、Windows CE版和桌面引擎版。

2、Microsoft Windows NT Server 4.0、Microsoft Windows NT Server 4.0 企业版、Windows 2000 Server、Windows 2000 Advanced Server 和 Windows 2000 Data Center Server

第二章

1、C 2、A

3、Exec sp_addtype New_str,'Varchar(12)', 'Not Null '

第三章

1． 数据文件、事务日志、mdf、ldf。 2． CREATE DATABASE。 3． SP_HELPDB.

4． ALTER DATABASE.。

5． DBCC SHRINKFILE、DBCC SHRINKDATABASE。 6． SP_DBOPTION。 7． DROP DATABASE。 8． SP_DETACH。

9． 带FOR ATTACH子句的CREATE DATABASE语句。 10． CREATE TABLE。 11． ALTER TABLE。 12． DROP TABLE。 13． INSERT。 14． UPDATE。 15． DELETE。

16．约束定义了关于允许什么数据进入数据库的规则。约束有五种类型，分别是 PRIMARY KEY约束、FOREIGN KEY约束、UNION约束、CHECK约束、DEFAULT约束。约束的作用是防止列出现非法数据，以保证数据库中数据的一致性和完整性。

17．

(1) DELETE E2 FROM E2, E1

WHERE E2.编号 IN (SELECT 编号 FROM E1) (2) INSERT INTO E1 SELECT * FROM E2

18. UPDATE G1 SET G1.数量= G1.数量+ G2.数量 FROM G1, G2

1

WHERE G1.编号= G2.编号 GO

INSERT INTO G1

SELECT * FROM G2 WHERE NOT (G2.编号 IN (SELECT 编号 FROM G1))

第四章

1. A、B、D 2.B、C、D 3.

（1）程序为：SELECT top 6 * FROM goods

（2）程序为：SELECT 商品名称,进货价 FROM goods ORDER BY 进货价 DESC （3）程序为：SELECT 商品名称,零售价,进货价,剩余数量

FROM goods INNER JOIN sell ON goods.商品编号=sell.商品编号

（4）程序为：SELECT 商品编号,数量,零售价 FROM goods

COMPUTE AVG(零售价)

（5）程序为：SELECT 商品名称,goods.数量,售出时间 FROM goods,sell

WHERE 售出时间 between '2004.1.1'AND '2005.1.1' and goods.商品编号=sell.商品编号

（6）程序为：SELECT * FROM employees

WHERE 编号=ANY (SELECT 售货员工编号 FROM sell) SELECT 售货员工编号,商品编号,数量

FROM sell order by 售货员工编号 COMPUTE SUM(数量) BY 售货员工编号 （7）程序为：SELECT 商品名称,goods.数量,剩余数量 FROM goods,sell

WHERE goods.商品编号=sell.商品编号 and 商品名称 LIKE '%显示器%'

（8）程序为：SELECT SUM(sell.数量) as 打印机销售数量 FROM sell,goods

WHERE sell.商品编号=goods.商品编号 and 商品名称='打印机'

第五章

1. C 2. D

3．答：索引是一个单独的，物理的数据库结构。它由某个表中的一列或者若干列的值，以及这些值的记录在数据表中存储位置的物理地址所组成。使用索引可以极大的改善数据库的性能，加快数据查找速度。但是，索引也会消耗硬盘空间并招致开销和维护成本。

4．答：设置索引的原则是：在主键上创建索引；在经常需要检索的字段上创建索引；在外键上创建索引；在经常要排序的列上创建索引。

5．答：按照存储结构划分，索引分为聚集索引和非聚集索引。

在聚集索引中，表中各行的物理顺序与索引中行的物理顺序是相同，创建任何非聚集索引之前要首先创建聚集索引，聚集索引需要将表中的所有数据完全重新排列；而聚集索引不会改变表中数据行的物理存储位置和顺序，它只包含索引值和指向数据行的指

2

针。

6．答：数据完整性是指存储在数据库中的数据的一致性和准确性。数据完整性有3中类型：域完整性、实体完整性和参照完整性。

约束确保在数据中输入有效的数据值，是强制数据完整性的首选方法。

第六章

1. C 2. B 3．视图

4．对视图的定义进行加密

强制视图上执行的所有数据修改语句都必须符合由 select_statement 设置的准则。 5.判断题 √、×、√ 第7章

1、系统，用户 2、符号，运算符

3、B 4、B、C

5、批处理就是单个或多个Transact-SQL语句的集合，由应用程序一次性发送给SQL Server解释并执行批处理内的所有语句指令。批处理的结束标志是GO。建立批处理要注意：

(1) CREATE DEFAULT、CREATE RULE、CREATE VIEW、CREATE PROCEDURE、和CREATE TRIGGER语句，只能在单独的批处理中执行。

(2) 将默认值和规则绑定到表字段或用户自定义数据类型上之后，不能立即在同一个批处理中使用它们。

(3) 定义一个CHECK约束之后，不能立即在同一个批处理中使用这个约束。 (4) 修改表中的字段名之后，不能立即在同一个批处理中使用这个新字段名。 (5) 用户定义的局部变量的作用范围局限于一个批处理内，并且在CO命令后不能再引用这个变量。

(6) 如果一个批处理中的第一条语句是执行某存储过程的EXEC语句，则EXEC关键字可以省略不写；如果不是批处理的第一条语句，则EXEC关键字必须要有。

6、在使用局部变量以前，必须使用DECLARE语句来声明这个局部变量。语法格式为：DECLARE @局部变量名 数据类型[,…n]

给局部变量赋值有两种方法，可以使用SET语句赋值，也可以使用SELECT语句赋值。

使用SET语句赋值的语法格式为：SET @局部变量名=表达式[,…n]

使用SELECT语句赋值的语法格式为：SELECT @局部变量名=表达式[,…n] 7、使用游标要遵循以下顺序：

声明游标→打开游标→读取数据→关闭游标→删除游标

3

8、 DECLARE @srv varchar(20) SET @srv= @@servername PRINT @srv

9、DECLARE @Today DATETIME

SET @Today=GETDATE( )

SELECT YEAR(@Today) AS 年,MONTH(@Today) AS 月,DAY(@Today) AS 日 10、 USE STUINFO

GO

DECLARE @Stuname char(8) SET @Stuname ='张三'

IF EXISTS(SELECT * FROM t_student WHERE S_name=@stuname)

BEGIN

PRINT @stuname+'的信息如下：'

SELECT * FROM t_student WHERE S_name=@stuname END

ELSE

BEGIN

PRINT '在数据库中无此人信息!' END

GO

11、 USE STUINFO

GO

SELECT S_number, score,折合成绩=

CASE

WHEN score <60 THEN '不及格'

WHEN score >=60 AND score<70 THEN '及格' WHEN score >=70 AND score<80 THEN '中等' WHEN score >=80 AND score<90 THEN '良好' WHEN score >=90 THEN '优秀'

ELSE '无成绩' END

FROM t_score JOIN t_course ON t_score.C_number=t_course.C_number WHERE C_name='SQL Server程序设计' GO

12、 USE STUINFO

GO

--声明游标

DECLARE Stu_cursor CURSOR

FOR

4

SELECT * FROM t_student FOR READ ONLY --打开游标

OPEN Stu_cursor

--第一次读取，得到结果集的首行记录 FETCH NEXT FROM Stu_cursor --循环读取结果集中剩余的数据行 WHILE @@FETCH_STATUS=0 BEGIN

FETCH NEXT FROM Stu_cursor END

--关闭游标

CLOSE Stu_cursor --释放（删除）游标

DEALLOCATE Stu_cursor GO

13、 USE STUINFO

GO

DECLARE @score real --声明一个可更新的游标

DECLARE Stu_score CURSOR KEYSET FOR SELECT score FROM t_score

WHERE S_number= '2004016' AND C_number= '001'

FOR UPDATE OPEN Stu_score

FETCH ABSOLUTE 1 FROM Stu_score INTO @score

PRINT '修改前的成绩是：'+CONVERT(Varchar(6),@score) --通过游标修改数据 UPDATE t_score SET score= score+5

WHERE CURRENT OF Stu_score

FETCH ABSOLUTE 1 FROM Stu_score INTO @score

PRINT '修改后的成绩是：'+CONVERT(Varchar(6),@score) --关闭游标

CLOSE Stu_score --释放（删除）游标

DEALLOCATE Stu_score GO

5

第8章

1、 执行存储过程，字符串变量 2、A、D

3、存储过程与存储在客户计算机的本地T-SQL语句相比，它具有如下优点： 存储过程在服务器端运行，而且存储过程是预编译的，执行速度快。如果某个操作需要大量的T-SQL语句或重复执行，那么使用存储过程比直接使用T-SQL语句执行得更快。

用户可以通过发送一条执行存储过程的语句实现一个复杂的操作，而不需要在网络上发送几百条T-SQL语句，这样可以减少在网络流量

使用存储过程可以增强代码的重用性和共享性，存储过程在被创建后，可以在程序中被多次调用，而不必重新编写。

4、SQL Server支持五种类型的存储过程：系统存储过程、本地存储过程、临时存储过程、远程存储过程、扩展存储过程。 5、USE Sales

GO

CREATE PROC proc_find @goodname Varchar(20) AS

IF EXISTS(SELECT * FROM Goods WHERE 商品名称=@goodname) RETURN 1 ELSE RETURN 0 6、在Sales数据库中建立一个名为date_to_date_sales的存储过程：该存储过程将返回在两个指定日期之间的所有销售记录。

USE Sales GO

CREATE PROC date_to_date_sales @StartDate datetime, @EndDate datetime AS

SELECT * FROM sell

WHERE 售出时间 BETWEEN @StartDate AND @EndDate

第9章

1、inserted,UPDATE,DELETE 2、D

3、触发器是一种与数据表紧密关联的特殊的存储过程，当该数据表有插入（INSERT）、更改（UPDATE）或删除（DELETE）事件发生时，所设置的触发器就会自动被执行，以进行维护数据完整性，或其他一些特殊的数据处理工作。触发器分为AFTER

6

触发器和INSTEAD OF 触发器两种类型。

4、USE Sales GO

CREATE TRIGGER tri_ReportGoods ON Goods FOR UPDATE AS

IF UPDATE(数量) BEGIN

IF (SELECT MIN(数量) FROM deleted ) <5

PRINT '有商品库存低于5件，请核查，速进货！' END

5、USE Sales GO

CREATE TRIGGER tri_GoodsCount ON Sell FOR INSERT AS BEGIN

DECLARE @GoodNO int,@Sellnum int

SELECT @GoodNO=商品编号, @Sellnum=数量 FROM inserted UPDATE Goods

SET 数量=数量-@Sellnum WHERE 商品编号=@GoodNO END

第十章

1、

(1)定义函数

在查询分析器中运行以下命令： USE Sales GO

CREATE FUNCTION Fn_Sell (@bt DateTime) RETURNS TABLE AS

RETURN

(SELECT Goods.商品名称,Sell.数量 FROM Goods,Sell

7

WHERE (Sell.售出时间=@bt AND Goods.商品编号=Sell.商品编号) ) (2)测试

在查询分析器中运行以下命令：

SELECT * FROM dbo.Fn_Sell('2005-6-5') 2、

(1)定义函数

在查询分析器中运行以下命令： USE Sales GO

CREATE FUNCTION Fn_DateTotal (@m Int)

RETURNS @Fn_DateTotal TABLE

(日期 DateTime PRIMARY KEY NOT NULL, 日销售额 Money NOT NULL ) AS

BEGIN

INSERT @Fn_DateTotal

SELECT Sell.售出时间 AS 日期,SUM(Sell.数量*Goods.零售价) AS 日销售额 FROM Sell,Goods

WHERE (MONTH(Sell.售出时间)=@m AND Sell.商品编号=Goods.商品编号) GROUP BY Sell.售出时间 RETURN END (2)测试

在查询分析器中运行以下命令：

SELECT * FROM dbo．Fn_DateTotal(6) 返回结果是6月份销售统计结果。 在查询分析器中运行以下命令： Declare @err Int USE Sales

BEGIN TRANSACTION

UPDATE T1 SET T1.数量= T1.数量+ GG.数量 FROM T1,GG

WHERE T1.编号= GG.编号 SELECT @err=@@error IF @err!=0

--更新失败，回滚所有操作

8

BEGIN

ROLLBACK TRANSACTION RETURN END

INSERT T1

SELECT * FROM GG

WHERE NOT (编号 IN (SELECT 编号 FROM T1)) SELECT @err=@@error IF @err!=0

--更新失败，回滚所有操作 BEGIN

ROLLBACK TRANSACTION RETURN END

COMMIT TRANSACTION 3、

在查询分析器中运行以下命令： Declare @err Int USE Sales

BEGIN TRANSACTION

UPDATE T1 SET T1.数量= T1.数量+ GG.数量 FROM T1,GG

WHERE T1.编号= GG.编号 SELECT @err=@@error IF @err!=0

--更新失败，回滚所有操作 BEGIN

ROLLBACK TRANSACTION RETURN END

INSERT T1

SELECT * FROM GG

WHERE NOT (编号 IN (SELECT 编号 FROM SELECT @err=@@error IF @err!=0

--更新失败，回滚所有操作 BEGIN

ROLLBACK TRANSACTION RETURN

9

T1)) END

COMMIT TRANSACTION

实验参考答案

实验二

1.

CREATE DATABASE StuInfo ON

PRIMARY (NAME = StuInfo _Data,， FILENAME = 'D:\\ StuInfo _Data.Mdf', SIZE =2MB,

MAXSIZE = 10MB, FILEGROWTH =1 MB) LOG ON

(NAME = StuInfo _Log ，

FILENAME = 'D:\\ StuInfo _Log.Ldf', SIZE = 1MB,

MAXSIZE =5MB,

FILEGROWTH = 1MB) GO

2.

USE StuInfo GO

--创建学生表T_Student CREATE TABLE T_Student_

( S_Number Char(8) NOT NULL

CONSTRAINT PK_ S_Number PRIMARY KEY CLUSTERED ,

S_Name Char(10) NOT NULL, Sex Char(2) NULL,

Birthday DateTime NOT NULL, ) GO

--创建课程表T_Course CREATE TABLE T_Course

( C_Number Char(4) NOT NULL

10

CONSTRAINT PK_ C_Number PRIMARY KEY CLUSTERED,

C_Name Char(16) NOT NULL , Teacher Char(10) NULL, Hours Int NOT NULL, Credit Int NULL ) GO

--创建课程表T_Score CREATE TABLE T_Score

(S_Number Char(8) NOT NULL

CONSTRAINT FK_S_Number FOREIGN KEY (S_Number) REFERENCES T_Student (S_Number),

C_Number Char(4) NOT NULL

CONSTRAINT PK_SC_Number PRIMARY KEY (S_Number, C_Number)

CONSTRAINT FK_C_Number FOREIGN KEY (C_Number) REFERENCES T_Course (C_Number),

Score Real NULL )

3.在企业管理器中打开表，按照表5-4、5-5、5-6的内容直接输入数据即可。 4．显示错误信息，不能保存该条记录。因为T_Score表的S_Number列与表T_Student的S_Number列存在着外键约束关系，而表T_Student不存在S_Number为20030103的记录。

5. 显示错误信息，不能保存该条记录。因为T_Score表的C_Number列与表T_Course的C_Number列存在着外键约束关系，而表T_Course不存在C_Number为3002的记录。

6. 显示错误信息，不能保存该条记录。因为T_Score表的S_Number列与表T_Student的S_Number列存在着外键约束关系，而表T_Student不存在S_Number为20030105的记录。

7. 显示错误信息，不能删除该条记录。因为T_Score表的C_Number列与表

T_Course的C_Number列存在着外键约束关系，为保证表T_Score数据的完整性，不能删除表T_Course中C_Number为3001的记录。

8.首先删除T_Score表中C_Number=’3001’的所有记录，然后再删除表T_Course中C_Number为3001的记录。

9.

在查询分析器输入如下命令：

EXEC SP_DETACH_DB 'StuInfo', 'true'

单击工具栏中的“运行”按钮，返回“命令已成功完成”的信息，说明已成功分离数据库。

在附加数据库前，将上面分离出来的数据库文件'StuInfo _Data．Mdf、

11

'StuInfo_Log．Ldf复制到另一台计算机的C：\\。然后在查询分析器中运行如下命令： CREATE DATABASE StuInfo ON

PRIMARY (NAME= 'StuInfo_Data', FILENAME = 'C:\\ StuInfo_Data.Mdf' )

LOG ON

(NAME='StuInfo_Log',

FILENAME = 'C:\\ StuInfo_Log.Ldf') FOR ATTACH GO

实验三

1. 查询所有男学生的姓名、出生日期、年龄。

use stuinfo go

Select s_name,birthday, year(getdate())-year(birthday) 年龄 from t_student Where sex='男'

2. 查询所有女学生详细信息和女学生的总人数。

use stuinfo go

select *,count(sex) 人数 from t_student

group by s_number,birthday,sex,s_name having sex='女' compute sum(count(sex))

3. 查询SQL Server课程的总成绩、平均成绩、及格学生人数和不及格学生人数。

use stuinfo go

select t_course.c_name 课程名称,t_score.score 成绩 from t_course,t_score

where t_course.c_number=t_score.c_number and c_name='SQL Server' compute avg(score) compute sum(score) go

select count(*) 'sql server及格学生人数' from t_score where score>=60 and c_number='1'

select count(*) 'sql server不及格学生人数' from t_score where score<60 and c_number='1'

12

4. 分别查询sql server课程所有男生成绩，总分、平均分和所有女生的成绩、总分、平均分。

use stuinfo go

select t_student.s_name 姓名,t_student.sex 性别, t_course.c_name 课程名称,t_score.score 成绩 from t_student,t_course,t_score

where t_course.c_number=t_score.c_number and t_student.s_number=t_score.s_number and c_name='sql server' and t_student.sex='男' compute avg(score) compute sum(score) go

select t_student.s_name 姓名,t_student.sex 性别, t_course.c_name 课程名称,t_score.score 成绩 from t_student,t_course,t_score

where t_course.c_number=t_score.c_number and t_student.s_number=t_score.s_number and c_name='sql server' and t_student.sex='女' compute avg(score) compute sum(score)

5. 查询所有姓李的男学生的选修课程和成绩。

Use stuinfo Go

Select t_student.s_name 姓名,t_student.sex 性别, t_course.c_name 课程名称,t_score.score 成绩 From t_student, t_course, t_score

Where t_student.s_number=t_score.s_number and

t_score.c_number=t_course.c_number and s_name like '李%' and sex='男'

6. 查询所有不及格学生的姓名、不及格课程和不及格课程的成绩。

Use stuinfo Go

Select t_student.s_name,t_course.c_name,t_score.score From t_student,t_course,t_score

Where t_student.s_number=t_score.s_number and t_score.c_number=t_course.c_number and score<60

7. 按男同学进行分组。

Use stuinfo Go

13

Select * from t_student

Group by s_number, s_name, birthday,sex having sex='男'

8. 将t_student表中的前40%条记录在s_number,s_name,polity字段上的值插入到新表t_student1中。

Use stuinfo Go

Select top 40 percent s_number,s_name into t_student1 from t_student

Select * from t_student2

实验四

1. 分别利用企业管理器和创建索引向导为t_score表的score字段建立非聚集索引，索引名为i_score,并按降序排序。（略）

2. 利用transact sql语句新建一个数据表t_user，要求使用CREATE INDEX语句为U_id字段创建一个唯一性聚集索引，索引名为i_id。

第二题代码：

use stuinfo go

create table t_user ( U_id char(10) not null, U_name char(20),

U_password char(10) dafault ‘000000’, U_popedom char(20) )

create unique clustered index i_id on t_user(U_id)

3. 使用SQL语句为t_score表的s_number、c_number字段创建一个复合索引，索引名为i_t_score。设置填充因子为40,指定过期的索引统计不自动重新计算。

第三题程序为：

use stuinfo go

create index i_t_score on t_user(s_number、c_number) WITH PAD_INDEX, FILLFACTOR=50, STATISTICS_NORECOMPUTE

4. 使用存储过程将上题中建立的索引i_t_score重命名为index_s_c。 第四题源代码：sp_rename i_t_score,index_s_c

5. 分别使用企业管理器和SQL语句两种方法删除索引index_s_c。

14

第五题程序为：drop index index_s_c

实验五

1、如何为“姓名”字段添加一个唯一性约束？代码如下：

use student2 go

ALTER TABLE 基本表

ADD CONSTRAINT IX_E UNIQUE (姓名) GO

2、利用T-SQL语句创建一个名为“课程信息表” 的表，代码如下：

use student2 go

create table课程信息表 (

课程编号 char(10) not null primary key, 课程名 char(30), 学时 int,

学分 real default '4' )

go

3. 利用T-SQL语句创建一个名为“学生成绩表”的表，代码如下：

use student2 go

create table 学生成绩表( 学号 char(10) not null, 课程编号 char(10) not null, 分数 real,

constraint pk_s primary key (学号, 课程编号), constraint chk_score check (分数>0 and 分数<100), constraint con_num references 课程信息表(课程编号)

) go

4. 如何使用“alter table……add”命令为“课程名”字段增加唯一性约束。

use student2 go

alter table 课程信息表

add constraint uk_c unique(课程名) go

5. 如何使用命令删除学生成绩表中的一个外键约束，代码如下：

15

use student2 go

alter table 学生成绩表 drop constraint con_ num go

6. 利用存储过程sp_help查看3个表的信息，代码如下：

exec sp_help 基本表；exec sp_help 课程信息表；exec sp_help 学生成绩表。

7. 如何在一张表创建之后通过T-SQL语句定义主键约束？以基本表为例，代码如下：

use stuinfo go

alter table 基本表

add constraint pk_num primary key(学号)

实验六

1、 use student go

create view y_view as

select s_name,sex,polity,score,c_number from t_student,t_score

where t_student.s_number=t_score.s_number 2、 use student go

create view v_1 as select * from t_student

where polity='团员' and sex='男' 3、 use student go

create view v_2 as select * from t_student

where polity='团员' and sex='女'

16

with check option

实验七

1、在查询分析器中书写T-SQL语句创建和执行存储过程 （1）创建和执行存储过程Proc_score。

USE STUINFO GO

--创建存储过程Proc_score CREATE PROC Proc_score

WITH RECOMPILE,ENCRYPTION AS

SELECT S_number,score FROM t_score

WHERE C_number='2' --执行存储过程Proc_score EXEC Proc_score

（2）创建和执行带输入参数的存储过程Proc_list

USE STUINFO GO

--创建存储过程Proc_list CREATE PROC Proc_list @cno char(4) AS

SELECT TOP 5 WITH TIES S_number,score FROM t_score

WHERE C_number=@cno

ORDER BY score DESC,S_number ASC

--执行存储过程Proc_list，查询“1”号课程成绩排名前5位的学生成绩记录。 EXEC Proc_list '1' 其中，“1”为要传递给存储过程Proc_list的输入参数。 （3）创建和执行带输入和输出参数的存储过程ProcAvgScore

CREATE PROC ProcAvgScore @cno char(4),

@maxscore real OUTPUT, @minscore real OUTPUT,

@avgscore numeric(5,2) OUTPUT AS

SELECT @maxscore=MAX(score),@minscore=MIN(score)

17

,@avgscore=AVG(score) FROM t_score

WHERE C_number=@cno GROUP BY C_number

/*执行存储过程ProcAvgScore，查询选修“1”号课程学生成绩的最高分，最低分和平均分*/

DECLARE @maxscore real,@minscore real,@avgscore numeric(5,2) EXEC ProcAvgScore '1',@maxscore OUTPUT ,@minscore OUTPUT,@avgscore OUTPUT

PRINT '该门课程成绩最高分为：'+CAST(@maxscore AS char(6)) PRINT '该门课程成绩最高分为：'+CAST(@minscore AS char(6)) PRINT '该门课程成绩最高分为：'+CAST(@avgscore AS char(6)) 2、使用T-SQL语句查看、修改和删除存储过程 （1）查看存储过程

a.查看加密存储过程的定义 EXEC sp_helptext Proc_score

注意观察执行结果。能够查看到该存储过程的定义文本吗？ b.查看未加密存储过程的定义 EXEC sp_helptext Proc_list

EXEC sp_helptext ProcAvgScore c.查看有关存储过程的信息。 EXEC sp_help Proc_score EXEC sp_help Proc_list

EXEC sp_help ProcAvgScore （2）修改存储过程Proc_score USE STUINFO GO

ALTER PROC Proc_score WITH RECOMPILE AS

SELECT S_number,score FROM t_score

WHERE C_number='1' （3）删除上述创建的存储过程

DROP PROC Proc_score,Proc_list,ProcAvgScore 思考与练习：

1、USE STUINFO

GO

18

CREATE PROC insert_tri @Cnum Char (4), @Cname char (16),

@Teacher char (10) =NULL, @Hours int,

@Credit int =NULL AS

IF @Cnum IS NULL OR @Cname IS NULL OR @Hours IS NULL BEGIN

PRINT '你必须提供课程号，课程名，课程学时！' PRINT '(授课教师，学分可以为空。)' RETURN END

BEGIN TRANSACTION

INSERT t_course (C_number,C_name, Teacher, Hours, Credit) VALUES (@Cnum,@Cname,@Teacher,@Hours,@Credit) IF @@error <> 0 BEGIN ROLLBACK TRAN RETURN END

PRINT '新课程已经添加' COMMIT TRANSACTION GO

2、USE STUINFO GO

CREATE PROC GetCredit @Sno char(8), @cname char(16),

@score real OUTPUT, @GetCredit int OUTPUT AS

SELECT @score =score, @GetCredit =CASE WHEN score <60 THEN 0 ELSE Credit END

FROM t_course JOIN t_score ON t_course.C_number=t_score.C_number WHERE t_score.S_number=@Sno AND C_name=@cname --执行存储过程

19

DECLARE @score real,@GetCredit int

EXEC GetCredit '2004001','SQL Server程序设计', @score OUTPUT,@GetCredit

OUTPUT

PRINT '成绩='+CONVERT(varchar(6),@score)+' ，所获学分='+CONVERT

(char(2), @GetCredit)

实验八 思考与练习

1、USE STUINFO

GO

CREATE TRIGGER Score_update2 ON t_course FOR UPDATE AS

IF UPDATE(credit) BEGIN

RAISERROR('禁止用户修改credit列！',10,1) ROLLBACK TRANSACTION END

2、USE STUINFO

GO

CREATE TRIGGER Delete_score ON t_course FOR DELETE AS BEGIN

DELETE t_score FROM t_score, deleted

WHERE t_score.C_number=deleted.C_number END

3、USE STUINFO

GO

CREATE TRIGGER tri_insert ON t_score FOR INSERT AS BEGIN

DECLARE @credit int,@score real SELECT @score=score FROM inserted

20

SELECT @credit=t_course.credit FROM t_course,inserted WHERE t_course.C_number= inserted.C_number IF @score>=60 BEGIN

UPDATE t_score SET credit=@credit FROM t_score,inserted

WHERE t_score.S_number=inserted.S_number END END

实验九

1、

在查询分析器中运行以下命令： USE Stuinfo GO

CREATE FUNCTION Fn_Grad (@ClassNo Char(6))

RETURNS @Fn_Grad TABLE

(课程编号 Char(4) PRIMARY KEY NOT NULL, 平均成绩 Decimal(6,2) NOT NULL ) AS BEGIN

INSERT @Fn_Grad

SELECT C_Number AS 课程编号,AVG(score) AS 平均成绩 FROM T_Score

WHERE (LEFT(S_Number,6)=@ClassNo) GROUP BY C_Number RETURN END (2)测试

在查询分析器中运行以下命令： SELECT * FROM dbo.Fn_Grad('200301') 返回结果是200301班的成绩统计结果。 2、

在查询分析器中运行以下命令： Declare @err Int

21

USE Sales

BEGIN TRANSACTION

UPDATE T1 SET T1.数量= T1.数量+ GG.数量 FROM T1,GG

WHERE T1.编号= GG.编号 SELECT @err=@@error IF @err!=0

--更新失败，回滚所有操作 BEGIN

ROLLBACK TRANSACTION RETURN END

INSERT T1

SELECT * FROM GG

WHERE NOT (编号 IN (SELECT SELECT @err=@@error IF @err!=0

--更新失败，回滚所有操作 BEGIN

ROLLBACK TRANSACTION RETURN END

COMMIT TRANSACTION 22

编号 FROM T1))

本文来源：https://www.bwwdw.com/article/ofv3.html