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

Staff(Sno,Sname,Sage,Job,Pay,Dno)
Depart(Dno,Dname,Mname,Add,Tel)
（1）显然，主码应为编号（不会重复）。

CREATE TABLE Staff
	(Sno CHAR(20) PRIMARY KEY,
	Sname CHAR(20),
	Sage SMALLINT,
	Job CHAR(20),
	Pal INT,
	Dno CHAR(20)
	)
CREATE TABLE Depart
	(Dno CHAR(20) PRIMARY KEY,
	Dname CHAR(20),
	Mname CHAR(20),
	Tel CHAR(20)
	)

（2）

CREATE TABLE Depart
	(Dno CHAR(20) PRIMARY KEY,
	Dname CHAR(20),
	Mname CHAR(20),
	Tel CHAR(20)
	)
CREATE TABLE Staff
	(Sno CHAR(20) PRIMARY KEY,
	Sname CHAR(20),
	Sage SMALLINT,
	Job CHAR(20),
	Pal INT,
	Dno CHAR(20),
	FOREIGN KEY(Dno) REFERENCES Depart(Dno)
	)

（3）

CREATE TABLE Depart
	(Dno CHAR(20) PRIMARY KEY,
	Dname CHAR(20),
	Mname CHAR(20),
	Tel CHAR(20)
	)
CREATE TABLE Staff
	(Sno CHAR(20) PRIMARY KEY,
	Sname CHAR(20),
	Sage SMALLINT CHECK(Sage<=60),
	Job CHAR(20),
	Pal INT,
	Dno CHAR(20),
	FOREIGN KEY(Dno) REFERENCES Depart(Dno)
	)

（1）先来建立需要的表吧

CREATE TABLE SS
(
Score CHAR(20),
Num INT 
)
INSERT INTO SS VALUES('[0,60)',0)
INSERT INTO SS VALUES('[60,80)',0)
INSERT INTO SS VALUES('[80,100]',0)

虽然我不喜欢验证的过程（其实就是懒得截图），但毕竟是第一次正经的SQL编程，就尊敬一下吧。

IF (exists (select * from sys.objects where name = 'Statistic'))
    DROP PROCEDURE Statistic 
GO
CREATE PROCEDURE Statistic
AS
BEGIN
DECLARE	
@0until60   INT,                 
@60until80   INT,  
@80until100   INT,  
@CNO CHAR(8);  

SELECT @CNO=Cno 
FROM   Course 
WHERE  Cname='离散数学';/*获取离散数学课程号*/

SELECT @80until100=COUNT(*)/*符合筛选条件开始计数*/
FROM SC
WHERE Grade>80 AND Cno=@CNO
SELECT @60until80=COUNT(*)
FROM SC
WHERE Grade>60 AND Cno=@CNO
SELECT @0until60=COUNT(*)
FROM SC
WHERE Grade>0 AND Cno=@CNO
/*更新计数*/
UPDATE SS SET Num=@0until60 WHERE Score='[0,60)';
UPDATE SS SET Num=@60until80 WHERE Score='[60,80)';
UPDATE SS SET Num=@80until100 WHERE Score='[80,100]';
END
EXEC Statistic

结果：

（2）
还是先建表

CREATE TABLE AvSC
(
Cno CHAR(20),
Cname CHAR(20),
AScore FLOAT 
)
INSERT INTO AvSC VALUES('1','离散数学',0)
INSERT INTO AvSC VALUES('2','高等数学',0)
INSERT INTO AvSC VALUES('3','英语',0)

IF(exists(select * from sys.objects where name='Statistic'))
	DROP PROCEDURE Statistic
GO
CREATE PROCEDURE Statistic
AS
BEGIN
	DECLARE
	@Avg1 FLOAT,
	@Avg2 FLOAT,
	@Avg3 FLOAT;
	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'
    EXEC Statistic

（3）

ALTER TABLE SC ADD NewGrade CHAR(1);
IF(exists(select * from sys.objects where name='Statistic'))
	DROP PROCEDURE Statistic
GO
CREATE PROCEDURE Statistic
AS
	UPDATE SC SET NewGrade='D' WHERE Grade<60;
	UPDATE SC SET NewGrade='C' WHERE Grade>=60 AND Grade<70;
	UPDATE SC SET NewGrade='B' WHERE Grade>=70 AND Grade<80;
	UPDATE SC SET NewGrade='A' WHERE Grade>=80;

事实证明编程的思路都是相似的，但是由于语言规定不同，会出现不少未曾设想的错误。
以及不知不觉又退回了面向过程的习惯，将来探索一下面向对象的办法吧。