数据库原理（第五章作业）-一一网

【摘要】 6.假设有下面两个关系式：职工（职工号，姓名，年龄，职务，工资，部门号），其中职工号为主码部门（部门号，名称，经理名，电话），其中部门号为主码。用SQL语言定义这两个关系模式，要求在模式中完成以下完整性约束条件的定义：（1）定义每个模式的主码（primarykey）（2）定义参照完整性（3）定义职工年龄不得超过60岁
CREATE TABLE 部门
(部门…

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

CREATE TABLE 部门
(部门号 char(9) primary key ,
名称 char(9),
经理名 char(9),
电话 char(11));
CREATE TABLE 职工
(职员号 char(9) primary key,
姓名   char(9) ,
年龄   smallint check (年龄<=60),
职务   char(9),
工资   smallint,
部门号 char (9),
foreign key(部门号) references 部门(部门号));

  
 
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13

第二题：
先建SC表如下：

Create Table SC(
Sno Char(12),
Cno Char(12),
Cname Char(12),
Sname Char(12),
Ssex char(2) check(Ssex in('男','女')),
Primary Key(Sno, Cno),
Grade SmallInt Check(Grade >= 0 And Grade <= 100)
);
Insert Into SC Values('20191102001', '1', '离散数学', '张三', '男', 91);
Insert Into SC Values('20191102001', '2', '高等数学','张三', '男', 93);
Insert Into SC Values('20191102001', '3', '线性代数','张三', '男', 90);
Insert Into SC Values('20191102001', '4', '运筹学','张三', '男', 78);
Insert Into SC Values('20191102002', '1', '离散数学', '李四', '女', 51);
Insert Into SC Values('20191102002', '2', '高等数学','李四', '女', 76);
Insert Into SC Values('20191102002', '3', '线性代数','李四', '女', 66);
Insert Into SC Values('20191102002', '4', '运筹学','李四', '女', 99);
Insert Into SC Values('20191102003', '1', '离散数学', '王五', '男', 80);
Insert Into SC Values('20191102003', '2', '高等数学','王五', '男', 87);
Insert Into SC Values('20191102003', '3', '线性代数','王五', '男', 84);
Insert Into SC Values('20191102003', '4', '运筹学','王五', '男', 78);
Insert Into SC Values('20191102004', '1', '离散数学', '赵六', '女', 75);
Insert Into SC Values('20191102004', '2', '高等数学','赵六', '女', 74);
Insert Into SC Values('20191102004', '3', '线性代数','赵六', '女', 73);
Insert Into SC Values('20191102004', '4', '运筹学','赵六', '女', 77);

  
 
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25

在这里插入图片描述

对学生-课程数据库编写存储过程,完成下述功能:
(1)统计离散数学的成绩分布情况,即按照各分数段统计人数。

Select
COUNT(case when Grade between 90 and 100 And Cname = '离散数学' then 1 end) as 优秀,
COUNT(case when Grade between 80 and 89 And Cname = '离散数学' then 1 end) as 良好,
COUNT(case when Grade between 60 and 79 And Cname = '离散数学' then 1 end) as 及格,
COUNT(case when Grade < 60 And Cname = '离散数学' then 1 end)  as 不及格
from SC

  
 
  1
  2
  3
  4
  5
  6

在这里插入图片描述

(2)统计任意一门课的平均成绩。

Select 
AVG(Case When Cname = '离散数学' Then Grade End) As 离散数学平均分,
AVG(Case When Cname = '高等数学' Then Grade End) As 高等数学平均分,
AVG(Case When Cname = '线性代数' Then Grade End) As 线性代数平均分,
AVG(Case When Cname = '运筹学' Then Grade End) As 运筹学平均分
From SC

  
 
  1
  2
  3
  4
  5
  6

在这里插入图片描述

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

Select 
Sno,Cno,Cname,Sname,Ssex,
Case When Grade Between 90 And 100 Then 'A'
When Grade Between 80 And 89 Then 'B'
When Grade Between 70 And 79 Then 'C'
When Grade Between 60 And 69 Then 'D'
When Grade Between 0 And 59 Then 'E'
End As 等级
From SC

  
 
  1
  2
  3
  4
  5
  6
  7
  8
  9