数据库作业14：第五章： 数据库完整性 习题 + 存储过程

CREATE TABLE 部门		--先定义部门，因为参照完整性
(	部门号 CHAR(20) PRIMARY KEY,		--主码
	名称 CHAR(20),
	经理名 CHAR(20),
	电话 CHAR(20)
);
CREATE TABLE 职工
(	职工号 CHAR(20) PRIMARY KEY,		--主码
	姓名 CHAR(20),
	年龄 INT CHECK(年龄<=60),		--列值应该满足的条件
	职务 CHAR(20),
	工资 INT,
	部门号 CHAR(20),
	FOREIGN KEY (部门号) REFERENCES 部门(部门号)		--参照完整性
);

(1)

DROP TABLE IF EXISTS 离散数学成绩分布;
CREATE TABLE 离散数学成绩分布(分数段 CHAR(20),人数 INT);
INSERT INTO 离散数学成绩分布 VALUES('[0,60)',0);
INSERT INTO 离散数学成绩分布 VALUES('[60,70)',0);
INSERT INTO 离散数学成绩分布 VALUES('[70,80)',0);
INSERT INTO 离散数学成绩分布 VALUES('[80,90)',0);
INSERT INTO 离散数学成绩分布 VALUES('[90,100]',0);
/*建立存储过程*/
IF (exists (select * from sys.objects where name = '统计离散数学成绩'))
    DROP PROCEDURE 统计离散数学成绩
GO
CREATE PROCEDURE 统计离散数学成绩 
AS
BEGIN TRANSACTION TRANS   
   	DECLARE		/*定义变量*/
	@CNO CHAR(20),
	@0_60 INT,
	@60_70 INT,
	@70_80 INT,
	@80_90 INT,
	@90_100 INT;     
	SELECT @CNO = Cno FROM Course WHERE Cname='离散数学';
	SELECT @0_60=COUNT(*) FROM SC WHERE Grade<60 AND Cno=@CNO
	SELECT @60_70=COUNT(*) FROM SC WHERE Grade<70 AND Grade>=60 AND Cno=@CNO
	SELECT @70_80=COUNT(*) FROM SC WHERE Grade<80 AND Grade>=70 AND Cno=@CNO
	SELECT @80_90=COUNT(*) FROM SC WHERE Grade<90 AND Grade>=80 AND Cno=@CNO
	SELECT @90_100=COUNT(*) FROM SC WHERE  Grade>=90 AND Cno=@CNO
	UPDATE 离散数学成绩分布 SET 人数=@0_60 WHERE 分数段='[0,60)'
	UPDATE 离散数学成绩分布 SET 人数=@60_70 WHERE 分数段='[60,70)'
	UPDATE 离散数学成绩分布 SET 人数=@70_80 WHERE 分数段='[70,80)'
	UPDATE 离散数学成绩分布 SET 人数=@80_90 WHERE 分数段='[80,90)'
	UPDATE 离散数学成绩分布 SET 人数=@90_100 WHERE 分数段='[90,100)'

(2)

DROP TABLE IF EXISTS 任意课平均成绩;
CREATE TABLE 任意课平均成绩(课程号 CHAR(20),课程名 CHAR(20),平均成绩 FLOAT);
/*建立存储过程*/
IF (exists (select * from sys.objects where name = '统计任意课平均成绩')) 
	DROP PROCEDURE 统计任意课平均成绩
GO
CREATE PROCEDURE 统计任意课平均成绩
@统计的课程号 CHAR(20)  --参数
AS
BEGIN TRANSACTION TRANS   
   	DECLARE		/*定义变量*/
	@平均成绩 FLOAT,
	@课程名 CHAR(20);
     
	SELECT @平均成绩 =AVG(Grade) FROM SC WHERE Cno=@统计的课程号
	SELECT @课程名 =Cname FROM Course WHERE Cno=@统计的课程号

	DELETE FROM 任意课平均成绩 WHERE 课程号=@统计的课程号;
	INSERT INTO 任意课平均成绩 VALUES(@统计的课程号,@课程名,@平均成绩);

EXEC	统计任意课平均成绩
		@统计的课程号 = '1'
SELECT * FROM Account

(3)

ALTER TABLE SC ADD ABCDE CHAR(1);

IF (exists (select * from sys.objects where name = '成绩等级制')) 
	DROP PROCEDURE 成绩等级制
GO
CREATE PROCEDURE 成绩等级制
AS
BEGIN TRANSACTION TRANS     
   	UPDATE SC SET ABCDE='E' WHERE Grade <60 AND Grade >0;
	UPDATE SC SET ABCDE='D' WHERE Grade <70 AND Grade >=60;
	UPDATE SC SET ABCDE='C' WHERE Grade <80 AND Grade >=70;
	UPDATE SC SET ABCDE='B' WHERE Grade <90 AND Grade >=80;
	UPDATE SC SET ABCDE='A' WHERE Grade <100 AND Grade >=90;
	RETURN;

EXECUTE 后的事务计数指示 BEGIN 和 COMMIT 语句的数目不匹配。上一计数 = 0，当前计数 = 1。