计算机 2019 级 3-4 班第 1、2、3、9 各章小测验

一、简答题

1.1 数据库系统的三级模式结构和数据库的三级模式结构分别是什么？

模式，内模式，外模式。并简单说明一下。

1.2 三个关系完整性约束分别是什么？ 并进行简要说明。

1. 实体完整性
   主码唯⼀且⾮空
2. 参照完整性
   外码要么为空,要么对应另⼀表的主码
3. ⽤户定义完整性
   你⾃⼰写的要完整,⽐如学号多少,姓名,班级,专业之类

---

二、有学生-课程关系数据库，关系模式如下，且数据库中学生没有重名的情况。

Student(Sno，Sname，Sdept，Sex，Sage)； Course(Cno,Cname,cpno)； SC (Sno,Cno,Grade)；

2.1 用关系代数和 SQL 语言分别写出下列功能

2.1.1查询选修了全部课程的学生的姓名

关系代数：

sql语句：

SELECT Sname FROM student WHERE NOT EXISTS
( SELECT * FROM Course WHERE NOT EXISTS
( SELECT * FROM SC WHERE SC.Cno= Course.Cno  AND SC.Sno=Student.Sno))

sql语句的由来：

参考大佬：查询选修了全部课程的学生姓名

2.2.2查询没有选修课程的学生的姓名

关系代数：

注：‘-’（减号）代表的是集合运算中的差

sql语句：

 SELECT Sname FROM Student  WHERE Sno NOT IN (SELECT Sno FROM SC ，Student where Student.Sno = SC.Sno) 

2.2 用关系代数对下列查询进行代数优化处理，并画出优化前的关系代数语法树，优化后的标准语法树。

SELECT Sno, Sname, Cname, Grade FROM Student, Course, SC WHERE

Student.Sno=SC.Sno AND SC.Cno=Course.Cno AND Student.Sdept=‘CS’AND SC.Grade>80； 

---

三、综合题

3.1 有学生-课程关系数据库，其数据库关系模式如下，且数据库中学生没有重名的情况。

3.1.1 查询物联网系选修了 6 号课程的学生的学号、姓名。

关系代数：

sql语句：

select Sno, Sname from Student ,SC where Student.Sno=SC.Sno and Sdept='物联网' and Cno=6

3.1.2 查询选修了全部课程的学生的学号、姓名。

参考本博客：2.1.1查询选修了全部课程的学生的姓名

3.1.3 查询没有选修课程的学生的学号、姓名。

参考本博客：2.2.2没有选修课程的学生的姓名

3.1.4 查询先修课为 7 号课程的所有课程。

关系代数：

sql语句：

select Cno,Cname from Course where Cpno=7

3.1.5 查询选修所有课程并且平均成绩在 90 分以上的学生学号和姓名。

SELECT Sname FROM student WHERE NOT EXISTS
( SELECT * FROM Course WHERE NOT EXISTS
( SELECT * FROM SC WHERE SC.Cno= Course.Cno  AND SC.Sno=Student.Sno))//查询选修所有课程
group by Sno having avg(Grade) > 90 //平均成绩在 90 分以上

参考本博客：2.1.1查询选修了全部课程的学生的姓名

3.1.6 删除姓名为“王宝宝”的学生及其所有选课记录。

//删除姓名为“王宝宝”的学生其所有选课记录。
 DELETE FROM SC WHERE  Sno in (SELECT Sno FROM Student where Student.Sname = ‘王宝宝’)
 
//删除姓名为“王宝宝”的学生
 DELETE FROM Student WHERE Student.Sname = ‘王宝宝’

3.1.7 将所有男性学生中,年龄在 18 到 20 之间的学生的年龄加 1 岁。

UPDATE Student SET Sage = Sage+1 WHERE Sex = ‘男’ AND Sage BETWEEN 18 AND 20 

3.1.8 向学生选课表 SC 中插入一个元组，内容自己定。

insert into Student values (201701130124,'宋志豪','cs','男',22)   //引号有没有，看题目给属性的类型是什么

3.1.9 将姓名为“李美丽”的学生由原来的“软件系”更改为“网络系”。

UPDATE Student SET Sdept = ‘网络系’  WHRER  Sname = ‘李美丽’  and sdept = '软件系'

3.1.10 建一个视图，包含两个属性： 计算机系中，姓名里包括“赵”字的学生的学号及相 应学生的成绩。

CREATE  VIEW  S_Student (SStudent, GGrade) AS
   SELECT Sno, Grade FROM Student, SC 
   WHERE Student.Sname LIKE '赵%' AND Student.Sdept = ‘计算机系’ AND Student.Sno=SC.Sno

3.2.现有一个图书数据库，包括学生表、图书表和图书借阅表，其关系模式定义如下

用 SQL 完成操作： 函数 DATEDIFF (date1,date2)可以返回 date1 和 date2 之间的天数。

3.2.1 查询书名包含“需求工程”， 并且作者是王宝宝的所有图书信息。

SELECT * FROM 图书 WHERE 书名 LIKE '%需求工程%' AND 作者 = ‘王宝宝’；

3.2.2 查询李雪花借阅的科学出版社的所有图书，查询结果包括： 书号，书名，作者，单价。

SELECT书号，书名，作者，单价 FROM 学生，图书，图书借阅
WHERE 学生.学号 = 图书借阅.学号 AND 图书.书号 = 图书借阅.书号
	AND 姓名 =‘李雪花’ AND 出版社=‘科学出版社’；

3.2.3 学号为 20180301 的学生在 2018 年 11 月 1 日借出了书号为 22 的图书，假设图书借阅 期限为 30 天，将该信息记录到图书借阅表中。

INSERT INTO 图书借阅（学号，书号，借出日期，应还日期） VALUES(22，'2018030'，'2018-11-1'，'2018-11-31')；

3.2.4 学号为 20180302 的学生在 2019 年 12 月 1 日归还了书号为 5 的图书，将该信息增加到 图书借阅表中。

UPDATE 图书借阅 SET 归还日期=‘2019-12-1’WHERE 学号=‘20180302’ AND 书号=‘5’；

3.2.5 到今天为止（2021-10-18），查询到 2020 年 7 月 1 日到 2020 年 9 月 1 日之间，所有逾期未还的图书，查询结果包 括： 学号、姓名、书名、借出日期、应还日期、超期天数。

sql语句：

SELECT 学号，姓名，书名，借出日期，应还日期，DATEDIFF（‘2021-10-18’,应还日期） AS 超期天数 
FROM  学生，图书，图书借阅	
WHERE 学生.学号 = 图书借阅.学号 AND 图书.书号 = 图书借阅.书号 
AND  归还日期 IS NULL 
AND 应还日期 < ‘2020-9-1’ AND 应还日期 > ‘2020-7-1’ ；