本⽂是《数据库系统概论》的第五章（数据库完整性）的部分课后习题和存储过程习题，题⽬来源：
共有两题
数据库完整性习题6
假设有下⾯两个关系模式:
职⼯(职⼯号，姓名，年龄，职务，⼯资，部门号)，其中职⼯号为主码:
部门(部门号，名称，经理名，电话)，其中部门号为主码。
中国梦之声杨成瑞⽤SQL语⾔定义这两个关系模式，要求在模式中完成以下完整性约束条件的定义：
①定义每个模式的主码；②定义参照完整性；③定义职⼯年龄不得超过60岁。
（ps：因为之前我就建⽴过COMPANY数据库，⾥⾯含有这两张表，不过关系模式不⼀样，所以我先将原有的两张表删除）-- 切换⾄COMPANY数据库，并删除原来的两张表
USE COMPANY;
我是一只小鸟作文
DROP TABLE Staff;/*这⾥需要注意要先删除Staff表，因为它引⽤了Dept表的主码作为外码约束，必须先把它删了才删得了Dept表*/
DROP TABLE Dept;
节假日高速-- 按该题要求新建Dept和Staff表，分别对应部门和职⼯两个关系模式
CREATE TABLE Dept (
DNO VARCHAR(10)PRIMARY KEY,
DNAME VARCHAR(10)UNIQUE,/*给部门名设置UNIQUE，隐含着NOT NULL*/
MNAME VARCHAR(10)NOT NULL,/*经理名*/
PHONE INT NOT NULL
)
CREATE TABLE Staff (
SNO VARCHAR(10)PRIMARY KEY,
SNAME VARCHAR(10)NOT NULL,
AGE INT CHECK(AGE <=60AND AGE >0)NOT NULL,
DUTY VARCHAR(8)NOT NULL,/*职务*/
SALARY INT NOT NULL,
DNO VARCHAR(10)REFERENCES Dept(DNO)
)
因为职⼯表Staff ⾥的部门号DNO引⽤的是部门表Dept的主码DNO，所以得先建⽴Dept表再建⽴Staff表~
如下图，新建成功！
值得注意的是：给部门名DNAME设置的UNIQUE键其实某种意义上隐含着不能为空，因为如果出现多个空值的话，他们就是相同的，
即违反了UNIQUE键，如下图：
存储过程习题2
对学⽣-课程数据库编写存储过程，完成下述功能：
1）统计离散数学的成绩分布情况，即按照各分数段统计⼈数：
（ps：学⽣-课程数据库涉及Student、Course、SC三张表，之前在写操作基础篇的时候⽤到很多次啦，因为⽬前课程表Course中没有离散数学这个科⽬，所以我先添加课程：离散数学且同时给SC表添加相应数据）
-- 添加课程：离散数学
INSERT
INTO Course
VALUES('8','离散数学',NULL,4);
SELECT*FROM Course;
（ps：Cpno是先修课的课程号，即学了先修课才能学该课程，我这⾥就不设置先修课啦，Ccredit是该课程的学分）
-- 给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表⾥的数据清空了~
— — — — — — — — — — — — — — — — — — — — — — 好了，现在开始按要求完成该题
① 先建⽴⼀张表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赋值过来查询的时候⽤
@under60INT,--低于60分
@f60t70INT,--⼤于等于60⼩于70
@f70t80INT,
@f80t90INT,
@beyond90INT;--90及其以上
-- 给@Cno赋值
SELECT@Cno= Cno FROM Course WHERE Cname ='离散数学';
-- 分数⼩于60的
SELECT@under60=COUNT(*)FROM SC WHERE Grade <60AND Cno =@Cno;
UPDATE Amount SET Number =@under60WHERE ScoreLevel ='⼩于60';
-
- ⼤于等于60⼩于70
SELECT@f60t70=COUNT(*)FROM SC WHERE Grade >=60AND Grade <70AND Cno =@Cno; UPDATE Amount SET Number =@f60t70WHERE ScoreLevel ='[60,70)';
-- ⼤于等于70⼩于80
SELECT@f70t80=COUNT(*)FROM SC WHERE Grade >=70AND Grade <80AND Cno =@Cno; UPDATE Amount SET Number =@f70t80WHERE ScoreLevel ='[70,80)';
/*⼤于等于80⼩于90*/
SELECT@f80t90=COUNT(*)FROM SC WHERE Grade >=80AND Grade <90AND Cno =@Cno; UPDATE Amount SET Number =@f80t90WHERE ScoreLevel ='[80,90)';
/*⼤于等90*/
SELECT@beyond90=COUNT(*)FROM SC WHERE Grade >=90AND Cno =@Cno;
UPDATE Amount SET Number =@beyond90WHERE ScoreLevel ='⼤于等于90';
END;
EXEC Proc_SCORELEVEL;-- 执⾏刚创建好的存储过程
pc肌肉练习图解SELECT*FROM Amount;
来看结果如何：
执⾏存储过程前：
执⾏后：
对⽐刚刚的Amount表和数据可以看到，该存储过程创建和执⾏都稳了！
2）统计任意⼀门课的平均成绩：
① 先建⽴⼀张表AvgSC来记录任意⼀门课的平均成绩：
怎么用-- 新建表
CREATE TABLE AvgSC (
Cname CHAR(10),-- 课程名
AvgScore FLOAT--平均分
)
-- 插⼊数据
INSERT
INTO AvgSC
VALUES('数据库',0),('数据结构',0),('离散数学',0);
SELECT*FROM AvgSC;
② 创建存储过程：
发布评论