第五章 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 KEYREFERENCES 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_AvgSELECT * 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_LevelSELECT * FROM SC

心得

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