数据库完整性和存储过程的两个习题

完整性和存储过程习题

本篇文章记录了第十四次作业
使用的数据库是SQL Server，使用的数据库管理软件是SQL Server Management Studio.

当然这里是写题目用的全部都是标准SQL。

---

1、假设有下面两个关系模式：

  职工(职工号，姓名，年龄，职务，工资，部门号)，其中职工号为主码；
  部门(部门号，名称，经理名，电话)，其中部门号为主码。
用SQL语言定义这两个关系模式，要求在模式中完成以下完整性约束条件的定义：

(1) 定义每个模式的主码；

(2)定义参照完整性；

(3)定义职工年龄不得超过60岁。

 这个在写建表的时候就能够一口气都写完达成这些要求。

	--首先是职工的表
	CREATE TABLE EMP
		(Eno NUMBER(4) PRIMARY KEY,
		 Ename CHAR(10),
		 Age NUMBER(4),
		 	CONSTRAINT C1 CHECK (Age<=60),
		 Job CHAR(10),
		 Sal NUMBER(8,2),
		 Dno NUMBER(2),	
		 FOREIGN KEY(Dno) REFERENCES DEPT(Dno)
		);
	--部门表
	CREATE TABLE DEPT     
		(Dno NUMBER(2) PRIMARY KEY,
	 	 Dname CHAR(10),     
	 	 Manager CHAR(10),
	  	 Tel CHAR(12) 
     	);

2、对学生-课程数据库编写存储过程，完成下述功能：

(1) 统计离散数学的成绩分布情况，即按照各分数段统计人数。

 这题跟刚刚那个简直不是一个等级的。
 那么我们怎么写？首先得建立一个表，让它能够存取我们的统计人数，然后再写一个存储过程。

	--建表
	CREATE TABLE MBCOUNT	/*MB是离散数学*/
	(GRADE CHAR(20),
	 NUM SMALLINT
	);
	INSERT INTO MBCOUNT VALUES('[0,60)',0)
	INSERT INTO MBCOUNT VALUES('[60,70)',0)
	INSERT INTO MBCOUNT VALUES('[70,80)',0)
	INSERT INTO MBCOUNT VALUES('[80,90)',0)
	INSERT INTO MBCOUNT VALUES('[90,100]',0)
	SELECT * FROM MBCOUNT;
	--建立存储过程
	CREATE OR REPLACE PROCEDURE MB_S()
	AS DECLARE
		S060 SMALLINT;
		S6070 SMALLINT;
		S7080 SMALLINT;
		S8090 SMALLINT;
		S90100 SMALLINT;
	BEGIN
		SELECT S060=COUNT(*)
		FROM SC
		WHERE Grade<60 AND Cname=MB;
		UPDATE MBCOUNT SET Num=S060 WHERE GRADE='[0,60)'

		SELECT S6070=COUNT(*)
		FROM SC
		WHERE Grade>=60 AND Grede<70 AND Cname=MB;
		UPDATE MBCOUNT SET Num=S060 WHERE GRADE='[60,70)'

		SELECT S7080=COUNT(*)
		FROM SC
		WHERE Grade>=70 AND Grade<80 AND Cname=MB;
		UPDATE MBCOUNT SET Num=S060 WHERE GRADE='[70,80)'

		SELECT S8090=COUNT(*)
		FROM SC
		WHERE Grade>=80 AND Grade<90 AND Cname=MB;
		UPDATE MBCOUNT SET Num=S060 WHERE GRADE='[80,90)'

		SELECT S90100=COUNT(*)
		FROM SC
		WHERE Grade<=100 AND Grade>=90 AND Cname=MB;
		UPDATE MBCOUNT SET Num=S060 WHERE GRADE='[90,100]'
		COMMIT;
	END;
	CALL PROCEDURE MB_S();
	SELECT * FROM MBCOUNT;	

(2) 统计任意一门课的平均成绩。

 统计任何一门课的平均成绩也可以统计所有课的平均成绩，然后按需求拿出来就好了。同上文思路类似。

	--建表
	CREATE TABLE SCAVG	/*MB是离散数学*/
	(Cno CHAR(4),
	 Cname CHAR(20),
	 AvgScore FLOAT
	);
	INSERT INTO SCAVG VALUES('1','课程1',0);
	INSERT INTO SCAVG VALUES('2','课程2',0);
	INSERT INTO SCAVG VALUES('3','课程3',0);
	SELECT * FROM SCAVG;
	--建立存储过程
	CREATE OR REPLACE PROCEDURE AVGSCORE()
	AS DECLARE
		AVG1 FLOAT;
		AVG2 FLOAT;
		AVG3 FLOAT;
		
		SELECT AVG1=AVG(Grade)
		FROM SC
		WHERE Cno='1'
		UPDATE SCAVG SET AvgScore=AVG WHERE Cno='1';

		SELECT AVG2=AVG(Grade)
		FROM SC
		WHERE Cno='2'
		UPDATE SCAVG SET AvgScore=AVG WHERE Cno='2';

		SELECT AVG3=AVG(Grade)
		FROM SC
		WHERE Cno='3'
		UPDATE SCAVG SET AvgScore=AVG WHERE Cno='3';
		COMMIT;
	END;
	--执行
	CALL PROCEDURE AVGSCORE();
	SELECT * FROM SCAVG;

(3) 将学生选课成绩从百分制改为等级制(即A、B、C、D、E)

 与第一问的分段程序类似。

	--增加等级制
	ALTER TABLE SC ADD GRADE CHAR(1);
	--建立存储过程
	CREATE OR REPLACE PROCEDURE CGRADE()
	BEGIN 
		UPDATE SC SET GRADE='E' WHERE Grade<60;
		UPDATE SC SET GRADE='D' WHERE Grade>=60 AND Grade<70;
		UPDATE SC SET GRADE='C' WHERE Grade>=70 AND Grade<80;
		UPDATE SC SET GRADE='B' WHERE Grade>=80 AND Grade<90;
		UPDATE SC SET GRADE='A' WHERE Grade>=90 AND Grade<100;
		COMMIT;
	END;
	CALL PROCEDURE CGRADE();
	SELECT* FROM SC;

 这一个大题我不能保证没有错误，要说像上面那个简单题就算了，但这个是我第一次写存储过程，很可能有BUG。
 
 
参考文献：
[1]萨师煊，王珊，数据库系统概论.5版.北京:高等教育出版社,2014.
[2] 咸鱼4号的博客