SQL Server 数据库实验课第八周——第五章作业

第五章 6.假设有下面两个关系模式

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

DROP TABLE IF EXISTS Employee
DROP TABLE IF EXISTS Department
CREATE TABLE Department
(
	ID CHAR(11) PRIMARY KEY,
	Name CHAR(5) UNIQUE NOT NULL,
	Manager CHAR(5) NOT NULL,
	Location SMALLINT,
	PhoneNumber CHAR(11),
)
CREATE TABLE Employee
(
	ID CHAR(11) PRIMARY KEY,
	Name CHAR(5) NOT NULL,
	Age SMALLINT CHECK(Age>0 AND Age<60),
	Salary Money,
	Job CHAR(20),
	DepartmentId CHAR(11) FOREIGN KEY
	REFERENCES Department(ID) ON DELETE CASCADE
)

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

在之前建过的表中插入课程离散数学

INSERT INTO Course VALUES('8','离散数学',NULL,4)

插入选修课数据

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

1.建表Rank

DROP TABLE IF EXISTS Rank;
CREATE TABLE Rank(
	Grade CHAR(20),--成绩划分等级
	num INT--该分数段人数
)
INSERT INTO Rank
VALUES('[0,20)',0),('[20,40)',0),('[40,60)',0),('[60,80)',0),('[80,100]',0);
SELECT * FROM Rank

IF(EXISTS(SELECT * FROM sys.objects WHERE name='Proc_Grade'))
DROP PROCEDURE Proc_Grade
GO 
CREATE PROCEDURE Proc_Grade
AS 
DECLARE 
@less20 INT,
@20to40 INT,
@40to60 INT,
@60to80 INT,
@80to100 INT,
@Cno CHAR(4);


SELECT @Cno=Cno FROM Course WHERE Cname='离散数学';
--统计各个分数段人数
SELECT @less20=COUNT(*) FROM SC WHERE Grade<20 AND Cno=@Cno;--将该分数段的人存储在@less20中
UPDATE Rank SET num=@less20 WHERE Grade='[0,20)';
SELECT @20to40=COUNT(*) FROM SC WHERE Grade<40 AND Grade>=20 AND Cno=@Cno;
UPDATE Rank SET num=@20to40 WHERE Grade='[20,40)';
SELECT @40to60=COUNT(*) FROM SC WHERE Grade<60 AND Grade>=40 AND Cno=@Cno;
UPDATE Rank SET num=@40to60 WHERE Grade='[40,60)';
SELECT @60to80=COUNT(*) FROM SC WHERE Grade<80 AND Grade>=60 AND Cno=@Cno;
UPDATE Rank SET num=@60to80 WHERE Grade='[60,80)';
SELECT @80to100=COUNT(*) FROM SC WHERE Grade<=100 AND Grade>=80 AND Cno=@Cno;
UPDATE Rank SET num=@80to100 WHERE Grade='[80,100]';

创建好存储过程后运行，必须先创建，再EXEC，分成两步。否则存储过程执行不了

EXEC Proc_Grade;
SELECT *FROM SC WHERE Cno=8
SELECT *FROM Rank

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

先看看有分的有多少门

SELECT DISTINCT SC.Cno,Course.Cname
FROM SC,Course
WHERE SC.Cno=Course.Cno

建表

CREATE TABLE AvgGrade(
Cname CHAR(50),--课程名
Avg FLOAT
)
INSERT INTO AvgGrade VALUES('数据库',0),('数学',0),('信息系统',0),('离散数学',0);
SELECT * FROM AvgGrade

创建存储过程

IF(EXISTS(SELECT * FROM sys.objects WHERE name='Proc_Avg'))
DROP PROCEDURE Proc_Avg
GO 
CREATE PROCEDURE Proc_Avg
AS 
DECLARE --定义变量
@数据库 FLOAT,
@数学 FLOAT,
@信息系统 FLOAT,
@离散数学 FLOAT;

SELECT @数据库=Avg(Grade) FROM SC WHERE Cno='1';
UPDATE AvgGrade SET Avg=@数据库 WHERE Cname='数据库';
SELECT @数学=Avg(Grade) FROM SC WHERE Cno='2';
UPDATE AvgGrade SET Avg=@数学 WHERE Cname='数学';
SELECT @信息系统=Avg(Grade) FROM SC WHERE Cno='3';
UPDATE AvgGrade SET Avg=@信息系统 WHERE Cname='信息系统';
SELECT @离散数学=Avg(Grade) FROM SC WHERE Cno='8';
UPDATE AvgGrade SET Avg=@离散数学 WHERE Cname='离散数学'

执行存储过程

 EXEC Proc_Avg
 SELECT * FROM AvgGrade

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

1.给SC表增加属性列GradeLevel用来显示等级

ALTER TABLE SC ADD Level char(5)--增加GradeLevel列
SELECT * FROM SC

创建存储过程

IF(EXISTS(SELECT * FROM sys.objects WHERE name='Proc_Level'))
DROP PROCEDURE Proc_Level
GO 
CREATE PROCEDURE Proc_Level
AS 
UPDATE SC SET Level='A' WHERE Grade>=80 AND Grade<=100;
UPDATE SC SET Level='B' WHERE Grade>=60 AND Grade<80;
UPDATE SC SET Level='C' WHERE Grade>=40 AND Grade<60;
UPDATE SC SET Level='D' WHERE Grade<20 

执行存储过程

 EXEC Proc_Level
 SELECT * FROM SC

心得

啊啊啊啊啊啊啊终于写完了啊啊啊啊啊啊