SQL数据库期末考试复习题

成绩 int null default 0 check(成绩>=0 and 成绩<=100), primary key(学号,课程号))

alter table CJB

add foreign key (学号) references XSB(学号)

alter table CJB

add foreign key (课程号) references KCB(课程号)

第7章要求掌握例题： 【例7.2】 从PXSCJ1数据库的三个表中查询某人指定课程的成绩和学分。该存储过程接受与传递参数精确匹配的值。

CREATE PROCEDURE student_info1 @name char (8), @cname char(16) AS SELECT a.学号, 姓名, 课程名, 成绩, t.学分 FROM XSB a INNER JOIN CJB b ON a.学号 = b.学号 INNER JOIN KCB t ON b.课程号= t.课程号 WHERE a.姓名=@name and t.课程名=@cname 或

CREATE PROCEDURE student_info1 @name char (8), @cname char(16) AS SELECT a.学号, 姓名, 课程名, 成绩, t.学分 FROM XSB a ， CJB b，KCB t WHERE a.姓名=@name and t.课程名=@cname and a.学号 = b.学号and b.课程号= t.课程号

【例7.4】 从三个表的连接中返回指定学生的学号、姓名、所选课程名称及该课程的成绩。该存储过程在参数中使用了模式匹配，如果没有提供参数，则使用预设的默认值。 CREATE PROCEDURE st_info @name varchar(30) = '李%' AS SELECT a.学号,a.姓名,c.课程名,b.成绩 FROM XSB a INNER JOIN CJB b ON a.学号 =b.学号 INNER JOIN KCB c ON c.课程号= b.课程号 WHERE 姓名 LIKE @name EXECUTE st_info '王%'

【例7.11】 创建触发器，当向CJB表中插入一个学生的成绩时，将XSB表中该学生的总学分加上添加的课程的学分。

CREATE TRIGGER cjb_insert ON CJB AFTER INSERT AS

29

BEGIN DECLARE @num char(6), @kc_num char(3) DECLARE @xf int SELECT @num=学号, @kc_num=课程号 from inserted SELECT @xf=学分 FROM KCB WHERE 课程号=@kc_num UPDATE XSB SET 总学分=总学分+@xf WHERE 学号=@num PRINT '修改成功' END

INSERT INTO cjb VALUES('081101','208',65)

【例7.12】 创建触发器，当修改XSB表中的学号时，同时也要将CJB表中的学号修改成相应的学号（假设XSB表和CJB表之间没有定义外键约束）。 CREATE TRIGGER xsb_update ON XSB AFTER UPDATE AS BEGIN DECLARE @old_num char(6), @new_num char(6) SELECT @old_num=学号 FROM deleted SELECT @new_num=学号 FROM inserted UPDATE CJB SET 学号=@new_num WHERE 学号=@old_num END

UPDATE XSB SET 学号='081120' WHERE 学号='081101'

【例7.13】 在删除XSB表中的一条学生记录时将CJB表中该学生的相应记录也删除。 CREATE TRIGGER xsb_delete ON XSB AFTER DELETE AS BEGIN DELETE FROM CJB WHERE 学号 IN ( SELECT 学号 FROM deleted ) END

DELETE FROM xsb WHERE 学号='081120' 【例7.14】 在KCB表中创建UPDATE和DELETE触发器，当修改或删除KCB表中的课程号字段时，同时修改或删除CJB表中的该课程号。

CREATE TRIGGER kcb_trig ON KCB AFTER UPDATE, DELETE AS BEGIN IF (UPDATE(课程号)) UPDATE CJB SET 课程号=(SELECT 课程号 FROM inserted) WHERE 课程号=(SELECT 课程号 FROM deleted) ELSE DELETE FROM CJB

30

WHERE 课程号 IN(SELECT 课程号 FROM deleted) END

Update kcb set 课程号='401' where 课程号 ='101'

31