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

代码：

CREATE TABLE Department
	(Dno CHAR(20) PRIMARY KEY,/*主码*/
	Dname CHAR(20),
	Manager CHAR(20),
	Tel CHAR(10)
	)

CREATE TABLE Staff
 (Sno CHAR(9) PRIMARY KEY,/*主码*/
 Sname CHAR(20),
 Sage SMALLINT CHECK(Sage<=60),/*用户定义的完整性*/
 SJob CHAR(10),
 Sal INT,
 Dno CHAR(20),
 FOREIGN KEY(Dno) REFERENCES Department(Dno)/*参照完整性*/
 )

代码：

先建仨表准备存数据

DROP TABLE IF EXISTS SCount;
CREATE TABLE SCount
(
Score CHAR(20),/*成绩区间*/
Num INT /*人数*/
)
--插入元组
INSERT INTO SCount VALUES('[0,60)',0)
INSERT INTO SCount VALUES('[60,70)',0)
INSERT INTO SCount VALUES('[70,80)',0)
INSERT INTO SCount VALUES('[80,90)',0)
INSERT INTO SCount VALUES('[90,100]',0)

SELECT * FROM SCount/*显示*/

DROP TABLE IF EXISTS SC_Avg;
--平均成绩
CREATE TABLE SC_Avg
(
Cno CHAR(4),
Cname CHAR(10),
Avg_Score FLOAT 
)
INSERT INTO SC_Avg VALUES('1','高等数学',0)
INSERT INTO SC_Avg VALUES('2','计算机基础',0)
INSERT INTO SC_Avg VALUES('3','离散数学',0)

SELECT * FROM SC_Avg  /*显示*/

第三个表我们就不新建了，在成绩表里加一列就好了

ALTER TABLE SC ADD Gradele CHAR(1);

IF(exists(select * from sys.objects where name='P1'))
	DROP PROCEDURE P1
GO
CREATE PROCEDURE P1
AS
BEGIN TRANSACTION TRANS
 DECLARE /*定义变量*/
 @less60 INT,
 @l70m60 INT,
 @l80m70 INT,
 @l90m80 INT,
 @m90    INT,
 @CNO CHAR(4),
 @Avg1 FLOAT,
 @Avg2 FLOAT,
 @Avg3 FLOAT;
 
 SELECT @CNO =Cno FROM Course WHERE Cname='离散数学'
/*小于60*/
SELECT @less60=COUNT (*)
FROM SC
WHERE Grade<60 AND Cno=@CNO
UPDATE SCount SET Num=@less60 WHERE Score='[0,60)'

--大于等于60小于70
 SELECT @l70m60=COUNT (*)
 FROM SC
 WHERE Grade<70 AND Grade>=60 AND Cno=@CNO
 UPDATE SCount SET Num=@l70m60 WHERE Score='[60,70)'
 
--大于等于70小于80
 SELECT @l80m70=COUNT (*)
 FROM SC
 WHERE Grade<80 AND Grade>=70 AND Cno=@CNO
 UPDATE SCount SET Num=@l80m70 WHERE Score='[70,80)'
 
--大于等于80小于90
 SELECT @l90m80=COUNT (*)
 FROM SC
 WHERE Grade<90 AND Grade>=80  AND Cno=@CNO
 UPDATE SCount SET Num=@l90m80 WHERE Score='[80,90)'
 
--大于等90
SELECT @m90=COUNT (*)
FROM SC
WHERE Grade>=90 AND Cno=@CNO
UPDATE SCount SET Num=@m90 WHERE Score='[90,100]'

	SELECT @Avg1=AVG(Grade)
	FROM SC
	WHERE Cno ='1'
	UPDATE SC_Avg SET Avg_Score=@Avg1 WHERE Cno ='1'
	
	SELECT @Avg2=AVG(Grade)
	FROM SC
	WHERE Cno ='2'
	UPDATE SC_Avg SET Avg_Score=@Avg2 WHERE Cno ='2'
	
	SELECT @Avg3=AVG(Grade)
	FROM SC
	WHERE Cno ='3'
	UPDATE SC_Avg SET Avg_Score=@Avg3 WHERE Cno ='3'
	

 UPDATE SC SET Gradele='D' WHERE Grade<60;
 UPDATE SC SET Gradele='C' WHERE Grade>=60 AND Grade<75;
 UPDATE SC SET Gradele='B' WHERE Grade>=75 AND Grade<85;
 UPDATE SC SET Gradele='A' WHERE Grade>=85;
 
COMMIT TRANSACTION TRANS