数据库第五章作业

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

//部门表
CREATE TABLE DEPT
(Deptno NUMERIC(2),
 Deptname VARCHAR(10),
 Manager VARCHAR(10),
 PhoneNumber CHAR(12)
 CONSTRAINT PK_SC PRIMARY KEY(Deptno)
);

//职工表
CREATE TABLE EMP
(Empno INT,
 ENAME VARCHAR(10),
 Age SMALLINT,
 CONSTRAINT C1 CHECK(Age<60),
 Job VARCHAR(9),
 Sal NUMERIC(7,2),
 Deptno NUMERIC(2),
 CONSTRAINT FK_DEPTNO FOREIGN KEY(Deptno) REFERENCES DEPT(Deptno)
);

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

-- 添加课程：离散数学
INSERT
INTO Course
VALUES ('8', '离散数学', NULL, 4);
SELECT * FROM Course;

-- 给SC表添加数据
INSERT
INTO SC
VALUES ('201215121', '8', 80), ('201215122', '8', 85), ('201215124', '8', 96),
	   ('201215129', '8', 72), ('201215130', '8', 60), ('201215131', '8', 59);
SELECT * FROM SC;

为了方便查看，我把之前SC表里的数据清空了(更新SC表是注意外键约束)

① 先建立一张表Amount来记录各个分数段及其人数：

 -- 新建表Amount
CREATE TABLE Amount (
	ScoreLevel CHAR(15),
	Number INT
)
-- 插入数据
INSERT
INTO Amount
VALUES ('小于60', 0), ('[60,70)', 0), ('[70,80)', 0),
       ('[80,90)', 0), ('大于等于90', 0);     
SELECT * FROM Amount;

② 创建存储过程

IF(exists(select * from sys.objects where name='Proc_SCORELEVEL'))
	DROP PROCEDURE Proc_SCORELEVEL;
GO
-- 创建存储过程Proc_SCORELEVEL
CREATE PROCEDURE Proc_SCORELEVEL
AS
BEGIN
    --定义变量
	DECLARE @Cno CHAR(4), --需要将Course表中的Cno赋值过来查询的时候用
			@under60 INT, --低于60分
			@f60t70 INT,  --大于等于60小于70
			@f70t80 INT,
			@f80t90 INT,
			@beyond90    INT; --90及其以上
    -- 给@Cno赋值	
	SELECT @Cno = Cno FROM Course WHERE Cname = '离散数学';

	-- 分数小于60的
	SELECT @under60 = COUNT (*) FROM SC WHERE Grade < 60 AND Cno = @Cno;
	UPDATE Amount SET Number = @under60 WHERE ScoreLevel ='小于60';

	-- 大于等于60小于70
	SELECT @f60t70 = COUNT (*) FROM SC WHERE Grade >= 60 AND Grade < 70 AND Cno = @Cno;
	UPDATE Amount SET Number = @f60t70 WHERE ScoreLevel = '[60,70)';

	-- 大于等于70小于80
	SELECT @f70t80 = COUNT (*) FROM SC WHERE Grade >= 70 AND Grade < 80 AND Cno = @Cno;
	UPDATE Amount SET Number = @f70t80 WHERE ScoreLevel = '[70,80)';

	/*大于等于80小于90*/
	SELECT @f80t90 = COUNT (*) FROM SC WHERE Grade >= 80 AND Grade < 90 AND Cno = @Cno;
	UPDATE Amount SET Number = @f80t90 WHERE ScoreLevel = '[80,90)';

	/*大于等90*/
	SELECT @beyond90 = COUNT (*) FROM SC WHERE Grade >= 90 AND Cno = @Cno;
	UPDATE Amount SET Number = @beyond90 WHERE ScoreLevel = '大于等于90';
END;
EXEC Proc_SCORELEVEL;  -- 执行刚创建好的存储过程
SELECT * FROM Amount;

 

2）统计任意一门课的平均成绩：
① 先建立一张表AvgSC来记录任意一门课的平均成绩：

-- 新建表
CREATE TABLE AvgSC (
	Cname CHAR(10),  -- 课程名
	AvgScore FLOAT  --平均分
)
-- 插入数据
INSERT
INTO AvgSC
VALUES ('数据库', 0), ('数据结构', 0), ('离散数学', 0);
SELECT * FROM AvgSC;

② 创建存储过程:

IF(exists(select * from sys.objects where name='Proc_AVGSC'))
	DROP PROCEDURE Proc_AVGSC;
GO
-- 创建存储过程
CREATE PROCEDURE Proc_AVGSC
AS
BEGIN
	-- 定义变量
	DECLARE @Avg1 FLOAT,
			@Avg2 FLOAT,
			@Avg3 FLOAT;
			
	SELECT @Avg1 = AVG(Grade) FROM SC WHERE Cno = '1';  -- SC表中数据库的课程号是1
	UPDATE AvgSC SET AvgScore = @Avg1 WHERE Cname = '数据库';

	SELECT @Avg2 = AVG(Grade) FROM SC WHERE Cno = '5';  -- SC表中数据结构的课程号是5
	UPDATE AvgSC SET AvgScore = @Avg2 WHERE Cname = '数据结构';

	SELECT @Avg3 = AVG(Grade) FROM SC WHERE Cno = '8';  -- SC表中离散数学的课程号是8
	UPDATE AvgSC SET AvgScore = @Avg3 WHERE Cname = '离散数学';
END;

EXEC Proc_AVGSC;

SELECT * FROM AvgSC;

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

-- 给SC表为增加一个属性列ScoreLevel*/
ALTER TABLE SC ADD ScoreLevel CHAR(4);

IF(exists(select * from sys.objects where name='Proc_CHANGE'))
	DROP PROCEDURE Proc_CHANGE;
GO
-- 新建存储过程
CREATE PROCEDURE Proc_CHANGE
AS
BEGIN
	
	UPDATE SC SET ScoreLevel = 'E' WHERE Grade < 60;
	UPDATE SC SET ScoreLevel = 'D' WHERE Grade >= 60 AND Grade < 70;
	UPDATE SC SET ScoreLevel = 'C' WHERE Grade >= 70 AND Grade < 80;
	UPDATE SC SET ScoreLevel = 'B' WHERE Grade >= 80 AND Grade < 90;
	UPDATE SC SET ScoreLevel = 'A' WHERE Grade >= 90;
END;

EXEC Proc_CHANGE;

SELECT * FROM SC;

这里需要注意当按照分数段划分等级时用 比较运算符> < = 来划分范围，如果用BETWEEN和AND的话因为BETWEEN和AND是包括边界的，所以会造成结果错误。