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

CREATE TABLE 部门
(
部门号 CHAR(12) PRIMARY KEY,
名称 CHAR(4),
经理名 CHAR(4),
电话 CHAR(10)
);

CREATE TABLE 职工
(
	职工号 CHAR(12) PRIMARY KEY,
	姓名 CHAR(4),
	年龄 SMALLINT CHECK(年龄<=60),
	职务 CHAR(2),
	工资 SMALLINT,
	部门号 CHAR(12),
	FOREIGN KEY (部门号) REFERENCES 部门(部门号)
);

INSERT INTO Course VALUES(9,‘离散’,NULL,2);

INSERT INTO SC VALUES('20121512',9,12);
INSERT INTO SC VALUES('201215122',9,22);
INSERT INTO SC VALUES('201215123',9,32);
INSERT INTO SC VALUES('201215125',9,42);
INSERT INTO SC VALUES('201215128',9,52);
INSERT INTO SC VALUES('201515129',9,62);
INSERT INTO SC VALUES('201515131',9,72);
INSERT INTO SC VALUES('201515132',9,82);
INSERT INTO SC VALUES('201515133',9,92);

DROP TABLE IF EXISTS Section;
CREATE TABLE Section
(
grade CHAR(10), /成绩分段/
num INT /人数/
);

INSERT INTO Section VALUES('0-20',NULL);
INSERT INTO Section VALUES('21-40',NULL);
INSERT INTO Section VALUES('41-60',NULL);
INSERT INTO Section VALUES('61-80',NULL);
INSERT INTO Section VALUES('81-10',NULL);
	IF (exists (select * from sys.objects where name = 'Sec_statistics'))
    DROP PROCEDURE Sec_statistics
GO
CREATE PROCEDURE Sec_statistics

AS
	DECLARE   /*定义变量*/
	@20Num SMALLINT,
	@40Num SMALLINT,
	@60Num SMALLINT,
	@80Num SMALLINT,
	@100Num SMALLINT,      /*各分数段的人数*/
	@Cno char(4);        /*课程号*/
	
		SELECT @Cno=Cno     /*离散的课程号*/
		FROM Course
		WHERE Cname='离散';
		
		SELECT @20Num=count(*)   /*筛选出相应的信息*/
		FROM SC
		WHERE Cno=@Cno AND Grade<=20 AND Grade>0;
		
		SELECT @40Num=count(*)
		FROM SC
		WHERE Cno=@Cno AND Grade<=40 AND Grade>20;
		
		SELECT @60Num=count(*)
		FROM SC
		WHERE Cno=@Cno AND Grade<=60 AND Grade>40;

		SELECT @80Num=count(*)
		FROM SC
		WHERE Cno=@Cno AND Grade<=80 AND Grade>60;

		SELECT @100Num=count(*)
		FROM SC
		WHERE Cno=@Cno AND Grade<=100 AND Grade>80;

/*将各分段数量填入表中*/
	INSERT INTO Section VALUES('0-20',@20Num);
	INSERT INTO Section VALUES('21-40',@40Num);
	INSERT INTO Section VALUES('41-60',@60Num);
	INSERT INTO Section VALUES('61-80',@80Num);
	INSERT INTO Section VALUES('81-10',@100Num);
  EXEC Sec_statistics
	SELECT *
	FROM Section
	（2）

/*建立平均成绩表*/
DROP TABLE IF EXISTS Avggrade;
CREATE TABLE Avggrade
(
	Cno CHAR(10),
	Avg FLOAT
); 


IF (exists (select * from sys.objects where name = 'Avg'))
    DROP PROCEDURE Avg
GO
CREATE PROCEDURE Avg
AS
	DECLARE 
	@AVG1 FLOAT,
	@AVG2 FLOAT,
	@AVG3 FLOAT,
	@AVG4 FLOAT,
	@AVG5 FLOAT;
	
	SELECT @AVG1=AVG(Grade)
	FROM SC
	WHERE Cno=1;

	SELECT @AVG2=AVG(Grade)
	FROM SC
	WHERE Cno=6;
	
	SELECT @AVG3=AVG(Grade)
	FROM SC
	WHERE Cno=3;
	
	SELECT @AVG4=AVG(Grade)
	FROM SC
	WHERE Cno=4;
	
	SELECT @AVG5=AVG(Grade)
	FROM SC
	WHERE Cno=5;
	
	INSERT INTO Avggrade VALUES(1,@AVG1);
	INSERT INTO  Avggrade VALUES(6,@AVG2);
	INSERT INTO  Avggrade VALUES(3,@AVG3);
	INSERT INTO  Avggrade VALUES(4,@AVG4);
	INSERT INTO  Avggrade VALUES(5,@AVG5);
    EXEC Avg
	SELECT *
	FROM Avggrade;

/添加一列/
ALTER TABLE SC ADD Rank CHAR(2);

IF (exists (select * from sys.objects where name = 'Update'))
    DROP PROCEDURE Update
GO
CREATE PROCEDURE Update
AS
 	UPDATE SC SET Rank='E' WHERE Grade<=20;
 	UPDATE SC SET Rank='D' WHERE Grade>20 AND Grade<=40;
 	UPDATE SC SET Rank='C' WHERE Grade>40 AND Grade<=60;
 	UPDATE SC SET Rank='C' WHERE Grade>60 AND Grade<=80;
 	UPDATE SC SET Rank='A' WHERE Grade>80 AND Grade<=100;

EXEC Up
SELECT *
FROM SC