SQL Server 2000实用教程(第二版)习题参考答案(6)

SQL Server 2000实用教程(第二版)习题参考答案

IF @avg>80 PRINT '数据结构平均分高于80分'

3．USE teachdb

GO

IF EXISTS(SELECT name FROM sysobjects WHERE name='proc1' AND type='P')

DROP PROCEDURE proc1

GO

CREATE PROCEDURE proc1

@student_no CHAR(4)

AS

SELECT student.s_no,s_name,c_name FROM student,choice,course

WHERE student.s_no=choice.s_no AND course.c_no=choice.c_no

AND student.s_no=@student_no

GO

EXEC proc1 101

EXEC proc1 102

4．USE teachdb

IF EXISTS(SELECT name FROM sysobjects

WHERE name='trig1' AND type='TR')

DROP TRIGGER trig1

GO

CREATE TRIGGER trig1 ON teaching

AFTER INSERT

AS

IF EXISTS(SELECT * FROM teaching

WHERE teaching.t_no NOT IN(SELECT teacher.t_no

teacher) OR teaching.c_no NOT IN(SELECT course.c_no

course))

BEGIN

RAISERROR('违背数据的一致性,不允许插入',16,1)

ROLLBACK TRANSACTION

END

5．USE teachdb

GO

CREATE FUNCTION sum_m(@m AS int)

RETURNS int

BEGIN FROM FROM