数据库习第五章习题作业

6.假设有下面两个关系模式。

职工（职工号，姓名，年龄，职务，工资，部门号），其中职工号为主码

部门（部门号，名称，经理名，电话），其中部门号为主码。

用SQL语言定义这两个关系模式，要求在模式中完成以下完整性约束条件的定义。

首先，建表。(先检查是否已经存在表，若存在，先删除再重建)

DROP TABLE IF EXISTS Worker
DROP TABLE IF EXISTS Department_1
CREATE TABLE Worker
(Wno CHAR(8) ,
Wname CHAR(6) NOT NULL,
Wage INT CHECK(Wage<=60),
Wjob CHAR(9),
Sallary INT,
Dno CHAR(5),
PRIMARY KEY(Wno),
FOREIGN KEY(Dno)
REFERENCES Department_1(Dno)
);

CREATE TABLE Department_1
(Dno CHAR(5),
Dname CHAR(8),
Manager CHAR(10),
PhoneNumber CHAR(11)
PRIMARY KEY(Dno)
);

（1）定义在每个模式的主码。

在Worker中为

PRIMARY KEY(Wno),

在Department_1中是

 PRIMARY KEY(Dno)

（2）参照完整性。

表Worker的Dno参照表Department_1的Dno

FOREIGN KEY(Dno)
REFERENCES Department_1(Dno)

（3）定义员工年龄不得超过60岁。

Wage INT CHECK(Wage<=60),

第八章 存储过程

2.对学生-课程数据库编写存储过程，完成下述功能。

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

先在Course中查询是否存在离散数学这门课

SELECT Cno,Cname,Ccredit
FROM Course
WHERE(Cname='离散数学');

建立离散数学的成绩表

CREATE TABLE DMgrade
(Grade CHAR(5),--将成绩分为五段
Pnum INT --记录各段的人数
);

将成绩分为五段：
A：[0,60)
B：[60,70)
C：[70,80)
D：[80,90)
E：[90,100]
填入表格，并初始化
后续需要根据SC表的数据更改Pnum

INSERT INTO DMgrade
VALUES('A',0);
INSERT INTO DMgrade
VALUES('B',0);
INSERT INTO DMgrade
VALUES('C',0);
INSERT INTO DMgrade
VALUES('D',0);
INSERT INTO DMgrade
VALUES('E',0);
SELECT *
FROM DMgrade;

然后，定义存储过程，首先要判断是否存在，如果有，删除重建

IF(EXISTS(SELECT * FROM sys.objects WHERE name='Grade'))
DROP PROCEDURE Grade
GO 
--创建存储过程Grade
CREATE PROCEDURE Grade
AS
DECLARE
@A INT,
@B INT,
@C INT,
@D INT,
@E INT,
@Cno CHAR(4);

--在其中定义Cno使其等于Course中的Cno，即存储离散数学的课程号
SELECT @Cno=Cno FROM Course WHERE Cname='离散数学';

--分段，将成绩分为五段，分别更新DMgrade中的Pnum值
SELECT @A=COUNT(*) FROM SC_1 WHERE Grade<60 AND Cno=@Cno;
UPDATE DMgrade SET Pnum=@A WHERE Grade='A';
SELECT @B=COUNT(*) FROM SC_1 WHERE Grade>=60 AND Grade<70 AND  Cno=@Cno;
UPDATE DMgrade SET Pnum=@B WHERE Grade='B';
SELECT @C=COUNT(*) FROM SC_1 WHERE Grade>=70 AND Grade<80  AND Cno=@Cno;
UPDATE DMgrade SET Pnum=@C WHERE Grade='C';
SELECT @D=COUNT(*) FROM SC_1 WHERE Grade>=80 AND Grade<90 AND Cno=@Cno;
UPDATE DMgrade SET Pnum=@D WHERE Grade='D';
SELECT @E=COUNT(*) FROM SC_1 WHERE Grade>=90 AND Grade<=100  AND Cno=@Cno;
UPDATE DMgrade SET Pnum=@E WHERE Grade='E';

