数据库作业十四

** 1.假设有下面两个关系模式：
职工（职工号，姓名，年龄，职务，工资，部门号）， 其中职工号为主码；
部门（部门号，名称，经理名，电话），其中部门号为主码。
用 SQL 语言定义这两个关系模式，要求在模式中完成以下完整性约束条件的定义：
（1）定义每个模式的主码；（2）定义参照完整性；（3）定义职工年龄不得超过60岁 **

CREATE TABLE Department
     (Dno   CHAR(9)  PRIMARY KEY,
      Dname CHAR(20),     
      Mname CHAR(20),
      Tel   CHAR(20)
     )
     
CREATE TABLE Staff
     (Sno   CHAR(9)  PRIMARY KEY,
      Sname CHAR(20),     
      Ssex  CHAR(2),
      Sage  SMALLINT,
      CONSTRAINT C1 CHECK(Sage<=60),
      Post  CHAR(20),
      Pay   CHAR(20),
      Dno   CHAR(9),
      FOREIGN KEY (Dno) REFERENCES Department(Dno)
     )

2.对学生-课程数据库编写存储过程， 完成下述功能：
（1）统计离散数学的成绩分布情况，即按照各分数段统计人数。

IF (EXISTS (SELECT *FROM sys.objects WHERE name='usp_Grade_distribution'))
DROP PROC usp_Grade_distribution
GO 
CREATE PROC usp_Grade_distribution
AS 
BEGIN
DECLARE  @A INT,
@B INT,
@C INT,
@D INT,
@E INT,
@CNO varchar(8)
 SELECT @CNO=Cno
 FROM Cource
 WHERE Cname=N'离散数学'

 SELECT @A=COUNT(*)
 FROM SC
 WHERE Grade<=100 AND Grade>=90 AND Cno=@CNO

 SELECT @B=COUNT(*)
 FROM SC
 WHERE Grade<90 AND Grade>=80 AND Cno=@CNO

 SELECT @C=COUNT(*)
 FROM SC
 WHERE Grade <80 AND Grade>=70 AND Cno=@CNO

 SELECT @D=COUNT(*)
 FROM SC
 WHERE Grade<70 AND Grade>=60 AND Cno=@CNO

 SELECT @E=COUNT(*)
 FROM SC
 WHERE Grade<60 AND Cno=@CNO

 UPDATE Grade_distribution SET NUM=@A WHERE Level='A'
 UPDATE Grade_distribution SET NUM=@B WHERE Level='B'
 UPDATE Grade_distribution SET NUM=@C WHERE Level='C'
 UPDATE Grade_distribution SET NUM=@D WHERE Level='D'
 UPDATE Grade_distribution SET NUM=@E WHERE Level='E'
 END

（2）统计任意一门课的平均成绩

 IF (EXISTS (SELECT *FROM sys.objects WHERE name='usp_SC'))
 DROP PROC usp_SC
 GO 
CREATE PROC usp_SC
	@Cno char(4)
	AS
	BEGIN
	DECLARE @avg_Score Float
	SET @avg_Score=(SELECT AVG(Grade) FROM SC WHERE Cno=@Cno)
	INSERT INTO Avg_Score VALUES(@Cno,@avg_Score)
	END

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

IF (exists (select * from sys.objects where name = 'usp_Level'))
    DROP PROC usp_Level
GO
CREATE PROC usp_Level
AS
UPDATE SC SET Level='A' WHERE Grade>=90 AND Grade<=100;
UPDATE SC SET Level='B' WHERE Grade<90 AND Grade>=80;
UPDATE SC SET Level='C' WHERE Grade<80 AND Grade>=70;
UPDATE SC SET Level='D' WHERE Grade<70 AND Grade>=60;
UPDATE SC SET Level='E' WHERE Grade<60;

---

这题不难，不过一开始看题时，看懵了，不知道题想要干什么，比如第一小问，要统计分段信息，但题目没有给出分段依据，然后参考了下别人的，发现是自己想的太多了。