【2019-2020春学期】数据库作业14：第五章： 数据库完整性 习题 + 存储过程

6、
STAFF(Snum,Sname,Sage,Duty,Salary,Dnum)
DEPARTMENT(Dnum,Dname,Mname,Tnum)

	CREATE TABLE DEPARTMENT     /* 部门，因为职工中要引用部门的部门号作为外键，所以要先创建部门的表 */
	(Dnum NUMERIC(6),     /* 部门号 */
	Dname CHAR(10),     /* 名称 */
	Mname CHAR(10),     /* 经理名 */
	Tnum CHAR(12),     /* 电话 */
	CONSTRAINT DepartmentKey PRIMARY KEY(Dnum)     /* 将部门号设为主码 */
	);
	CREATE TABLE STAFF     /* 职工 */
	(Snum NUMERIC(6)PRIMARY KEY,     /* 职工号,设为主码 */
	Sname CHAR(10),     /* 姓名 */
	Sage INT,     /* 年龄 */
	CONSTRAINT Sage1 CHECK(Sage<=60),     /* 职工年龄不得超过60岁 */
	Duty CHAR(10),     /* 职务 */
	Salary INT,     /* 工资 */
	Dnum NUMERIC(6),     /* 所在部门号 */
	CONSTRAINT FK_DEPTON FOREIGN KEY(Dnum)     /* Dnum为外键，引用DEPARTMENT表中的Dnum */
		REFERENCES DEPARTMENT(Dnum)     
	);

2、
建立成绩表

	CREATE TABLE COURSE  /*成绩表*/
	(snum NUMERIC(6),    /* 学号 */
	sname CHAR(10),    /* 姓名 */
	Grade INT,     /* 成绩 */
	CONSTRAINT Grade CHECK(Grade>=0 AND Grade<=100),    /* 限制成绩在0-100 */
	Cname CHAR(10)   /* 科目 */
	);

（1）统计离散数学的成绩发布情况，即按照各分数段统计人数

建立一个离散数学按各分段统计人数的表

	CREATE TABLE COURSR_LS
	(Grade  CHAR(10),  /* 分数段 */ 	
	 Count INT,       /* 人数*/
     Cname CHAR(10)  /* 科目*/	
	);

存储过程

	IF (exists (select * from sys.objects where name = 'LS_counts'))
	DROP PROCEDURE LS_counts
	GO
	CREATE  PROCEDURE LS_counts
	AS
	BEGIN TRANSACTION TRANS  
	DECLARE 
	    @60 INT,    /* <60  */
	    @70 INT,    /*  >=60 AND <70 */
	    @80 INT,    /*  >=70 AND <80 */
	    @90 INT,	  /*  >=80 AND <90 */
	    @100 INT   /*  >=90 AND <=100 */
	SELECT @60=COUNT(*) FROM COURSE
						WHERE  Cname='离散数学' AND Grade<60

	SELECT @70=COUNT(*) FROM COURSE
						WHERE  Cname='离散数学' AND Grade<70 AND Grade>=60

	SELECT @80=COUNT(*) FROM COURSE
						WHERE  Cname='离散数学' AND Grade<80 AND Grade>=70

	SELECT @90=COUNT(*) FROM COURSE
						WHERE  Cname='离散数学' AND Grade<90 AND Grade>=80

	SELECT @100=COUNT(*) FROM COURSE
						WHERE  Cname='离散数学' AND Grade<=100 AND Grade>=90
	
	BEGIN 
	UPDATE COURSR_LS SET Count=@60 WHERE Grade='0-59'
	UPDATE COURSR_LS SET Count=@70 WHERE Grade='60-69'
	UPDATE COURSR_LS SET Count=@80 WHERE Grade='70-79'
	UPDATE COURSR_LS SET Count=@90 WHERE Grade='80-89'
	UPDATE COURSR_LS SET Count=@100 WHERE Grade='90-100'
	COMMIT TRANSACTION TRANS; 
	END

执行存储过程

	EXEC LS_counts
	SELECT *
	FROM COURSR_LS

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

新建一个平均成绩的表

	CREATE TABLE AVG
	(
		Cname CHAR(10),   /* 科目*/	
		avg INT
	);

建立存储过程

	IF (exists (select * from sys.objects where name = 'COURSE_AVG'))
	DROP PROCEDURE COURSE_AVG
	GO
	CREATE  PROCEDURE COURSE_AVG
	AS
	BEGIN TRANSACTION TRANS  
	DECLARE 
	    @LS_C INT,    /* 离散总分 */
	    @GS_C INT,    /* 高数总分 */
	    @XD_C INT,    /* 线代总分 */
		@DX_C INT,    /* 电子线路总分 */
		@SJK_C INT   /* 数据库总分 */
	SELECT @LS_C=AVG(Grade) FROM COURSE
						WHERE  Cname='离散数学' 

	SELECT @GS_C=AVG(Grade) FROM COURSE
						WHERE  Cname='高等数学'

	SELECT @XD_C=AVG(Grade) FROM COURSE
						WHERE  Cname='线性代数'

	SELECT @DX_C=AVG(Grade) FROM COURSE
						WHERE  Cname='电子线路'

	SELECT @SJK_C=AVG(Grade) FROM COURSE
						WHERE  Cname='数据库'
	
	BEGIN 
	UPDATE AVG SET avg=@LS_C WHERE Cname='离散数学'
	UPDATE AVG SET avg=@GS_C WHERE Cname='高等数学'
	UPDATE AVG SET avg=@XD_C WHERE Cname='线性代数'
	UPDATE AVG SET avg=@DX_C WHERE Cname='电子线路'
	UPDATE AVG SET avg=@SJK_C WHERE Cname='数据库'
	COMMIT TRANSACTION TRANS; 
	END

执行存储过程

	EXEC COURSE_AVG
	SELECT *
	FROM AVG

（3）将学生选课成绩由百分制改为等级制

原表：

	ALTER TABLE COURSE ADD  Grade_Level CHAR;
	IF (exists (select * from sys.objects where name = 'Up'))
	DROP PROCEDURE Up
	GO
	CREATE  PROCEDURE Up
	AS
	BEGIN TRANSACTION TRANS  		
	BEGIN
	UPDATE COURSE SET Grade_Level='A' WHERE Grade>=90 AND Grade<=100
	UPDATE COURSE SET Grade_Level='B' WHERE Grade>=80 AND Grade<90
	UPDATE COURSE SET Grade_Level='C' WHERE Grade>=70 AND Grade<80
	UPDATE COURSE SET Grade_Level='D' WHERE Grade>=60 AND Grade<70
	UPDATE COURSE SET Grade_Level='E' WHERE Grade<60
	COMMIT TRANSACTION TRANS; 
	END

这里 ALTER TABLE COURSE ADD Grade_Level CHAR; 在存储过程中容易反复调用，建议如上放置存储过程外

执行存储过程

	EXEC Up
	SELECT *
	FROM COURSE

终于写完了，从下午2点到现在8点，除去吃饭的半个小时，写了将近5个半小时。仅仅一个第2题的（1）我就写了三个多小时，我也不知道是什么情况，可能是我记得还不牢，不能灵活运用。这些题都是我一个一个打在软件上，解决报错确认无误后写上来的。过了（1）后面两小题，我明显快了非常多，遇到问题也能很快的解决。我觉得做这个作业收获挺多的，我是更喜欢实践的，写作业，让我实际运用更加自如。第2题的第（1）小题，我有参考老师同学，因为不知道为什么不对，找出了自己的问题。后面就是自己写了。