运行，并显示结果

EXEC Grade;
SELECT * 
FROM SC_1
WHERE Cno=11;

SELECT *
FROM DMgrade;

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

首先查询同学们选了哪些课，只有选上了的课才会有成绩。

SELECT DISTINCT SC_1.Cno,Course.Cname
FROM SC_1,Course
WHERE SC_1.Cno=Course.Cno;

接下来，建表，为这四门课建立一个平均成绩的表。

CREATE TABLE Average
(Cname CHAR(10),
Aver FLOAT
);
INSERT INTO Average
VALUES('数据库',0);
INSERT INTO Average
VALUES('线性代数',0);
INSERT INTO Average
VALUES('信息系统',0);
INSERT INTO Average
VALUES('离散数学',0);

创建存储过程，定义变量

IF(EXISTS(SELECT * FROM sys.objects WHERE name='AvGrade'))
DROP PROCEDURE AvGrade
GO 
CREATE PROCEDURE AvGrade
AS
DECLARE
@A FLOAT,
@B FLOAT,
@C FLOAT,
@D FLOAT;
--四个变量分别为数据库，线性代数，信息系统，离散数学的平均成绩，后面需要更新

SELECT @A=Avg(Grade) FROM SC_1 WHERE Cno=1;
UPDATE Average SET Aver=@A WHERE Cname='数据库';

SELECT @B=Avg(Grade) FROM SC_1 WHERE Cno=2;
UPDATE Average SET Aver=@B WHERE Cname='线性代数';

SELECT @C=Avg(Grade) FROM SC_1 WHERE Cno=3;
UPDATE Average SET Aver=@C WHERE Cname='信息系统';

SELECT @D=Avg(Grade) FROM SC_1 WHERE Cno=11;
UPDATE Average SET Aver=@D WHERE Cname='离散数学';

出现了一点小差错，无论怎么运行，最后出来的平均成绩均为0
原因是因为数据类型，在SC_1表中Cno定义为int类型，可是查询的时候使用的是Cno=‘1’，自然就查询不出来，当然也就不能计算更改相应的值，好在，最终已解决。

运行并显示结果

EXEC AvGrade
SELECT *
FROM Average;

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

对于这个题，如果新建一个表也是需要学号，选的课程号，所以可以直接在SC表中操作，直接在成绩后面显示等级。

ALTER TABLE SC_1
ADD 
Grade CHAR(5) NULL;
SELECT *
FROM SC_1;

IF(EXISTS(SELECT * FROM sys.objects WHERE name='Level_1'))
DROP PROCEDURE Level_1
GO 
CREATE PROCEDURE Level_1
AS
UPDATE SC_1 SET Grade_1='A' WHERE Grade>=90 AND Grade<=100;--当在90到100之间时，记为A
UPDATE SC_1 SET Grade_1='B' WHERE Grade>=80 AND Grade<90;--[80,90)为B
UPDATE SC_1 SET Grade_1='C' WHERE Grade>=70 AND Grade<80;--[70，80)为C
UPDATE SC_1 SET Grade_1='D' WHERE Grade>=60 AND Grade<70;--[60，70)为D
UPDATE SC_1 SET Grade_1='E' WHERE Grade<60;--[0，60)为E

运行并显示

EXEC Level_1
SELECT *
FROM SC_1;

注：
1.在定义存储过程（或者表）的时候一定要注意看是否已存在，如存在要先删除再重新建立
2.在对数据进行插入或者比较的时候一定要注意数据类型，例如前面8.（2）中的错误
3.8.（2）中，要统计任意一门的平均成绩，前提是课程已经被选，才会有成绩，要先查询符合的课程再建立有这些课程的表
4.对于8.（3），可以直接在SC_1的后面添加成绩等级列。

总的来说：相比前面，这章确实有点小难，存储过程还是要多看多做哇