目录
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的后面添加成绩等级列。
总的来说:相比前面,这章确实有点小难,存储过程还是要多看多做哇